MySQL 5.6 innodb存储引擎参数接受

7iuyk

innodb_read_io_threads=8
innodb_write_io_threads=8

此参数可以在linux平台上可以根据CPU核数进一步更改来提高性能，读操作比较多可以提高read_io_threads数量。可以通过show engine innodb status\G 在FILE I/O段查看到，如下：
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)



提高IO处理能力：
innodb_io_capacity=200

该参数表示一次过将多少个脏数据写进硬盘，普通单个SATA可以设置200，对于SSD可以设置3000，而拥有5个磁盘组成的RAID5可以设置成2000.
增加自适应刷新脏页功能：
innodb_adaptive_flushing
由于该参数默认为开启状态，所以不需要在my.cnf里面设置。
mysql在以下三种情况下将脏页刷磁盘：

• 当超过innodb_max_dirty_pages_pct设定值
  
  
• 重做日志ib_logfile文件写满
  
• 机器空闲时
  
  

关于innodb_max_dirty_pages_pct该参数建议在75-80之间即可，因为如果设置过大内存也很大或者服务器压力很大（数据库崩溃时候恢复时间增加，可以提高性能），那么效率很降低，如果设置的值过小，那么硬盘的压力会增加（崩溃恢复时间减少，不过性能降低）。

可以通过show engine innodb status\G  查看

Buffer pool size        65532
Buffer pool size, bytes 1073676288

Buffer pool size × 16 ×1024 等于Buffer pool size, bytes,（简称buffersize）然后将Modified db pages 的数乘16再乘1024(简称modiffiedsize), 将modiffiedsize / buffersize * 100 ，查出此数是否大于20%，然后通过减少该参数值来降低Modified db pages（这个是脏页数量），动态设置：
set globalinnodb_max_dirty_pages_pct=5;
(不过个人认为会受到innodb_adaptive_flushing参数的影响）

innodb_buffer_pool_instance 参数可以增加buffer pool的数量，不过应该在innodb_buffer_pool_size大于1G时候才使用。在percona里面innodb_buffer_pool_instances默认应该是8这个与社区里的mysql有点不同。（建议根据CPU核数2倍来调整）
innodb 的自适应hash索引，可以通过skip_innodb_adaptive_hash_index来进行关闭，不过如果单表大小小于内存大小的时候不建议关闭

利用TCMalloc提升内存分配性能
下载google-perftools 1.9编译并安装：
./configure --enable-frame-pointers

make && make install
修改/usr/local/mysql/bin/mysqld_safe文件，在第13行增加下面一行：
export LD_PRELOAD=/usr/local/lib/libtcmalloc.so

重启mysql
innodb_purge_threads 默认为1 ，独立的清除线程

innodb_change_buffering用默认all即可

innodb_stats_on_metadata
Innodb更新元数据统计功能，建议关闭，其实不关闭也不会有太大影响。
innodb_stats_on_metadata=off
percona默认关闭。

innodb_strict_mode 严格检查模式，此参数只是可以防止问题发生而已，不对性能有任何影响。