truncate table in mysql 8.0 is slower than older version

Question

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:

[mysqld] 
skip-external-locking
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

skip-name-resolve

innodb_buffer_pool_size=256M
innodb_log_buffer_size=32M
innodb_log_file_size=64M

innodb_flush_method=O_DIRECT
innodb_thread_concurrency=64
innodb_write_io_threads=32
innodb_read_io_threads=32
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.

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

Setup:

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