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:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5.28 OS:Any
Assigned to: CPU Architecture:Any

[25 Jan 2013 19:50] William Gunty
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
[25 Jan 2013 21:51] Mark Callaghan
From my memory of the new code, the fast DROP TABLE optimizations are not done for TRUNCATE
[25 Jan 2013 22:26] Mark Callaghan
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.

                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);

                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);
[27 Jan 2013 6:02] Shane Bester
also see comment in http://bugs.mysql.com/bug.php?id=56696
[29 Jan 2013 15:18] Sinisa Milivojevic
There are already some plans of addressing this problem, but scheduling is not determined yet.
[20 May 2013 14:57] Inaam Rana
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.
[8 Jan 2014 8:49] Erlend Dahl
Setting to "verified" since we will discontinue the use of "to be fixed
[10 Feb 2014 10:39] Vasil Dimov
Posted by developer:

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.
[9 Nov 2015 7:48] Marko Mäkelä
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).
[13 Nov 2015 21:00] Daniel Price
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.