Faster TRUNCATE/DROP TABLE

在社区MySQL 5.7中,当执行TRUNCATE TABLE和DROP TABLE时,需要扫描整个缓存池(Buffer Pool),将表空间对应的所有数据页从LRU list和FLUSH list中删除。当Buffer Pool比较大时,该操作耗时较长。PolarDB对DDL过程中的Buffer Pool管理机制进行了优化,能够有效提升Buffer Pool的扫描效率,提升TRUNCATE TABLE和DROP TABLE的执行效率。

使用限制

PolarDB MySQL版集群版本需为5.7版本且Revision version为5.7.1.0.21或以上

说明

您可以参见查询版本号确认集群版本。

使用方法

您可以通过loose_innodb_flush_pages_using_space_id参数开启Faster TRUNCATE/DROP TABLE功能,具体操作请参见设置集群参数和节点参数。参数说明如下:

参数

级别

说明

loose_innodb_flush_pages_using_space_id

Global

Faster TRUNCATE/DROP TABLE功能开关。取值范围如下:

  • ON:开启Faster TRUNCATE/DROP TABLE功能

  • OFF:关闭Faster TRUNCATE/DROP TABLE功能(默认值)

性能测试

本文档以TRUNCATE TABLE操作为例,对比了不同规格的集群下,开启和关闭Faster TRUNCATE/DROP TABLE功能对表执行TRUNCATE TABLE操作的执行效率影响情况。

测试方法

  1. 使用如下命令先将不同规格集群的Buffer Pool打满,避免Buffer Pool影响TRUNCATE TABLE操作的执行效率。

    ip=xxx.xxx.xx.xx
    user='user_name'
    psw='password'
    port=xxx
    MYSQL="mysql --host=$ip -p$psw --port=$port -u$user  -vvv -e"
    $MYSQL "create database test;"
    $MYSQL "use test;create table if not exists t3 (a bigint,b char(250),c char(250),d char(250),e char(250)) charset=latin1;"
    $MYSQL "use test;insert into t3 values(1,repeat('x', 255),repeat('x', 255),repeat('x', 255),repeat('x', 255));"
    for ((i=1; i<=32; i+=1))
    do
       $MYSQL --host=$ip -p$psw --port=$port -u$user  -vvv -e "use test;insert into t3 select * from t3;"
    done
    $MYSQL  "use test; select count(*) from t1;"
  2. 创建待TRUNCATE的表t1t2,并分别插入8196行和2097152行数据。

    #创建表t1,并插入8196行数据。
    row_num=13
    ip=xxx.xxx.xx.xx
    user='user_name'
    psw='password'
    port=xxx
    MYSQL="mysql --host=$ip -p$psw --port=$port -u$user  -vvv -e"
    $MYSQL "use test;create table if not exists t1 (a bigint,b char(250),c char(250),d char(250),e char(250)) charset=latin1;"
    $MYSQL "use test;insert into t1 values(1,repeat('x', 255),repeat('x', 255),repeat('x', 255),repeat('x', 255));"
    for ((i=1; i<=$row_num; i+=1))
    do
       $MYSQL "use test;insert into t1 select * from t1;"
    done
    #创建表t2,并插入2097152行数据。
    row_num=21
    ip=xxx.xxx.xx.xx
    user='user_name'
    psw='password'
    port=xxx
    MYSQL="mysql --host=$ip -p$psw --port=$port -u$user  -vvv -e"
    $MYSQL "use test;create table if not exists t2 (a bigint,b char(250),c char(250),d char(250),e char(250)) charset=latin1;"
    $MYSQL "use test;insert into t2 values(1,repeat('x', 255),repeat('x', 255),repeat('x', 255),repeat('x', 255));"
    for ((i=1; i<=$row_num; i+=1))
    do
       $MYSQL "use test;insert into t2 select * from t2;"
    done
  3. 在不同规格的集群中,分别开启和关闭Faster TRUNCATE/DROP TABLE功能,对表t1t2进行TRUNCATE TABLE操作。

测试结果

在不同规格的集群中,分别记录开启和关闭Faster TRUNCATE/DROP TABLE功能后,对表t1t2进行TRUNCATE TABLE操作所需的执行时间(秒),实验结果如下所示:

集群规格

Buffer Pool(GB)

t1

t2

ON

OFF

提升率

ON

OFF

提升率

64核512 GB

374

0.01

5.2

99.81%

0.11

9.48

98.84%

32核256 GB

192

0.02

2.45

99.18%

0.1

2.65

96.23%

16核128 GB

96

0.01

1.73

99.42%

0.12

1.86

93.55%

8核64 GB

42

0.01

0.73

98.63%

0.12

0.79

84.81%

4核32 GB

24

0.02

0.45

95.56%

0.13

0.53

75.47%

4核16 GB

12

0.03

0.23

86.96%

0.12

0.35

65.71%

从上表可以看出,开启Faster TRUNCATE/DROP TABLE功能后,能显著提升TRUNCATE TABLE操作的执行效率。

联系我们

若您对DDL操作有任何疑问,可通过钉钉搜索群号入群咨询。您可以直接@群内专家,并附上您要咨询的问题;同时群内也有PolarDB MySQL版小助手24*7小时在线回答您的问题。钉钉群号:15375044501。