MySql日常监控运营系统状况

MySql日常监控运营系统状况

作者官方网站:http://www.wxl568.cn

1. 查看事务隔离级别
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
2. 设置隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ |
SERIALIZABLE}
例如：set session transaction isolation level read uncommitted;
3. 查看auto_increment机制模式
show variables like ‘innodb_autoinc_lock_mode’;
4. 查看表状态
show table status like ‘plan_branch’\G;
show table status from test like ‘plan_branch’\G;
5. 查看SQL性能
show profiles
show profile for query 1;
6. 查看当前最新事务ID
每开启一个新事务，记录当前最新事务的id，可用于后续死锁分析。
show engine innodb status\G;
7. 查看事务锁等待状态情况
select from information_schema.innodb_locks;
select from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;
show status like 'innodb_row_lock%';
show variables like ‘innodb_locks_unsafe_for_binlog’; （是否开启间隙锁）
8. 查看innodb状态(包含最近的死锁日志)
show engine innodb status;