Right after a restart TRUNCATEs are super quick, but when the buffer pool is getting filled (but not full) TRUNCATEs take 1-2 seconds halting all queries across different databases with a SYSTEM LOCK.
cnf:
[mysqld]
innodb_file_per_table
innodb_buffer_pool_instances = 40
innodb_buffer_pool_size = 60G
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit=0
skip-innodb_doublewrite
innodb_log_buffer_size = 1G
innodb_log_file_size=2G
innodb_stats_on_metadata = 0
innodb_io_capacity = 1000
key_buffer_size = 8196M
max_allowed_packet = 32M
thread_stack = 192K
thread_cache_size = 8
max_connections = 1000
userstat = 1
max_heap_table_size = 128M
tmp_table_size = 512M
open_files_limit = 40k
table_open_cache = 28k
Server:
124 GB RAM
Intel(R) Xeon(R) CPU E5-2620 v2 @ 2.10GHz, Intel(R) Xeon(R) CPU E5-2620 v2 @ 2.10GHz
Debian Jessie
ZFS Raid1 on 2xSSD
DB:
Server version: 5.7.18-16-log - Percona Server (GPL), Release ‘16’, Revision ‘d7301f8’
550 databases with a total of 100.000 tables, approximately 15% is active the rest is only accessed occationally.
Active data < RAM < Total data size
How do I find the reason TRUNCATE is locking for so long? Profiling the query only shows the 2 seconds SYSTEM LOCK but no further info.
My guess so far is either
A) TRUNCATE has to traverse the buffer pool for some reason, and this takes more time on larger pools
B) TRUNCATE has some unintended overhead “opening” the new empty table (I assume 5.7 implements TRUNCATE as a RENAME - CREATE LIKE - DROP in order to fix the older 5.5 slow TRUNCATE on innodb_file_per_table, and so will require a new table to open?) This resonates with my number of tables > open_files_limit but not with the SYSTEM LOCK that halts other queries.
What is the recommended strategy for maintaining sub second TRUNCATES on an environment like this? Alternatively make TRUNCATE non blocking for other non related queries.
Best
Martin