Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

What can cause queries to block on 'opening tables'?

jamesjames ContributorInactive User Role Beginner
I have an InnoDB table used for logging ('log') and an archive of that table in MyISAM ('log_archive'). There is a trigger on 'log' that, on insert, inserts an identical record into 'log_archive'. The 'log' table is emptied nightly; the 'log_archive' table is never emptied.

We currently have no queries that retrieve data from either table.

log has a few thousand records (we insert about 250,000 per day); log_archive has about 9 million.

Inserts into the log table get stuck frequently on 'opening tables,' sometimes for more than a minute. I can't figure out why, though, since all we're doing are simple inserts, and the MySQL manual says opening table should complete instantaneously unless the thread is blocked.

Usually all of the inserts will eventually complete and the server will go about its business, but when they get stuck for upwards of a minute, the max connections is reached and load spikes on the server.

The server is not too heavily loaded.

I'm running 5.0.22 (64-bit).
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.