Faster TRUNCATE/DROP TABLE

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

• MySQL 5.7 with revision version 5.7.1.0.19 or later.

• MySQL 8.0.1 with revision version 8.0.1.1.50 or later.

• MySQL 8.0.2 with revision version 8.0.2.2.30.1 or later.

You can use the loose_innodb_flush_pages_using_space_id parameter to enable the Faster TRUNCATE/DROP TABLE feature. For instructions, see Set cluster parameters and node parameters. The following table describes the parameter.

Test method

1. Run the following command to fill the buffer pool of clusters with different specifications. This prevents the buffer pool from affecting the execution efficiency 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 the tables to be truncated, t1 and t2, and insert 8,192 rows and 2,097,152 rows of data into them respectively.

   # Create the t1 table and insert 8,196 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, enable and disable the Faster TRUNCATE/DROP TABLE feature, and run the TRUNCATE TABLE operation on the tables t1 and t2.