Analysing MySQL Slow Query Logs

MySQL has a really useful feature that allows it to log slow queries where slow is a minimum time defined by you in micro seconds. It helps a lot is diagnosing website outages or slow responsiveness issues after the fact.

Unfortunately I couldn’t find any nice graphical tools for analysing these but there are a few command line tools:

mysqldumpslow

MySQL’s own tool, mysqldumpslow, which aggregates queries and allows you to sort them by: query time or average query time; lock time or average lock time; rows sent or average rows sent; or the number of queries.

Percona’s MySQL Slow Query Log Analyser

Dating from 2006, Percona’s Peter Zaitsev wrote about their own version of a slow query log analyser (local copy) which has given me good results. Note that their micro time patch has since been incorporated into MySQL mainstream.

One of the main differences over MySQL’s own version is that as well as printing the aggregated query (with number and string literals wildcarded), it also prints a real example of the query allowing a copy and paste to MySQL for execution with EXPLAIN.

Example output with query details redacted:

 

New Replication Features in MySQL 5.6

I’ve just been reading an article on replication in MySQL 5.6 and there are quite a few new cool features that will vastly improve replication environments with MySQL. Some of these include:

  • Optimised row based replication (documentation here) – replication used to mean execute the exact same queries on the slave server(s) as the master. This, as you can imagine, was incredibly inefficient. With row based replication, the resultant changed row of an INSERT, UPDATE or DELETE operation was replicated saving significant resources on slave servers. This was a big win. It now looks like it’s been further optimised in that only the changed elements (and a primary key) need to be replicated.
  • Introduction of Global Transaction Identifiers (GTIDs) allowing the source and sequence of a replication statement to be globally unique within a replication cluster. This with some cool new utilities (mysqlfailover and mysqlrpladmin) create a lot of native options for failover for maintenance or failure (see more about GTIDs and the utilities).
  • Time delayed replication allowing a slave to purposefully remain behind the master for any given delay. This may be a life saver for that table you accidently dropped!

There’s a lot more too and you can read about it all here.

We’ve Just Launched TallyStick

TallyStick allows you to focus on what you love to do while it takes care of tracking and accumulating the time you spend on projects. As of today, TallyStick is live and open for business. Please sign up, take advantage of the one month’s free trial (with no credit card details required) and tell us what you think.

[TallyStick Logo]

Overt at Open Solutions, we manage networks, build Internet infrastructure, consult and develop web applications for many customers. Tracking the time our engineers spent on different projects – especially those on the front line hopping from customer to customer – was becoming an issue.

And we had enough. So we built TallyStick. Necessity, as the saying goes, is the mother of invention.

We believe in web applications that are simple, beautiful and functional. And after an exhaustive search, we couldn’t find one to fit our needs… so we built it. And who better to build a time tracking application for IT and other consulting professionals than those same professionals!

TallyStick allows you to focus on what you love to do while it takes care of tracking and accumulating the time you spend on projects. We believe it’s simple, efficient and quick. And we hope you agree.

As of today, TallyStick is live and open for business. Please sign up, take advantage of the one month’s free trial (with no credit card details required) and tell us what you think.

Also, all users signing up during the beta period will receive a lifetime discount of 10% on that account!

Using Doctrine ORM with Zend Application

In this first of a serious of articles where we delve into some of the hidden treasures in our ViMbAdmin application, we look at how to integrate Doctrine ORM with Zend – and specifically Zend_Application and Zend_Controller.

In this article we delve into our ViMbAdmin application and we look at how to integrate Doctrine ORM with Zend – and specifically Zend_Application and Zend_Controller.

The first assumption (and requirement) we are going to make is that you are using Zend_Application. If you want to see a working application set up and configured for this, please checkout or browse our ViMbAdmin source code – which we’ll reference throughout this document.

Zend Application has a resource framework which allows us to bootstrap various resources on demand. We have created a Doctrine resource for this very purpose which you can download from here (and you may need to edit the class name and change the plugin path in the config code below to match your setup). Our implementation does many things:

  • instantiates the Doctrine object;
  • sets up an autoloader for Doctrine models;
  • instantiates the Doctrine manager;
  • opens the connection to the database;
  • sets all collations and character sets to UTF8 (this is hard coded but can easily be changed);
  • sets various hard coded Doctrine attributes which can also be changed.

We the add various configuration parameters to the application.ini file:

 

Or the following where $application is the instance of Zend_Application:

From that, you can use Doctrine to your hearts content!

We also have a Doctrine CLI script which works from the same resource. See:

http://code.google.com/p/vimbadmin/source/browse/trunk/bin/doctrine-cli.php

 

Doctrine ORM – Find Many to Many Objects Without a Relationship

Hmmm, does the title of this post make sense? Probably not but it’s not an easy concept to squeeze into a few words.

Here’s the scenario, I have two tables A and B in Doctrine ORM with a many-to-many relationship defined in table AB.

Now, I want to find all objects in A that do not have a relationship with an object in B via AB.

Here’s what I have:

This works but is it the best way?