Mysql – Small MySQL table swap/replace taking incredibly long time

innodbMySQL

We have an import job that commits data to a staging table, and then swaps the production/staging tables using the below SQL:

RENAME TABLE user_accounts TO user_accounts_temp, user_accounts_staging TO user_accounts, user_accounts_temp TO user_accounts_staging;

Aside from writing to the staging table during the import, the tables are only read by our application (no other writes). The staging/production tables are always around same size (~40k rows). All reads to the staging/production table queries are by ID. There are no FKs in the tables.

The mystery is that the time to do the RENAME takes between 0sec-8min (usually it's 0sec). But in the latter case, it blocks reads to the table causing our production site to hang.

How can I fix/understand what the problem is? My understanding of what I've read about the rename operation is that this should always be nearly instant, and this is the best practice for doing periodic imports.

Best Answer

Rename table requires a LOCK on the table to complete.

In your 0sec case there were no connections using the table and it works immediately.

In you 8 min case, The RENAME table waits in a stage something like "waiting for table metadata lock" until the existing SELECTS are done. Also the SELECTs that are issued after the RENAME waits on the lock. Hence the hang you notice.

You could run show full processlist when this occurs to confirm.

To avoid this, you will have to time the rename in the same app that is reading or kill the existing connections that are holding the locks.

Perhaps the SELECT queries need to be tuned as well. 40K row table shouldn't take that long to return. Since you mentioned all the queries are by ID, is there an INDEX on ID?

Related Question