Slow Truncate Table With a Large InnoDB Buffer Pool (Doc ID 1630499.1)

Last updated on MARCH 11, 2016

Applies to:

MySQL Server - Version 5.5 and later
Information in this document applies to any platform.

Symptoms

TRUNCATE TABLE is much slower when a big InnoDB buffer pool is full.

Example: nearly 3 seconds when the buffer pool is 99 % full.

mysql> select avg(free_buffers/pool_size) free  from INNODB_BUFFER_POOL_STATS\G
*************************** 1. row ***************************
free: 0.76932710
1 row in set (0.00 sec)

mysql> truncate table test.t;
Query OK, 0 rows affected (0.28 sec

mysql> select avg(free_buffers/pool_size) free  from INNODB_BUFFER_POOL_STATS\G
*************************** 1. row ***************************
free: 0.59533072
1 row in set (0.00 sec)

mysql> truncate table test.t;
Query OK, 0 rows affected (0.49 sec)

mysql> select avg(free_buffers/pool_size) free  from INNODB_BUFFER_POOL_STATS\G
*************************** 1. row ***************************
free: 0.00846470
1 row in set (0.00 sec)

mysql> truncate table test.t;
Query OK, 0 rows affected (2.74 sec)

  

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms