专栏首页MYSQL轻松学truncate 引起 system lock 分析

truncate 引起 system lock 分析

system lock

线程是被mysql_lock_tables()函数调用,并且此后该线程未更新过状态。

这种现象很普遍,造成的原因有多种:

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

如果是大事务,虽然也会加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方式

本文分享自微信公众号 - MYSQL轻松学(learnmysql),作者:Liang

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-01-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL binlog日志大小超过限定范围

    my.cnf中有两个参数设置: expire_logs_days = 7 #binlog保留时间7天 max_binlog_size = 1G ...

    MySQL轻松学
  • 开源数据闪回工具—binlog2sql介绍

    binlog2sql是国内MySQL大佬danfengcao使用python开发,许多MySQL爱好者参与改进的一款MySQL binlog解析软件。根据不同选...

    MySQL轻松学
  • Mysql索引长度计算

    (root:hostname:Fri May 29 14:10:50 2015)[liangxl]> show create table liang_2; +-...

    MySQL轻松学
  • 实时大数据开发实践

    本文主要从大数据起源谈起,介绍了几种主要的大数据处理框架,包括其中的容错机制,实现细节及原理等。再主要介绍了使用storm进行大数据开发的具体过程,以及开发过程...

    gaofc
  • 分布式Session

    Session 是客户端与服务器通讯会话跟踪技术,服务器与客户端保持整个通讯的会话基本信息。

    用户3467126
  • 面向对象设计的设计模式(二):工厂方法模式

    工厂方法模式的适用场景与简单工厂类似,都是创建数据和行为比较类似的对象。但是和简单工厂不同的是:在工厂方法模式中,因为创建对象的责任移交给了抽象工厂的子类,因此...

    用户2932962
  • 面向对象设计的设计模式(二):工厂方法模式

    工厂方法模式的适用场景与简单工厂类似,都是创建数据和行为比较类似的对象。但是和简单工厂不同的是:在工厂方法模式中,因为创建对象的责任移交给了抽象工厂的子类,因此...

    用户1740424
  • CNN卷积算法应用---手写数字识别的两次训练结果展示

    98k
  • Net连接mysql的公共Helper类MySqlHelper.cs带MySql.Data.dll下载

    MySqlHelper.cs代码如下: using System; using System.Collections.Generic; using System...

    Java中文社群_老王
  • 马云又憋了个大招:这个平台会成为下个天猫?

    近日,苹果10亿美金投资了中国共享经济领域的现象级公司滴滴出行,表明其对中国共享经济市场的看好。“共享经济”,是2015年十分热门的概念。汽车座位、家庭餐桌、闲...

    罗超频道

扫码关注云+社区

领取腾讯云代金券