Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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                   
share|improve this question
    
Certainly is slow. Some tips johanandersson.blogspot.co.nz/2012/04/… –  KCD Nov 12 '13 at 21:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.