Rotten MySQL performance can generally be attributed to a couple of simple optimization errors I see over and over again. Sometimes the problems are from our initial server setup, other times they are due to the growth of our database size. Give these a check:
- Using MyISAM tables– MyISAM tables are a deadend. No more development is coming on them, and honestly, there is only one reaason I can think of other than laziness to use them: if you are using full text search functions in MySQL. Here are a couple reasons why not to use them:
- Table locking – once you get over 50k rows of data in the table, it every write will lock the whole table. InnoDB offers row level locking that resolves this.
- MyISAM is prone to table corruption, which can ruin your day.
- MyISAM only buffers indexes – properly configured InnoDB tables can be fully buffered.
- InnoDB Buffer size – Did you ever try running a marathon with a sweatsock stuffed in your mouth? That’s essentially what you do when you don’t give the server adequate InnoDB buffer size. The default is 8m which is ludicrously small – I like to see a full gig or more depending on the db server. Read more here at the MySQL Performance Blog. I have seen 10x speed improvements from just this one change.
- Undersized DB Machine– it shouldn’t happen with relatively cheap and available machines, memory and disk space, but hardwae is a perennial problem.
- Not enough disk space
- Not enough memory (*remember, you’re going to want to keep the whole db in buffers…)
- Overused resource – you’re running HOW MANY DBs on that server?
- Storing Transient Data forever – another classic – how about 100 million rows of application data you never should have stored in the first place? Or how about low level logging of user actions like “SnurfMonkey uploaded a file at 10:23” – there are better places for that stuff, even if it isn’t traditional transient data.
- Backups – do you have backups? Do they work? Honestly most people don’t really know. Even at fairly large companies, many mission critical dbs may not be fully backed up and their utility is rarely tested. In the case of small hosted sites, most site owners rely on the hosting company to handle backups. The problem is, they have no idea if the backups are really happening, or where. For a system like WordPress, I use a plugin to simply backup and mail a copy of the db to me for safekeeping. Its saved me a bunch of times.