内存

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中。

参考