本文共 3243 字，大约阅读时间需要 10 分钟。
mysql 查看当前正在执行的语句;
查看当前正在执行的语句 show processlist：
root@localhost [(none)]>show processlist;+----+-------------+---------------------+------+-------------+-------+---------------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-------------+---------------------+------+-------------+-------+---------------------------------------------------------------+------------------+| 3 | system user | | NULL | Connect | 53528 | Waiting for master to send event | NULL || 4 | system user | | NULL | Connect | 53528 | Slave has read all relay log; waiting for more updates | NULL || 5 | repluser | 192.168.30.37:51622 | NULL | Binlog Dump | 53496 | Master has sent all binlog to slave; waiting for more updates | NULL || 13 | root | localhost | NULL | Query | 1 | starting | show processlist |+----+-------------+---------------------+------+-------------+-------+---------------------------------------------------------------+------------------+4 rows in set (0.22 sec)
结束正在执行的语句进程 kill 进程id
select @@global.query_cache_size;
set @@global.query_cache_size=16777216;
stream {
server { listen 3336; proxy_pass db; } upstream db { server 10.211.7.11:3306; server 10.211.7.12:3306 backup; } }mysql找到所有索引
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) ASColumns FROM information_schema.statistics a GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name mysql主从时不时进入Slave_SQL_Running_State:system lock，然后恢复到 Slave has read all relay log; waiting for more updates
五、system lock 延迟的原因
这里先直接给出原因供大家直接参考，简单的说从库出现 system lock 应该视为正在干活，而不是名称看到的“lock”，这是由于 slave 端不存在语句（row 格式）的执行，都是 Event 的直接 apply，状态没有切换的机会，也可以认为是 slave 端状态划分不严谨，其实做一个 pstack 就能完全看出问题。下面是产生的必要条件：
由于大量的小事物，比如 UPDATE/DELETE table where 处理一行数据，这会出现只包含一行数据库的 DML event 的语句，如果 table 是一张大表，则会加剧这种可能。
这个表上没有主键或者唯一键，问题加剧。 由于类似 Innodb lock 堵塞，也就是 slave 从库修改了数据同时和 sql_thread 也在修改同样的数据，问题加剧。 确实 I/O 扛不住了，可以尝试修改参数。 如果是大量的表没有主键或者唯一键可以考虑修改参数 slave_rows_search_algorithms 试试。关于 slave_rows_search_algorithms 在我的系列中有一章详细讨论，这里不做赘述。 这里还特地请教了阿里的印风兄验证了一下 mysql_lock_tables 是 myisam 实现表锁的函数 Innodb 会设置为共享锁。这里我们先抛开 query event/map event 等。只考虑 DML event，下面就是 system lock 出现的流程：
如果一个小事物只有一条 DML event 的场景下逻辑如下：
->进入reading eventfrom the relay log状态 ->读取一条event(参考next_event函数) ->进入system lock状态 ->Innodb层进行查找和修改数据
如果是一个大事物则包含了多条 DML event 的场景逻辑如下：
->进入reading eventfrom the relay log状态 ->读取一条event(参考next_event函数) ->进入system lock状态 ->Innodb层进行查找和修改数据 ->进入reading eventfrom the relay log状态 ->读取一条event(参考next_event函数) ->Innodb层进行查找和修改数据 ->进入reading eventfrom the relay log状态 ->读取一条event(参考next_event函数) ->Innodb层进行查找和修改数据 ....直到本事物event执行完成
因此我们看到对于一个小事物我们的 sql_thread 会在加 system lock 的情况下进行对数据进行查找和修改。
因此得出了我的结论，同时如果是 Innodb 层锁造成的 sqlthread 堵塞也会在持有 system lock 的状态下。但是对于一个大事物则不一样，虽然出现同样的问题，但是其状态是 reading event from the relay log。
所以如果出现 system lock 一般就是考虑前文给出的结论。
转载地址：http://vqzgn.baihongyu.com/