MySQL Forums :: InnoDB :: Mass Import Innodb


Advanced Search

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 2477 Basilis Papadopoulos 01/29/2012 07:10AM
Re: Mass Import Innodb 799 Aftab Khan 01/30/2012 09:07AM
Re: Mass Import Innodb 820 Basilis Papadopoulos 01/30/2012 10:24AM
Re: Mass Import Innodb 697 Aftab Khan 01/31/2012 07:06AM
Re: Mass Import Innodb 694 Basilis Papadopoulos 01/31/2012 02:51AM
Re: Mass Import Innodb 621 Basilis Papadopoulos 01/31/2012 03:21AM
Re: Mass Import Innodb 760 Rick James 01/31/2012 10:20AM
Re: Mass Import Innodb 954 Basilis Papadopoulos 01/31/2012 10:44AM
Re: Mass Import Innodb 686 Rick James 02/01/2012 02:22AM
Re: Mass Import Innodb 821 Basilis Papadopoulos 02/01/2012 04:33AM
Re: Mass Import Innodb 798 Aftab Khan 02/06/2012 05:59AM
Re: Mass Import Innodb 812 Rick James 02/07/2012 10:38AM
Re: Mass Import Innodb 739 Aftab Khan 02/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.