MySQL - how can I avoid high load when I switch schemas (databases)?

Question

I have approx 10M rows of data across 20 tables, which I process once a month.

I do this by copying those tables into a new schema (database) then making the changes, so the updates are not running against tables that are being queried.

To make the new data live, I have tried:

1. changing the database name that's coded into the application to point to the new data
2. running a big RENAME TABLE statement that adds a prefix e.g. OLD to the live tables and moves the new tables into the live database

With both methods the server load goes from approx 0.1 to over 50, before gradually returning to normal. I would like to avoid this spike if possible.

The query cache is switched off, I've tried FLUSH TABLES and restarting MySQL immediately after the change.

The data is only read (not updated) from my site, so I guess it may be Linux (we use RHEL5.3) buffering the 5GB of data, and having to rebuffer the new data.

Has anyone got any suggestions on how to avoid the increased load?

Answer 1

The increase in load you're seeing is probably due to the fact that all the caches that MySQL use to increase performance will contain data specific to the old tables, and it will take a number of queries before this data is purged and replaced with data from the new tables.

My recommendation would be that you run a number of typical queries against the new tables to "warm up" the caches before making the switch in your application logic. You could also implement a scheme where you redirect a small part of the traffic to the new tables for a short period of time before making the switch for all requests.

Don't rename any tables in MySQL. That will screw up your caches even more.