BenMorel
asked on
Safe table rotation in MySQL (MyISAM)
Hi all,
I have a big MyISAM table (hence not supporting transactions, but I do need MyISAM for performance reasons), that needs to have a lot of records deleted / added every day at a fixed time (by a cron), and only at this time.
To avoid downtimes caused by the locks I need to do on the table, I want to copy the table to a new one, make my mods on the new table, then rotate the tables.
The code snippet below shows my idea, for rotating a "content" table.
The problem is that in the (very small) amount of time between DROP TABLE and RENAME, a query could be done by another script on the "content" table, which does not exist anymore. This is likely to happen under a very high load.
So I would like to "lock" any thread trying to access the "content" table, even if it does not exist.
I tried with a LOCK TABLES content WRITE, but the lock is of course released as soon as the table is droped.
Is there any way to achieve this ? Some kind of locking a table name even if the table does not exist ?
Thanks,
Ben
I have a big MyISAM table (hence not supporting transactions, but I do need MyISAM for performance reasons), that needs to have a lot of records deleted / added every day at a fixed time (by a cron), and only at this time.
To avoid downtimes caused by the locks I need to do on the table, I want to copy the table to a new one, make my mods on the new table, then rotate the tables.
The code snippet below shows my idea, for rotating a "content" table.
The problem is that in the (very small) amount of time between DROP TABLE and RENAME, a query could be done by another script on the "content" table, which does not exist anymore. This is likely to happen under a very high load.
So I would like to "lock" any thread trying to access the "content" table, even if it does not exist.
I tried with a LOCK TABLES content WRITE, but the lock is of course released as soon as the table is droped.
Is there any way to achieve this ? Some kind of locking a table name even if the table does not exist ?
Thanks,
Ben
CREATE TABLE content_temp (...);
INSERT INTO content_temp SELECT * FROM content;
/* ... make my mods here on the content_temp table ... */
DROP TABLE content;
ALTER TABLE content_temp RENAME content;
more advanced - if you have high frequence of inserts into content.
But remeber that constructions like CREATE TABLE content_temp SELECT * ... are locking the whole table content.
CREATE TABLE content_temp SELECT * FROM content;
ADD primary key, indexes etc .....
SELECT MAX(id) last_id from content_temp;
START TRANSACTION;
INSERT INTO content_temp SELECT * FROM content WHERE id > $last_id;
ALTER TABLE content RENAME old_content;
ALTER TABLE content_temp RENAME content;
COMMIT;
DROP TABLE old_content;
But remeber that constructions like CREATE TABLE content_temp SELECT * ... are locking the whole table content.
CREATE TABLE content_temp SELECT * FROM content;
ADD primary key, indexes etc .....
SELECT MAX(id) last_id from content_temp;
START TRANSACTION;
INSERT INTO content_temp SELECT * FROM content WHERE id > $last_id;
ALTER TABLE content RENAME old_content;
ALTER TABLE content_temp RENAME content;
COMMIT;
DROP TABLE old_content;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@racek : thank you but as I stated, I use a MyISAM table which does not support transactions.
@sjon1966 : nice, I didn't know RENAME TABLE ! Thanks a lot, you got the points :)
Ben
@sjon1966 : nice, I didn't know RENAME TABLE ! Thanks a lot, you got the points :)
Ben
ASKER
Exactly what I wanted. Thanks.
BEGIN TRANSACTION;
ALTER TABLE content RENAME old_content;
ALTER TABLE content_temp RENAME content;
COMMIT;
DROP TABLE old_content;