truncate 时发生 system lock

最新推荐文章于 2024-03-15 20:03:59 发布

jerry-89

最新推荐文章于 2024-03-15 20:03:59 发布

阅读量1.2k

点赞数 1

分类专栏：故障解决

本文链接：https://blog.csdn.net/eagle89/article/details/105706254

版权

故障解决专栏收录该内容

8 篇文章 0 订阅

订阅专栏

system lock

线程是被mysql_lock_tables()函数调用，该线程未更新过状态。

这种现象很普遍，造成的原因有多种：

可能一个线程想请求或者正在等一个表的内部或者外部的system lock； 如：从库复制sql_thread，在小事务较多时，会在加system lock的情况下对数据进行查找和修改。 show full processlist;

如果是大事务，虽然也会加system lock，但状态为reading event from the relay log或Executing event。

也可能是InnoDB在执行lock tables的时候，等表级锁； 如：truncate 操作
也可能是请求内部锁； 如：访问相同MyISM表没有用多个mysqld服务;

遇到这种情况，可以用--skip-external-locking选项，禁用内部的system locks。然而，内部锁如果默认禁用的话，那个选项就会没用了。如果是在show profile的时候遇到这种状态，就说明这个线程正在请求锁（不是等）。

truncate引起system lock分析

truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放，不记录每行删除的日志。

truncate是把表中数据全部清空，需要有drop权限； truncate操作分为drop table 和create table操作； truncate不记录二进制日志且无法回滚； truncate如果有外键，则不能执行； truncate结果正常显示0行受影响，表示无信息； truncate操作将自增值重置为起始值； truncate分区表，保留分区信息；

现象：

空间紧张，需要清除历史数据，有一个大表可以全部清空，我们知道这种操作，truncate最为擅长。但在truncate过程中，出现system lock，导致CPU升高，性能下降。

分析： MySQL实例中的所有数据库都维护一个查询缓存，它们之间实际上没有隔离。truncate将不得不使查询缓存中的所有依赖查询失效，这可能是锁定的原因。如果查询缓存足够大，则删除缓存可能需要更长时间。

MySQL在5.5.23版本之前的处理方式即同步模式: 当要drop table的时候，会在整个操作过程中持有buffer pool的mutex，然后扫描两次LRU链表，把属于这个table的page失效掉，buffer pool中page的个数越多，持有mutex时间就会越长，对在线业务的影响也就越明显。

MySQL在5.5.23版本之后，对drop table的处理做了修改，即在扫描LRU链表过程中，如果dirty page属于drop table，那么就直接从flush list中remove掉，如果删除的page个数超过了1024个数目的话，释放buffer pool mutex，flush list mutex，释放cpu资源，重新持有mutex再释放。

buf_LRU_flush_or_remove_pages(id, BUF_REMOVE_FLUSH_NO_WRITE, 0);
buf_pool_mutex_enter(buf_pool);
err = buf_flush_or_remove_pages(buf_pool, id, flush, trx);
......
buf_pool_mutex_exit(buf_pool);
/* BUF_REMOVE_FLUSH_NO_WRITE：意思表示，只对dirty block进行remove操作，不做写入。

虽然5.5.23版本后drop table解决了该问题，但truncate操作并没有升级，还是采用5.5.23版本之前的删除方式，直到8.0版本才解决。

truncate 修改后部分代码：

Truncate calls row_discard_tablespace_for_mysql -> fil_discard_tablespace -> fil_delete_tablespace with evict_all=TRUE. When evict_all is true for the call to buf_LRU_flush_or_remove_pages then BUF_REMOVE_ALL_NO_WRITE is used.

buf_LRU_flush_or_remove_pages(
id, evict_all
? BUF_REMOVE_ALL_NO_WRITE
: BUF_REMOVE_FLUSH_NO_WRITE);

... then the slow path is used. so this stall is expected. would be nice for it to be fixed.

switch (buf_remove) {
case BUF_REMOVE_ALL_NO_WRITE:
/* A DISCARD tablespace case. Remove AHI entries
and evict all pages from LRU. */

/* Before we attempt to drop pages hash entries
one by one we first attempt to drop page hash
index entries in batches to make it more
efficient. The batching attempt is a best effort
attempt and does not guarantee that all pages
hash entries will be dropped. We get rid of
remaining page hash entries one by one below. */
buf_LRU_drop_page_hash_for_tablespace(buf_pool, id);
buf_LRU_remove_all_pages(buf_pool, id);
break;

case BUF_REMOVE_FLUSH_NO_WRITE:
/* A DROP table case. AHI entries are already
removed. No need to evict all pages from LRU
list. Just evict pages from flush list without
writing. */
buf_flush_dirty_pages(buf_pool, id);
break;
}
}

MySQL8.0针对该问题说明：

On a system with a large InnoDB buffer pool and innodb_adaptive_hash_index enabled, TRUNCATE TABLE operations could cause a temporary drop in system performance due to an LRU scan that occurred when removing an InnoDB table's adaptive hash index entries.

To address this problem, TRUNCATE TABLE now invokes the same code as DROP TABLE and CREATE TABLE. The problem was addressed for DROP TABLE in MySQL 5.5.23.

意思为：

当InnoDB buffer pool比较大和innodb_adaptive_hash_index启用时，TRUNCATE TABLE操作可能由于发生了LRU扫描，删除InnoDB表的自适应散列索引项时，导致系统性能暂时下降。为了解决这个问题，TRUNCATE TABLE现在调用与DROP TABLE相同的代码删除表。因为在MySQL 5.5.23后，DROP TABLE解决了这个问题。

总结：

5.5.23版本之前，采用truncate+drop方式

5.5.23版本之后，采用drop方式

8.0版本之后，采用truncate方式