Slow Create Temp Table/ CREATE INDEX on temp table

version | 5.7.26-29

version_comment | Percona Server (GPL), Release 29, Revision 11ad961

Sometimes creating empty temp tables or adding indexes on emprty temp tables can take seconds rather than milliseconds.

Looking at events_waits_current data when the objects are being created the waits are.

last_wait: wait/io/table/sql/handler

last_wait: wait/io/file/sql/FRM

I also found the /mysql/data/ibtmp1 has the highest wait time in the file_summary_by_instance table. The server is an ec2 instance and we don’t appear to getting near the provisioned iops for the MySQL drive.

Sometimes the objects are created fast other times they are really slow. Please can anyone suggest the underlying cause or how I can understand the problem better.

Thanks.

I’m still struggling with this so if anyone can offer any insights it would be great.

When the problem occurs it’s like many statements are stuck. As there are many statements with similar processlist time. This would indicate blocking of some description but I can’t see any contention.

There are no rows in the innodb_locks or lock_waits.

The create temporary table statement in question has a processlist_time of 14 seconds and state: creating table.

extended_waits_current has the following values:

last_statement_latency: 13.61 s

last_wait: NULL

last_wait_latency: NULL

source: NULL

CPU was 29%

No spike in IPOS or queue.

Just one thing to add. Many of the other statements that are running at the same time have a state: Opening tables.

Just wondering if anyone has any suggestions what the bottleneck might be? Thanks.