With MySQL 5.1, getting and analyzing all the queries hitting the database is very easy even if you don’t have access to the MySQL enterprise Monitor. You can either activate the general log or, my favourite option, activate the slow query log with a sub millisecond long query time. Long query time below one second is an option introduced in MySQL 5.1. In order to catch all the queries, you will also need to disable the query cache. The main advantages of the slow query log over the general log is that you have access to more information like the query time, the lock time, the number of rows examined and the number of rows sent back. All the options surrounding the slow query log in MySQL 5.1 are dynamic so it is easy to turn the logging ON and OFF. Once you have the queries, tools, like mysqlsla, can be used to anlyzed the queries and group them by type.
All these tips are nice if you are using MySQL 5.1 but what if …
[Read more]