MySQL调优配置整理

📅 2015-12-02 | 🖱️

内存 #

innodb_buffer_pool_size #

innodb_buffer_pool_size是使用InnoDB存储引擎时最关键的配置项。InnoDB使用一块专门的内存区域做IO缓存，该缓存既缓存InnoDB的索引块，又会缓存InnoDB的数据块。这个缓存区就是InnoDB Buffer Pool，使用innodb_buffer_pool_size设置其大小，在保证系统及其他程序有可用内存的情况下，设置的越大，缓存命中率越高，访问InnoDB的磁盘IO越少，性能越好。

在专门用作MySQL数据库的服务器上，可以设置物理内存的50%~~70%。如：8G内存服务器设置5-6G，32G内存设置20~~25G，128G内存设置100~120GB。

使用 show variables查看(单位byte)：

1 show variables like 'innodb_buffer_pool_size';
2+-------------------------+------------+
3| Variable_name           | Value      |
4+-------------------------+------------+
5| innodb_buffer_pool_size | 4294967296 |
6+-------------------------+------------+

innodb_log_buffer_size #

innodb_log_buffer_size用来设置缓存还未提交的事务的缓冲区的大小。一般默认值16MB是够用的，但如果事务之中含有blog/text等大字段，这个缓冲区会被很快填满会引起额外的IO负载。可通过查看innodb_log_waits状态，如果不为0的话，则需要增加innodb_log_buffer_size。

1show status like 'innodb_log_waits';
2+------------------+-------+
3| Variable_name    | Value |
4+------------------+-------+
5| Innodb_log_waits | 0     |
6+------------------+-------+

query_cache_size #

query_cache_size用来设置查询缓存的大小，一般如果新的应用初始化的MySQL实例建议禁用MySQL的查询缓存，建议通过别的方式提高查询效率，如合适的索引、应用层的缓存、读写分离等等。

1 show variables like 'query_cache_size';
2+------------------+-------+
3| Variable_name    | Value |
4+------------------+-------+
5| query_cache_size | 0     |
6+------------------+-------+

日志和文件 #

log_bin #

log_bin: 用于开启binlog。如果一个节点是主从关系中的主节点，开启binlog是必须的，同时需要设置全局唯一的server_id。对于单节点MySQL如果希望实现基于时间点的数据库恢复，也需要开启binlog。

binlog创建后会被永久保存，可以通过设置expire_logs_days指定自动清理多少天之前的binlog，或者通过PURGE BINARY LOGS命令清理。

1PURGE BINARY LOGS TO 'mysql-bin.010';
2PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

innodb_flush_log_at_trx_commit #

innodb_flush_log_at_trx_commit：默认值为1，InnoDB将完全支持ACID。在Master节点主要关注数据安全性，设置为1是合适的选择，但这会对速度缓慢的磁盘造成很大的开销，每次将更新刷新到redo log时都需要额外的fsnyc操作。设置为2，可靠性会稍微差一些，已提交事务只会1秒一次刷新到redo log，理论上操作系统崩溃或掉电只会丢失最后一秒的事务。某些场景下对于master节点仍然可以接受，对于主从的slave节点是一个不错的选择。设置为0，速度最快，但在崩溃时会丢失一部分数据。

innodb_flush_method #

innodb_flush_method：此配置设置数据和日志刷新到磁盘的方式。服务器有RAID控制器、battery-backed write cache机制时，设置0_DIRECT较好，其他场景使用默认的fdatasync即可。

innodb_log_file_size #

MySQL使用redo log机制保证事务ACID。redo log可以确保数据更新能够更快的写入并持久化，同时又用作崩溃恢复(crash recover)。当一个日志文件写满后，会自动切换到另一个日志文件，切换过程会影响性能。因此对于频繁写的应用可以设置大一些，需要注意如果设置过大对崩溃回复的影响。

innodb_log_file_size的默认值是512MB已经满足一般情况下的需要。

1show variables like 'innodb_log_file_size';
2+----------------------+----------+
3| Variable_name        | Value    |
4+----------------------+----------+
5| innodb_log_file_size | 50331648 |
6+----------------------+----------+

sync_binlog #

sync_binlog的默认值为0，也是建议设置值。表示MySQL不控制binlog的刷新，由文件系统自己控制其缓存的刷新，此时的性能最好，但是风险最大，一旦系统崩溃则在binlog_cache中的binlog有可能会丢失。
如果sync_binlog大于0，那么只有当sync_binlog设定的语句或事务数被写入binlog后，MySQL服务器会将它的binlog同步到硬盘上。因此值设置为1时是最安全的，在崩溃时最多丢失binlog中的一个语句或事务，当这也是性能最差的选择。。

innodb_file_per_table #

默认值为ON，即表示为数据库的每张表使用独立的表空间，数据库的每个表使用一个单独的.ibd文件存储数据和索引。

修改这个系统变量并不能带来性能提升，使用共享表空间的场景只有一个，即数据库下有很多的表，如10000张以上的表的时候。

连接 #

max_connections #

max_connections：可参考Max_used_connections设置，如果Connection_errors_max_connections不为0,并且不断增长应该考虑增大该值。需要注意因为MySQL的连接区、排序区等缓存是分配给每个数据库会话专用的，如果max_connections设置的过大，在并发连接较高时有可能会耗尽物理内存，设置时应该结合并发连接和内存使用综合考虑，而且在应用层要考虑使用连接池。

max_connections的默认值是151.

skip_name_resolve #

当客户端连接到MySQL时，Server端会进行主机名解析操作，当DNS很慢时，建立连接的速度就会很慢。因此建议在MySQL启动的时候设置skip_name_resolve来禁用DNS查找。

设置skip_name_resolve后，GRANT语句仅能使用IP地址，在已有数据库系统中开启这个配置时要注意。

注意事项 #

在修改系统变量配置时，一次建议只修改一个配置项并验证效果，大多数配置项都可以使用SET GLOBAL命令修改，测试修改有效后，再将配置更新到配置文件my.cnf中。