Update MyISAM tables on live machine (MySQL)

Question

I need a way to update tables on live machine without affecting users experience.

I have few tables on MyISAM engine which are being updated every 15 minutes. Update script just truncates table and inserts data from select sentence. Problem appears then user tries to view information from table which is being updated - user experience lags, uncorrect information is being shown.

It would be easy to do with InnoDB transactions, but what about MyISAM?

score 2 · Answer 1 · answered Jun 18 '12 at 13:45

2

As you wrote yourself. Move to InnoDB, Or explain what stops u from doing that move.

answered Jun 18 '12 at 13:45

Itay Moav -Malimovka

52,579
61
190
278

score 2 · Answer 2 · answered Jun 18 '12 at 13:48

If you can't move to InnoDB, the only thing I can think of is add a layer of caching when updating. (APC or memcached for instance?)

You can even write something that normally skips the cache, and when you start an update cycle sets the current contents in the cache with an expiration of "far into the future", serve contents from cache, and invalidate them when the update is done.

Added advantage is that you now have a working cache system, maybe making everything faster :)

score 1 · Accepted Answer · answered Jun 18 '12 at 14:00

You might try this kind of thing if you MUST use myISAM. You need to be at version 5.0 or higher. If you're not, upgrade.

Create a new table with a temporary name
Populate the new table (this is the time-consuming operation)
Lock your production table.
Rename the production table to something else.
Rename your temporary table to the name used for your production table.
Unlock the production table.
Drop the old production table (or do something else to re-use the disk space).

If you can make the software you're using to read this resilient to a SQL error that complains about a missing table, and simply have it try again after a second or so, you can skip the locking and unlocking steps. But those steps protect your system from the brief moment, during the renaming shuffle where there's no table with the correct name for the production table.

This is monkey business, and takes a bit of debugging. But it has worked very well for me.

(I define "monkey business" to be stuff outside the scope of pure, elegant, SQL.)

score 1 · Answer 4 · answered Jun 18 '12 at 14:07

1

You could work with temporary table names and present the user a VIEW.

The steps would be:

CREATE TABLE newtable
INSERT INTO newtable
CREATE OR REPLACE VIEW
DROP TABLE oldtable

answered Jun 18 '12 at 14:07

glglgl

89,107
13
149
217

Update MyISAM tables on live machine (MySQL)

4 Answers4