mysql 运维常用命令

【配置的确认与修改】

要测试InnoDB的锁互斥，以及死锁，有几个配置务必要提前确认：

区间锁是否关闭

事务自动提交(auto commit)是否关闭

事务的隔离级别(isolation level)

这几个参数，会影响实验结果。

【事务的隔离级别】辅助材料：

《事务的隔离级别，InnoDB如何实现？》

间隙锁是否关闭

区间锁(间隙锁，临键锁)是InnoDB特有施加在索引记录区间的锁，MySQL5.6可以手动关闭区间锁，它由innodb_locks_unsafe_for_binlog参数控制：

设置为ON，表示关闭区间锁，此时一致性会被破坏（所以是unsafe）

设置为OFF，表示开启区间锁

可以这么查询该参数：

show global variables like "innodb_locks%";

【间隙锁，临键锁】辅助材料：

《InnoDB，索引记录上的三种锁》

事务自动提交

MySQL默认把每一个单独的SQL语句作为一个事务，自动提交。

可以这么查询事务自动提交的参数：

show global variables like "autocommit";

事务的隔离级别

不同事务的隔离级别，InnoDB的锁实现是不一样。

可以这么查询事务的隔离级别：

show global variables like "tx_isolation";

可以这么设置事务的隔离级别：

set session transaction isolation level X;

X取：

read uncommitted

read committed

repeatable read

serializable

图片

这三个参数，MySQL5.6的默认值如上：

OFF，表示使用区间锁

On，表示事务自动提交

RR，事务隔离级别为可重复读

要模拟并发事务，需要修改事务自动提交这个选项，每个session要改为手动提交。

任何连上MySQL的session，都要手动执行：

set session autocommit=0;

以手动控制事务的提交。

图片

如上图，需要把session的autocommit设置为OFF。

可以看到，修改session变量，并不影响global变量，全局其他的session仍然是ON。

画外音：session变量默认继承global变量，也可以单独修改。

【查看死锁日志和内存使用情况 注意 是 innodb引擎】

show engine innodb status

运行中的mysql状态查看 QPS/TPS 缓存命中率

对正在运行的mysql进行监控，其中一个方式就是查看mysql运行状态。

(1)QPS(每秒Query量)
QPS = Questions(or Queries) / seconds
mysql > show global status like 'Question%';

(2)TPS(每秒事务量)
TPS = (Com_commit + Com_rollback) / seconds
mysql > show global status like 'Com_commit';
mysql > show global status like 'Com_rollback';

(3)key Buffer 命中率
mysql>show global status like 'key%';
key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

(4)InnoDB Buffer命中率
mysql> show status like 'innodb_buffer_pool_read%';
innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

(5)Query Cache命中率
mysql> show status like 'Qcache%';
Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

(6)Table Cache状态量
mysql> show global status like 'open%';
比较 open_tables 与 opend_tables 值

(7)Thread Cache 命中率
mysql> show global status like 'Thread%';
mysql> show global status like 'Connections';
Thread_cache_hits = (1 - Threads_created / connections ) * 100%

(8)锁定状态
mysql> show global status like '%lock%';
Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重
Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的

(9)复制延时量
mysql > show slave status
查看延时时间

(10) Tmp Table 状况(临时表状况)
mysql > show status like 'Create_tmp%';
Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大，
可能是排序句子过多或者是连接句子不够优化

(11) Binlog Cache 使用状况
mysql > show status like 'Binlog_cache%';
如果Binlog_cache_disk_use值不为0 ，可能需要调大 binlog_cache_size大小

(12) Innodb_log_waits 量
mysql > show status like 'innodb_log_waits';
Innodb_log_waits值不等于0的话，表明 innodb log buffer 因为空间不足而等待

比如命令：

show global status;

虽然可以使用：

show global status like %...%;

来过滤，但是对应长长的list，每一项都代表什么意思，还是有必要弄清楚。

查看正在执行的SQL

SELECT * FROM information_schema.PROCESSLIST WHERE command != 'Sleep' ORDER BY time DESC

select * from information_schema.innodb_trx;
select * from information_schema.INNODB_LOCKS;
select * from information_schema.INNODB_LOCK_WAITS;
SHOW ENGINE INNODB STATUS;

SHOW PROCESSLIST

//设置 innodb_buffer_pool_size 大小
SET GLOBAL innodb_buffer_pool_size = 1073741824;