Skip navigation links

MySQL Forums :: InnoDB :: Performance Problems for large inserts into INNODB table via LOAD DATA


Advanced Search

Re: Performance Problems for large inserts into INNODB table via LOAD DATA
Posted by: Rick James ()
Date: December 29, 2010 10:17AM

I'm not a fan of TABLESPACEs; let us know if they really help any.

I suspect, without any evidence, that 2 is the only reasonable value for this:
innodb_log_files_in_group=4

innodb_flush_log_at_trx_commit=1
Consider changing to 2, at least until the load is finished.

sync_binlog = 0

Minor issue:
GENStartYear INT NULL,
There is a native YEAR datatype.

TIMESTAMP takes 4 bytes; DATETIME takes 8.

If you could load discoverydb.Tickets in PRIMARY KEY order (or approximately), the loading of it might run faster. (With 38GB of buffer pool, perhaps you never hit the disk anyway.)

How big (GB) do you expect the tables to be? SHOW TABLE STATUS if you have finished loading them.

Consider compressing (in the client) the larger VARCHAR fields. Smaller --> More cacheable --> Faster.

Another approach is to LOAD DATA into MyISAM tables, then do ALTER TABLE ... ENGINE=InnoDB. Additionally, have not indexes on the MyISAM tables, then add the indexes when altering to InnoDB.

Options: ReplyQuote


Subject Views Written By Posted
Performance Problems for large inserts into INNODB table via LOAD DATA 3482 Michael Vitale 12/27/2010 09:59AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1177 Rick James 12/29/2010 10:17AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1683 Michael Vitale 02/27/2011 07:11AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 938 Michael Vitale 02/27/2011 07:18AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1376 Rick James 02/27/2011 09:04AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1561 Michael Vitale 02/28/2011 09:01AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1528 Rick James 02/28/2011 11:12AM


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.