Mysql – Slow-running query on clustered MySQL environment

innodbMySQLmysql-clusterndbcluster

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.

When I run the same query on one of our clustered load-balanced servers, it takes upwards of half an hour to complete. The data on both servers is exactly the same.

Why is this occurring?

Here is some more info on the setup:

  • The total number of rows in the table is ~14k, so it doesn't get anywhere near the LIMIT.
  • 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 NDBCLUSTER
  • InnoDB on my local server

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;

The query returns 11,154 records, which is the correct amount for the query.

Regarding latency between the data nodes, this is what a typical ping request returns:

64 bytes from 192.168.3.45: icmp_req=1 ttl=64 time=0.479 ms

Best Answer

You may want to consider if you really need the MySQL Cluster, it may be overkill for your purposes and may not justify the complexity. A pair of MySQL Master-Master or Master-Slave instances with replication are good enough for many shops and will deliver better performance in this scenario.

If you have to stay with your current architecture, you can add Index on column "somecol", first try HASH, then B-TREE index. These should help, still performance would not be as good as a simple host instance.

Related Question