MySQL 运行状态监控方法

最新推荐文章于 2024-09-13 11:25:40 发布

weixin_33753845

最新推荐文章于 2024-09-13 11:25:40 发布

阅读量692

点赞数

文章标签：数据库 python shell

原文链接：https://my.oschina.net/peakfang/blog/2876327

版权

2019独角兽企业重金招聘Python工程师标准>>>

一、通过shell脚本监控mysql的qps,tps,io详细见附件脚本《mysqlgather.sh》
因在脚本中直接设置密码会提示告警信息，需要在my.cnf文件中的[client]标签下增加默认的用户名和密码，并重启mysql服务器；
[client]
socket = /mysqldb/sockandpid/mysql.sock
user ="root"
password="Aa123456789"
1、关注MYSQL三个方面的性能指标，分别为query数，transaction数,io请求数
2、在某个时间范围内（例如20秒)，统计MYSQL中上面的三个指标的总量，以及每一秒的量 ,同时每隔一秒种打印一个当前的指标量，在最后再计算并打印时间段内总量及每秒量
3、在IO的统计公式如下：
Key_reads * 2 + Key_writes * 2 + Key_read_requests + Innodb_data_reads + Innodb_data_writes + Innodb_dblwr_writes + Innodb_log_writes （该公式最早从taobaodba.com这个网上传出，这里学习借鉴）key_reads 和 key_writes都乘以2的原因是因为如果从磁盘中读或写索引块之后会再去磁盘读或写数据块，所以就会有至少两倍的IO请求.事务数总量为 com_commit+com_rollbackquery数通过 com_select获取
4、虽然统计的时间范围可以自己指定（例如20秒)，但最终计算每秒的指标量时，是以脚本开始执行时间至脚本执行结束时mysql uptime的时间差来算的，因为脚本执行会消耗一定时间，这样，实际的时间会多于我们指定的时间(结束时的量-开始时的量)/(结束时uptime-开始时uptime）

二、通过mysqladmin和awk命令进行监控
使用mysqladmin extended-status命令可以获得所有MySQL性能指标，即show global status的输出，不过，因为多数这些指标都是累计值，如果想了解当前的状态，则需要进行一次差值计算，这就是mysqladmin extended-status的一个额外功能，非常实用。
默认的，使用extended-status，看到也是累计值，但是，加上参数-r(--relative)，就可以看到各个指标的差值，配合参数-i(--sleep)就可以指定刷新的频率
《mysqladminextend.sh》
[root@mysqlnode02 ~]# chmod +x mysqladminextend.sh
[root@mysqlnode02 ~]# ./mysqladminextend.sh
------------------------------------------------------------------------------------------------------------------------------------
Time-----| QPS | Commit Rollback TPS | select insert update delete | read inserted updated deleted | logical physical | Tcon Trun
------------------------------------------------------------------------------------------------------------------------------------
17:08:47 | 1 | 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 | 1 1
17:08:57 | 1 | 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 | 1 1

三、mysql常用监控指标
在my.cnf文件中配置了客户端的用户名和密码后，直接执行以下命令不用登录mysql
[root@mysqlnode02 ~]# mysql -uroot -e "show status like 'Threads_running';"
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 1 |
+-----------------+-------+
[root@mysqlnode02 ~]# mysql -uroot -e "show status like 'Handler%';"

1、MySQL是否可用
[root@mysqlnode02 ~]# ps -ef | grep mysql
[root@mysqlnode02 ~]# netstat -nltp| grep 3306
2、数据库用户及数据库足够安全
是否存在很多“host”为“%”的用户，是否必要这些用户；
是否grant all on*.*给了用户，这些用户是否有必要拥有这些权限；
什么样的用户该赋予哪些权限能从哪些地方接入都是要慎重考虑的，不要为了一时方便而给别有用心之人留下可乘之机！
select * from mysql.user;
3、连接数是否正常
来查看连接数，如果是root用户将会看到所有的连接而其它用户只能看到自己占用的连接
mysql> show full processlist;
查看当前失败连接数
mysql> show global status like 'aborted_connects';
查看有多少由于客户没有正确关闭连接而死掉的连接数
mysql> show global status like 'aborted_clients';
查看最大连接数
mysql> show global variables like 'max_connections';
mysql> show global status like 'max_connections';
4、错误日志
错误日志不止包含了MySQL服务在启动和停止过程中的相关信息，它还包括服务运行过程中一些重要（危险）的信息。特别是日志中带有[error]的信息应得到特殊的照顾！
mysql> show variables like 'log_error';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| log_error | /mysqldb/errorlog/error.log |
+---------------+-----------------------------+
[root@mysqlnode02 ~]# cat /mysqldb/errorlog/error.log | grep -i error | grep -v SSL
5、Innodb死锁
当遇到死锁时Innodb会回滚事务。了解死锁何时发生对于追溯其发生的根本原因非常重要。我们必须知道产生了什么样的死锁，相关应用是否正确处理或已采取了相关措施。可以通过查看死锁相关状况
mysql> show engine innodb status\G
mysql> show status like '%lock%';
6、服务配置文件最近是否有更改
配置文件对MySQL服务来说是至关重要的，一般不要轻易更改！在改之前最好先备份一份，改动的地方要做好注释（顺便提一句，在备份数据库的时候别忘记备份配置文件！）。
7、慢查询日志
慢查询日志对SQL调优来说是非常重要的，它记录了超过指定时间（long_query_time）的查询语句；一般只在需要时开启。
8、从服务器落后了多少
MySQL的主从复制在生产环境中经常用到，从服务器能跟上主服务器的步伐么？落后的时间（seconds_behind_master）是否在安全范围内？可以通过查看从服务器当前状态
mysql> show slave status;
9、全表扫描比例
计算方式如下((Handler_read_rnd_next+Handler_read_rnd)/(Handler_read_rnd_next+Handler_read_rnd+Handler_read_first+Handler_read_next+Handler_read_key+Handler_read_prev))对于全表扫描对性能的影响我想大家都比我清楚得多！我们可以通过取得相关参数的值再进行计算。我们应该知道为什么会产生全表扫描及是否有必要做出相应的调整。
mysql> show global status like 'Handler_read%';
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Handler_read_first | 14 |
| Handler_read_key | 37 |
| Handler_read_last | 0 |
| Handler_read_next | 26 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 149759 |
+-----------------------+--------+
7 rows in set (0.01 sec)