MySQL Forums
Forum List  »  InnoDB

Mass Import Innodb
Posted by: Basilis Papadopoulos
Date: January 29, 2012 07:10AM

Hello,

I have been experiencing performance problems while adding massive amount of data on innodb tables. I have spent several weeks reading the manual and articles concerning innodb optimization and after several tests I'm not able to load the data fast enough.

I am forced to use INSERT...SELECT statements to populate the DB, primarely because while copying the original DB to a new one, several modifications are made on the keys on the fly. This is done in order to sort the most important records by primary key and improve SELECT speeds on new DB. Moreover I do this in order to maintain the innodb table files as small as possible, since they only tend to increase constantly. The DB is over 350GB and the biggest table which stores documents is over 150GB long.

I have tried adding the data by having several INSERT...SELECTs on groups of 1000-10000 records. I removed any indexes while importing data disabled any foreignkey checks or unique checks. When I check the stats of the linux system while executing the process and the system does not write a lots of data on hard disk, does not utilizes all the CPU and does not uses all the available RAM.

Any ideas on where I should look for the bottleneck?

I use MySQL 5.5.9 and here is part of config file:

innodb_flush_method=O_DIRECT
innodb_support_xa=0
innodb_buffer_pool_size=1024M
innodb_log_file_size=256M
innodb_read_io_threads=32
innodb_write_io_threads=32
innodb_io_capacity=500
innodb_flush_log_at_trx_commit=2

Thanks in advance!

Options: ReplyQuote


Subject
Views
Written By
Posted
Mass Import Innodb
2544
January 29, 2012 07:10AM
813
January 30, 2012 09:07AM
835
January 30, 2012 10:24AM
716
January 31, 2012 07:06AM
709
January 31, 2012 02:51AM
637
January 31, 2012 03:21AM
785
January 31, 2012 10:20AM
967
January 31, 2012 10:44AM
702
February 01, 2012 02:22AM
836
February 01, 2012 04:33AM
810
February 06, 2012 05:59AM
830
February 07, 2012 10:38AM
753
February 07, 2012 11:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.