MySQL监控指标及采集方法

MySQL5.7的新特性中，非常突出的特性之一就是sys库，不仅可以通过sys库完成MySQL信息的收集，还可以用来监控和排查问题。下面介绍一些常用的用法。

---

一、用户、连接类

1、查看每个客户端IP过来的连接消耗资源情况。

select * from sys.host_summary;

2、查看每个用户消耗资源情况

select * from sys.user_summary;

3、查看当前连接情况（有多少连接就应该有多少行）

select host,current_connections,statements from sys.host_summary;

4、查看当前正在执行的SQL

和执行show full processlist的结果差不多

select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session

---

二、SQL 和io类

1、查看发生IO请求前5名的文件。

select * from sys.io_global_by_file_by_bytes order by total limit 5;

---

三、buffer pool 、内存

1、查看总共分配了多少内存

select * from sys.memory_global_total;select * from sys.memory_global_by_current_bytes;

2、每个库（database）占用多少buffer pool

select * from sys.innodb_buffer_stats_by_schema order by allocated desc;

pages是指在buffer pool中的page数量；pages_old指在LUR 列表中处于后37%位置的page。

当出现buffer page不够用时，就会征用这些page所占的空间。37%是默认位置，具体可以自定义。

3、统计每张表具体在InnoDB中具体的情况，比如占多少页？

注意和前面的pages的总数都是相等的，也可以借用sum（pages）运算验证一下。

select * from sys.innodb_buffer_stats_by_table;

4、查询每个连接分配了多少内存

利用session表和memory_by_thread_by_current_bytes分配表进行关联查询。

SELECT b.USER, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated, current_statement FROM sys.memory_by_thread_by_current_bytes a, sys.SESSION b WHERE a.thread_id = b.thd_id;

---

四、字段、索引、锁

1、查看表自增字段最大值和当前值，有时候做数据增长的监控，可以作为参考。

select * from sys.schema_auto_increment_columns;

2、MySQL索引使用情况统计

select * from sys.schema_index_statistics order by rows_selected desc;

3、MySQL中有哪些冗余索引和无用索引

若库中展示没有冗余索引，则没有数据；当有联合索引idx_abc(a,b,c)和idx_a(a)，那么idx_a就算冗余索引了。

select * from sys.schema_redundant_indexes;

4、查看INNODB 锁信息

在未来的版本将被移除，可以采用其他方式

select * from sys.innodb_lock_waits

5、查看库级别的锁信息，这个需要先打开MDL锁的监控：

--打开MDL锁监控update performance_schema.setup_instruments set enabled='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl';select * from sys.schema_table_lock_waits;

---

五、线程类

1、MySQL内部有多个线程在运行，线程类型及数量

select user,count(*) from sys.`processlist` group by user;

---

六、主键自增

查看MySQL自增id的使用情况

SELECT table_schema, table_name, ENGINE, Auto_increment FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ( "INFORMATION_SCHEMA", "PERFORMANCE_SCHEMA", "MYSQL", "SYS" )

背景：线上生产环境MySQL的架构是一主双从，为了更好了解MySQL集群运行状况，我们需要对以下指标进行监控！

一、对数据库服务可用性进行监控

思路：

1.1 通过测试账号ping命令返回的信息判断数据库可以通过网络连接

[root@host-39-108-217-12 scripts]# /usr/bin/mysqladmin -uroot -p123456 ping

mysqld is alive

1.2 确认数据库是否可读写

a.检查数据库的read_only参数是否为off

[root@host-47-106-141-17 scripts]# mysql -uroot -p123456 -P3306 -e "show global variables like 'read_only'" | grep read_only

read_only OFF

b.执行简单的数据库查询，如：select @@version;

[root@host-47-106-141-17 scripts]# mysql -uroot -p123456 -P3306 -e "select @@version" | grep MariaDB

5.5.56-MariaDB

二、对数据库性能进行监控

2.1 监控数据库连接数可用性

a.数据库最大连接数

[root@host-47-106-141-17 scripts]# mysql -uroot -p123456 -e "show variables like 'max_connections'"

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| max_connections | 151 |

+-----------------+-------+

b.数据库当前打开的连接数

[root@host-47-106-141-17 scripts]# mysqladmin -uroot -p123456 extended-status | grep -w "Threads_connected"

| Threads_connected | 1 |

注：如何计算当前打开的连接数占用最大连接数的比例呢？

result=Threads_connected/max_connections,在做监控报警或可视化监控时能够很好的根据这个比例及时调整最大连接数。

2.2 数据库性能监控

a.QPS:每秒的查询数

QPS计算方法

Questions = SHOW GLOBAL STATUS LIKE 'Questions';

Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';

QPS=Questions/Uptime

b.TPS:每秒的事物量（commit与rollback的之和）

TPS计算方法

Com_commit = SHOW GLOBAL STATUS LIKE 'Com_commit';

Com_rollback = SHOW GLOBAL STATUS LIKE 'Com_rollback';

Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';

TPS=(Com_commit + Com_rollback)/Uptime

2.3 数据库并发请求数量

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Threads_running';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| Threads_running | 3 |

+-----------------+-------+

1 row in set (0.00 sec)

注：并发请求数量通常会远小于同一时间内连接到数据库的连接数数量。

2.4 监控innodb阻塞情况

a. innodb

三、对主从复制进行监控

3.1 主从复制链路状态的监控

3.2 主从复制延迟时间的监控

3.3 定期确认主从复制的数据是否一致

————————————————

转自：MySQL监控指标及采集方法_Data & Analysis-CSDN博客

mysql DBA巡检技巧--通过sys库完成数据库信息的收集及监控​www.szzyqc666.com/jishu/29950.html正在上传…重新上传取消

如何定位问题SQL

概述

如果是Oracle数据库我们可以很容易通过sql来定位到当前数据库中哪些消耗CPU高的语句，而mysql数据库可以怎么定位呢？这里用一个简单例子说明下...

主要是了解如何定位的思路，具体看官网介绍..

参考：

https://www.percona.com/blog/2020/04/23/a-simple-approach-to-troubleshooting-high-cpu-in-mysql/

主要意思是针对定位CPU的问题，Percona增加了对通过信息的TID列将processlist ID映射到OS线程ID的支持，而MySQL在5.7版本后在PERFORMANCE_SCHEMA.THREADS表加了一个THREAD_OS_ID新列来实现，以下方法适用于在其他内核正常运行时，某个特定CPU的查询过载的情况。

find out which session is using the most CPU resources in my database?

核心思想：先找数据库相关的异常进程号，再通过MySQL进程号查找异常线程号，再根据异常线程号查找异常会话号，然后再查找会话号中的具体SQL，再利用explain定位具体SQL问题

定位线程

pidstat 的用法：
pidstat [ 选项 ] [ <时间间隔> ] [ <次数> ]

常用的参数：

• -u：默认的参数，显示各个进程的cpu使用统计
• -r：显示各个进程的内存使用统计
• -d：显示各个进程的IO使用情况
• -p：指定进程号
• -w：显示每个进程的上下文切换情况
• -t：显示选择任务的线程的统计信息外的额外信息
• -T { TASK | CHILD | ALL }
  这个选项指定了pidstat监控的。TASK表示报告独立的task，CHILD关键字表示报告进程下所有线程统计信息。ALL表示报告独立的task和task下面的所有线程。
  注意：task和子线程的全局的统计信息和pidstat选项无关。这些统计信息不会对应到当前的统计间隔，这些统计信息只有在子线程kill或者完成的时候才会被收集。
• -V：版本号
• -h：在一行上显示了所有活动，这样其他程序可以容易解析。
• -I：在SMP环境，表示任务的CPU使用率/内核数量
• -l：显示命令名和所有参数

pidstat -t -p <mysqld_pid> 1 5
pidstat是sysstat工具的一个命令，用于监控全部或指定进程的cpu、内存、线程、设备IO等系统资源的占用情况。

图片

通过该命令我们可以定位到**「802、4445等线程消耗了大量的CPU」**，这里尽量确保在pidstat的多个样本中验证消耗是恒定的。根据这些信息，我们可以登录到数据库，并使用以下查询找出哪个MySQL线程是罪魁祸首。

定位问题sql

select * from performance_schema.threads where thread_os_id = xx ;  
------更新线程号查找异常的会话号

PROCESSLIST_ID
对于前台线程（与用户连接相关联），这是连接标识符。这与显示在表ID 列中INFORMATION_SCHEMA PROCESSLIST、显示在输出Id列中 SHOW PROCESSLIST以及由CONNECTION_ID()线程内的函数返回的 值相同。

图片

select * from information_schema.`PROCESSLIST` where id=threads.processlist_id -------根据连接会话号查找SQL

该PROCESSLIST表具有以下列：

• ID
  连接标识符。这与显示在语句Id列中 SHOW PROCESSLIST、显示在PROCESSLIST_IDPerformance Schemathreads 表列中以及由CONNECTION_ID()线程内的函数返回的 值相同。
• USER
  发布声明的 MySQL 用户。值是 system user指由服务器生成的用于在内部处理任务的非客户端线程，例如，延迟行处理程序线程或副本主机上使用的 I/O 或 SQL 线程。对于system user，Host列中没有指定主机。 unauthenticated user指已与客户端连接相关联但尚未进行客户端用户身份验证的线程。 event_scheduler是指监视调度事件的线程。
• HOST
  发出语句的客户端的主机名（除了 system user没有主机的 ）。TCP/IP 连接的主机名以 格式报告， 以便更容易确定哪个客户端在做什么。 host_name:client_port
• DB
  线程的默认数据库，或者 NULL如果没有选择。
• COMMAND
  线程代表客户端执行的命令类型，或者Sleep会话是否空闲。此列的值对应于客户端/服务器协议和状态变量的 命令 。
• TIME
  线程处于当前状态的时间（以秒为单位）。对于副本 SQL 线程，该值是上次复制事件的时间戳与副本主机的实时时间之间的秒数。
• STATE
  指示线程正在执行的操作的操作、事件或状态。
  大多数状态对应于非常快速的操作。如果一个线程在给定状态下停留了很多秒，那么可能存在需要调查的问题。
• INFO
  线程正在执行的语句，或者 NULL如果它没有执行任何语句。该语句可能是发送到服务器的语句，或者如果语句执行其他语句，则是最里面的语句。例如，如果CALL语句执行正在执行语句的存储过程，则 SELECT该 INFO值显示该 SELECT语句。

根据操作系统id可以到processlist表找到对应的会话，如下：

图片

查看问题sql执行计划

这里对应看一下执行计划基本就可以判断当前数据库CPU为什么消耗这么高了...

至于优化的点只需要在dock建一个索引即可，这里就不介绍了。

图片