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

举报
梦想橡皮擦 发表于 2022/04/18 15:59:58 2022/04/18
【摘要】 📢📢📢📢📢📢哈喽!大家好,我是 【梦想橡皮擦】,10 年产研经验,致力于 Python 相关技术栈传播 💗 ⛳️ 实战场景很多时候,当我们在服务器端安装 MySQL 软件之后,就会立马进行数据表结构的创建,表数据的填充,但其实我们可以提前对 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_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 = 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

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

【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。