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 have a mysql cluster with 3 Data Nodes and one SQL node. Curently I have 1million 1kb records. The inserts are very fast, takes only a few ms. But the selects are very slow >20s.

All machines are m1.large EC2 instances (8GB RAM).

Here is the config.ini:

[NDB_MGMD]
NodeId=49
HostName=host1
DataDir=/var/lib/mysql-mgmd-data
Portnumber=1186

[NDBD DEFAULT]
NoOfReplicas=1
DataMemory=1536M
IndexMemory=2560M
StringMemory=25
MaxNoOfTables=4096
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=512
MaxNoOfAttributes=24576
MaxNoOfTriggers=14336

FragmentLogFileSize=256M
InitFragmentLogFiles=SPARSE
NoOfFragmentLogFiles=16
RedoBuffer=48M
MaxNoOfConcurrentOperations=100000
MaxNoOfConcurrentTransactions=16384
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=100
TimeBetweenEpochsTimeout=32000

DiskCheckpointSpeedInRestart=100M
DiskCheckpointSpeed=10M
TimeBetweenLocalCheckpoints=20

HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000

MemReportFrequency=30
BackupReportFrequency=10
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15

SharedGlobalMemory=384M
DiskPageBufferMemory=1G
BatchSizePerLocalScan=512
[NDBD]
NodeId=1
HostName=host2
DataDir=/mnt/mysql-cluster/1/
[NDBD]
NodeId=2
HostName=host3
DataDir=/mnt/mysql-cluster/2/
[NDBD]
NodeId=3
HostName=host4
DataDir=/mnt/mysql-cluster/3/
[MYSQLD DEFAULT]
[MYSQLD]
NodeId=53
HostName=host1

my.cnf

[mysqld]
ndbcluster
ndb-nodeid=53
ndb-connectstring=host1,

Insert:

INSERT INTO mytab(mykey, a, b, c, d, e, f, g, h, i,j)VALUES (1,1,2,3,4,5,6,7,8,9,0);

Select:

SELECT * FROM mytab WHERE mykey = 12345;

Create table statement:

 mytab | CREATE TABLE `mytab` (
  `mykey` varchar(32) NOT NULL,
  `a` varchar(100) DEFAULT NULL,
  `b` varchar(100) DEFAULT NULL,
  `c` varchar(100) DEFAULT NULL,
  `d` varchar(100) DEFAULT NULL,
  `e` varchar(100) DEFAULT NULL,
  `f` varchar(100) DEFAULT NULL,
  `g` varchar(100) DEFAULT NULL,
  `h` varchar(100) DEFAULT NULL,
  `i` varchar(100) DEFAULT NULL,
  `j` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`mykey`)
) /*!50100 TABLESPACE mytab_space STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 MAX_ROWS=1000000000
/*!50100 PARTITION BY KEY (mykey) */ |

Has someone an idea why it is so slow? Has someone experience with mysql-cluster and EC2 m1.large instances?

share|improve this question
    
Please post INSERT and SELECT examples. If your SELECTs are doing full scans or large range scans - 20s is not fully unexpected. MySQL Cluster is quite slow at those. And I do not think that EC2 is good place for Cluster. Data nodes are quite latency sensitive. –  Mindaugas Riauba Dec 13 '13 at 7:24
    
so you mean 10-20seconds for a select on a database containing 1 million 1kb records is normal? –  user2601966 Dec 13 '13 at 8:29
    
MySQL Cluster, EC2 and disk storage. No pun intended but you did everything to slow down things as much as possible. :) Still 20s for primary key lookup sounds way too slow. Look dev.mysql.com/doc/refman/5.5/en/… about disk data caching. And maybe you should try usual memory tables? Memory should be enough for your stated ~1GB of data. –  Mindaugas Riauba Dec 13 '13 at 8:54
    
damn, I want to increase the dataset to 35-45 million. So thanks, but you don't see any big mistake in the confi.ini?? –  user2601966 Dec 13 '13 at 9:14
add comment

2 Answers

It seems that for solving your problem you chose the wrong tool.

MySQL Cluster is good when you do mostly key based lookups from multiple threads from memory based tables. Disk based tables may be useful when you have rarely read data. Or your work dataset is small portion of the table which should fit into memory cache whose size is defined by DiskPageBufferMemory config variable.

If your queries need many range or full scans - MySQL Cluster is slow even on physical machines. That is because such queries need a lot of data exchanges between data nodes. Try pinging between your data nodes. And for range scan data nodes may need to exchange hundreds and thousands of such messages.

Also MySQL previously stated that for data nodes your should use physical machines and have good interconnect for data node traffic. I doubt that this recommendation is no more valid nowadays.

And I think you should try cleaning up your config. For testing most of those things hardly changes anything and some setting may be slowing down things. Try such simplified NDBD_DEFAULT section:

[NDBD DEFAULT]
NoOfReplicas=1
DataMemory=1536M
IndexMemory=2560M

LockPagesInMainMemory=2
ODirect=1
share|improve this answer
    
I have also tried it without tablespaces, memory only ->same result. Anyway thank you –  user2601966 Dec 13 '13 at 14:15
    
What is ping latency between data nodes? And I updated answer about config. –  Mindaugas Riauba Dec 13 '13 at 14:32
    
Ping results: rtt min/avg/max/mdev = 0.286/0.319/0.373/0.031 ms I replaced my config with yours, no improvement –  user2601966 Dec 13 '13 at 14:57
add comment

Thanks for your support! I guess it was my fault. The code I used was wrong. Is used setBytes(..) instead of setString(..). The latency dropped down from 20s to 4s. 4s is still not comparable with a NoSQL db like mongodb, but I guess there is not that much to improve.

preparedStatement = connection.prepareStatement("INSERT INTO "+ table+" VALUES (?,?,?,?,?,?,?,?,?,?,?)");
            preparedStatement.setString(1, key);
            for(int i=0; i<byteArray.length; i++){
                preparedStatement.setString(i+2, byteArray[i].toString());
            }
            preparedStatement.executeUpdate();
            preparedStatement.close();
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.