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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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.