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.
innodb_buffer_pool_instances = 40
innodb_buffer_pool_size = 60G
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 1G
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
124 GB RAM
Intel® Xeon® CPU E5-2620 v2 @ 2.10GHz, Intel® Xeon® CPU E5-2620 v2 @ 2.10GHz
ZFS Raid1 on 2xSSD
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.