MySQL Forums
Forum List  »  InnoDB

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




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.