I have few hundreds of tests which use mysql. I was using Mysql 5.7 (percona) and I changed it to Mysql 8.0 . My problem is that before every test I truncate ALL my tables. This was fine with 5.7 but mysql 8.0 takes 5 seconds to truncate all 60 tables.

This is mysql config which I use for both version:

key_buffer_size = 32M
max_allowed_packet  = 1G
thread_stack        = 512K
thread_cache_size       = 8
expire_logs_days    = 2
max_binlog_size         = 100M
max_connections = 1200

tmp_table_size                                  = 512M
max_heap_table_size                             = 512M



innodb_flush_log_at_trx_commit                  = 2
wait_timeout = 1800
interactive_timeout = 1800
innodb_file_per_table = 1


P.S: This is not just truncate, create table and alter table also take a lot more time compared to previous version.

P.S 2: I tested this both on my system and GitHub's Action. Same result for both.

1 Answer 1


Alas, DDL statements are slower in 8.0. This is because they can now be inside transactions and rolled back.

If the goal is to start with a fresh set of empty tables for a new test, I am having trouble coming up with a faster way. Perhaps something involving a "Logical Volume" (cf "LVM"). (Note MySQL needs to be stopped throughout the steps below.)


  • Establish a separate disk partition for the main directory tree for MySQL.
  • Clone it (LVM). (This will be the master for a later step)

When starting a new test:

  • Drop the current logical volume
  • Clone the above clone to get a fresh set of empty tables

(Sorry, I don't have all the details.)

  • The fastest solution I found so far is to run a delete and alter(for auto_increament). drops the execution time to one third. May 24, 2021 at 6:17
  • @HassanKhodadadeh - in 8.0, I would expect DELETE to be "faster" than TRUNCATE for small tables; vice versa for large tables.
    – Rick James
    May 24, 2021 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.