Bug #77064 Manual does not say anything about a known performance problem with TRUNCATE
Submitted: 18 May 2015 8:43 Modified: 27 Jul 2015 12:10
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.5, 5.6 OS:Any
Assigned to: Daniel Price CPU Architecture:Any
Tags: innodb, missing manual, truncate

[18 May 2015 8:43] Valeriy Kravchuk
Description:
TRUNCATE (unlike DROP) in MySQL 5.5 and 5.6 has a well known performance problem when applied to InnoDB table:

https://bugs.mysql.com/bug.php?id=68184

Last comment there says:

"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)."

So, for morethan a year it's clear that no one is going to fixc this problem in MySQL 5.6 and there is just a hope that in 5.7 it will be fixed by a different implementation. 

I wonder why there is no even a single note in the manual, http://dev.mysql.com/doc/refman/5.6/en/truncate-table.html, about this known performance problem.

How to repeat:
Try to find ourt in the manual (http://dev.mysql.com/doc/refman/5.6/en/truncate-table.html) anything about performance problems TRUNCATE may cause comparing to DELETE or DROP + CREATE. Then hit something like https://bugs.mysql.com/bug.php?id=68184 when you use TRUNCATE...

Suggested fix:
Please, explain current limitation/known deficiences of TRUNCATE in the manual of MySQL 5.5 and 5.6.
[19 May 2015 6:46] MySQL Verification Team
Hello Valeriy,

Thank you for the report.

Thanks,
Umesh
[27 Jul 2015 12:10] Daniel Price
Posted by developer:
 
The following information was added to the TRUNCATE TABLE documentation for 5.5, 5.6, and 5.7:

"On a system with a large InnoDB buffer pool and
innodb_adaptive_hash_index enabled, TRUNCATE TABLE operations may cause a
temporary drop in system performance due to an LRU scan that occurs when
removing an InnoDB table's adaptive hash index entries. The problem was
addressed for DROP TABLE in MySQL 5.5.23 (Bug #13704145, Bug #64284) but
remains a known issue for TRUNCATE TABLE (Bug #68184)."

Thank you for the bug report.