I have a mysql-5.5.22 ndb-7.2.6 cluster 64bit running on Centos 6 with each node installed on the same Citrix Xen Server communicating via a virtual private network. Each node has 4GB of RAM, 4 vCPUs and 10GB of diskspace. There are no other servers running on this Citrix Xen server.
Loading a text file into a ndbcluster table called domain10Millions, it is taking on average 9 hours to load 576MB of data at 306 rows per second. During the import, the cpu load average was between 1 & 2, RAM was more than sufficent and network traffic between each node was between 500K and 1000K per second.
Is there some kind of configurable limit in MySQL Cluster 7.2.6 that is keeping the import speed so slow?
Or, is there anything I can do to improve import speeds?
Cluster Setup
1 management node
1 SQL node
6 data nodes
/var/lib/mysql-cluster/config.ini
[ndbd default]
NoOfReplicas=2
DataMemory=2048M
IndexMemory=512M
HeartbeatIntervalDbDb=6000
HeartbeatIntervalDbApi=6000
TransactionDeadlockDetectionTimeout=1000000
TransactionInactiveTimeout=10000000
[MGM DEFAULT]
PortNumber: 1186
datadir=/var/lib/mysql-cluster
ArbitrationRank=1
[ndb_mgmd]
hostname=10.0.0.1
datadir=/var/lib/mysql-cluster
[ndbd]
hostname=10.0.0.2
datadir=/var/lib/mysql-cluster
[ndbd]... X 6
[mysqld]
hostname=10.0.0.6
ndb_mgm
Cluster Configuration
---------------------
[ndbd(NDB)] 6 node(s)
id=2 @10.0.0.2 (mysql-5.5.22 ndb-7.2.6, Nodegroup: 0, Master)
id=3 @10.0.0.3 (mysql-5.5.22 ndb-7.2.6, Nodegroup: 0)
id=4 @10.0.0.4 (mysql-5.5.22 ndb-7.2.6, Nodegroup: 1)
id=5 @10.0.0.5 (mysql-5.5.22 ndb-7.2.6, Nodegroup: 1)
id=6 @10.0.0.7 (mysql-5.5.22 ndb-7.2.6, Nodegroup: 2)
id=7 @10.0.0.8 (mysql-5.5.22 ndb-7.2.6, Nodegroup: 2)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.0.0.1 (mysql-5.5.22 ndb-7.2.6)
[mysqld(API)] 2 node(s)
id=8 @10.0.0.6 (mysql-5.5.22 ndb-7.2.6)
table structure
CREATE TABLE domain10Millions (
id bigint not null primary key auto_increment,
domains varchar(55)
) ENGINE=ndbcluster;
CREATE INDEX idx_domains on domain10Millions (domains(6) ASC);
LOAD DATA statement
LOAD DATA LOCAL INFILE "/tmp/generated10MillionsClean.txt"
INTO TABLE domain10Millions(domains);
DB size after import
mysql> select table_schema, sum(round(data_length/1024/1024,2)) as total_size_mb
from information_schema.tables where table_schema like 'domains';
+--------------+---------------+
| table_schema | total_size_mb |
+--------------+---------------+
| domains | 576.22 |
+--------------+---------------+
ndb_desc information
[root@localhost ~]# ndb_desc domain10Millions -d domains -p
-- domain10Millions --
Version: 16777217
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 2
Number of primary keys: 1
Length of frm data: 273
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 6
ExtraRowGciBits: 0
ExtraRowAuthorBits: 0
TableStatus: Retrieved
-- Attributes --
id Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
domains Varchar(55;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex
idx_domains(domains) - OrderedIndex
-- Per partition info --
Partition Row count Commit count Frag fixed memory Frag varsized memory Extent_space Free extent_space
0 1668423 1668423 60358656 40468480 0 0
1 1665562 1665562 60260352 40370176 0 0
3 1667112 1667112 60325888 40402944 0 0
4 1665095 1665095 60227584 40370176 0 0
2 1666575 1666575 60293120 40402944 0 0
5 1667231 1667231 60325888 40402944 0 0
