PolarDB:Faster TRUNCATE/DROP TABLE

Your PolarDB for MySQL cluster must meet one of the following requirements:

• MySQL 5.7, revision version 5.7.1.0.19 or later.

• MySQL 8.0.1, revision version 8.0.1.1.50 or later.

• MySQL 8.0.2, revision version 8.0.2.2.30.1 or later.

You can enable the faster TRUNCATE/DROP TABLE feature by setting the loose_innodb_flush_pages_using_space_id parameter. For more information, see Set cluster parameters and node parameters. The following table describes the parameter.

Test method

1. Run the following commands to fill the buffer pools on clusters with different specifications. This step ensures that the buffer pool status does not affect the performance of the TRUNCATE TABLE operation.

   ip=<cluster_endpoint>
   user=<database_account>
   psw=<database_password>
   port=<database_port>
   MYSQL="mysql -h $ip -P $port -u$user -p$psw -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 t3;"

2. Create two tables, t1 and t2. Insert 8,192 rows of data into t1 and 2,097,152 rows of data into t2.

   # Create the t1 table and insert 8,192 rows of data.
   row_num=13
   ip=<cluster_endpoint>
   user=<database_account>
   psw=<database_password>
   port=<database_port>
   MYSQL="mysql -h $ip -P $port -u$user -p$psw -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.
   row_num=21
   ip=<cluster_endpoint>
   user=<database_account>
   psw=<database_password>
   port=<database_port>
   MYSQL="mysql -h $ip -P $port -u$user -p$psw -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. On clusters with different specifications, run the TRUNCATE TABLE operation on tables t1 and t2 with the faster TRUNCATE/DROP TABLE feature enabled and disabled.