写点什么

佬技术总监,是这样优化产线 MySQL 数据库配置的

作者:梦想橡皮擦
  • 2022 年 5 月 19 日
  • 本文字数:2070 字

    阅读完需:约 7 分钟

⛳️ 实战场景

很多时候,当我们在服务器端安装 MySQL 软件之后,就会立马进行数据表结构的创建,表数据的填充,但其实我们可以提前对 MySQL 配置文件进行一些修改,从而让其效率达到最优。


典型的一些配置调整。

⛳️ 查询缓存

当数据库查询使用比较频繁的时候,开启该命令。

query_cache_size/query_cache_type

查询缓存,建议关闭,即设置为


query_cache_size = 256Mquery_cache_type = 1
复制代码


可以使用如下命令查询缓存状态是否开启


show VARIABLES like "query_cache%";
复制代码



如果查询到 query_cache_typeOFF ,表示未开启缓存,其余参数说明如下所示:


  • 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_sizejoin_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 = rowlog_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 = 2Mjoin_buffer_size = 2Mmax_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 = 10Ginnodb_flush_log_at_trx_commit = 2 # 事务提交日志参数
innodb_log_buffer_size = 32M # 日志缓冲区大小# 重做日志大小,用于 MySQL 崩溃时的重做恢复innodb_log_file_size = 128M# 日志组,默认为 2innodb_log_files_in_group = 2
复制代码


📣📣📣📣📣📣🌻 本文如果发现错误,欢迎在评论区中指正哦 💗


发布于: 刚刚阅读数: 2
用户头像

爬虫 100 例作者,蓝桥签约作者,博客专家 2021.02.06 加入

6 年产品经理+教学经验,3 年互联网项目管理经验; 互联网资深爱好者; 沉迷各种技术无法自拔,导致年龄被困在 25 岁; CSDN 爬虫 100 例作者。 个人公众号“梦想橡皮擦”。

评论

发布
暂无评论
佬技术总监,是这样优化产线MySQL数据库配置的_5月月更_梦想橡皮擦_InfoQ写作社区