0

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.

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

2
  • The fastest solution I found so far is to run a delete and alter(for auto_increament). drops the execution time to one third. – Hassan Khodadadeh May 24 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 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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