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
2656
January 29, 2012 07:10AM
822
January 30, 2012 09:07AM
840
January 30, 2012 10:24AM
724
January 31, 2012 07:06AM
717
January 31, 2012 02:51AM
647
January 31, 2012 03:21AM
794
January 31, 2012 10:20AM
975
January 31, 2012 10:44AM
709
February 01, 2012 02:22AM
842
February 01, 2012 04:33AM
816
February 06, 2012 05:59AM
841
February 07, 2012 10:38AM
761
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.