Slow-running query on clustered MySQL environment

Question

I've got a very simple query, directly equivalent to

SELECT * FROM sometable t WHERE somecol = 'somevalue' ORDER BY createdon DESC LIMIT 0,20000;

When I run this query on my non-clustered development machine, it runs in 0.07 of a second to return the 14k row result-set. When I run the same query on one of our clustered load-balanced servers, it takes upwards of half an hour to complete (if it completes at all). The data in both environments is exactly the same.

Running EXPLAIN against the query on the clustered box returns a 'row' value of 6, whereas if I run it on my dev machine it returns a row value of 11177!

Can anyone shed some light on why this is occurring? I suspect it's down to cluster or network lag/sync issues but I've no idea how to troubleshoot/diagnose for certain.

Here is some more info on the setup:

The total number of rows in the table is ~19k, so it doesn't even reach the LIMIT restriction
MySQL version is MySQL Server: 5.1.56-ndb-7.1.15a-cluster-gpl
Ubuntu 11.04 (GNU/Linux 2.6.38-8-server x86_64).
Storage Engine is NDBCLUSTER on the cluster, InnoDB on my dev machine

The table schema is as follows, where {X} is InnoDB on my local machine or NDBCluster on the production servers.

CREATE TABLE `sometable` (
    `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `UserId` bigint(20) unsigned NOT NULL,
    `Cookie` varchar(255) DEFAULT NULL,
    `somecol` varchar(30) DEFAULT NULL,
    `IpAddress` varchar(255) DEFAULT NULL,
    `SomeCollection` text,
    `someothercolumn` decimal(6,2) NOT NULL,
    `someothercolumn2` decimal(6,2) DEFAULT NULL,
    `Result` tinyint(4) NOT NULL,
    `Version` tinyint(4) NOT NULL,
    `Source` varchar(255) DEFAULT NULL,
    `CreatedOn` datetime NOT NULL,
    PRIMARY KEY (`Id`),
    KEY `CreatedOnIndex` (`CreatedOn`),
    KEY `SomeColIndex` (`somecol`),
    KEY `ResultIndex` (`Result`),
    KEY `SomeCol2Index` (`someothercolumn2`)
) ENGINE={X} AUTO_INCREMENT=97043 DEFAULT CHARSET=latin1;

Thanks in advance.

Dina Kaiser Dina Kaiser · Accepted Answer · 2012-08-07T12:44:32

First of all, you may want to consider if you really need the MySQL Cluster, its performance and complexity are the reason why many people choose not to use it. A pair of MySQL Master-Master replication or Master-Slave replication are good enough for many shops. Secondly if you have to stay with your current architecture, you can add Index on column "somecol", first try HASH, then B-TREE index, they should help, still performance would not be as good as your simple host instance.

Slow-running query on clustered MySQL environment

1 Answers