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).