测试驿栈-由浅入深学性能 Mysql 性能优化

飞天小子的性能课堂 · 2021年10月09日 · 最后由 飞天小子的性能课堂 回复于 2021年10月09日 · 45 次阅读

Mysql 性能优化
Mysql 的性能参数可以分为以下几个大类,这里仅整理一些常用的参数配置

连接参数

max_connections

mysql 服务器的最大连接数
show VARIABLES like '%connections%'
查看 mysql 服务器的最大连接数
Max_user_connections
单个用户最大连接数限制
set GLOBAL max_connections
设置 mysql 服务器的最大连接数
max_connect_errors
每个 IP 的连接请求异常中断的最大次数

Connections
试图连接到 mysql 服务器的连接总数
show STATUS like '%Connections%'
查看试图连接的总数
Max_used_connections
已经建立连接的总数

Aborted_connects
尝试重连的次数,结合 host_cache 分析,可能的原因有
1.连接到 MySQL 服务器的失败总数
2.客户端没有权限但是尝试访问 MySQL
3.客户端输入的密码不对
4.超过 connect_timeout 限制(mysql 默认是 10s,除非网络环境极端不好,一般不会超时)

Aborted_clients

客户端没有正常关闭连接而强行中止的连接数
有客户端成功建立连接,但是由于某些原因断开连接,一般是由于网络环境不稳定。主要的可能性有

1.客户端退出之前未调用 mysql_close()正确关闭 MySQL 连接
2.sleep 时间超过了变量 wait_timeout 和 interactive_timeout 的值,导致连接被 MySQL 进程终止
3.客户端在数据传输过程中突然结束

select USER,COUNT(USER) AS CNT from information_schema.PROCESSLIST GROUP BY USER ORDER BY CNT DESC;
查询执行 sql 的连接总数

注:VARIABLES 是参数变量,可以优化;status 是状态位,只能查看不能修改

缓冲空间

sort_buffer_size
查询排序缓存,为每个 connection 一次性分配设置的内存。500 个连接将会消耗 500*sort_buffer_size(8M)=4G 内存

join buffer
表关联缓存空间。增加 join buffer 的值来优化 join 查询,因为增加的 join buffer 可以容纳下更多的外表 join 字段记录

query_cache_limit
MySQL 将 SELECT 语句和查询结果存放在缓冲区,后期对于同样的 SELECT 语句,将直接从缓冲区读取结果
使用show status like "%Qcache%"查看缓冲区。如果Qcache_lowmem_prunes的值非常大,表明缓冲区不够,如果Qcache_hits也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;如果Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲

query_cache_size
MySQL 查询缓存保存查询返回的完整结果。查询命中该缓存,则立刻返回结果,跳过解析,优化和执行阶段。
如果表发生变更,要把Query_cache和该表相关的语句全部置为失效,然后再写入更新。如果Query_cache非常大,该表的查询结构又比较多,那么查询语句失效也会很慢,更新或是 Insert 就会更慢
数据库写入量或是更新量也比较大的系统,该参数不宜分配过大

query_cache_min_res_unit
指定分配缓冲区空间的最小单位,缺省为 4K。检查Qcache_free_blocks。如果该值非常大,则表明缓冲区中碎片很多,查询结果都比较小,此时需要减小尺寸。小数据查询,此值过大容易造成内存碎片和浪费

query_cache_type
设置为 0,OFF,缓存禁用
设置为 1,ON,缓存开启

innodb_buffer_pool_size
内存中开辟的缓存空间用于缓存索引和数据,。数据读写在内存中非常快, 它减少对磁盘的读写。 当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中

innodb_buffer_pool_instances
缓冲池总数
注意:mysql 的缓冲区空间尺寸只能在 my.cnf 里面修正

back_log
如果 MySql 的连接数达到 max_connections 时,新来的请求将会被存在堆栈中,以等待某一连接释放资源
这个值不能超过 TCP 的 net.ipv4.tcp_max_syn_backlog

socket 参数

Bytes_received
从客户端接收到的字节数,作为数据库网卡吞吐量的评测指标

Bytes_sent
发送给客户端的字节数,作为数据库网卡吞吐量的评测指标

max_allowed_packet
网络传输中单次接收数据包尺寸的上限值

线程参数

innodb_thread_concurrency
thread_stack
Threads_cached
缓存线程总数。空闲的连接线程放在连接池中,而不是立即销毁.当有一个新的请求的时候,mysql 不会立即去创建连接线程,而是先去 Thread_Cache 中去查找空闲的连接线程
Threads_connected【当前打开的连接数】
Threads_created【已创建的线程数】
Threads_running【已经激活的线程数】
thread_cache_size
当客户端断开连接后 将当前线程缓存起来 当在接到新的连接请求时快速响应 无需创建新的线程

文件参数

Created_tmp_files【创建的临时文件总数】
Open_files
Open_tables

日志参数

general_log = OFF 关闭通用查询日志
max_binlog_size = 50M
log_bin=ON
expire_logs_days = 10 清除过期日志

慢 sql

show variables like '%slow%';查询慢 sql 状态
show variables like "long%";慢查询时间
show global status like '%slow%'; 慢 sql 条数
set long_query_time=0.0001;【定义慢查询时间】
set global slow_query_log=ON;【开启慢查询日志】

Durid 连接池

spring.datasource.druid.initial-size=1 初始化连接
spring.datasource.druid.min-idle=1 最小空闲连接
spring.datasource.druid.max-active=30 最大活动连接
spring.datasource.druid.max-wait=60000 从连接池获取连接超时

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
共收到 2 条回复 时间 点赞

很详细,赞。

可以也分享下 mysql 的一些常用监控指标不?一般可能更多是从监控指标看异常,然后再逐步排查到这些参数配置。从监控指标开始实用性更强。

陈恒捷 回复

完全没问题

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册