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.
Questions: 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
