Once a day I need to update an MySQL table with a new file downloaded from the Net using ftp and then mysqlimport. However, I want my website to keep running smoothly during the mysqlimport operation, which takes quite some time (it's a big table). What would be a good way to assure that users do not wait for the import to finish? I am thinking of importing to a temporary table and then renaming it. Is that a good plan?
A:
If it's a case of an incremental update, you could simply throttle the insertion of new data via cron / scheduling.
Load XYZ records every minute, see if the previous update has finished, make a lockfile, delayed insert, rinse and repeat.
If you want to get real smart about it, have the update script check the server load at runtime and adjust the number of imports accordingly.
This will cause fresh data to appear in batches, but will let you keep the site running while it's being updated.
code_burgar
2009-12-05 18:25:48
+6
A:
A little known fact is that you can concatenate more than one rename statement in mysql and it will perform all of the name changes atomically.
create table newtable like oldtable;
insert into newtable .......
rename table oldtable to deleteme, newtable to oldtable;
drop table deleteme;
The rename table will block readers (in fact it will block any resolution of the name) but should be a very fast operation.
ʞɔıu
2009-12-05 18:29:12