This guide will get you up and running with how to identify the bottleneck queries using the excellent tool pt-query-digest. You will learn how to use and analyze the output returned by pt-query-digest. You will also learn some differences between slow query logging in various MySQL versions. Later on in the post I will also show you how to make use of the extra diagnostic data available with Percona Server.

When looking at a set of SQL statements in isolation with tools such as the slow query log, processlist and tcpdump/mk-query-digest it is easy to identify queries that are slow.

It is more difficult however to identify the frequency of the query, and whether the slow query is actually acceptable in your overall system design.

I very simple technique to help you is to comment your queries. For example:

SELECT /* 10m cache */ ...;

When I’m working for a client, this process enables me to realize the purpose of the query more quickly and to help prioritize which queries I need to improve first. This does not mean I simply ignore these less frequent queries, however my choices for reviewing and indexing queries on given tables is adjusted generally for OLTP queries first, and batch second.

As with all practices it is important to implement across your full code base.

This is a hack, but it is a good one. I was looking at some machines on a new client, and they had the general log turned on. I was surprised, because it was a fairly busy server, and they had had many problems with the server a few months ago. I thought perhaps they had turned on the general log to diagnose a problem and forgotten to turn it off, or something similar.

When I looked at the log on disk, I saw it was a symlink to /dev/null. They were running MySQL 5.0, so I immediately realized that it was a hack to have a general log they could “turn on” without having to restart mysqld.

On a different server, I saw the same link to /dev/null done with a slow query log.

The drawbacks to doing it this way is that MySQL still has the overhead of writing to the log file. The I/O overhead is greatly reduced because the writes are

  [Read more...]

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://technocation.org/node/663/play. To download the 146 Mb video to your computer for offline playback, go to http://technocation.org/node/663/download. The slides can be downloaded as a

  [Read more...]