MySQL Performance Blog

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 --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 --log-slow-queries=slow.log and --log-output=FILE.

Also you need to configure so called long query time. It’s done either with startup parameter --long_query_time=n or by setting MySQL system variable with 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 --log-slow-slave-statements. Please note, that any runtime changes of long_query_time or 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. 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 long_query_time=300000.

Maciek

In my previous post I was a bit wrong giving Innodb some properties it does not have.
In fact Innodb does not currently sort pages in their position order flushing them to disk. Pages to be flushed are instead identified by other means - LRU and pages which contain oldest LSN (so which were flushed longest time ago).

As pages are to be flushed are identified Innodb looks at pages pages N-1 and N+1 and if they are dirty schedules flush for these as well. Such approach allows to merge few pages together and perform flushes in larger blocks.

It is really interesting to see how much sorting would speed things up, ie how much flushing pages 1 10000 200 20000 300 30000 in order 1, 200, 300, 10000, 20000, 30000 will help compared to random order and if it is worth to bother or if optimization Innodb performs is good enough and IO subsystem and RAID can do the rest.