Microslow patch has been there for some time, but only for earlier MySQL editions such as 4.1 and 5.0. Now it’s also available for the latest 5.1.
Because MySQL went through a lot of internal changes, the patch had to be written from scratch. It introduces some minor change in existing functionality and also adds new.
For the patch to work you must of course enable slow logging with
1 | --slow-query-log |
parameter. MySQL 5.1 has this nice feature which allows you to redirect query log (it’s actually called general log now) and slow log to CSV tables mysql.general_log and mysql.slow_log respectively. The patch will not work as intended with those tables, because there is no space for storing 64bit integers for time with microsecond resolution. Instead you should redirect the output for your logs to the plain old-fashioned files. In MySQL 5.1 it’s done by setting
1 | --log-slow-queries=slow.log |
and
1 | --log-output=FILE |
.
Also you need to configure so called long query time. It’s done either with startup parameter
1 | --long_query_time=n |
or by setting MySQL system variable with
1 | SET [SESSION|GLOBAL] long_query_time=n |
. The value is in microseconds (0 means to log all queries).
The new feature is that patch can allow you to log queries executed by the slave thread. This can be achieved by running MySQL with startup time parameter
1 | --log-slow-slave-statements |
. Please note, that any runtime changes of
1 | long_query_time |
or
1 | min_examined_row_limit |
variables will take effect on the slave thread only after it’s restarted with SLAVE STOP and SLAVE START commands.
The change in functionality is more of a bugfix than anything else.
1 | long_query_time |
value was meant to set time limit in microseconds, but in the end it was always compared to seconds of query execution time. Now it’s fixed, so for example you can log only those queries that run 0.3s or more with
1 | long_query_time=300000 |
.
Maciek
That’s cool. I’ll test our apps ASAP. Let’s look where we can speed things app 😉