MySQL Forums
Forum List  »  InnoDB

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




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.