MySQL监控
MySQL可用性监控
- MySQL服务存活监控
- MySQL复制是否终止。延迟多大
- 其它,比如磁盘空间消耗
MySQL性能监控
- 每秒活跃DML数/事务数/请求数/当前并发连接/平均响应时长
- 数据库吞吐量(收、发字节数)
- 锁:表锁,行锁,锁等待,死锁
- 内存:buffer/cache命中率、等待释放
- 事务:事务ID增长率,unpurged历史事务
- 慢查询:平均耗时,平均次数
查看MySQL连接数、当前并发连接、最大连接。
mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 58 |
| Threads_connected | 57 | ###这个数值指的是打开的连接数
| Threads_created | 3676 |
| Threads_running | 4 | ###这个数值指的是激活的连接数。这个数值一般远低于connected数值
+-------------------+-------+
Threads_connected 跟show processlist结果同样,表示当前连接数。
准确的来说。Threads_running是代表当前并发数
这是是查询数据库当前设置的最大连接数
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
假设我们在MySQLserver配置文件里设置了thread_cache_size。当客户端断开之后,server处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。
Threads_created表示创建过的线程数。假设发现Threads_created值过大的话。表明MySQLserver一直在创建线程,这也是比較耗资源,能够适当添加配置文件里thread_cache_size值,查询server
thread_cache_size配置:
1mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+
show processlist; 能够显示前100条连接信息
show full processlist; 能够显示所有。
顺便说下。假设用普通账号登录,就仅仅显示这用户的。
锁等待
"root@localhost:mysql.sock [(none)]>show status like 'Innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
Innodb_row_lock_current_waits:当前等待的待锁定的数目(锁的数量,不是锁定的行的数量)。
Innodb_row_lock_waits:一行锁定必须等待的总时长
Table_locks_waited:表锁等待次数
死锁的话。须要找到两条SQL所在的事务,否则基本上无法排查出为什么会有死锁。
偶尔有死锁能够。仅仅要不是太频繁。
更关键的是当前行锁发生的次数。以及当前行锁等待发生的次数。
buffer/cache命中率一般99%以上。
更重要的是看内存够不够用。
怎么看内存够不够用呢?看内存等待释放的指标是不是大于0。Innodb_buffer_pool_wait_free。
查看innodb buffer pool实时状态信息:
mysql> show status like 'Innodb_buffer_pool_%';
+-----------------------------------------+---------------+
| Variable_name | Value |
+-----------------------------------------+---------------+
| Innodb_buffer_pool_pages_data | 999020 |
| Innodb_buffer_pool_pages_dirty | 47643 |
| Innodb_buffer_pool_pages_flushed | 474668167 |
| Innodb_buffer_pool_pages_LRU_flushed | 365125 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_made_not_young | 0 |
| Innodb_buffer_pool_pages_made_young | 203410903 |
| Innodb_buffer_pool_pages_misc | 49552 |
| Innodb_buffer_pool_pages_old | 368697 |
| Innodb_buffer_pool_pages_total | 1048572 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 66348855 |
| Innodb_buffer_pool_read_ahead_evicted | 3716819 |
| Innodb_buffer_pool_read_requests | 3215992991498 |
| Innodb_buffer_pool_reads | 65634998 |
| Innodb_buffer_pool_wait_free | 651 |
| Innodb_buffer_pool_write_requests | 21900970785 |
+-----------------------------------------+---------------+
事务看事务的增长频率。如每秒增长1000,则TPS就是1000。
另外就是看unpurge,undo的情况,避免由于事务长时间未提交导致大量的undo。unpurge。
慢日志要常常分析。
看TPS有几种方法:
1、(Handler_commit_d + Handler_rollback_d)/Uptime_d
2、innodb max trx id增长率
几个wait
Innodb_buffer_pool_wait_free:表示当前buffer pool不够用了,须要其它buffer释放,会产生等待事件。
Innodb_log_waits:表示当前redo log不够用了。
查看MySQL状态(总体状态)
- show [full] processlist
- show [global] status
- show engine innodb status\G
- tail -f slow.log
show [full] processlist:MySQL当前线程运行状态
MySQL锁监控
- 表级锁
- Table_locks_immediate
- Table_locks_waited
- 行级锁
- innodb_row_lock_current_waits 当前等待的行锁数量(不是行的数量,是锁的数量)
- innodb_row_lock_time 请求行锁总耗时(ms)
- innodb_row_lock_time_avg 请求行锁平均耗时(ms)
- innodb_row_lock_time_max 请求行锁最旧耗时(ms)
- innodb_row_lock_waits 行锁发生次数
查看processlist,假设状态为copying to temp table,代表没有索引。
还有看processlist的运行时间,长的话要注意。
看SQL的运行代价:
mysql > flush status;
mysql > select ...;
mysql > show status llike 'handler_read%'
看handler_read_rnd
看handler_read_rnd
看SQL的瓶颈在哪里:(用profiling)
mysql > set profiling=1;
mysql > select ...;
mysql > show profiles;
mysql > show profile for query N;
innodb status
show engine innodb status
活跃事务,活跃了多少秒,多少个行锁,锁了多少行。产生多少undo。
slow log
percona分支版本号会添加一些额外的信息。
等待事件
innodb_buffer_pool_wait_free
innodb_log_waits等
暂时表/暂时文件
created_tmp_disk_tables
created_tmp_files
打开表/文件数
open_files
open_table_definitions
open_tables
Opened_files
Opened_table_definitions
Opened_tables
并发连接
thread_running
thread_created
thread_cached
业务监控
数据库正常。但业务不可用,一点意义都没有。
show global status关键參数解读
- aborted_clients
由于客户端没有正确关闭导致客户端终止而中断的连接数。 aborted_connects
视图连接到MySQLserver而失败的连接数。
aborted_clients记录的是已经建立正常连接后又被异常断开的情形。而aborted_connects记录的是未能正常建立连接的情形。
假设aborted_clients非常高。可能是连接超时时间设置太短了,结果大量连接被自己主动关闭。Binlog_cache_disk_use
使用二进制日志缓存但超过 binlog_cache_size 值并使用暂时文件来保存事务中的语句的事务数量。原因是binlog cache设置的太小。- Binlog_cache_use
使用暂时二进制日志缓存的事务数量。
binlog hit ratio = (Binlog_cache_use)/( Binlog_cache_use + Binlog_cac he_disk_use)*100%
Binlog_stmt_cache_disk_use
The number of nontransaction statements that used the binary log statement cache but that exceeded the value of binlog_stmt_cache_size and used a temporary file to store those statements.
当非事务语句使用二进制日志缓存。可是超出 binlog_stmt_cache_size 时,使用一个暂时文件来存放这些语句。 (innodb的binlog存放在binlog cache中。非事务引擎的binlog存放在binlog_stmt_cache中)Connections
试图连接到(无论是否成功)MySQL server的连接数。Created_tmp_disk_tables
server运行语句时在硬盘上自己主动创建的暂时表的数量。是指在排序时。内存不够 用(tmp_table_size 小于须要排序的结果集),所以须要创建基于磁盘的暂时表进 行排序。
created_tmp_disk_tables/(created_tmp_disk_tables + created_tmp_tables) *100% >10%的话,须要适当提高tmp_table_size的大小,可是不能设置太大,由于它是每一个session都会分配的,可能会导致OOM。Handler_commit
内部提交语句数。
一次 update、delete 后,handler_commit 也是添加 2。一次 select 后,handler_commit 添加 1。
Handler_rollback
内部 ROLLBACK 语句的数量。Handler_read_first
索引中第一条记录被读的次数。假设较高,它表明server正运行大量全索引扫 ; 比如。SELECT * order by id。假定 id 列有索引。Handler_read_key
依据索引读一行的请求数。假设较高。说明查询和表的索引正确。 比如:select … where id = 1024;Handler_read_last
查询读索引最后一个索引键的请求数。当使用 ORDER BY 时。server优先发出使 用第一个索引的请求,之后顺序往后扫 索引。当使用 ORDER BY DESC 时,服务 器优先发出使用最后一个索引的请求。 之后向前扫 索引。
比如:select … order by id desc limit 10;Handler_read_next
依照索引顺序读下一行的请求数。假设你用范围约束或假设运行索引扫 来查询 索引列,该值添加。
比如:select id from t where id &get;= 1024 order by id limit 5;Handler_read_prev
依照索引顺序读前一行的请求数。该读方法主要用于优化 ORDER BY … DESC 比如:select id from t where id &get;= 1024 order by id DESC limit 5;
Handler_read_rnd
依据固定位置读一行的请求数。假设你正运行大量查询并须要对结果进行排序该 值较高,说明可能使用了大量须要 MySQL 扫 整个表的查询或没有正确使用索 引。
比如:select * from t limit 1000,1;
或者:select * from t order by non_key_col limit 100,1;Handler_read_rnd_next
在数据文件里读下一行的请求数。假设你正进行大量的表扫 。该值会较高。通 常说明你的表索引不对或写入的查询没有利用索引。
比如:select * from t order by non_key_col limit 100;Innodb_buffer_pool_wait_free
普通情况。通过后台向 Innodb buffer pool 写。可是,假设须要读或创建页,而且 没有干净的页可用。则它还须要先等待页面清空。该计数器对等待实例进行记数。 假设已经适当设置 Innodb buffer pool 大小,该值应小。
Innodb_log_waits
我们必须等待的时间。由于日志缓冲区太小,我们在继续前必须先等待对它清空。Innodb_row_lock_current_waits
当前等待的待锁定的行数。Innodb_row_lock_time
行锁定花费的总时间。单位毫秒。Innodb_row_lock_time_avg
行锁定的平均时间,单位毫秒。Innodb_row_lock_time_max
行锁定的最长时间,单位毫秒。这个值太大的话,能够考虑调低 innodb_lock_wait_timeout 值
Innodb_row_lock_waits
行锁发生次数。Open_table_definitions
The number of cached .frm files. 被缓存的.frm 文件数量。Opened_table_definitions
The number of .frm files that have been cached. 被缓存过的.FRM 文件的数量。 这个值假设远大于Open_table_definitions,说明说明table definition cache不够用。Open_tables
当前打开的表的数量Opened_tables
已经打开的表的数量。假设 Opened_tables 较大,table_open_cache 值可能太小。这个值假设远大于Open_tables,说明说明table open cache不够用。
Queries
已经发送给server的查询的个数。Questions
发送到server的请求次数(包括正常SQL查询,以及连接、set等所有请求。
QPS = Questions_d / Uptime_d
Select_full_join
没有使用索引的联接的数量。假设该值不为 0,你应细致检查表的索引。Select_scan
对第一个表进行全然扫 的联接的数量。- Slow_queries
查询时间超过 long_query_time 秒的查询的个数 - Sort_merge_passes
排序算法已经运行的合并的数量。假设这个变量值较大。应考虑添加 sort_buffer_size 系统变量的值。 - threads_connected
当前打开的连接的数量 - threads_created
创建用来处理连接的线程数。假设threads_created较大。你可能要添加threads_cache_size值。
缓存訪问率的计算方法:
threads_creates/Connections - threads_running
激活的(非睡眠状态)线程数
Threads_connected 假设比 Threads_created 小非常多。或者Threads_cached 和 *Threads_created 相比小非常多,那就须要加大 thread cache size。
innodb status解读
查看锁信息
—–5.7版本号
select * from sys.innodb_lock_waits\G
—–5.6版本号
information_schema以下的
innodb_trx
innodb_locks
innodb_lock_waits
SELECT lw.requesting_trx_id AS request_ID,
trx.trx_mysql_thread_id as request_mysql_ID,
trx.trx_query AS request_command,
lw.blocking_trx_id AS blocking_ID,
trx1.trx_mysql_thread_id as blocking_mysql_ID,
trx1.trx_query AS blocking_command,
lo.lock_index AS lock_index
FROM information_schema.innodb_lock_waits lw
INNER JOIN information_schema.innodb_locks lo
ON lw.requesting_trx_id = lo.lock_trx_id
INNER JOIN information_schema.innodb_locks lo1
ON lw.blocking_trx_id = lo1.lock_trx_id
INNER JOIN information_schema.innodb_trx trx
ON lo.lock_trx_id = trx.trx_id
INNER JOIN information_schema.innodb_trx trx1
ON lo1.lock_trx_id = trx1.trx_id;