Emergency

The new cool MySQL patch has landed! Check your queries performance!


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button

Microslow patch is used by many DBAs and developers to accurately time their queries and to catch those which run less than a second as they can also be a performance killer for a busy application.

Recently I have started the development of an updated version of the patch. The basic idea is the same as for its predecessor – to get more information about query execution logged into slow log, however the new version is loaded with a set of cool new features.


CONNECTION IDENTIFIER

Each slow log entry now contains a connection identifier, so you can trace all the queries coming from a single connection.

MICROTIME RESOLUTION QUERY TIMING

This is the original functionality offered by Microslow patch. The new edition is free of a tiny bug which was to treat

value as seconds. In effect one could only choose to log all queries to see those which take less than 1s. Now

is in microseconds and you can set it for example to 300000 which is 0.3s!

LOGGING OF THE REPLICATED STATEMENTS

Normally MySQL will not write into slow log any queries executed by the slave’s SQL thread. This patch allows you to change that behavior with the new parameter

.

THE DETAILED INFORMATION ABOUT QUERY EXECUTION

Each query can be executed in various ways. For example it may use indexes or do a full table scan, or a temporary table may be needed. These are the things that you can usually see by running EXPLAIN on the query. The patch will now allow you to see the most important facts about the execution in the log file.

tells whether query cache hit occurred or not. If it says ‘Yes’, then all other values will be ‘No’ or 0, because the query was not actually executed by the engine.

If

is set then most likely the query is a bad one, because it read all the rows from a table.

means any of the joins didn’t use indexes.

If a temporary table is required for query execution

will be set. Sometimes that table must be created on disk instead of in memory, in such case

will state that.

is for indicating that filesort algorithm was used, while

means that sorting was done through temporary files.

INNODB USAGE

The final part are the InnoDB usage statistics. MySQL currently allows you to see many per-session statistics for operations with

, but that does not include those of InnoDB, which are always global and shared by all the threads. The patch gives you the opportunity to see those values for a given query.

is the counter for the number of page read operations scheduled. The acutal number of read operations may be different, but since this can be done asynchronously, unfortunatelly there is no good way to measure that.

is the corresponding value in bytes.

With

you can see how much time (in seconds) it took InnoDB to actually read the data from storage.

is the time (in seconds) query had to wait on row locks.

is the time (in seconds) query spent either waiting to enter the InnoDB queue or inside that queue waiting for execution.

tells the number of unique pages accessed by the query. This is actually an approximation based on a small hash array representing the entire buffer pool, because it could take a lot of memory to map all the pages. The inaccuracy grows with the number of pages accessed by a query as there is more chance for hash collisions.

In case InnoDB is not used for the query execution that information is written into the log instead the above statistics.


The full slow log entry for a query using InnoDB can look like this:

or if InnoDB was not used:

Important note: Although the patch was tested and run successfully under stress with both synthetic benchmarks and real database traffic, it’s considered experimental and as such I don’t recommend using it in production or on any important system!

The patch is available for MySQL Community 5.0.45 on Linux. May not work on other systems.

Maciek Dobrzanski

Share Button
PREVIOUS POST
NEXT POST


Maciej Dobrzanski

Maciek is a former Percona employee. An IT consultant with the primary focus on systems, databases and application stacks performance and scalability. Expert on open source technologies such as Linux, BSD, Apache, nginx, MySQL, and many more. Co-author of dba square - a blog about how to manage, scale, and optimize MySQL performance!



Tags:

, ,

Categories:
Percona Software


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *