Not the answer you need?
Register and ask your own question!

Slow Create Temp Table/ CREATE INDEX on temp table

8adger8adger Current User Role Contributor

 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.

Answers

  • 8adger8adger Current User Role Contributor

    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.

  • 8adger8adger Current User Role Contributor

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

  • 8adger8adger Current User Role Contributor

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

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.