So, an AWS micro instance with around 300mb given to mysql and rest for apache with a live site on it. Everything runs fine in regards to the operation of this setup (didnt really test for ddos but otherwise fine)
New data comes in via a script, prices inserted into 1 MYISAM table of some 1 million records. Noticed a slowdown and an eventual crash of mysql and the whole server sometimes after an import. Import is scripted to mark all old records in a table, then it inserts around 600,000 new records, and finally deletes the marked old records. After this process, mysql seems to just slow down, its using all of its allowed RAM and while the site is still barely responsive it seems to eventually grind to a halt. After a restart everything is fine.
I believe whats happening is its paging all the inserts through ram and then tries to write to disk afterwards possibly causing a slowdown.
No other write operations are performed on the db, this import is the main one. Rest of the data is rarely changed, so all of the rest is mainly reads from tables for site functionality. How would you go about optimising this to remove the slowdown without increasing RAM or the instance size? My thoughts are:
- Optimise the import script, and instead of marking records for deletion, do the deletion immediately then proceed with new inserts (I know I could optimise the inserts for bulk inserts but the script is so complicated I would rather a lazier solution :-)
- Optimise the mysql setup forcing it to insert straight to disk (tried to find this but, there were so many settings to tweak that I just thought nah, too complicated)
- replicate the db into a copy, do the inserts on copy, then migrate the table in question to live db, i supopose theoretically maybe this may alleviate original db from any post processes?