MySQL Forums
Forum List  »  InnoDB

Re: Performance Problems for large inserts into INNODB table via LOAD DATA
Posted by: Rick James
Date: February 27, 2011 09:04AM

> PRIMARY KEY (TKT_ID), UNIQUE KEY iux_TicketsTKT_ID(TKT_ID),
A PRIMARY KEY is a UNIQUE KEY. So the second is totally redundant; recommend DROPing it. Having it significantly slows down the load.

TicketAttachments has no _explicit_ PRIMARY KEY. It is usually better to have one, even it it is just INT UNSIGNED NOT NULL AUTO_INCREMENT.

How big is the table?
SHOW TABLE STATUS LIKE 'Ticket%';

> innodb_io_capacity=2000
Are you using SSDs? Hardware RAID? If not, this value may be excessive. An ordinary drive cannot handle more than about 200.

> I do an explicit commit every 90 seconds.
Does this mean that your Python script generates a LOAD DATA file of a few thousand rows, then closes it and issues a COMMIT?

> create_date VARCHAR(40) NULL,
> mod_date VARCHAR(40) NULL,
> read_date VARCHAR(40) NULL,
There is a much more compact, and usable, DATE datatype.

What is your conclusion on Tablespaces, etc?

A significant issue in the performance of loading Tickets is whether TKT_ID values are ordered or random.

Do you find that performance starts out fast, and later slows down? Some other pattern?

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.