Using recently MySQL (5.5.23) in a project to persist JBoss JMS messages (among other things), I wanted to see if any slow or un-indexed queries were issued, and apparently the database server allows to log those through a simple change in my.ini configuration file.
If not specifying a file name, it will create a HOSTNAME-slow.log; in my case (Windows 2008 R2) the log was created in C:\ProgramData\MySQL\MySQL Server 5.5\data
Queries taking longer than specified amount of seconds will get logged.
Logs queries not using indexes or performing a table / index scan.
my.ini## Slow queries#log-slow-querieslong_query_time = 1log-queries-not-using-indexes
I found a small un-indexed query on JMS_USERS - although a small table of only 5 records, it does get issued repeatedly so I’ve added an index for it.
# User@Host: jboss[jboss] @ localhost [127.0.0.1]# Query_time: 0.000000 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 5SET timestamp=1334525528;SELECT JMSUSERID, PASSWD, CLIENTID FROM JMS_USERS WHERE CLIENTID='ID:1';
Also found a SELECT COUNT(*) query that was basically scanning the table - I could remove this one by changing mysql-ds.xml from using a <check-valid-connection-sql> statement to using <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
# User@Host: jboss[jboss] @ localhost [127.0.0.1]# Query_time: 0.000000 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 5SET timestamp=1334525528;SELECT COUNT(JMSUSERID) FROM JMS_USERS;
If you want to log all queries there’s another parameter for that, placing all queries in HOSTNAME.log, very helpful in improve performance further by simply reducing the amount of operations.
my.ini## All queries#general-log = 1