When you execute the TRUNCATE TABLE and DROP TABLE statements on MySQL Community 5.7, the entire buffer pool is scanned and all data pages corresponding to the table space are removed from the LRU list and FLUSH list. When the buffer pool is very large, this process takes a long time. PolarDB optimizes the buffer pool management mechanism for DDL statements to improve the scan efficiency of buffer pools and the execution efficiency of TRUNCATE TABLE and DROP TABLE statements.

Limits

A PolarDB for MySQL 5.7 cluster whose revision version is 5.7.1.0.21 or later.
Note For more information about how to check the version, see Query the engine version.

Usage

You can enable the faster TRUNCATE/DROP TABLE feature by specifying the loose_innodb_flush_pages_using_space_id parameter. For more information, see Specify cluster and node parameters. The following table describes the parameter.
Parameter Level Description
loose_innodb_flush_pages_using_space_id Global Specifies whether to enable the faster truncate/DROP TABLE feature. Default value: OFF. Valid values:
  • ON
  • OFF

Performance test

The TRUNCATE TABLE statement is used in the test to compare the impacts of enabling and disabling the faster TRUNCATE/DROP TABLE feature on the execution efficiency of the TRUNCATE TABLE statement on tables in clusters of different specifications.

Test method
  1. Run the following commands to exhaust the buffer pools for clusters of different specifications to prevent them from affecting the execution efficiency of the TRUNCATE TABLE statement: 
    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. Create the t1 and t2 tables on which the TRUNCATE TABLE statement is to be executed and insert 8196 and 2097152 rows of data to the t1 and t2 tables. 
    # Create the t1 table and insert 8,196 rows of data to it. 
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
    # Create the t2 table and insert 2,097,152 rows of data to it. 
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. For clusters of different specifications, enable and disable the faster TRUNCATE/DROP TABLE feature and execute the TRUNCATE TABLE statement on the t1 and t2 tables.

Test results

For clusters of different specifications, record the execution time (in seconds) required to execute the TRUNCATE TABLE statement on the t1 and t2 tables after the faster TRUNCATE/DROP TABLE function is enabled and disabled. The following table lists the test results.

Cluster specification Buffer pool (GB) t1 t2
ON OFF Improvement percentage ON OFF Improvement percentage
64 cores and 512 GB of memory 374 0.01 5.2 99.81% 0.11 9.48 98.84%
32 cores and 256 GB of memory 192 0.02 2.45 99.18% 0.1 2.65 96.23%
16 cores and 128 GB of memory 96 0.01 1.73 99.42% 0.12 1.86 93.55%
8 cores and 64 GB of memory 42 0.01 0.73 98.63% 0.12 0.79 84.81%
4 vCPUs and 32 GiB of memory 24 0.02 0.45 95.56% 0.13 0.53 75.47%
4 cores and 16 GB of memory 12 0.03 0.23 86.96% 0.12 0.35 65.71%

The preceding table shows that enabling the faster TRUNCATE/DROP TABLE feature can significantly improve the execution efficiency of the TRUNCATE TABLE statement.