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.csdn.net/kadwf123/article/details/82081815

query_cache_type= 0

query_cache_size=0

结果集缓存，一般设置为0.

详细参见：

https://blog.csdn.net/kadwf123/article/details/82081902

max_used_connections

最大连接数：

详细参见：

https://blog.csdn.net/kadwf123/article/details/82082836

 

interactive_timeout

#交互连接超时

wait_timeout

#非交互式连接等待超时

详细参加：

https://blog.csdn.net/kadwf123/article/details/82082913

 

innodb_io_capacity=20000

单位时间innodb后台刷缓存到磁盘的page页数

详细参见：

https://blog.csdn.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.csdn.net/kadwf123/article/details/82083166

 

innodb_log_file_size

binlog日志文件的大小：

经验值设置：如果是sas盘，设置1-2g，如果是ssd盘，设置4g。

如果过小，就会导致频繁的切换日志，在切换日志的时候，就会产生checkpoint，需要将数据缓存中的数据刷到数据文件中，如果发现检查点过于频繁，则有可能是这方面的问题。

详细参见：

https://blog.csdn.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.csdn.net/kadwf123/article/details/82633947

innodb_max_dirty_pages_pct

这个百分比是，最大脏页的百分数，当系统中 脏页 所占百分比超过这个值，INNODB就会进行写操作以把页中的已更新数据写入到磁盘文件中。

详细参见：

https://blog.csdn.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.csdn.net/kadwf123/article/details/82634135

transaction_isolation

隔离级别，最好设置为读提交RC，默认是可重复读RR。