Mysql5.5 InnoDB存儲引擎配置和優(yōu)化

WindySky 2016-04-13

展開全文

環(huán)境為CentOS系統(tǒng)，1G內存，Mysql5.5.30。
在/etc/my.cnf內添加：

skip-external-locking
skip-name-resolve
max_connections = 1024
query_cache_size = 16M
sort_buffer_size = 1M
table_cache = 256
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 4M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 2M
innodb_flush_log_at_trx_commit = 1

修改前先將mysql服務停止：
service mysqld restart
由于修改了日志大小重啟會報錯：
130319 04:45:14 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
130319 05:02:43 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130319 5:02:54 [Note] Plugin 'FEDERATED' is disabled.
130319 5:02:59 InnoDB: The InnoDB memory heap is disabled
130319 5:02:59 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
130319 5:02:59 InnoDB: Compressed tables use zlib 1.2.3
130319 5:02:59 InnoDB: Using Linux native AIO
130319 5:03:01 InnoDB: Initializing buffer pool, size = 128.0M
130319 5:03:02 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 134217728 bytes!
130319 5:03:02 [ERROR] Plugin 'InnoDB' init function returned error.
130319 5:03:02 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130319 5:03:02 [ERROR] Aborting
將/var/lib/mysql下原先的ib_logfile0、ib_logfile1刪除再重啟mysql。
service mysqld start
正常啟動：
130319 05:20:59 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
130319 05:22:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130319 5:22:56 [Note] Plugin 'FEDERATED' is disabled.
130319 5:22:57 InnoDB: The InnoDB memory heap is disabled
130319 5:22:57 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
130319 5:22:57 InnoDB: Compressed tables use zlib 1.2.3
130319 5:22:57 InnoDB: Using Linux native AIO
130319 5:22:57 InnoDB: Initializing buffer pool, size = 128.0M
130319 5:22:57 InnoDB: Completed initialization of buffer pool
130319 5:22:57 InnoDB: highest supported file format is Barracuda.
130319 5:22:58 InnoDB: Waiting for the background threads to start
130319 5:22:59 InnoDB: 5.5.30 started; log sequence number 2621452
130319 5:22:59 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130319 5:22:59 [Note] - '0.0.0.0' resolves to '0.0.0.0';
130319 5:22:59 [Note] Server socket created on IP: '0.0.0.0'.
130319 5:23:00 [Warning] 'proxies_priv' entry '@ root@server08' ignored in --skip-name-resolve mode.
130319 5:23:04 [Note] Event Scheduler: Loaded 0 events
130319 5:23:04 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.30' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi
使用mysqladmin variables命令可查看所有參數(shù)變量。

InnoDB引擎配置優(yōu)化
innodb_buffer_pool_size＝1G //一個Innodb最重要的參數(shù)，這個參數(shù)和MyISAM的key_buffer_size有相似之處，但也是有差別的。這個參數(shù)主要緩存innodb表的索引，數(shù)據(jù)，插入數(shù)據(jù)時的緩沖，設置得越大，存取表里面數(shù)據(jù)時所需要的磁盤I/O越少，一般是內存的一半，不超過2G，否則系統(tǒng)會崩潰。為Innodb加速優(yōu)化首要參數(shù)。該參數(shù)分配內存的原則：這個參數(shù)默認分配只有8M，可以說是非常小的一個值。如果是一個專用ＤＢ服務器，那么他可以占到內存的70%-80%。這個參數(shù)不能動態(tài)更改，所以分配需多考慮。分配過大，會使Swap占用過多，致使Mysql的查詢特慢。如果你的數(shù)據(jù)比較小，那么可分配是你的數(shù)據(jù)大小＋１０％左右做為這個參數(shù)的值。
例如：數(shù)據(jù)大小為50Ｍ,那么給這個值分配innodb_buffer_pool_size＝64Ｍ
innodb_additional_mem_pool_size=16M //用來存放Innodb的內部目錄這個值不用分配太大，系統(tǒng)可以自動調。不用設置太高。通常比較大數(shù)據(jù)設置16Ｍ夠用了，如果表比較多，可以適當?shù)脑龃?。如果這個值自動增加，會在error log有中顯示的。
innodb_log_file_size=256M //在日志組中每個日志文件的大小，一般是innodb_buffer_pool_size的25%，官方推薦是innodb_buffer_pool_size的40-50%。一般控制在幾個ＬＯＧ文件相加大小在２Ｇ以內為佳。具體情況還需要看你的事務大小，數(shù)據(jù)大小為依據(jù)。說明：這個值分配的大小和數(shù)據(jù)庫的寫入速度，事務大小，異常重啟后的恢復有很大的關系。
innodb_log_files_in_group=2 //指定你有幾個日志組。分配原則：一般我們可以用２－３個日值組。默認為兩個。
innodb_log_buffer_size＝3M //事務在內存中的緩沖。分配原則：控制在2-8M.這個值不用太多的。他里面的內存一般一秒鐘寫到磁盤一次。具體寫入方式和你的事務提交方式有關。在oracle等數(shù)據(jù)庫了解這個，一般最大指定為３Ｍ比較合適。
innodb_flush_logs_at_trx_commit＝0 //控制事務的提交方式分配原則：這個參數(shù)只有３個值，０，１，２請確認一下自已能接受的級別。默認為１，主庫請不要更改了。性能更高的可以設置為０或是２，但會丟失一秒鐘的事務。說明：這個參數(shù)的設置對Ｉｎｎｏｄｂ的性能有很大的影響，所以在這里給多說明一下。當這個值為1時：innodb 的事務LOG在每次提交后寫入日值文件，并對日值做刷新到磁盤。這個可以做到不丟任何一個事務。當這個值為2時：在每個提交，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新,在對日志文件的刷新在值為2的情況也每秒發(fā)生一次。但需要注意的是，由于進程調用方面的問題，并不能保證每秒１００％的發(fā)生。從而在性能上是最快的。但操作系統(tǒng)崩潰或掉電才會刪除最后一秒的事務。當這個值為0時：日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新，但是在一個事務提交不做任何操作。mysqld進程的崩潰會刪除崩潰前最后一秒的事務。