MySQL Bugs: #68184: Truncate table causes innodb stalls

Bug #68184	Truncate table causes innodb stalls
Submitted:	25 Jan 2013 19:50	Modified:	13 Nov 2015 21:00
Reporter:	William Gunty	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Drop table functionlity was improved in MySQL 5.5.23 by removing a scan of the buffer pool to remove adaptive hash index entries.

However, it appears that the "drop" functionality within the truncate table function still has this old issue.

How to repeat:
1) Run sysbench or other sort of artificial load generation
2) Run Truncate table on a table that is not being hit by sysbench.  

Here, you should notice a stall and a drop in performance as the table is removed.

3) Duplicate the table to be dropped, swap it with the current one, and then drop the old table.  

There will be a much less significant drop in performance here.

This shows that truncate table does create a stall within innodb, while drop table does not.

Suggested fix:
Remove the buffer pool scan from truncate table

From my memory of the new code, the fast DROP TABLE optimizations are not done for TRUNCATE

Truncate calls row_discard_tablespace_for_mysql -> fil_discard_tablespace -> fil_delete_tablespace with evict_all=TRUE. When evict_all is true for the call to buf_LRU_flush_or_remove_pages then BUF_REMOVE_ALL_NO_WRITE is used.

buf_LRU_flush_or_remove_pages(
id, evict_all
? BUF_REMOVE_ALL_NO_WRITE
: BUF_REMOVE_FLUSH_NO_WRITE);

... then the slow path is used. so this stall is expected. would be nice for it to be fixed.

switch (buf_remove) {
case BUF_REMOVE_ALL_NO_WRITE:
/* A DISCARD tablespace case. Remove AHI entries
and evict all pages from LRU. */

/* Before we attempt to drop pages hash entries
one by one we first attempt to drop page hash
index entries in batches to make it more
efficient. The batching attempt is a best effort
attempt and does not guarantee that all pages
hash entries will be dropped. We get rid of
remaining page hash entries one by one below. */
buf_LRU_drop_page_hash_for_tablespace(buf_pool, id);
buf_LRU_remove_all_pages(buf_pool, id);
break;

case BUF_REMOVE_FLUSH_NO_WRITE:
/* A DROP table case. AHI entries are already
removed. No need to evict all pages from LRU
list. Just evict pages from flush list without
writing. */
buf_flush_dirty_pages(buf_pool, id);
break;
}
}

also see comment in http://bugs.mysql.com/bug.php?id=56696

There are already some plans of addressing this problem, but scheduling is not determined yet.

I initially thought that during TRUNCATE we have to evict all pages from LRU because we reuse the same space_id. Looking at the code I see that we allocate a  new space_id. If that is the case then probably it is OK to leave pages in the LRU list like we do during DROP table.

Setting to "verified" since we will discontinue the use of "to be fixed
later".

Posted by developer:
 
Hello,

Aditya and I wrote a few patches to address this but after a thorough review and testing different problems were found in each one.

MySQL 5.7 code is already different for TRUNCATE than MySQL 5.6. In MySQL 5.7 TRUNCATE will be mapped to DROP+CREATE and this problem will not exist there. So an eventual patch needs to be developed specifically for 5.6.

Due to the complexity and risk involved I would suggest to not fix this in MySQL 5.6 (unless a simpler and working patch pops up). Another point to note is that this issue is not a crash or correctness issue, it is a performance one. My opinion is that the risk involved it too big and is not justified for fixing up a performance issue in TRUNCATE TABLE in MySQL 5.6.

Posted by developer:
 
In WL#6795, TRUNCATE TABLE was implemented by invoking the same code as CREATE TABLE and DROP TABLE.

WL#6795 was merged to mysql-trunk-wl6378, which was merged to mysql-trunk:

commit 52e261fb51bcdf6cf0d246ae292090d615627fa0
Author: Gopal Shankar <gopal.shankar@oracle.com>
Date:   Tue Nov 3 13:27:06 2015 +0530

    WL#6378 New data dictionary (umbrella).

Posted by developer:
 
Fixed as of the upcoming 5.8.0 release, and here's the changelog entry:

On a system with a large InnoDB buffer pool and
innodb_adaptive_hash_index enabled, TRUNCATE TABLE operations could cause
a temporary drop in system performance due to an LRU scan that occurred
when removing an InnoDB table's adaptive hash index entries. To address
this problem, TRUNCATE TABLE now invokes the same code as DROP TABLE and
CREATE TABLE. The problem was addressed for DROP TABLE in MySQL 5.5.23.

Thank you for the bug report.