Mysql 配置调优【16G/32G】
先确定 mysql 配置文件（my.ini）位置，一般在 ProgramData/MySQL/MySQL Server 5.7 目录下，备份原文件，然后根据下面的内存配置进行对应修改，没有的配置项则进行对应增加，修改完成之后重启 Mysql 服务。
配置详解
参数项
参数值
参数说明
[client]
客户端设置(当前为客户端默认参数)
port
3306
默认连接端口为3306
socket
/tmp/mysql.sock
本地连接的socket套接字
default_character_set
utf8
设置字符集,通常使用utf8
[mysqld_safe]
mysqld_safe是服务器端工具,用于启动mysqld,也是mysqld的守护进程。当mysql被kill时,mysqld_safe负责重启启动它。
open_files_limit
8192
此为MySQL打开的文件描述符限制,它是MySQL中的一个全局变量且不可动态修改。它控制着mysqld进程能使用的最大文件描述符数量。默认最小值为1024。需要注意的是这个变量的值并不一定是你在这里设置的值,mysqld会在系统允许的情况下尽量取最大值。当open_files_limit没有被配置时,比较max_connections5和ulimit -n的值,取最大值。当open_file_limit被配置时,比较open_files_limit和max_connections5的值,取最大值
user
mysql
用户名
log-error
error.log
错误log记录文件
[mysqld]
服务端基本配置
port
3306
mysqld服务端监听端口
socket
/tmp/mysql.sock
MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
max_allowed_packet
16M
允许最大接收数据包的大小,防止服务器发送过大的数据包。当发出长查询或mysqld返回较大结果时,mysqld才会分配内存,所以增大这个值风险不大,默认16M,也可以根据需求改大,但太大会有溢出风险。取较小值是一种安全措施,避免偶然出现但大数据包导致内存溢出。
default_storage_engine
InnoDB
创建数据表时,默认使用的存储引擎。这个变量还可以通过–default-table-type进行设置
max_connections
512
最大连接数,当前服务器允许多少并发连接。默认为100,一般设置为小于1000即可。太高会导致内存占用过多,MySQL服务器会卡死。作为参考,小型站设置100-300
max_user_connections
50
用户最大的连接数,默认值为50 一般使用默认即可。
thread_cache_size
64
线程缓存,用于缓存空闲的线程。这个数表示可重新使用保存在缓存中的线程数,当对方断开连接时,如果缓存还有空间,那么客户端的线程就会被放到缓存中,以便提高系统性能。我们可根据物理内存来对这个值进行设置,对应规则 1G 为 8;2G 为 16;3G 为 32;4G 为 64 等。
query_cache_type
1
设置为0时,则禁用查询缓存(尽管仍分配query_cache_size个字节的缓冲区)。设置为1时,除非指定SQL_NO_CACHE,否则所有SELECT查询都将被缓存。设置为2时,则仅缓存带有SQL CACHE子句的查询。请注意,如果在禁用查询缓存的情况下启动服务器,则无法在运行时启用服务器。
query_cache_size
64M
缓存select语句和结果集大小的参数。查询缓存会存储一个select查询的文本与被传送到客户端的相应结果。如果之后接收到一个相同的查询，服务器会从查询缓存中检索结果，而不是再次分析和执行这个同样的查询。如果你的环境中写操作很少，读操作频繁，那么打开query_cache_type=1，会对性能有明显提升。如果写操作频繁，则应该关闭它（query_cache_type=0）。
sort_buffer_size
2M
排序缓冲区的大小。增加这个值加速ORDER BY或GROUP BY操作。
join_buffer_size
128K
联接缓冲区大小。增加这个值加速联接查询。
table_definition_cache
400
这个参数控制着可以同时打开的.frm文件数,即表描述符缓存大小,默认为1400。增大该值可以减少打开表的时间。但设置太高会导致mysqld占用过多内存。
table_open_cache
400
这个参数控制着可以同时打开的表缓存数,默认为400。增大该值可以提高性能,但设置太高会导致mysqld占用过多内存。
log_error
error.log
记录启动,运行或停止 mysqld 时出现的问题的日志文件
log_warnings
2
日志记录级别,默认为1。可选值:1(记录错误和关键的警告)、2(记录错误、警告和信息性消息)、3(记录错误、警告、信息和调试消息)
slow_query_log
1
是否记录慢查询,默认不开启。可选值:1(开启)、0(关闭)
slow_query_log_file
slow.log
慢查询日志文件
long_query_time
0.5
日志记录的慢查询阈值,单位秒。超过这个时间将记录到慢查询日志中。
log_queries_not_using_indexes
1
是否记录未使用索引的查询,默认不记录。可选值:1(开启)、0(关闭)
min_examined_row_limit
100
未使用索引的查询最小检查行数,超过这个数值将会记录日志。默认为0,表示不限制。
general_log_file
general.log
普通查询日志文件,记录执行过的所有SQL语句,默认关闭
general_log
0
是否开启普通查询日志,可选值:1(开启)、0(关闭),默认关闭
server_id
42
服务器唯一ID,主从复制需要配置,范围1-232。每个ID必须不同。
log_bin
mysql-bin
二进制日志文件前缀
binlog_cache_size
1M
二进制日志缓存大小,非事务性表的增删改的数据,先存到这个缓存中,再定期写入二进制日志中。可以加速事务提交及增删改的速度,但需要消耗一些内存。
max_binlog_size
128M
单个二进制文件的最大大小,达到后会自动滚动生成新的二进制日志文件。可以通过expire_logs_days限制日志总大小。
sync_binlog
0
二进制日志同步模式,可选值:0 - 根据binlog_format的值判断是否同步;1 - 强制同步;N - 每N次事件同步一次
expire_logs_days
5
二进制日志自动删除/过期的天数。过期日志将被purge线程自动删除。0表示不自动删除。
binlog_format
ROW
二进制日志格式,可选值:STATEMENT、ROW、MIXED。建议使用ROW。
binlog_row_image
MINIMAL
二进制日志存储行图像的格式。FULL保存完整的行数据;MINIMAL只保存被修改的列及所需的其它列。MINIMAL可以减少日志量,节省IO开销。
key_buffer_size
8M
MyISAM引擎索引缓存区大小。增大可获取更好的索引处理性能,特别是对于读密集的应用,但会占用更多内存。一般设置为物理内存的15-25%。
innodb_buffer_pool_size
128M
InnoDB存储引擎表数据缓存区大小,对于InnoDB来说,是最重要的参数之一。默认为8M,通常设置为实例物理内存的50-80%,最大不要超过物理内存的80%。
innodb_log_file_size
48M
Redo log文件大小,建议设置为innodb_buffer_pool_size的1/4到1/2。
innodb_log_buffer_size
8M
Redo log buffer大小,用于写入redo log的内存缓冲区大小。
max_heap_table_size
32M
内存临时表最大大小。MYSQL会在需要时自动将大表或结果集放到磁盘上,已避免内存耗尽。默认为16M,可以根据需要调大,但不要超过物理内存的50%。
tmp_table_size
32M
内存临时表大小,默认32M。MYSQL在需要时也会自动将大表或结果集放到磁盘上,以避免内存耗尽。可根据需要调大,但不要超过物理内存的50%。
innodb_file_per_table
1
是否给每个InnoDB表分配独立的表空间文件。启用这个参数,可以减少表空间碎片,提高存储利用率。默认为0,即所有表共享一个系统表空间ibdata1文件。
innodb_flush_log_at_trx_commit
1
决定了事务提交时刷新日志到磁盘的频率。建议保持默认值1,以获得完全的事务安全性。
innodb_flush_method
O_DIRECT
InnoDB刷新数据文件和日志文件到磁盘的方法。建议使用O_DIRECT避免进行双写入。
skip_name_resolve
1
是否跳过DNS解析。设置为1可以加速新连接的建立,但无法使用主机名连接MYSQL。
slave_compressed_protocol
1
是否压缩主从复制通信协议的数据流。开启可以减少网络开销。
sync_master_info
10000
主从同步的master信息写入间隔,默认10000(10秒)。缩短这个时间可以减少主从同步中断时丢失的二进制日志量,但会增加一些IO消耗。
sync_relay_log
10000
中继日志同步到磁盘的时间间隔,默认10000(10秒)。缩短这个时间可以减少主从中断时丢失的中继日志量,但会增加IO消耗。
sync_relay_log_info
10000
中继日志信息同步到磁盘的时间间隔,默认10000(10秒)。
推荐数据库配置
16G 内存
参数名称
推荐值
key_buffer_size
128M
max_allowed_packet
256M
table_open_cache
4096
sort_buffer_size
16M
read_buffer_size
32M
read_rnd_buffer_size
16M
myisam_sort_buffer_size
128M
thread_cache_size
64
query_cache_type
0
tmp_table_size
64M
max_connections
500
max_connect_errors
100
open_files_limit
65535
innodb_file_per_table
1
innodb_buffer_pool_instances
4~8
innodb_buffer_pool_size
4G~8G
innodb_log_file_size
1G~2G
innodb_log_buffer_size
64
innodb_flush_log_at_trx_commit
2
innodb_lock_wait_timeout
60
back_log
128
32G 内存
参数名称
推荐值
key_buffer_size
128M
max_allowed_packet
256M
table_open_cache
4096
sort_buffer_size
16M
read_buffer_size
32M
read_rnd_buffer_size
16M
myisam_sort_buffer_size
128M
thread_cache_size
64
query_cache_type
0
tmp_table_size
128M
max_connections
500
max_connect_errors
100
open_files_limit
65535
innodb_file_per_table
1
innodb_buffer_pool_instances
8
innodb_buffer_pool_size
8G~16G
innodb_log_file_size
2G~4G
innodb_log_buffer_size
256M
innodb_flush_log_at_trx_commit
2
innodb_lock_wait_timeout
60
back_log
128
注意事项
innodb_buffer_pool_instances,innodb_log_file_size 参数调节注意
设置 innodb_buffer_pool_size 参数的时候，innodb_buffer_pool_instances 的取值约为 innodb_buffer_pool_size/1G，innodb_log_file_size 取值最小为 innodb_buffer_pool_size/4,向上取整数。
max_connections 参数调节
目前查询了线上几个数据库的 Max_used_connections 参数，平均为 200~300 左右，max_connections 的取值推荐为 Max_used_connections / 0.85,比如数据库的 Max_used_connections= 256，则 max_connections = 256 /0.85 = 300,最大不超过 2 倍 Max_used_connections，也就是 512。不太确定的直接 500~1000 即可,值过大可能导致性能损耗。
show global status like 'Max_used_connections';
！