MySQL巡检

操作系统层面
- cpu
- 内存
- I/O
- 磁盘
- 系统基础信息
- 操作系统日志
MySQL
- 重点参数
- MySQL的状态
- 库表情况
- MySQL主从检测
- 高可用层面
- 中间件的巡检

操作系统层面

cpu

#如果找不到sar命令，可以安装sysstat包，10s为一个间隔，运行3次

[root@host-172-15-110-233 ~]# sar -u 10 3

内存

[root@host-172-15-110-233 ~]# sar -r 10 3

I/O

[root@host-172-15-110-233 ~]# sar -b 10 3

磁盘

[root@host-172-15-110-233 ~]# df -h

操作系统日志

除此之外，还需要关注日志类信息，例如：

[root@host-172-15-110-233 ~]# tail -n 200 /var/log/messages
[root@host-172-15-110-233 ~]# dmesg | tail -n 200

MySQL

MySQL重点参数的检查，及主从健康状态的巡检。

重点参数

MySQL的状态

mysql> \s
mysql> show full processlist;
mysql> show engine innodb status\G
mysql> show slave hosts;

wait事件

Innodb_buffer_pool_wait_free：标志着脏页有没有成为系统的性能瓶颈；如果值很大，则需要增加innodb_page_cleaners值，同时增加写线程。

1、通常，对于buffer pool的写发生在后台，当innodb需要读或创建一个数据页，但是没有干净的可用页，innodb就会为等待的操作能完成先将一些脏页刷入磁盘。

2、Innodb_buffer_pool_wait_free就是这等待操作的实例数。如果innodb_buffer_pool_size的大小设置适当，这个值就会很小，甚至为0。

mysql> show global status like 'Innodb_buffer_pool_wait_free';
mysql> show global status like  'Innodb_log_waits';

锁

#表锁
# 表示需要等待的表锁数
mysql>show global status like 'Table_locks_immediate';
mysql>show global status like 'Table_locks_waited'; 
# 如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。

#行锁
mysql>show global status like 'Innodb_row_lock_current_waits';#当前等待锁的行锁数量
mysql>show global status like 'Innodb_row_lock_time';#请求行锁总耗时
mysql>show global status like 'Innodb_row_lock_time_avg';#请求行锁平均耗时
mysql>show global status like 'Innodb_row_lock_time_max';#请求行锁最久耗时
mysql>show global status like 'Innodb_row_lock_waits';#行锁发生次数

#还可以定时收集INFORMATION_SCHEMA里面的信息：
mysql>SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
mysql>SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; // MySQL 8.0 中已经不再使用，建议观测 sys 库

#临时表/临时文件
mysql>show global status like 'Created_tmp_disk_tables';
mysql>show global status like 'Created_tmp_files';

#打开表/文件数
mysql>show global status like 'Open_files';
mysql>show global status like 'Open_table_definitions';
mysql>show global status like 'Open_tables';

#并发连接数
mysql>show global status like 'Threads_running';
mysql>show global status like 'Threads_created';
mysql>show global status like 'Threads_cached';
mysql>show global status like 'Aborted_clients';

#客户端没有正确关闭连接导致客户端终止而中断的连接数
mysql>show global status like 'Aborted_connects';

#客户端没有正确关闭连接导致客户端终止而中断的连接数
mysql>show global status like 'Aborted_connects';

Binlog

# 使用临时二进制日志缓存但超过 binlog_cache_size 值，需要使用临时文件存储事务中的语句的事务数
mysql>show global status like 'binlog_cache_disk_use';

# 使用二进制日志缓存的事务数
mysql>show global status like 'binlog_cache_use';

# 使用二进制日志语句缓存但超过 binlog_stmt_cache_size 的值，需要使用临时文件存储这些语句的非事务语句的数量
mysql>show global status like 'binlog_stmt_cache_disk_use';

# 使用二进制日志语句缓存的非事务性语句的数量
mysql>show global status like 'binglog_cache_disk_use';

链接数

#当前连接数
mysql>show global status like 'Threads_connected'; 

#最大连接数
mysql>show global status like 'max_connections'; 

# 试图连接到（不管成不成功）mysql服务器的链接数
mysql>show global status like 'Connections';

临时表

# 服务器执行语句时,在硬盘上自动创建的临时表的数量,是指在排序时,内存不够用(tmp_table_size小于需要排序的结果集)，所以需要创建基于磁盘的临时表进行排序
mysql>show global status like 'Created_tmp_disk_tables';

# 服务器执行语句时自动创建的内存中的临时表的数量
mysql>show global status like 'Created_tmp_files';

索引

# 内部提交语句
mysql>show global status like 'Handler_commit';

# 内部 rollback语句数量
mysql>show global status like 'Handler_rollback';

# 索引第一条记录被读的次数,如果高,则它表明服务器正执行大量全索引扫描
mysql>show global status like 'Handler_read_first';  

# 根据索引读一行的请求数，如果较高，说明查询和表的索引正确
mysql>show global status like 'Handler_read_key';

# 查询读索引最后一个索引键请求数
mysql>show global status like 'Handler_read_last';

# 按照索引顺序读下一行的请求数
mysql>show global status like 'Handler_read_next';

# 按照索引顺序读前一行的请求数
mysql>show global status like 'Handler_read_prev';

# 根据固定位置读一行的请求数，如果值较高，说明可能使用了大量需要mysql扫整个表的查询或没有正确使用索引
mysql>show global status like 'Handler_read_rnd';

# 在数据文件中读下一行的请求数，如果你正进行大量的表扫，该值会较高
mysql>show global status like 'Handler_read_rnd_next';

# 被缓存的.frm文件数量
mysql>show global status like 'Open_table_definitions';

# 已经打开的表的数量,如果较大,table_open_cache值可能太小
mysql>show global status like 'Opened_tables';

# 当前打开的表的数量
mysql>show global status like 'Open_tables';

# 已经发送给服务器的查询个数
mysql>show global status like 'Queries';

# 没有使用索引的联接的数量,如果该值不为0,你应该仔细检查表的所有
mysql>show global status like 'Select_full_join';

# 对第一个表进行完全扫的联接的数量
mysql>show global status like 'Select_scan';

# 查询时间超过long_query_time秒的查询个数
mysql>show global status like 'Slow_queries';

# 排序算法已经执行的合并的数量,如果值较大,增加sort_buffer_size大小
mysql>show global status like 'Sort_merge_passes';

线程

# 线程缓存内的线程数量
mysql>show global status like 'Threads_cached';

# 当前打开的连接数量
mysql>show global status like 'Threads_connected';

# 创建用来处理连接的线程数
mysql>show global status like 'Threads_created';

# 激活的（非睡眠状态）线程数
mysql>show global status like 'Threads_running';

库表情况

自增id使用情况

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

表行数数据大小统计

mysql>SELECT table_schema "Database name", sum( table_rows ) "No. of rows", sum( data_length ) / 1024 / 1024 "Size data (MB)", sum( index_length )/ 1024 / 1024 "Size index (MB)" FROM information_schema.TABLES
GROUP BY table_schema;

表行数 TOP 30

mysql>SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS FROM `information_schema`.`tables` WHERE TABLE_SCHEMA not in('information_schema','sys','mysql','performance_schema') ORDER BY table_rows DESC LIMIT 30;

存储引擎不是innodb的表

mysql>SELECT TABLE_SCHEMA, TABLE_NAME,ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE != 'innodb'  AND TABLE_SCHEMA NOT IN ( "INFORMATION_SCHEMA", "PERFORMANCE_SCHEMA", "MYSQL", "SYS" );

表数据和碎片 TOP 30

mysql>select TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,DATA_FREE from information_schema.tables where DATA_FREE is not null ORDER BY DATA_FREE DESC LIMIT 30;

mysql>select concat('optimize table ',table_schema,'.',table_name,';'),data_free,engine from information_schema.tables where data_free>0 and engine !='MEMORY';

#不同数据库引擎有不同的方式进行优化表碎片
#MylSAM表，通过optimize table 表名的方式 。如果是lnnoDB表，之前执行了delete 命令，用optimize 也可以释放表空间
#lnnoDB表，通过alter table 表名 engine=InnoDB的方式进行优化。（本质上是 recreate）

无主键的表

mysql>SELECT t1.table_schema, t1.table_name, t1.table_type FROM information_schema.TABLES t1 LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA  AND t1.table_name = t2.TABLE_NAME  AND t2.CONSTRAINT_NAME IN ( 'PRIMARY' ) WHERE t2.table_name IS NULL  AND t1.TABLE_SCHEMA NOT IN ( 'information_schema', 'performance_schema', 'test', 'mysql', 'sys' ) AND t1.table_type = "BASE TABLE";

MySQL主从检测

#主从状态
mysql>show slave status\G
#主从是否延迟
Master_Log_File 是否等于 Relay_Master_Log_File
Read_Master_Log_Pos 是否等于 Exec_Master_Log_Pos

posted @ 2024-02-22 14:51 陶清刚阅读(23) 评论(0) 收藏举报

刷新页面返回顶部