MySQL数据库监控
2022/8/26 2:22:56
本文主要是介绍MySQL数据库监控，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
MYSQL常用的性能指标
(1) QPS(每秒Query量)
QPS = Questions(or Queries) / seconds
mysql > show global status like 'Question%';
(2) TPS(每秒事务量)
TPS = (Com_commit + Com_rollback) / seconds
mysql > show global status like'Com_commit';
mysql > show global status like'Com_rollback';
(3)key Buffer 命中率
mysql>show global status like 'key%';
key_buffer_read_hits = (1-key_reads /key_read_requests) * 100%
key_buffer_write_hits = (1-key_writes /key_write_requests) * 100%
(4)InnoDB Buffer命中率
mysql> show status like'innodb_buffer_pool_read%';
innodb_buffer_read_hits = (1 -innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%
(5)Query Cache命中率
mysql> show status like 'Qcache%';
Query_cache_hits = (Qcahce_hits /(Qcache_hits + Qcache_inserts )) * 100%;
(6)Table Cache状态量
mysql> show global status like 'open%';
比较 open_tables 与opend_tables 值
(7)Thread Cache 命中率
mysql> show global status like'Thread%';
mysql> show global status like'Connections';
Thread_cache_hits = (1 - Threads_created /connections ) * 100%
(8)锁定状态
mysql> show global status like '%lock%';
Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重
Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的
(9)复制延时量
mysql > show slave status
查看延时时间
(10) Tmp Table 状况(临时表状况)
mysql > show status like 'Create_tmp%';
Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大，
可能是排序句子过多或者是连接句子不够优化
(11) Binlog Cache 使用状况
mysql > show status like'Binlog_cache%';
如果Binlog_cache_disk_use值不为0 ，可能需要调大 binlog_cache_size大小
(12) Innodb_log_waits 量
mysql > show status like'innodb_log_waits';
Innodb_log_waits值不等于0的话，表明 innodblog buffer 因为空间不足而等待
(13)open file and table
mysql> mysql -e "show global statuslike 'Open_files';
mysql> mysql -e "show global statuslike 'Open_tables';
(14) 慢查询
开启慢查询：
编辑/etc/my.cnf，在[mysqld]域中添加：
slow_query_log= 1 # 开启慢查询
slow_query_log_file=/data/mysql/slow.log # 慢查询日志路径
long_query_time= 1 # 慢查询的时长
查看慢查询数量：
执行mysqladminstatus
(15)全日志
查看全日志：
show global variables like 'general_log';
开启全日志：
set global general_log=on;
注意开启全日志会消耗服务器性能，一般只有在排查问题时才会短暂打开。
########### 分割线 ###########
我们需要根据自己的情况进行配置优化，好的做法是MySQL服务器稳定运行了一段时间后，根据服务器的”状态”再进行优化。
数据库性能状态
QueryCache命中率
Query_cache_hits=(Qcahce_hits/(Qcache_hits+Qcache_inserts))*100%;
show status like 'Qcache%';
- QPS(每秒Query量)
QPS=Questions(orQueries)/seconds
show global status like 'Question%';
- TPS(每秒事务量)
TPS=(Com_commit+Com_rollback)/seconds
show status like 'Com_commit';
show status like 'Com_rollback';
(3)keyBuffer命中率
key_buffer_read_hits=(1-key_reads/key_read_requests)*100%
key_buffer_write_hits=(1-key_writes/key_write_requests)*100%
show status like 'key%';
(4)InnoDBBuffer命中率
innodb_buffer_read_hits=(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests)*100%
show status like 'innodb_buffer_pool_read%';
(5)QueryCache命中率
Query_cache_hits=(Qcahce_hits/(Qcache_hits+Qcache_inserts))*100%;
show status like 'Qcache%';
(6)TableCache状态量
show status like 'open%';
(7)ThreadCache命中率
Thread_cache_hits=(1-Threads_created/connections)*100%
show status like 'Thread%';
show status like 'Connections';
(8)锁定状态
show status like '%lock%';
(9)复制延时量
show slave status;
(10)TmpTable状况(临时表状况)
show status like 'Created_tmp%';
(11)BinlogCache使用状况
show status like 'Binlog_cache%';
(12)Innodb_log_waits量
show status like 'innodb_log_waits';
MySQL5.7的新特性之一就是sys库，sys库完成MySQL信息的收集
一、用户、连接类
1、查看每个客户端IP过来的连接消耗资源情况。
select * from sys.host_summary;
total_connections:
current_connections:当前连接数
statement_latency:声明延迟
statement_avg_latency:声明平均延迟
table_scans:扫描查询的总次数
file_ios:
file_io_latency:
current_memory:
total_memory_allocated:
unique_users:
2、查看每个用户消耗资源情况
select * from sys.user_summary;
user:
statements:
statement_latency:
statement_avg_latency:
table_scans:
file_ios:
file_io_latency:
current_connections:
total_connections:
unique_hosts:
current_memory:
total_memory_allocated:
3、查看当前连接情况（有多少连接就应该有多少行）
select host,current_connections,statements from sys.host_summary;
4、查看当前正在执行的SQL
select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session
执行show full processlist的结果
二、SQL 和io类
1、查看发生IO请求前5名的文件。
select * from sys.io_global_by_file_by_bytes order by total desc 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" )
一、对数据库服务可用性进行监控
思路：
1.1 通过测试账号ping命令返回的信息判断数据库可以通过网络连接
/usr/bin/mysqladmin -uroot -p123456 ping
mysqld is alive
1.2 确认数据库是否可读写
a.检查数据库的read_only参数是否为off
mysql -uroot -p123456 -P3306 -e "show global variables like 'read_only'" | grep read_only
read_only OFF
b.执行简单的数据库查询，如：select @@version;
mysql -uroot -p123456 -P3306 -e "select @@version"
二、对数据库性能进行监控
2.1 监控数据库连接数可用性
a.数据库最大连接数
mysql -uroot -p123456 -e "show variables like 'max_connections'"
b.数据库当前打开的连接数
mysqladmin -uroot -p123456 extended-status | grep -w "Threads_connected"
注：如何计算当前打开的连接数占用最大连接数的比例呢？
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 数据库并发请求数量
SHOW GLOBAL STATUS LIKE 'Threads_running';
注：并发请求数量通常会远小于同一时间内连接到数据库的连接数量。
2.4 监控innodb阻塞情况
a. innodb
三、对主从复制进行监控
3.1 主从复制链路状态的监控
3.2 主从复制延迟时间的监控
3.3 定期确认主从复制的数据是否一致
转载：https://www.q578.com/s-5-2839095-0/
这篇关于MySQL数据库监控的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-12-07MySQL读写分离入门：轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南
- 2024-12-07MySQL慢查询入门：快速掌握性能优化技巧
- 2024-12-07MySQL入门：新手必读的简单教程
- 2024-12-07MySQL入门：从零开始学习MySQL数据库
- 2024-12-07MySQL索引入门：新手快速掌握MySQL索引技巧
- 2024-12-06BinLog学习：MySQL数据库BinLog入门教程
- 2024-12-06Binlog学习：MySQL数据库的日志管理入门教程
