Sample Header Ad - 728x90

MySQL cluster fast inserts but slow selects

3 votes
3 answers
2293 views
I have a MySQL cluster with 3 Data Nodes and one SQL node. Currently I have 1 million 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?
Asked by user2601966 (41 rep)
Dec 13, 2013, 07:14 AM
Last activity: Oct 25, 2023, 12:04 PM