PolarDB:Faster TRUNCATE/DROP TABLE

A PolarDB for MySQL 5.7 cluster whose revision version is 5.7.1.0.21 or later.

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

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.