Y2 佬技术总监,是这样优化产线MySQL数据库配置的
📢📢📢📢📢📢
哈喽!大家好,我是 【梦想橡皮擦】,10 年产研经验,致力于 Python 相关技术栈传播 💗
⛳️ 实战场景
很多时候,当我们在服务器端安装 MySQL 软件之后,就会立马进行数据表结构的创建,表数据的填充,但其实我们可以提前对 MySQL 配置文件进行一些修改,从而让其效率达到最优。
典型的一些配置调整。
⛳️ 查询缓存
当数据库查询使用比较频繁的时候,开启该命令。
query_cache_size/query_cache_type
查询缓存,建议关闭,即设置为
query_cache_size = 256M
query_cache_type = 1
可以使用如下命令查询缓存状态是否开启
show VARIABLES like "query_cache%";
如果查询到 query_cache_type
为 OFF
,表示未开启缓存,其余参数说明如下所示:
query_cache_limit
:缓存大小限制,超过不缓存,缺省值为 1M,可以设置为 2M;query_cache_min_res_unit
:缓存块大小,默认 4KB;query_cache_size
:查询缓存大小,需要设置为 1024 的倍数,单位为字节;query_cache_type
:缓存类型,0 为不设置,1 为缓存所有结果,2 为缓存 select 语句中通过SQL_CACHE
指定的缓存。
禁止查询缓存的方法是直接注释 query_cache_limit
即可。
⛳️ 各种缓存
sort_buffer_size
可以设置为
sort_buffer_size = 4M
查询排序时可使用的缓冲区大小,该参数对应的分配内存是单连接独占,只对 order by 和 group by 起作用,如果有 10 个连接,实际分配的内存是 10x4M = 10M,对于 4GB 内存的服务器,可以设置为 4~8M。
与该参数对应的两个参数是 read_buffer_size
和 join_buffer_size
,分别表示读查询缓冲区大小,联合查询缓冲区大小。
key_buffer_size
索引的缓冲区大小,根据服务器内存进行配置,一般占用实际内存的 10%左右即可。
max_allowed_packet
网络传输中消息的最大值,默认为 1M,最大值是 1024M,必须是 1024 的倍数。
⛳️ Innodb 缓存及其相关
innodb_buffer_pool_size
缓冲池大小,当使用 InnoDB 之后应该设置的选项,该值原则上越大越好,一般设置内存总量的 70%~80%大小。
设置格式如下所示,注意单位。
innodb_buffer_pool_size = 10G
innodb_log_file_size
redo 日志的大小,重写日志用于确保写操作崩溃时的快速恢复,以 M 为单位,提高该值也能提高效率。
innodb_log_file_size = 128M
innodb_lock_wait_timeout
默认为 50 ,修改为 30
innodb_lock_wait_timeout = 30
innodb_lock_wait_timeout
MySQL 事务超时时间,默认时间为 50S,当数据库锁超过这个值就会报错。
⛳️ 连接数
max_connections
MySQL 最大连接数,当服务器并发量比较大的时候,会返回 too many connections
错误时,可以调整该值进行处理,该值默认值为 100,你可以设置为更高的值。
设置的时候,可以参考产线环境,在操作库上执行下述代码。
show variables like '%max_connections%';
show status like '%max_used_connections%';
对比得到的结果集,如果 max_used_connections
= max_connections
,即调高 max_connections
。
如果 max_used_connections
远远小于 max_connections
,则调低该值。
该内容还会衍生出一个配置,即暂存链接数 back_log
。该值表示的是 MySQL 暂停新请求之前,短时间内可以寄存的请求数,一般设置为 100~200 即可。
⛳️ 时间
wait_timeout 和 interactive_timeout
wait_timeout
表示 MySQL 关闭非交互的连接之前的等待秒数;
interactive_timeout
表示 MySQL 关闭交互连接之前要等待的秒数。
interactive_timeout
默认值是 28800,可以优化到 6000(即 100 分钟), wait_timeout
酌情进行处理即可。
show variables like "wait_timeout";
show variables like "interactive_timeout";
常用的配置如下:
日志格式与路径
binlog_format = row
log_bin = /data/mysql/data/mysql-bin # 电脑路径
log-error = /data/mysql/data/error.log # 电脑路径
表名忽略大小写
lower_case_table_name = 1
慢日志相关
slow_query_log = 1 # 开启慢日志
slow_query_log_file = /data/mysql/data/slow-query.log # 慢日志地址
long_query_time = 3 # 慢日志记录时间
连接数配置
max_connections = 1024 # 最大连接数
back_log = 500 # 暂存连接数
max_connect_errors = 20 # 最大错误连接数
超时配置
wait_timeout = 60 # 非交互的连接
interactive_timeout = 6000 # 交互的连接
缓存相关配置
key_buffer_size=256M # 索引缓存区大小
query_cache_size = 256M # 查询索引大小
query_cache_type=1 # 缓存存储类型
query_cache_limit=50M # 缓存大小限制
sort_buffer_size = 2M
join_buffer_size = 2M
max_allowed_packet=32M # 额外分配内存
thread_cache_size=200 # 线程缓存大小
read_buffer_size=1M # 读取缓存大小
read_rnd_buffer_size=16M # 随机缓存区大小
bulk_insert_buffer_size=64M # 批量插入
innodb 缓冲区配置
# 缓存innodb表的索引,数据,插入数据时的缓冲总大小
innodb_buffer_pool_size = 10G
innodb_flush_log_at_trx_commit = 2 # 事务提交日志参数
innodb_log_buffer_size = 32M # 日志缓冲区大小
# 重做日志大小,用于 MySQL 崩溃时的重做恢复
innodb_log_file_size = 128M
# 日志组,默认为 2
innodb_log_files_in_group = 2
📣📣📣📣📣📣
🌻 本文如果发现错误,欢迎在评论区中指正哦 💗
- 点赞
- 收藏
- 关注作者
评论(0)