MYSQL常用的监控命令

最新推荐文章于 2024-06-16 11:48:42 发布

李大川的博客

最新推荐文章于 2024-06-16 11:48:42 发布

阅读量532

点赞数

文章标签：数据库 sql

本文链接：https://blog.csdn.net/liys0811/article/details/132148894

版权

1.查看innodb_trx中处于running事务的个数
SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;
running
lock_wait
rolling_back
committing

2.查看数据库大小
select truncate(sum(data_length)/1024/1024/1024,2) + truncate(sum(index_length)/1024/1024/1024,2) as data_size from information_schema.tables;

3.查看每个IP连接数据库的数量
select substring_index(host, ':',1) ip, count(*) from information_schema.processlist group by db, ip;

4.锁等时长超过30秒的会话个数
select count(*) from sys.innodb_lock_waits where wait_age_secs>30;

5.10分钟未提交的事务
select count(*) from information_schema.innodb_trx where trx_is_read_only = 0 and trx_started <= subdate(now(),interval 10 minute);

6.执行时间超过3小时的会话个数
select count(*) from information_schema.processlist where user not in ('system user','repl') and time > 10800 and command not in ('Binlog Dump GTID','Binlog Dump','Sleep','Daemon','Time');