load data infile real-time data load

[Brian Kiesel]

I am attempting to load data via "load data infile" into mysql at a rate of 22k rows / second every second (1.9 billion rows per day) concurrently with user queries. This is a crazy load rate I realize.

This data is loaded into 1 table (about 60 columns with a avg row length of 300 bytes) using the innodb engine. The table has 1 index comprised of 4 columns (date and a few number columns) It is running under 64 bit linux on a HP DL580 with 4 quad processors, 128 GB RAM and a 2TB raid 5 (16 discs) (yes I know raid 0 is faster for intensive write and will be trying this soon).

I am running 4 concurrent load data infile processes and am able to achieve a rate of 32k rows per second initially. By the time 90 million rows have been loaded, my rate is down to 25K/sec. By 300 million it is only 9K/sec. With the indexes removed from the table, I was able to scale to maintain a 32K/sec rate all the way up to 90 million rows. So it appears to initially indicate that updating the growing index is causing the rate to decline. Can anyone shed some light on this?

Under Oracle I would place the indexes and data in their own partition. I would also partition the table into hourly chunks each it its own tablespace and datafiles. This data is time dependent and all new data being loaded is in the current hour so I would try placing the current index partition in a RAM disk for performance. It appears as though NONE of these options are available to me in MySQL. I am fairly new to this product so tell me if I am wrong about that. Am I missing some other tricks here.

Also under oracle my redo logs would be large enough to assure that they don't fill and roll more often then every 15 minutes. Under MySQL I am limited to 4GB total for the 2 logs (2Gb each) and they are switching every 1-2 minutes at these data rates. Is this hurting performance as it would be in Oracle.

Thank:
Brian