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: February 28, 2011 11:12AM

"KEY" and "INDEX" are synonyms (in MySQL).
They provide an efficient (usually BTree) mechanism for finding row(s).
"UNIQUE" is an INDEX, plus a uniqueness constraint.
"PRIMARY KEY", in MyISAM is identical in functionality to a UNIQUE KEY. In InnoDB, PRIMARY KEY does that, plus causes the data to be 'clustered'.

KEY(x)
KEY(x)
-- redundant and wasteful

KEY(x) -- DROP this one
UNIQUE(x) -- keep this one

KEY(x) -- DROP this one
PRIMARY KEY(x) -- keep this one

UNIQUE(x) -- DROP this one
PRIMARY KEY(x) -- keep this one

KEY(x,y)
KEY(y,x)
Probably keep both -- they are different

KEY, INDEX, UNIQUE, PRIMARY KEY -- Each of these provides efficient access to the data.

TicketAttachments -- oops.

innodb_io_capacity = 400 (maybe) for your software RAID.

Only 1-20 rows in a LOAD DATA file -- it works, but is not very efficient.

> I do not see any measurable performance difference whether I use tablespaces or not.
Thanks for supporting my belief. ;)

> Yes, I see performance starts out fast (1000-5000 rows inserted per second), then slows down after about an hour (100-500 rows per second).
That is usually a symptom of some of these:
* "Random" key(s) (not the PK, you say)
* Inadequate value for innodb_buffer_pool_size (but you have a huge buffer_pool)
* Frequent COMMITs (together with certain settings) (but that is not your case)

Even with a lot of random keys, the huge buffer_pool should keep the slowdown from happening for a few hours. If it is coming sooner than that, I don't know the answer.

Hardware RAID, with a "write cache" would probably speed things up by a noticeable factor. (But costly.)

Another note from an older posting: OPTIMIZE TABLE is essentially useless for InnoDB; don't bother using it. It takes a long time, and does not improve space or speed much (except in pathological cases).

Options: ReplyQuote


Subject Views Written By Posted
Performance Problems for large inserts into INNODB table via LOAD DATA 3460 Michael Vitale 12/27/2010 09:59AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1173 Rick James 12/29/2010 10:17AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1676 Michael Vitale 02/27/2011 07:11AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 931 Michael Vitale 02/27/2011 07:18AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1372 Rick James 02/27/2011 09:04AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1557 Michael Vitale 02/28/2011 09:01AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1515 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.