mysql insert into indexed table taking long time after few million records

Question

I have a table like this

CREATE TABLE IF NOT EXISTS `dnddata` (
  `numbers` varchar(10) NOT NULL,
  `opstype` char(1) NOT NULL,
  PRIMARY KEY (`numbers`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (numbers)
PARTITIONS 25 */;

i have to insert 300 million records. i am inserting 10 million records each time using load data in file from csv file.

To insert 10 million records taking nearly 5 min first time. Time is increasing each time gradually. after 30 million records it stops inserting and memory using 100% server not responding.

below my my.cnf file setting

bulk_insert_buffer_size = 100M
key_buffer = 100M
sort_buffer_size = 50M
read_buffer = 50M

i am using cpu with 2 G memory.

details for 30 million records

    Space usage
Type    Usage
Data    545.3   MiB
Index   694.8   MiB
Total   1,240.1 MiB

MySQL client version: 5.5.14

with out index it is inserting fine 10 million in 50 sec.

Please tell me what kind of setting need to change.

Edit based on user answers

I have changed my.cnf setting to below

key_buffer_size = 1G
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 2M
bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 256M

no use. issue is not resolved.

I have tried below mentioned methods to load data

set autocommit = 0; //for innodb
load data infile into …
COMMIT;

START TRANSACTION; 
load data infile into …
COMMIT;

ALTER TABLE dnddata DISABLE KEYS;
load data infile into …
ALTER TABLE dnddata ENABLE KEYS;

No luck..

Answer 1

You should load such a huge file in chunk for faster loading of data, Here it is mentioned problems loading huge Load local data file and solution how to solve it and make it faster.

http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/

• MyISAM tables you should follow this steps to fasten loading data:

With some extra work, it is possible to make LOAD DATA INFILE run even faster for a MyISAM table when the table has many indexes.

1. Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

2. Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name to remove all use of indexes for the table.

3. Insert data into the table with LOAD DATA INFILE. This does not update any indexes and therefore is very fast.

4. Re-create the indexes with myisamchk -rq /path/to/db/tbl_name. This creates the index tree in memory before writing it to disk, which is much faster that updating the index during LOAD DATA INFILE because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced.

5. Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

LOAD DATA INFILE performs the preceding optimization automatically if the MyISAM table into which you insert data is empty. The main difference between automatic optimization and using the procedure explicitly is that you can let myisamchk allocate much more temporary memory for the index creation than you might want the server to allocate for index re-creation when it executes the LOAD DATA INFILE statement.

You can also disable or enable the nonunique indexes for a MyISAM table by using the following statements rather than myisamchk. If you use these statements, you can skip the FLUSH TABLE operations:

ALTER TABLE tbl_name DISABLE KEYS;
ALTER TABLE tbl_name ENABLE KEYS;

For more information refer these:

http://dev.mysql.com/doc/refman/5.5/en/optimizing-myisam-bulk-data-loading.html

https://wiki.rice.edu/confluence/display/~as43/Make+LOAD+DATA+INFILE+run+even+faster+for+a+MyISAM+table

Answer 2

I didn't see any indexes in your CREATE TABLE command (other than the primary key). If you just left those out of the question and they really do exist, then you can try disabling indexes prior to the LOAD DATA command and rebuild them after it's complete:

ALTER TABLE dnddata DISABLE KEYS;

LOAD DATA INFILE ... ;

ALTER TABLE dnddata ENABLE KEYS;

As your table grows, the time to write indexes will also increase.

Answer 3

Since you are dealing with a MyISAM table

CREATE TABLE dndtemp ENGINE=MyISAM SELECT * FROM dnddata WHERE 1=2;
LOAD DATA INFILE ... INTO TABLE dndtemp ... ;
INSERT INTO dnddata SELECT * FROM dndtemp;
DROP TABLE dndtemp;

The temp table has no indexes, so the LOAD DATA INFILE should be faster

DISABLE KEYS / ENABLE KEYS does not help in this instance because there are no secondary indexes.

Your key buffer can be resized to 768M for now because the sum of your MyISAM indexes is 694.8 MiB and MyISAM only caches index pages.

Give it a Try !!!

UPDATE 2012-07-25 10:06 EDT

You should increase your bulk_insert_buffer_size to 512M because it accommodates bulk loading of MyISAM tables. According to the MySQL Documentation on bulk_insert_buffer_size:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

In light of this and of you only having 2GB of RAM (how does DB even survive now?), here are my recommended settings

[mysqld]
bulk_insert_buffer_size=512M
key_buffer_size=512M

Answer 4

IMHO, 2GB of RAM is not enough for MySQL to efficiently deal with 30 million rows.

Nonetheless, your key_buffer is waaaay too small. If this box is a dedicated database box, and all you have is 2G RAM, you could safely up that to 1G and not swap to disk as often.

key_buffer = 1GB