Why Is TRUNCATE So Slow in MySQL? Root Causes and Solutions

Problem Phenomenon

Users reported slow TRUNCATE operations in a MySQL 5.7 test environment, with execution times exceeding 100ms (threshold: 0.1s). Concerns arose about potential impacts on production systems.

Key Analysis Steps

1. ​Execution Flow:
   • TRUNCATE triggers row_drop_table_for_mysql and os_file_delete_func, involving adaptive hash index (AHI) cleanup and file deletion.
   • File descriptor (FD) behavior differs between MySQL 5.7 (file truncation) and 8.0 (rename + create + drop).
2. ​Performance Bottlenecks:
   • ​High I/O Pressure: Large files trigger slow unlink operations during cleanup.
   • ​Memory Overhead: Flushes LRU buffers and acquires mutex locks, causing contention in high-concurrency scenarios.
   • ​Version-Specific Codepaths:
     • MySQL 5.7: Independent TRUNCATE code (avoids DDL scope guards).
     • MySQL 8.0: Leverages DDL crash safety via SCOPE GUARD for cleaner file operations.
3. ​Diagnostic Tools:
   • ​Debugging: Enabled debug='d:t:T:i:n:N:o,/tmp/debug.trace' to trace function-level timing.
   • ​Parameter Impact:
     • Disabling innodb_adaptive_hash_index reduced latency by 97% (0.98s → 0.03s).
     • Setting innodb_flush_method=fsync increased latency due to synchronous writes.

Optimization Strategies

1. ​MySQL 5.7-Specific Fixes:
   • Disable AHI via innodb_adaptive_hash_index=OFF.
   • Use O_DIRECT for innodb_flush_method to bypass OS caching.
2. ​MySQL 8.0 Enhancements:
   • Adopt SCOPE GUARD for safer DDL operations.
   • Leverage DROP TABLE optimizations (e.g., HARD LINK for faster file removal).
3. ​Production Precautions:
   • Avoid TRUNCATE during peak hours to minimize buffer pool contention.
   • Monitor IO metrics (e.g., SHOW ENGINE INNODB STATUS) during large-scale operations.

Version Comparison

‍

Conclusion

TRUNCATE slowdowns stem from file I/O, memory management, and version-specific DDL implementations. By disabling adaptive hash indexes, tuning flush methods, and leveraging MySQL 8.0’s crash-safe features, teams can mitigate latency risks. Always test optimizations in staging environments before production deployment.

‍