mysql performance degrades after increasing data stored. Need hints on systematic diagnostic procedure

Question

We have an established web application, which is supported by a mysql database. We add data incrementally to various tables on a monthly basis. After recent updates, the database performance has degraded significantly and we had to back out the updates. So far I have identified the queries involved in the web service logic - there are no updates, just reads. I've established that all the queries are using efficient table indexes.

I know this is a difficult problem to diagnose, and I'm not expecting a definitive answer here, I just need some advice on further diagnostics, and hints for areas to investigate which will pin down what's causing the problem.

Answer 1

One thing that can have a huge impact is how you allocate memory to MySQL for you given usage pattern.

The relative size of your query cache, innodb buffer, etc should be tuned for your specific needs.

Here are a few links that helped me resolve the same type of issue.

MySQL Server Performance (requires free registration)

10 MySQL Variables that You Should Monitor

Understanding Performance Statistics

MySQLTuner is a great (free) tuning diagnosis tool written perl.

Make sure you have enabled the slow query log and review it periodically. As your data volume grows, things that used to be fast will be slow. After you re-tune your memory parameters, fast queries can also become slow and vice versa as you shift resources from one type of query to another.

Even though your queries look good to you, make sure and run the Explain command to verify that the indexes are being used.

Additionally I purchased MySQL Enterprise from Sun and find their included performance monitor and query analyzer very useful.