乐趣区

Show-proceslist时发现大量的sleep有什么风险吗该如何处理

目录

  • Show proceslist 时发现大量的 sleep,有什么风险吗,该如何处理?
  • MySQL 如何对只对个别列授权?
  • 如何正确监控 MySQL 主从复制延迟(请考虑 5.6 版本前后区别,即并行复制及 GTID 等因素)?

一、Show proceslist 时发现大量的 sleep,有什么风险吗,该如何处理?

答:

(一)可能的风险有:

1、大量 sleep 线程会占用连接数,当超过 max_connections 后,新连接无法再建立,业务不可用;

2、这些 sleep 线程中,有些可能有未提交事务,可能还伴随着行锁未释放,有可能会造成严重锁等待;

3、这些 sleep 线程中,可能仍有一些内存未释放,数量太多的话,是会消耗大量无谓的内存的,影响性能。

(二)建议应对措施:

1、升级到 5.7 及以上版本,连接性能有所提升;

2、采用 MariaDB/Percona 版本,根据情况决定是否启用 thread pool 功能;

3、适当调低 wait_timeout/interactive_timeout 值,例如只比 java 连接池的 timeout 时间略高些即可;

4、利用 pt-kill 或辅助脚本 / 工具巡查并杀掉无用 sleep 进程;

5、利用 5.7 的新特性,适当设置 max_execution_time 阈值,消除长时间执行的 SQL;

6、定期检查 show processlist 的结果,找到长时间 sleep 的线程,根据 host&port 反推找到相关应用负责人,协商优化方案。

二、MySQL 如何对只对个别列授权?

答:其实挺简单的,用 GRANT 授权即可,例如:

GRANT SELECT (c1), INSERT (c1, c2) ON zhishutang.yewen TO 'yewen'@'zhishutang.com';

三、如何正确监控 MySQL 主从复制延迟(请考虑 5.6 版本前后区别,即并行复制及 GTID 等因素)?

答:基于 Binlog 和 Postion 复制

1、通过观察 io 线程减去 sql 线程对比的方式对比:Master_Log_File == Relay_Master_Log_File && Read_Master_Log_Pos == Exec_Master_Log_Pos

基于 GTID 复制

2、通过接受事务数减去已经执行事务数对比:Retrieved_Gtid_Set == Executed_Gtid_Set

基于并行复制

3、先通过 P_S 库 replication_applier_status_by_coordinatorreplication_applier_status_by_worker表来观察每个复制线程的状态,后配合 postion 复制或 GTID 复制方法来监控复制延迟。

4、可以采用 pt-heartbeat 工具。


公众号:知数堂,更多 MySQL 干货知识,关注公众号获取。

原文链接:https://zhishutang.com/BAT
推荐阅读:https://zhishutang.com/xdI

退出移动版