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
Last activity: Oct 25, 2023, 12:04 PM