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 global innodb_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 严格检查模式,此参数只是可以防止问题发生而已,不对性能有任何影响。
转载于:https://blog.51cto.com/raytech/1701814