调整 MySQL 并发相关的参数

灰暗的星星灰暗的星星灰暗的星星灰暗的星星灰暗的星星
 
分类:技术文章

MySQL server 是多线程结构,包括后台线程和客户服务线程。多线程可以有效利用服务器资源,提高数据库的并发性能。在 MySQL 中,控制并发连接和线程的主要参数包括 max_connections、back_log、thread_cache_size 以及 table_open_cache 等。

一、调整 max_connections,提高并发连接

参数 max_connections 控制允许连接到 MySQL 数据库的最大数量。如果状态变量 connection_errors_max_connections 不为零,并且一直增长,就说明不断有连接请求因数据库连接数已达到最大允许的值而失败,应考虑增大max_connections 的值。

MySQL 最大可支持的数据库连接取决于很多因素,包括给定操作系统平台线程库的质量、内存大小、每个连接的符合以及期望的响应时间等。在 Linux 平台下,MySQL 支持 500~1000 个连接不是难事,如果内存足够,不考虑响应时间,甚至能达到上万个连接。而在 windows 平台下,受其所用线程库的影响,最大连接数有以下限制:

(open tables * 2 + open connections) < 2048

每个session 操作 MySQL 数据库表都需要占用文件描述符,数据库连接本身也要占用文件描述符,因此,在增大 max_connections 时,也要注意评估 open-files-limit (文件描述符)的设置是否够用。

二、调整 back_log

back_log 参数控制 MySQL 监听 tcp 端口时设置的积压请求栈大小,5.6.6版本以前的默认值是 50,5.6.6 版本以后的默认值是 50 + (max_connections/5),但最大不能超过 900。

如果需要数据库在较短时间内处理大量连接请求,可以考虑适当增大 back_log 的值。

三、调整 table_open_cache

每一个 sql 执行线程至少都要打开一个表缓存,参数 table_open_cache 控制所有 sql 执行线程可打开表缓存的数量。这个参数的值应根据最大连接数 max_connections 以及每个连接执行关联查询中所涉及表的最大个数(用 N 表示)来设定:

max_connection * N

在未执行 flush tables 命令的情况下,如果 MySQL 状态值 opened_tables 的值较大,就说明 table_open_cache 设置的太小,应适当增大。增大 table_open_cache 的值,会增加 MySQL 对文件描述符的使用量,因此,也要注意评估 open-files-limit 的设置是或否够用。

四、调整 thread_cache_size

为加快连接数据库的速度,MySQL 会缓存一定数量的客户服务线程以备重用,通过参数 thread_cache_size 可控制 MySQL 缓存客户端线程的数量。
可以通过计算线程 cache 的失效率 threads_created / connections 来衡量 thread_cahce_size 的设置是否合适,该值越接近 1,说明线程 cache 命中率越低,应考虑适当增加 thread_cahce_size 的值。

五、innodb_lock_wait_timeout 的设置

参数 innodb_lock_wait_timeout 可以控制 innodb 事务等待行锁的时间,默认值是 50ms,可以根据需要动态设置。对于需要快速反馈的交互式应用,可以将行锁等待超时时间调大,以避免发生大的回滚操作。