<snip> > > >Thanks for the advice. > >We've got 12GB of RAM, I'll increase the key_buffer_size. Unfortunately >I can't turn off indexes, then index after. At these rates, I'd never >catch up.
I don't agree. It takes longer to build the index than to load the data if you have indexes active when loading the data. But if you disable the index, or not have any indexes on the table during the Load Data, then re-enable the index later, MySQL will build the index at least 10x faster if you have a large key_buffer_size because it does it all in memory. I've had Load Data go from 24 hours to 40 minutes just by adding more memory to key_buffer_size and disabling the index and re-enabling it later. I'd recommend using at least 6000M for key_buffer_size as a start. You want to try and get as much of the index in memory as possible. >I had hoped I could use partitions like in Oracle. 1 partition every >hour (or 3). I don't think the merge tables will work however. We >currently only keep 15 days of data and that fills the array. If a merge >table uses disk space, it won't work for us. A Merge Table can be built in just ms. It is a logical join between the tables and does *not* occupy more disk space. Think of it as a view that joins tables of similar schema together vertically so it looks like 1 large table. Mike Ah, very cool. Thanks again. Loading 500,000 rows with 200M rows in the DB with Indexes on takes 22 Minutes. Loading 500,000 rows with 200M rows in the DB with indexes turned off and then build indexes after the load took over 75 minutes. This would probably work if we only inserted 40-80 million rows a day total, or had a few hours where data was not being inserted. Daily partitions are created then sub partitioned across 6 data disks and 6 index disks. We attempted to build a new table per hour, and merge them after 3 hours. We killed the processes after 2 hours. 1 hour of data is approx 18GB. The server only has 12GB of RAM. I wish we could partition down to TO_HOUR instead of TO_DAY -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
