参考资料：

《Mysql配置文件/etc/my.cnf解析》

《MySQL InnoDB Buffer Pool》

相关文章：

《性能优化：MySQL使用优化（1）》

《性能优化：MySQL使用优化（2）》

《MySQL：更新过程（buffer pool与redo、bin、undo log）》

《MySQL：基础架构与存储引擎》

        写在开头：本文为学习后的总结，可能有不到位的地方，错误的地方，欢迎各位指正。

前言

        在之前的文章中，我们介绍了就基于sql的调整的优化，这篇文章我们将会结合之前的文章解释下如何基于mysql的配置进行优化。

       深入理解本文需要对MySQL的内部结构（三大日志体系：undo log、redo log、bin log，buffer pool）有基本的了解，如果不熟悉的朋友可以先从我之前的文章开始看起（《MySQL：基础架构与存储引擎》《MySQL：更新过程（buffer pool与redo、bin、undo log）》）。

目录

前言

一、连接配置

        1、连接数配置

        2、超时配置

二、数据库数据交换配置

        1、交互配置

        2、缓存配置

三、日志配置

        1、慢查询

        2、bin log

四、InnoDB 引擎配置

一、连接配置

        1、连接数配置

        （1）max_connections

        最大连接数，可设最大值 16384，一般考虑根据同时在线人数设置一个比较综合的数字，鉴于该数值增大并不太消耗系统资源，建议直接设 10000。

        如果在访问时经常出现 Too Many Connections 的错误提示，则需要增大该参数值。

        （2）max_connect_errors

        默认值 100，最大错误连接数，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。如要考虑高并发场景下的容错，可以进行加大。

        （3）open_files_limit 

        MySQL 打开的文件描述符限制，默认最小 1024;

• 当 open_files_limit 没有被配置的时候，比较 max_connections*5 和 ulimit -n（系统能打开的最大句柄数） 的值，哪个大用哪个。
• 当 open_file_limit 被配置的时候，比较 open_files_limit 和 max_connections*5 的值，哪个大用哪个。

        注意：仍然可能出现报错信息 Can't create a new thread；此时观察系统 cat /proc/mysql 进程号/limits，观察进程 ulimit 限制情况。过小的话，考虑修改系统配置表，/etc/security/limits.conf 和 /etc/security/limits.d/90-nproc.conf。

        2、超时配置

        （1）interactive_timeout、wait_timeout

        MySQL 连接闲置超过一定时间后，默认8小时。

• wait_timeout 指的是mysql在关闭一个非交互的连接之前所要等待的秒数

• interactive_time 指的是mysql在关闭一个交互的连接之前所要等待的秒数

        对于交互和非交互连接，说得直白一点就是，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。

        如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，最终肯定会达到MySQL Server的连接上限数，这会报'too many connections'的错误。

        （2）back_log

        在 MySQL 暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中，官方建议 back_log = 50 + (max_connections / 5)，封顶数为 900。

二、数据库数据交换配置

        1、交互配置

        （1）max_allowed_packet 

        该参数限制服务器端，接受的数据包大小，如果有 BLOB 子段，建议增大此值，避免写入或者更新出错。有 BLOB 子段，建议改为 1024M。

        （2）tmp_table_size

        内存临时表的最大值，默认 16M，此处设置成 64M        

        （3）max_heap_table_size 

        用户创建的内存表的大小，默认 16M，往往和 tmp_table_size 一起设置，限制用户临时表大小。

        2、缓存配置

        在之前介绍MySQL架构的文章中我们介绍了缓存器这个结构，8.0版本已将该模块删除，老版本也不建议使用数据库缓存，因为往往弊大于利。因此本文只列出这些参数，不做具体解释，有兴趣的朋友可以自行了解。

        query_cache_type、query_cache_size、query_cache_limit、query_cache_min_res_unit 。

三、日志配置

        1、慢查询

        慢查询的相关内容可以看我的这篇文章《mysql之慢sql与pt-query-digest》

        （1）slow_query_log 

        开启慢查询

        （2）long_query_time 

        开启慢查询时间，达到此值才记录数据

        （3）min_examined_row_limit 

        检索行数达到此数值，才记录慢查询日志中

        （4）log_throttle_queries_not_using_indexes 

        mysql 5.6.5 新增，用来表示每分钟允许记录到 slow log 的且未使用索引的 SQL 语句次数，默认值为 0，不限制。

        （5）slow_query_log_file 

        慢查询日志文件地址

        （6）log-queries-not-using-indexes

        开启记录没有使用索引查询语句

        2、bin log

        （1）expire_logs_days 

        mysql 清除过期日志的时间，默认值 0，不自动清理，而是使用滚动循环的方式。

        （2）max_binlog_size 

        如果二进制日志写入的内容超出给定值，日志就会发生滚动。你不能将该变量设置为大于 1GB 或小于 4096 字节。 默认值是 1GB。

        （3）binlog_format 

        binlog 的格式也有三种：STATEMENT，ROW，MIXED。mysql 5.7.7 后，默认值从 MIXED 改为 ROW。

四、InnoDB 引擎配置

        （1）innodb_buffer_pool_size 

        buffer pool的大小，建议设置为物理内存的 60%-80%，意别设置的过大，会导致 system 的 swap 空间被占用，导致操作系统变慢，从而减低 sql 查询的效率。

        （2）innodb_buffer_pool_instances 

        buffer_pool中划分的实例个数，如果设置的pool的size超过了1G的话，建议使用多个pool实例，来优化多线程情况下，并发读取同一个pool造成的锁的竞争。

        （3）innodb_buffer_pool_chunk_size 

        mysql 5.7 新特性，当增加或减少innodb_buffer_pool_size时，操作以块（chunk）形式执行。块大小由innodb_buffer_pool_chunk_size配置选项定义，默认值128M。

        缓冲池大小配置必须始终等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。如果配置innodb_buffer_pool_size为不等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数，则缓冲池大小将自动调整为等于或不小于指定缓冲池大小的innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数。

        （4）innodb_buffer_pool_dump_at_shutdown、innodb_buffer_pool_load_at_startup

         可以配置在MySQL关闭之前，保存InnoDB当前的缓冲池的状态，以避免在服务器重新启动后，还要经历一个预热的暖机时间。通过innodb_buffer_pool_dump_at_shutdown（服务器关闭前设置）来设置。
        当设置这个参数以后MySQL就会在机器关闭时保存InnoDB当前的状态信息到磁盘上。当启动MySQL服务器时要恢复服务器缓冲池状态，请在启动服务器时开启innodb_buffer_pool_load_at_startup参数。个人认为这个值还是需要配置一下的，MySQL 5.7.6版本之前这两个值默认是关闭的，但从MySQL 5.7.7版本开始这两个值就默认为开启状态了。       

        （5）innodb_page_size 

        buffer pool中每一页的大小，一般 8k 和 16k 中选择，8k 的话，cpu 消耗小些，selcet 效率高一点，默认值：16k，一般不用改。

        （6）innodb_page_cleaners 

        将脏数据写入到磁盘的线程数，默认值1，建议值：4-8；并且必须小于innodb_buffer_pool_instances。

        （7）innodb_io_capacity、innodb_io_capacity_max 

        innodb_io_capacity 设置了 Mysql 后台任务（例如页刷新和 merge dadta from buffer pool）每秒 io 操作的上限。

        innodb_io_capacity_max 默认值为innodb_io_capacity 的两倍。建议值：例如用 iometer 测试后的 iops 数值就好。

        （8）innodb_flush_method 

        控制着 innodb 数据文件及 redo log 的打开、刷写模式，三种模式：fdatasync(默认)，O_DSYNC，O_DIRECT

• fdatasync：数据文件，buffer pool->os cache->磁盘；日志文件，buffer pool->os cache->磁盘；
• O_DSYNC： 数据文件，buffer pool->os cache->磁盘；日志文件，buffer pool->磁盘；
• O_DIRECT： 数据文件，buffer pool->磁盘； 日志文件，buffer pool->os cache->磁盘；

        对于硬盘性能好的，选择O_DIRECT方式，避免在操作系统缓存和innodb缓存中存两份数据， innodb缓存的效率更高，对于读操作大大多于写操作的系统，设置成fsync性能会略好。

        （9）innodb_undo_log_truncate 

        5.7.5 后开始使用，在线收缩 undo log 使用的空间，默认关闭，建议开启（设置为1）。

        （10）innodb_max_undo_log_size 

        默认为1G，结合 innodb_undo_log_truncate，实现 undo 空间收缩功能。

        （11）innodb_log_file_size 

        redo log日志文件的大小，默认值48M，建议根据你系统的磁盘空间和日志增长情况调整大小。        

        （12）innodb_log_files_in_group

         指定redo日志文件的个数，默认值为2，最大值为100，建议根据你系统的磁盘空间和日志增长情况调整大小。        

        （13）innodb_log_buffer_size 

        确定redo log buffer所用的内存大小，以 M 为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL 开发人员建议设置为 1－8M 之间

        （14）innodb_flush_log_at_trx_commit 

        redo log buffer 中的数据写入到 redo log 磁盘文件中的策略，共三种可选值。
        参数 0：表示每隔一秒把log buffer刷到os cache去，并且调用“flush”操作将缓存刷新到磁盘上去。也就是说一秒之前的日志都保存在日志缓冲区，也就是内存上，如果机器宕掉，可能丢失1秒的事务数据。
        参数 1：表示在每次事务提交的时候，都把log buffer刷到os cache中去，并且调用“flush”操作将缓存刷新到磁盘上去。这样的话，数据库对IO的要求就非常高了，如果底层的硬件提供的IOPS比较差，那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升。
        参数 2：表示在每次事务提交的时候会把log buffer刷到os cache中去，但并不会立即刷写到磁盘，具体落地时间由os cache决定。如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失1秒之类的事务数据。这样的好处，减少了事务数据丢失的概率，而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中，一般只是从log buffer的内存转移的文件系统的内存缓存中，对底层IO没有压力)。

        （15）innodb_flush_log_at_timeout  

        针对innodb_flush_log_at_trx_commit 为2时可以控制 log 从系统 buffer 刷入磁盘文件的刷新频率，增大可减轻系统负荷，默认值为1。

        （16）innodb_read_io_threads、innodb_write_io_threads 

        innodb 处理 io 读写的后台并发线程数量，根据 cpu 核来确认，默认值4，建议值与逻辑 cpu 数量的一半保持一致。

        （17）innodb_purge_threads 

        mysql 5.5 之后，支持多线程清除操作，可以配置多个purge线程。（注意purge与脏数据写回线程的区别，purge线程的作用在于清理 deleted_flag 为 true 的记录，即真正的delete操作）。

        （18）innodb_old_blocks_pct 

        冷热分离lru中冷数据（old blocks） 的占比，默认值是37。

        （19）innodb_old_blocks_time

        新数据被载入缓冲池，进入冷数据区，当 1 秒（默认值1000毫秒）后再次访问，则提升进入热数据区。