MySQL服务器参数配置

MySQL运行时的性能与配置参数有着密切的联系，为了保证我们的MySQL服务高效，就必须对各项参数进行优化。

MariaDB提供了多个适合不同规模的配置文件供选择和参考，位于/usr/share/mysql

[root@localhost ~]# ls /usr/share/mysql/my-*.cnf -l
-rw-r--r--. 1 root root  4920 5月   2 01:18 /usr/share/mysql/my-huge.cnf
-rw-r--r--. 1 root root 20441 5月   2 01:18 /usr/share/mysql/my-innodb-heavy-4G.cnf
-rw-r--r--. 1 root root  4907 5月   2 01:18 /usr/share/mysql/my-large.cnf
-rw-r--r--. 1 root root  4920 5月   2 01:18 /usr/share/mysql/my-medium.cnf
-rw-r--r--. 1 root root  2846 5月   2 01:18 /usr/share/mysql/my-small.cnf
[root@localhost ~]#

1. 内存配置参数

1.1 配置MySQL可以使用的内存上限

1.2 配置每个MySQL连接（线程）使用的内存

# 排序缓冲区
sort_buffer_size
# 连接查询缓冲区
join_buffer_size
# 对MyISAM进行全表扫描时的读缓冲区
read_buffer_size
# 索引缓冲区
read_rnd_buffer_size

1.3 为缓存池分配内存

# innodb缓存池：总内存-(每个线程内存*连接数)-系统保留内存；经验值为服务器内存的75%
Innodb_buffer_pool_size
# MyISAM缓存池：即使数据库表全部为InnoDB，也要分配此缓存池（因为MySQL系统表依然使用MyISAM引擎）
key_buffer_size
# 以下sql可以查看
mysql> select sum(index_length) from information_schema.tables where engine = 'myisam'\G;

2. IO配置参数

2.1 InnoDB

# 事务日志文件大小（如果业务繁忙，建议事务日志文件大小设置大些）
Innodb_log_file_size
# 事务日志文件数量
Innodb_log_files_in_group
# 事务日志缓冲区，不需要太大，32M-128M足够了（事务日志不是直接写入磁盘文件）
Innodb_log_buffer_size
# 事务日志刷新频率
#   0：每秒从缓冲区将log写入操作系统cache，并flush log到磁盘
#   1：每次提交事务，从缓冲区将log写入操作系统cache，并flush log到磁盘（默认）
#   2：每次提交事务，从缓冲区将log写入操作系统cache，每秒flush log到磁盘（建议）
Innodb_flush_log_at_trx_commit
# InnoDB数据文件和日志文件刷新方式
Innodb_flush_method=O_DIRECT（不缓存，直接写入存储设备，避免双重缓存）
# 设为1，InnoDB为每个表建立单独的表空间
Innodb_file_per_table=1（强烈建议）
# 双写缓冲，避免数据页没有写完整造成数据损坏，增加安全性
Innodb_doublewrite=1

2.2 MyISAM

# 延迟刷新关键字缓冲中的脏块到磁盘文件
# OFF：每次写操作后刷新键缓冲中的脏块到磁盘
# ON：只对制定了delay_key_write的表使用延迟刷新
# ALL：对所有表使用延迟刷新
delay_key_write

3. 安全配置参数

# 自动清理binlog日志的天数（至少覆盖两次全备间隔的天数）
expire_logs_days
# MySQL可以接受的包的大小 （建议32M左右）
max_allow_packet
# 禁用DNS查找（此时应使用ip授权，或者对MySQL服务器host中出现的域名授权）
skip_dns_resolve
# 确保sysdate()返回确定性日期
sysdate_is_now
# 禁止非super权限用户写操作（建议在从库中启用）
read_only
# 禁止slave重启后自动复制（建议在从库启用）
skip_slave_start
# MySQL使用的sql模式
sql_mode

4. 其它配置参数

# MySQL何时向磁盘刷新binlog
# 0：不主动刷新，操作系统决定何时刷新（默认为0）
# n：没n次写操作，向磁盘刷新binlog（建议Master设为1）
sync_binlog
# 控制内存临时表大小（设为一致，不要太大）
tmp_table_size和max_heap_table_size
# MySQL允许的最大连接数（默认为100，建议1000、2000...）
max_connections

最后，MySQL服务器参数配置应综合考虑硬件环境、操作系统以及业务场景等多种因素。