Having run across a couple very fascinating leads on query/index tuning, i’m listing here the steps i’m taking to do so myself.

1. Get a hint that you have slow queries. status on the mysql command line produces: …..

Threads: 31 Questions: 1850200 Slow queries: 16 Opens: 1268 Flush tables: 1 Open tables: 64 Queries per second avg: 10.706

1. Add the following (or similar) to your my.cnf:

Gordon 20041001: Changed longquerytime to 5s

set-variable = longquerytime=5 logslowqueries = /var/log/mysql/mysqlslowqueries.log

Log queries that don’t use indexes

loglongformat NOTE: If you put a space anywhere in longquerytime=5, it will cause the server to fail.

1. Make sure that the mysql slow queries log is writable by your mysql daemon user.

2. Restart MySQL. It might be helpful to tail your error log to check for errors during restart.

3. Your slow queries log should now be created in the directory you specified. Verify it exists, and tail it while you run your application.

– I’ll update this later with additional analysis tips.

References: http://dev.mysql.com/doc/mysql/en/Slowquerylog.html - MySQL Manual Reference http://databasejournal.com/features/mysql/article.php/2013631 - Article http://retards.org/projects/mysql/ - Perl slow query log parser

Uncategorized