Analysis of Slow TRUNCATE and DROP TABLE Operations in MySQL 5.7 and 8.0

Problem Description

Feedback from a test environment indicated that truncate statements were showing up in the slow‑query log, raising concerns about potential impact on production databases.

Key Configuration

Configuration Item

Description

Database Version

MySQL 5.7

Parameter

long_query_time

Slow‑query threshold (seconds)

0.1 (100 ms)

Parameter

innodb_adaptive_hash_index

ON

Problem Analysis Summary

The main issues identified are:

Q1: How is the TRUNCATE statement executed? Why does the file descriptor not change? Why is execution time long?

Execution flow – The stack trace shows that the time‑consuming steps are row_drop_table_for_mysql and os_file_delete_func . The former calls btr_drop_ahi_for_table to delete AHI pages, while the latter invokes unlink to clean up files.

File descriptor behavior – During TRUNCATE, the table is first rename d, releasing its fd (e.g., 43). A new table is then created, often re‑using the released fd, so the fd appears unchanged unless another file opens the descriptor in the interim.

Note: MySQL 8.0 implements TRUNCATE via rename + create + drop , while MySQL 5.7 uses a direct file truncate operation.

Q2: How to analyze the slowness of TRUNCATE?

Slow‑log only shows the final duration, not the root cause.

Execution plan is not supported for truncate .

PROFILE shows the time spent in System lock but cannot drill deeper.

Q3: Can we optimize? Where is the bottleneck? How is post_ddl invoked?

For MySQL 8.0, disabling innodb_adaptive_hash_index speeds up row_drop_table_for_mysql . Setting innodb_flush_method=O_DIRECT or using hard links improves os_file_delete_func . In MySQL 5.7, the bottlenecks are in dict_drop_index_tree and os_file_truncate , with limited optimization options.

Q4: Production risks of executing TRUNCATE

IO pressure from rapid file deletions, memory‑concurrency contention on buffer‑pool mutexes, and lock conflicts on dict_operation_lock can affect normal workloads, especially under high traffic.

Q5: Version differences in TRUNCATE implementation

MySQL 8.0 uses the same code path as DROP (via rename + create + drop ), while MySQL 5.7 employs distinct implementations ( row_truncate_table_for_mysql vs. row_drop_table_for_mysql ) with different hot‑spots such as dict_drop_index_tree and os_file_truncate .

DROP TABLE Optimization Failure Analysis

In a MySQL 5.7 test environment, the proposed optimization (e.g., innodb_flush_method=O_DIRECT ) did not improve DROP TABLE latency because the time‑consuming function was dict_drop_index_tree, which remained unaffected.

The same innodb_flush_method=O_DIRECT setting is applicable to MySQL 8.0.

MySQL 8.0 removes the expensive DROP_TABLE_PROC and related functions via the NEW DD (Data Dictionary) implementation, eliminating the previous bottlenecks.

Testing Procedure

DROP PROCEDURE truncate_test;
DELIMITER //
CREATE PROCEDURE truncate_test()
BEGIN
  insert into t1 select * from t1_bak;
  truncate table t1;
END//
DELIMITER ;

CALL truncate_test();

Profiling in MySQL 5.7 shows the truncate part taking over a minute when preceded by a large INSERT SELECT . In MySQL 8.0, SHOW PROFILES separates the INSERT and TRUNCATE durations, making the latter appear much faster.

References

Scope guard in MySQL 8.0 – link

Drop Table performance impact analysis – link

InnoDB file system physical structure – link

Promotional Section (Community)

The article is originally posted by the 爱可生开源社区. It also includes links to other technical posts and a call for contributions to the SQLE open‑source project.