性能优化：MySQL使用优化（3）-CSDN博客

本文链接：https://blog.csdn.net/wzngzaixiaomantou/article/details/127856417

参考资料：

《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、连接数配置

（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毫秒）后再次访问，则提升进入热数据区。