Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;

Thanks in advance.

share|improve this question
    
You mentioned that the table has only 14K rows. How many rows does the query return? Do you have an index on (somecol, createdon)? Or on (somecol, createdon, PK)? On (createdon, somecol)? –  ypercube Jul 17 '12 at 19:46
    
It returns 11154 records, which is the correct amount for the query. Yes the table has been indexed appropriately. –  keithl8041 Jul 17 '12 at 20:39
    
Can you provide the table's definition (especially the clustered one)? –  ypercube Jul 18 '12 at 7:51
    
Updated question with table schema. I should have put that in originally, apologies and thank for your interest in the question. –  keithl8041 Jul 18 '12 at 9:26
1  
This might be a silly question (and given the age of the post, probably only useful for those who stumble upon here from google) but is there latency between the data nodes? Even a small amount of latency can make otherwise normal queries incredibly slow. –  dpk Oct 27 '12 at 17:10
show 1 more comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.