MySQL优化之——参数调优最佳实践

前面介绍了MySQL的最重要的一个内存参数。这次介绍其他的一些参数优化最佳实践。

9de7bb31d0644e7393039222babfed10

#gtid-mode

默认值: OFF 建议: ON

控制是否启用基于GTID的日志记录以及日志可以包含的事务类型

#enforce_gtid_consistency

默认值: OFF 建议: ON

根据此变量的值, 服务器通过只允许执行可以使用GTID安全记录的语句来强制执行GTID一致性. 在启用基于GTID的复制之前, 必须将此变量设置为ON

#binlog_checksum

默认值: CRC32 建议: NONE

启用时, 此变量会导致源为二进制日志中的每个事件写入校验和, 禁用时, 服务器将通过写入和检查每个事件的事件长度(而不是校验和)来验证是否只将完整事件写入二进制日志

#disabled_storage_engines

默认值: 空 建议: MyISAM,BLACKHOLE,ARCHIVE,MEMORY

此变量指示哪些存储引擎不能用于创建表或表空间

#tmpdir

默认值: /tmp 建议: 单独的比较大的文件系统

临时目录,不要采用默认值,因为根目录一般不太大,容易占满

#innodb_buffer_pool_size

默认值: 134217728 最小值: 5242880 最大值: 2**64-1(64bit)/2**32-1(32bit) 建议: 对于单独的 MySQL数据库服务器,设置为内存的 80%

对于 InnoDB 表来说, innodb_buffer_pool_size 的作用就相当于 key_buffer_size 对于 MyISAM表的作用一样. InnoDB 使用该参数指定大小的内存来缓冲数据和索引

根据MySQL手册,对于 2G 内存的机器,推荐值是 1G (50%).如果你的数据量不大,并且不会暴增,那么无需把 innodb_buffer_pool_size 设置的太大

#binlog_cache_size

默认值: 32768 最小值: 4096 最大值: 18446744073709547520(64bit) 4294963200(32bit) 建议: 1 M

binlog_cache_size 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存, 提高记录 bin-log 的效率. 没有什么大事务, dml 也不是很频繁的情况下可以设置小一点,如果事务大而且多, dml 操作也频繁,则可以适当的调大一点. 前者建议是 1M,后者建议是: 即 2-4M

#group_concat_max_len

默认值: 1024 最小值: 4 最大值: 18446744073709551615(64bit) 4294967295(32bit) 建议: 4294967295

拼接的字符串的长度字节

#log-bin-trust-function-creators

默认值: OFF 建议: ON

当二进制日志启用后,这个变量就会启用. 它控制是否可以信任存储函数创建者,不会创建写入二进制日志引起不安全事件的存储函数. 如果设置为0 (默认值),用户不得创建或修改存储函数,除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限. 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制. 如果变量设置为1,MySQL不会对创建存储函数实施这些限制. 此变量也适用于触发器的创建

#skip_name_resolve

默认值: OFF 建议: ON

禁止对外部连接进行DNS解析, 消除DNS解析时间, 但需要所有远程主机用IP访问, 也只能使用IP进行grant赋权, 不能使用主机名, 通常主机名不会变, 而IP改变的可能性比主机名大

#default_authentication_plugin

默认值: caching_sha2_password 推荐: mysql_native_password

默认认证插件, 默认值容易导致 navicat 和 dotnet 客户端连接不上

#max_connections

默认值: 151 最小值: 1 最大值: 100000 推荐: 1000

MySQL的最大连接数, 如果服务器的并发连接请求量比较大, 建议调高此值, 以增加并行连接数量, 当然这建立在机器能支撑的情况下, 因为如果连接数越多, MySQL 会为每个连接提供连接缓冲区, 就会开销越多的内存, 所以要适当调整该值, 不能盲目提高设值. 设置过小会经常出现 ERROR 1040: Too many connections 错误

这个参数应当设置为经过压力测试验证后系统能承受的最多连接数. 可以参考状态参数 Max_used_connections 和 Max_used_connections_time, 它们记录了系统连接数曾经达到的最大值和发生时间.

max_used_connections / max_connections * 100% (理想值 超过 85% 表示设置过低或者超过服务器负载上限了,低于 10% 则设置过大

#wait_timeout 和 interactive_timeout

默认值: 28800 最小值: 1 最大值: 2147483(windows) 31536000(linux) 建议调优为 1200

wait_timeout 指的是 MySQL 在关闭一个非交互的连接之前所要等待的秒数(默认的连接mysql api程序,jdbc连接数据库等)

interactive_time 指的是 MySQL 在关闭一个交互的连接之前所要等待的秒数(mysql工具, mysqldump等)

通过MySQL客户端连接数据库的是交互会话, 通过jdbc等程序连接数据库的是非交互会话, mysql程序即是交互连接, 如果没有操作的时间超过了 interactive_time 设置的时间就会自动断开

wait_timeout对性能的影响:

● 如果设置大小, 那么连接关闭的很快, 从而使一些持久的连接不起作用

● 如果设置太大, 容易造成连接打开时间过长, 在 show processlist时, 能看到太多的 sleep 状态的连接, 造成 too many connections 错误

● 一般希望 wait_timeout 尽可能地低

交互式连接测试结果: session wait_timeout 继承于 global interactive_timeout

非交互是连接结果: session wait_timeout 继承于 global wait_timeout

#log_timestamps

默认值: UTC 建议: SYSTEM

此变量控制写入错误日志的消息中的时间戳的时区, 以及写入文件的常规查询日志和慢速查询日志消息中的时区. 它不会影响写入表(mysql.general_log, mysql.slow_log)的常规查询日志和慢速查询日志消息的时区

#default_time_zone

建议: +8:00

默认时区

#local_infile

默认值: OFF 推荐: ON

此变量控制 LOAD DATA 语句的服务器端 LOCAL 功能. 根据 local_infile 设置, 服务器拒绝或允许在客户端启用 local 的客户端加载本地数据

#federated

默认值: OFF 建议: ON

访问远程MySQL数据库中的数据

#event_scheduler

默认值: ON 可选: ON OFF DISABLED 推荐: 主库: ON 从库:OFF

此变量控制事件调度器

#innodb_print_all_deadlocks

默认值: OFF 推荐: ON

启用此选项后, 有关 InnoDB 用户事务中所有死锁的信息将记录在 mysqld 错误日志中. 否则, 使用 SHOW ENGINE INNODB STATUS 命令, 您只能看到有关最后一个死锁的信息

#read_only

默认值: OFF 推荐: 从库OFF

如果启用此变量, 除具有 CONNECTION_ADMIN 权限(或已弃用的 SUPER 权限)的用户外, 服务器不允许任何客户端更新

#admin_address

默认值: 无 推荐: 服务器IP

在管理网络接口上侦听TCP/IP连接的IP地址, 没有默认的admin_address值. 如果在启动时未指定此变量, 则服务器不会维护任何管理界面

#admin_port

默认值: 33062 最小值: 0 最大值: 65535 推荐值: 默认值

用于管理网络接口上连接的 TCP/IP 端口号, 将此变量设置为0将使用默认值. 如果未指定 admin_address, 则设置 admin_port 无效, 因为在这种情况下, 服务器没有维护管理网络接口

#以上参数建议按推荐修改, 以下参数视实际需要修改

#innodb_log_buffer_size

默认值: 16777216 最小值: 1048576 最大值: 4294967295

此参数确定些日志文件所用的内存大小,以 M 为单位. 日志缓冲区是一个内存缓冲区, InnoDB 使用它来缓冲重做日志事件,然后再将其写入磁盘(事务日志默认在 datadir下 有两个 48MB 的日志文件ib_logfile0 和ib_logfile1). 缓冲区更大能提高性能,对于较大的事务,可以增大缓存大小为 innodb_log_buffer_size=32M

#key_buffer_size

默认值: 8388608 最小值: 0 最大值: 4294967295(32bit)/OS_PER_PROCESS_LIMIT(64bit)

索引块的缓存大小,增加会提升索引处理速度,对MyISAM表性能影响最大,需要注意的是它只对 MyISAM 表起作用,对 InnoDB 表无效

#read_buffer_size

默认值: 131072 最小值: 8192 最大值: 2147479552

MySQL 读入缓冲区大小. 对表进行顺序扫描的请求将分配一个读入缓冲区, MySQL 会为它分配一段内存缓冲区. 如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能. 和 sort_buffer_size 一样,该参数对应的分配内存也是每个连接独享

#read_rnd_buffer_size

默认值: 262144 最小值: 1 最大值: 2147483647

MySQL 的随机读 (查询操作)缓冲区大小. 当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区. 进行排序查询时, MySQL 会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值. 但 MySQL 会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大

注: 顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据. 随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据,而辅助索引和主键所在的数据段不同,因此访问方式是随机的

#sort_buffer_size

默认值: 262144 最小值: 32768 最大值: 4294967295(windows) 18446744073709551615(64bit) 18446744073709551615(32bit)

每个需要进行排序的线程分配该大小的一个缓冲区. 增加这个值加速 ORDER BY 或 GROUP BY操作, sort_buffer_size 是一个 connection 级参数,在每个 connection (session)第一次需要使用这个 buffer 的时候,一次性分配设置的内存. sort_buffer_size 并不是越大越好,由于是 connection 级的参数,过大的设置+高并发可能会耗尽系统内存资源. 例: 500 个连接将会消耗500*sort_buffer_size(2M)=1G 内存

#join_buffer_size

默认值: 262144 最小值: 128 最大值: 4294967168(windows) 18446744073709551488(64bit) 4294967168(32bit)

用于表间关联缓存的大小,和 sort_buffer_size 一样,该参数对应的分配内存也是每个连接独享

#innodb_tmpdir

默认值: NULL 建议: 保持默认

nnodb在做 online-ddl 的时候会向临时目录写入 "临时排序文件", 文件的大小基本上就要与表的数据+索引的大小差不多, 如果未设置, 以 tmpdir 为准

#back_log

默认值: -1(自动调整大小不要分配此文字值) 最小值: 1 最大值: 65535

MySQL能暂存的连接数量. 当主要 MySQL 线程在一个很短时间内得到非常多的连接请求, 它就会起作用. 如果 MySQL 的连接数据达到 max_connections 时, 新来的请求将会被存在堆栈中, 以等待某一连接释放资源, 该堆栈的数量即 back_log, 如果等待连接的数量超过 back_log, 将不被授予连接资源

back_log 值指出在 MySQL 暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中. 只有如果期望在一个短时间内有很多连接, 你需要增加它

当 processlist 发现大量xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect |NULL | login | NULL 的待连接进程时, 就要加大 back_log 或 max_connections 的值

#max_connect_errors

默认值: 100 最小值: 1 最大值: 18446744073709551615(64bit) 4294967295(32bit)

是一个 MySQL中与安全有关的计数器值, 它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况, 当超过指定次数, MYSQL 服务器将禁止 host 的连接请求,直到 mysql 服务器重启或通过 flush hosts 命令清空此 host 的相关信息, max_connect_errors 的值与性能并无太大关系

#max_allowed_packet

默认值: 67108864 最小值: 1024 最大值: 1073741824

MySQL根据配置文件会限制 Server 接受的数据包大小. 有时候大的插入和更新会受 max_allowed_packet 参数限制,导致写入或者更新失败. 最大值是 1GB,必须设置 1024 的倍数

#thread_cache_size

默认值: -1(自动调整大小不要分配此文字值) 最小值: 0 最大值: 16384

服务器线程缓存, 这个值表示可以重新利用保存在缓存中线程的数量, 当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限), 如果线程重新被请求, 那么请求将从缓存中读取,如果缓存中是空的或者是新的请求, 那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能

通过比较 Connections 和 Threads_created 状态的变量, 可以看到这个变量的作用. 设置规则如下

1GB 内存配置为 8, 2GB 配 置为 16, 3GB 配置为 32, 4GB 或更高内存,可配置更大. 服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

show status like 'Connections'; 试图连接到 MySQL(不管是否连接成功)的连接数

show status like 'Threads_cached'; 线程缓存中的线程数

show status like 'Threads_connected'; 当前打开的连接数

show status like 'Threads_created'; 从最近一次服务启动,为处理连接而创建的线程数. 如果较大, 则需要增加 thread_cache_size 值. 缓存未命中率可以计算为 Threads_created/Connections

show status like 'Threads_running'; 未休眠的线程数

#innodb_flush_log_at_trx_commit

默认值: 1

主要控制了 innodb 将 log buffer 中的数据写入日志文件并 flush 磁盘的时间点,取值分别为 0, 1, 2 三个

0 表示当事务提交时, 不做日志写入操作, 而是每秒钟将 log buffer 中的数据写入日志文件并 flush 磁盘一次

1 则在每秒钟或是每次事物的提交都会引起日志文件写入, flush 磁盘的操作,确保了事务的 ACID

2 每次事务提交引起写入日志文件的动作, 但每秒钟完成一次 flush 磁盘操作

实际测试发现,该值对插入数据的速度影响非常大,设置为 2 时插入 10000 条记录只需要 2 秒,设置为 0 时只需要 1 秒,而设置为 1 时则需要 229 秒. 因此,MySQL手册也建议尽量将插入操作合并成一个事务,这样可以大幅提高速度

根据MySQL手册,在允许丢失最近部分事务的危险前提下,可以把该值设为 0 或 2

#innodb_thread_concurrency

默认值: 0 最小值: 0 最大值: 1000 推荐值: cpu*2

此参数用来设置 innodb 线程的并发数量,默认值为 0 表示不限制,要设置则与服务器的CPU 核数相同或是 cpu 的核数的 2 倍,建议用默认设置,一般为 8

#innodb_log_file_size

默认值: 50331648 最小值: 4194304 最大值: 512GB/innodb_log_files_in_group

此参数确定数据日志文件的大小,以 M 为单位,更大的设置可以提高性能

#innodb_log_files_in_group

默认值: 2 最小值: 2 最大值: 100

为提高性能, MySQL可以以循环方式将日志文件写到多个文件. 推荐设置为 3

#bulk_insert_buffer_size

默认值: 8388608 最小值: 0 最大值: 18446744073709551615(64bit) 4294967295(32bit)

批量插入数据缓存大小,可以有效提高插入效率,默认为 8M

#max_binlog_cache_size

默认值: 18446744073709547520 最小值: 4096 最大值: 18446744073709547520

max_binlog_cache_size = 8M 表示的是 binlog 能够使用的最大 cache 内存大小

#max_binlog_size

默认值: 1073741824 最小值: 4096 最大值: 1073741824

max_binlog_size = 512M 指定 binlog 日志文件的大小,如果当前的日志大小达到 max_binlog_size,会自动创建新的二进制日志. 变量可设置范围是 4096 字节 和 1GB 之间 默认值是 1GB.

#sql_log_bin

在导入大容量的 sql 文件时,建议关闭 否则硬盘扛不住,而且建议定期做删除

#binlog_order_commits

默认值: ON 建议: ON

如果把这个参数设置为 OFF 将不能保证事务的提交顺序和写入二进制日志的顺序一致, 这不会影响到数据一致性, 在高并发场景下还能提升一定的吞吐量