MySQL NDB cluster slow for no reason

3
votes

I have a mysql ndb cluster (details below). The problem is when I do the simplest thing such as restore a database that was dumped using mysqldump, it take an absolute age! IE 6 hours to restore a db that's 745MB in size and has approx 2.7 million rows across about 30 tables, all pretty standard stuff.

I've looked for bottlenecks, no 1 cpu core is overloaded, nor the disks, nor the network, so why so slow?

FYI, while importing a database the network is utilised at approx 2Mbit/s and the ndb nodes are writing to disk at about 1MB per second... hardly utilised. There is no swapping... the db is entirely in memory... no single core is maxed out by a process... no wait-state to note....

I've got two machines each with 4 quad core xeon cpus, 32GB ram. Between them they host a mysql cluster, the nodes are hosted with virtualbox and specs are as follows:

sql API * 2: 4GB ram 4 cores
sql NDB * 2: 19GB ram 8 cores
management node: 4GB 4 cores

Note: I run the NDB nodes using ndbmtd, the sql api nodes use the ndb-cluster-connection-pool=4 param.

Does anyone have any idea why its so slow? I'm simply unable to find a single bottleneck?!?

config.ini

[ndb_mgmd default]
DataDir=/var/lib/mysql-cluster

[ndb_mgmd]
HostName=mgm-alpha
NodeId=1

[TCP DEFAULT]
SendBufferMemory=12M
ReceiveBufferMemory=12M

[ndbd default]
NoOfReplicas=2
DataMemory=15000M
IndexMemory=1048M
MaxNoOfConcurrentOperations=100000
RedoBuffer=32M
MaxNoOfAttributes=5000
MaxNoOfOrderedIndexes=1000
TimeBetweenEpochs=500
DiskCheckpointSpeed=20M
DiskCheckpointSpeedInRestart=100M
MaxNoOfExecutionThreads=8
DataDir=/var/lib/mysql-cluster

[ndbd]
HostName=cl1-alpha
NodeId=2

[ndbd]
HostName=cl2-bravo
NodeId=3

[mysqld]
HostName=sq1-alpha
NodeId=4

[mysqld]
HostName=sq1-alpha
NodeId=5

[mysqld]
HostName=sq1-alpha
NodeId=6

[mysqld]
HostName=sq1-alpha
NodeId=7

[mysqld]
HostName=sq2-bravo
NodeId=8

[mysqld]
HostName=sq2-bravo
NodeId=9

[mysqld]
HostName=sq2-bravo
NodeId=10

[mysqld]
HostName=sq2-bravo
NodeId=11

my.cnf on mysql api nodes

[mysqld]
# Options for mysqld process:
ndbcluster
ndb-connectstring=mgm-alpha
default_storage_engine=ndbcluster
ndb-mgmd-host = mgm-alpha:1186
ndb-cluster-connection-pool=4

[mysql_cluster]
# Options for MySQL Cluster processes:
ndb-connectstring=mgm-alpha  # location of management server
1
mysqlperformancecluster-computing
Also just thought I should point out the system is disproportionation slow. IE import 25Mb table takes 1 min, 300Mb table takes 30 mins, all roughly the same, couple of indexes, primary index on id auto-increment. - stryker
If your comment is any indicator, it sounds like it's swapping... - Denis de Bernardy
I've checked that, I can safely say its not... also other tools like dstat show no pages written/read and disk access is usually around 2MB write per second so not much going on. - stryker
Is this MySQL Cluster 7.3? Are you using foreign keys (allowed from 7.3)? If so can you switch them off and try the reload? Alternatively, try building indexes after the data load. - Don

1 Answers