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).<\/p>\n
The output is put in \/var\/log\/mysqld.slow.log\u00a0 ( on the dbhost server! )<\/p>\n
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. \u00a0It is called mysqldumpslow<\/strong><\/p>\n 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.\u00a0 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 )<\/p>\n Typically adding indexes or rewriting the query to be more efficient can greatly improve performance.<\/p>\n 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\u00a0 \u2014 so it was sequentially scanning through the users table every time.\u00a0\u00a0 I created an index on pki_dn that should make that check faster.<\/p>\n To use it:<\/p>\n \u00a0<\/p>\n \/usr\/bin\/mysqldumpslow \/var\/log\/mysqld.slow.log<\/p><\/blockquote>\n \u00a0<\/p>\n NOTE: your mysql database must be configure to log slow queries.\u00a0 This is done with the following queries in \/etc\/my.cnf\u00a0 ( in the main [mysqld] section )<\/p>\n log-slow-queries = \/var\/log\/mysqld.slow.log \u00a0<\/p>\n If the file doesn\u2019t already exist it won\u2019t 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:<\/p>\n > \/var\/log\/mysqld.slow.log<\/p>\n chown mysql:mysql \/var\/log\/mysqld.slow.log<\/p>\n chmod 770 \/var\/log\/mysqld.slow.log<\/p>\n service mysqld restart<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":" 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\u00a0 ( on the dbhost server! ) … Continue reading
\nlog-long-format
\nlong_query_time = 5
\nlog-queries-not-using-indexes=1<\/p><\/blockquote>\n