Re: poor performance on load data to NDB (possible solution)

[Andy Kwong]

I do some more test, I'm seeing the data loading row by row where I do a select count(*)... This is definitely slowing down the bulk loading process.

I think I may have found a way - it took about 6s to load data to a MYISAM table and then 26s to ALTER table to NDB. This is total of 32s instead of 323s. I can live with that; although it would really super if there is a way to load anywhere closer to the 6s ... some kind of BULK load to NDB and then sync in bulk - not the row-by-row sync.

Here is what is looks like - BEFORE
=====================================
mysql> drop table account;
Query OK, 0 rows affected (0.73 sec)

mysql> create table account (id bigint(20) primary key, balance decimal(10,10), currency varchar(3), time timestamp default current_timestamp) engine=ndb;
Query OK, 0 rows affected (0.89 sec)

mysql> load data infile '/tmp/a1m.txt' replace into table account fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';
Query OK, 1000000 rows affected, 65535 warnings (5 min 24.60 sec)
Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 1291201

Now, use ALTER TBALE - AFTER:
=============================
mysql> drop table account;
Query OK, 0 rows affected (0.73 sec)

mysql> create table account (id bigint(20) primary key, balance decimal(10,10), currency varchar(3), time timestamp default current_timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/tmp/a1m.txt' replace into table account fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';
Query OK, 1000000 rows affected, 65535 warnings (5.97 sec)
Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 1291201

mysql> alter table account engine=ndb;
Query OK, 1000000 rows affected (26.17 sec)
Records: 1000000 Duplicates: 0 Warnings: 0



Edited 1 time(s). Last edit at 10/06/2006 10:13AM by Andy Kwong.