I certainly don’t profess to be a MySQL performance tuning expert. What I am is a long-time MySQL lack of performance sufferer. In fact, writing this I’m in the process of waiting for several table deletes and MySQL WorkBench has frozen (again).
Anyway, here’s some thoughts:
- If you have heavy writes bear in mind this severely affects locking for MyISAM tables. One thing that sucks with MySQL table engines is you trade off performance (MyISAM) against granular locking (InnoDB).
- Try to avoid tables getting too large to manage. If you’re storing historical data write an archive / cull script early on to keep the database manageable.
- Use SHOW PROCESSLIST to see what’s running if you’re experiencing problems. Processes marked ‘locked’ or ‘freeing items’ are bad.
- If you get into trouble and need to delete all the rows in a table, use TRUNCATE TABLE [tblname] rather than DELETE FROM. This is significantly quicker. (Thanks Rainer for that one.)
- Most obviously – use indexes judiciously. Adding indexes slows down insert speed to allow better retrieval rate. If the tables are too index heavy the insert speed will slow down such that it causes more locking.