Smart database usage is about more than optimizing slow queries. You also need to make sure you’re only running queries when necessary. A single N+1 query can really cause some serious problems, but luckily there’s a pretty easy way to find these hot spots: MySql query logging.
To enable query logging follow these steps (Tested with MySql 5.0):
- Add this to your my.cnf:
log=/var/log/mysql/query.log
- Make sure the directory exists and the user that is running mysql has write access to if.
sudo mkdir -p /var/log/mysql/ sudo chmod 750 /var/log/mysql/ sudo chown mysql:mysql /var/log/mysql/
- Restart your MySql server using whatever command is appropriate for your OS:
/etc/init.d/mysql restart - Generate some traffic
- Note the file size of the log increasing. It’s going to grow pretty fast so don’t leave it running overnight or your disk may fill up!
Now that you have some data you’ll need to analyze it. There are lots of tools available, but I’ve had good luck with the simple but awesome open source php script called myprofi
php ./parser.php query.log > analysis.txt
