mysql实例优化
实例优化相关参数:
参数配置:
innodb_buffer_pool_size
#相当于oracle的sga,共享内存大小,一般设置为物理内存的60-80%。总的io处理能力上限。
#比如,总内存128G,单实例下可设置为80g,两个实例则设置为40g
innodb_thread_concurrency
#cpu的线程数,2物理cpu,一颗是12core,每个core是2个线程,那该情况下总共48线程,这个值可以设置为接近48的值,预留一部分给操作系统或其它进程。等于0由innodb内部进行控制。
详细说明参见:
https://blog.****.net/kadwf123/article/details/82081815
query_cache_type= 0
query_cache_size=0
结果集缓存,一般设置为0.
详细参见:
https://blog.****.net/kadwf123/article/details/82081902
max_used_connections
最大连接数:
详细参见:
https://blog.****.net/kadwf123/article/details/82082836
interactive_timeout
#交互连接超时
wait_timeout
#非交互式连接等待超时
详细参加:
https://blog.****.net/kadwf123/article/details/82082913
innodb_io_capacity=20000
单位时间innodb后台刷缓存到磁盘的page页数
详细参见:
https://blog.****.net/kadwf123/article/details/82083020
innodb_flush_log_at_trx_commit
值:
0,每秒刷一次log_buffer中的数据到磁盘日志中,不受commit提交语句的控制。
1,每次提交,log_buffer缓存日志都写入磁盘日志文件。
2,每个事物提交,写log_buffer,但是不写磁盘日志文件。
sync_binlog
日志提交次数:
值:
1,每一个提交,日志都刷到磁盘日志文件中
0,默认值,mysql并不关心日志缓存是否刷到磁盘日志文件中,而是由操作系统决定什么时候刷进去,此时性能最好,但是最不安全。
其它非0值,是多少个提交事务语句,然后才刷新磁盘日志文件。
详细参加:
https://blog.****.net/kadwf123/article/details/82083166
innodb_log_file_size
binlog日志文件的大小:
经验值设置:如果是sas盘,设置1-2g,如果是ssd盘,设置4g。
如果过小,就会导致频繁的切换日志,在切换日志的时候,就会产生checkpoint,需要将数据缓存中的数据刷到数据文件中,如果发现检查点过于频繁,则有可能是这方面的问题。
详细参见:
https://blog.****.net/kadwf123/article/details/82083247
innodb_log_files_in_group
innodb日志文件个数:
5.6.39默认是4个
mysql> show variables like '%innodb_log_files_in_group%' ;
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 4 |
+---------------------------+-------+
innodb_flush_method
innodb日志缓存和数据缓存刷新到磁盘的方式:
三个值:
fdatasync、O_DSYNC、O_DIRECT
详细参见:
https://blog.****.net/kadwf123/article/details/82633947
innodb_max_dirty_pages_pct
这个百分比是,最大脏页的百分数,当系统中 脏页 所占百分比超过这个值,INNODB就会进行写操作以把页中的已更新数据写入到磁盘文件中。
详细参见:
https://blog.****.net/kadwf123/article/details/82634077
Innodb_flush_neighbors
当刷新一个脏页时,innodb存储引擎会检测该页所在区(extent)的所有页,如果是脏页,那么一起进行刷新。这样做的好处显而易见,通过AIO可以将多个IO写入操作合并为一个IO操作,增大写入量,减少了物理写IO,故该工作机制在传统机械磁盘下有着显著的优势。
1、在写入次数基本不增加的情况下,增加了写入的量;
2、加速了脏页的回收;
3、充分利用double write每次1M写入的特征;
4、这个功能打开以后会发现iostat里面的wrqm(合并写)这个值会比较高;
2、问题考虑
1、是不是可能将不怎么脏的页进行了写入,而该页之后又会很快变成脏页?
2、固态硬盘有着较高的 IOPS,是否还需要这个特性?
为此,InnoDB 存储引擎从 1.2.x 版本开始提供了参数 innodb_flush_neighbors,用来控制是否启用该特性。对于传统机械硬盘建议启用该特性,而对于固态硬盘有着超高 IOPS 性能的磁盘,则建议将该参数设置为 0、即关闭此特性。
详细参加:
https://blog.****.net/kadwf123/article/details/82634135
transaction_isolation
隔离级别,最好设置为读提交RC,默认是可重复读RR。