作者:王向
爱可生 DBA 团队成员,负责公司 DMP 产品的运维和客户 MySQL 问题的解决。善于数据库故障解决。对数据库技术和 python 有着浓重的趣味。
本文起源:原创投稿
* 爱可生开源社区出品,原创内容未经受权不得随便应用,转载请分割小编并注明起源。
MySQL 巡检
操作系统层面
巡检嘛没啥特地的,就直奔主题把。
cpu
sar -u 10 3
内存
sar -r 10 3
I/O
sar -b 10 3
磁盘
df -h
零碎根底信息
当然,查看是否应用 numa 和 swap,或是否频繁交互信息等。还有其余的监控我的项目,这里就不一一赘述了。
操作系统日志
除此之外,还须要关注日志类信息,例如:
tail 200 /var/log/messages
dmesg | tail 200
MySQL
MySQL 重点参数的查看,及主从衰弱状态的巡检。
重点参数
参数 | 参考值 |
---|---|
innodb_buffer_pool_size | 零碎的 50%-75% |
binlog_format | ROW |
sync_binlog | 1 |
innodb_flush_log_at_trx_commit | 1 |
read_only | 从库 ON,主库 OFF |
super_read_only | 从库 ON,主库 OFF |
log_slave_updates | 1 |
innodb_io_capacity | sata/sas 硬盘这个值在 200<br/>sas raid10: 2000<br/>ssd 硬盘:8000<br/>fusion-io(闪存卡):25,000-50,000 |
max_connections |
MySQL 的状态
\s
show full processlist;
show engine innodb status\G
show slave hosts;
wait 事件
show global status like 'Innodb_buffer_pool_wait_free';
show global status like 'Innodb_log_waits';
锁
# 表锁
show global status like 'Table_locks_waited';
show global status like 'Table_locks_immediate';
#行锁
show global status like 'Innodb_row_lock_current_waits'; 以后期待锁的行锁数量
show global status like 'Innodb_row_lock_time'; 申请行锁总耗时
show global status like 'Innodb_row_lock_time_avg'; 申请行锁均匀耗时
show global status like 'Innodb_row_lock_time_max'; 申请行锁最久耗时
show global status like 'Innodb_row_lock_waits'; 行锁产生次数
#还能够定时收集 INFORMATION_SCHEMA 外面的信息:SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
#长期表 / 临时文件
show global status like 'Created_tmp_disk_tables';
show global status like 'Created_tmp_files';
#关上表 / 文件数
show global status like 'Open_files';
show global status like 'Open_table_definitions';
show global status like 'Open_tables';
#并发连接数
show global status like 'Threads_running';
show global status like 'Threads_created';
show global status like 'Threads_cached';
show global status like 'Aborted_clients';
#客户端没有正确敞开连贯导致客户端终止而中断的连接数
show global status like 'Aborted_connects';
Binlog
# 应用长期二进制日志缓冲但超过 binlog_cache_size 值并应用临时文件
show global status like 'Binlog_cache_disk_use';
# 应用长期二进制日志缓冲的事务数量
show global status like 'Binlog_cache_use';
# 当非事务语句应用二进制日志缓存
show global status like 'Binlog_stmt_cache_disk_use';
# 应用二进制日志缓冲非事务语句数量
show global status like 'Binlog_cache_disk_use';
链接数
# 试图连贯到(不论成不胜利)mysql 服务器的链接数
show global status like 'Connection';
长期表
# 服务器执行语句时, 在硬盘上主动创立的长期表的数量, 是指在排序时, 内存不够用 (tmp_table_size 小于须要排序的后果集),所以须要创立基于磁盘的长期表进行排序
show global status like 'Created_tmp_disk_tables';
# 服务器执行语句时主动创立的内存中的长期表的数量
show global status like 'Created_tmp_files';
索引
# 外部交语句
show global status like 'Handler_commit';
# 外部 rollback 语句数量
show global status like 'Handler_rollback';
# 索引第一条记录被读的次数, 如果高, 则它表明服务器正执行大量全索引扫描
show global status like 'Handler_read_first';
# 依据索引读一行的申请数,如果较高,阐明查问和表的索引正确
show global status like 'Handler_read_key';
# 查问读索引最初一个索引键申请数
show global status like 'Handler_read_last';
# 依照索引程序读下一行的申请数
show global status like 'Handler_read_next';
# 依照索引程序读前一行的申请数
show global status like 'Handler_read_prev';
# 依据固定地位读一行的申请数,如果值较高,阐明可能应用了大量须要 mysql 扫整个表的查问或没有正确应用索引
show global status like 'Handler_read_rnd';
# 在数据文件中读下一行的申请数,如果你正进行大量的表扫,该值会较高
show global status like 'Handler_read_rnd_next';
# 被缓存的.frm 文件数量
show global status like 'Open_table_definitions';
# 曾经关上的表的数量, 如果较大,table_open_cache 值可能太小
show global status like 'Opened_tables';
# 以后关上的表的数量
show global status like 'Open_tables';
# 曾经发送给服务器的查问个数
show global status like 'Queries';
# 没有应用索引的联接的数量, 如果该值不为 0, 你应该仔细检查表的所有
show global status like 'Select_full_join';
# 对第一个表进行齐全扫的联接的数量
show global status like 'Select_scan';
# 查问工夫超过 long_query_time 秒的查问个数
show global status like 'Slow_queries';
# 排序算法曾经执行的合并的数量, 如果值较大, 减少 sort_buffer_size 大小
show global status like 'Sort_merge_passes';
线程
# 线程缓存内的线程数量
show global status like 'Threads_cached';
# 以后关上的连贯数量
show global status like 'Threads_connected';
# 创立用来解决连贯的线程数
show global status like 'Threads_created';
# 激活的(非睡眠状态)线程数
show global status like 'Threads_running';
库表状况
自增 id 应用状况
SELECT
table_schema,
table_name,
ENGINE,
Auto_increment
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA NOT IN (
"INFORMATION_SCHEMA",
"PERFORMANCE_SCHEMA",
"MYSQL",
"SYS") limit 30;
表行数数据大小统计
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
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 的表
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
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;
无主键的表
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 主从检测
# 主从状态
show slave status\G
#主从是否提早
Master_Log_File == Relay_Master_Log_File
&& Read_Master_Log_Pos == Exec_Master_Log_Pos
高可用层面
MHA && keepalived
察看日志看是否有频繁主从切换,如果有的话就剖析一下是什么起因导致频繁切换?
中间件的巡检
mycat && proxysql
这些中间件的巡检,首先参考零碎巡检,再看一下中间件自身的日志类和状态类信息,网络提早或丢包的查看,也是必须要做工作。