The mysql servers we use are configured to log slow queries ( those that take over 5 seconds to run or queries that do not use any index).
The output is put in /var/log/mysqld.slow.log ( on the dbhost server! )
I found a program today that is part of mysql that makes it easier to use this log file to pinpoint queries that might be slowing things down. It is called mysqldumpslow
To run it just pass the name of the log file and it converts the variables in the queries in the log file to tokens so it can group similar queries together and tell you how often a particular query is run. It sorts the queries by how long they take to run, so you can focus on queries your apps are running that are taking a long time to run ( and making the user wait )
Typically adding indexes or rewriting the query to be more efficient can greatly improve performance.
For example I just ran it on our db1 log and immediately realized that the query we run to check a persons PKI DN certificate ( which is run for every page we display ) was doing the query with no index on pki_dn field — so it was sequentially scanning through the users table every time. I created an index on pki_dn that should make that check faster.
To use it:
/usr/bin/mysqldumpslow /var/log/mysqld.slow.log
NOTE: your mysql database must be configure to log slow queries. This is done with the following queries in /etc/my.cnf ( in the main [mysqld] section )
log-slow-queries = /var/log/mysqld.slow.log
log-long-format
long_query_time = 5
log-queries-not-using-indexes=1
If the file doesn’t already exist it won’t create it. So be sure to make the file /var/log/mysqld.slow.log and set permissions to be writable by mysql user and restart mysql like this:
> /var/log/mysqld.slow.log
chown mysql:mysql /var/log/mysqld.slow.log
chmod 770 /var/log/mysqld.slow.log
service mysqld restart