MySQL Forums
Forum List  »  Performance

Insert problems on very large DB
Posted by: Roland Rabben
Date: November 25, 2004 07:32AM

I have a problem with inserting records into my very large MySQL database. I am inserting about 60 million records pr. day using the LOAD DATA INFILE command. I will keep the records for about 90 days. Total number of records is about 5,4 billion records.

The table looks like this:
CREATE TABLE `events` (
`player_id` smallint(8) unsigned NOT NULL default '0',
`ad_id` smallint(8) unsigned NOT NULL default '0',
`start_date` datetime NOT NULL default '0000-00-00 00:00:00',
`duration` smallint(5) unsigned default '0'

I have created an Index like this:
CREATE INDEX date_index ON events (start_date,player_id,ad_id);

Inserting records on my indexed table takes very long time. When I reach 300 million records, it takes about 30 sec to insert a file containing 20 000 rows. It is no problem to insert when table is empty or when Index switched off.

I have tried tweaking the KEY_BUFFER setting. But my Index grows too large to store in KEY_BUFFER. I have also tried dropping the index during insert, but I have problem creating the index after insert is complete.

I am running MySQL 4.1 on a Widows Server 2003 Enterprise Edition, 1 GB RAM, Dual 2,4Ghz Xeon processor. 3X16GB SCSI RAID5 (10 000 RPM).
It will run on a larger server when in production. Any recommendations?

What can I do to speed up my INSERTS?

Options: ReplyQuote

Written By
Insert problems on very large DB
November 25, 2004 07:32AM
November 25, 2004 09:47AM
November 25, 2004 01:21PM
November 25, 2004 01:48PM
November 25, 2004 02:32PM

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.