title: Specific MySQL bulk insertion performance tuning
body: <p>I know this question has been asked over and over. However, this is a very specific question for a very specific scenario. Hopefully you will be able to help me.</p>
  
  <p>I run a logging database, with about 10 tables. The main table which stores the actual log entries has some 30 fields, of which 5 are searchable. I'd say the database has recently become moderately sized, as we're reaching 200 million entries in that table. Other tables store common data, of which the largest one has 4 fields, all searchable, with almost 1 million entries. All other tables contain less than 100 thousand records each.</p>
  
  <p>Inserts come in spikes. I get the logs from the previous day in (quite poorly formatted) csv files every day at 2 AM, and I have until 8 AM to insert them (around 20 files, 100 thousand lines each) into the database. Then I get very few selects (maybe some 1000 a day) during the working day. Then rinse and repeat.</p>
  
  <p>The SELECT queries are quite simple, as they mostly consist of one or two joins with one or two GROUP BY statements. The people who search this database want immediate results, so I have 5 multi-column indexes in the main table, which help on the precise searches I have, and currently, SELECT performance is quite good. No query has taken more than 0.1 seconds so far. There are some reports, but these take around 10 seconds to generate, and that's acceptable.</p>
  
  <p>Currently I have a C program I wrote to read the data from the CSV files, clean it, and insert it in batches of 1000 rows per INSERT query. These INSERTs are not entirely dumb, because I need to get the common data, see if it is already on the other tables, insert it if it's not, and cache it if it is. It also gives me performance data in the form of how many records it's inserting per second. This program is quite fast, and without sending the data into the database, I get around 100 thousand rows per second. Of course, this program and the database lie on the same physical computer.</p>
  
  <p>Now, the data I get every day is growing linearily, and the performance of the INSERTs is decreasing logarithmically. Yesterday's data took 5 hours and a half to insert, at around 400 row insertions per second.</p>
  
  <p>I got some benchmark data by inserting the first 1 million rows with different configurations into an empty database, and this is pretty much what I got:</p>
  
  <p>MyISAM tables: starts at 1500 rows per second, decreases logarithmically down to around 700 rows per second by the time it inserts the 1 millionth row
  InnoDB tables: same as MyISAM, only around 100 rows per second faster
  InnoDB with all indexes disabled on the main table: starts at 2100 rows per second, decreases down to 1000 rows per second.
  InnoDB With indexes, with the filesystem mounted with data writeback (ext3): same as InnoDB, just slightly but almost unnoticeably faster.</p>
  
  <p>innodb_buffer_pool_size is set to 1000MB</p>
  
  <p>Avoiding index creation is not an option, but it is obvious that it has a large impact in performance. However, I need much faster inserts. As the data shows, inserts will take longer as the database grows, so as the data I get is larger every day, I need a huge leap in insert performance. If I could get it to 10000 inserts per second or more, it would be really great.</p>
  
  <p>The system monitor tells me my main resource consumption is disk I/O, which goes almost to 100% when inserting. Because of that, I need an ultra fast way to insert data. My theoretical limit is that of the SATA bus, but that's still quite far away. Memory usage doesn't seem to be that high at some 20% (or MySQL isn't using memory correctly)</p>
  
  <p>To achieve this, it is acceptable to recreate the database over the course of several days, and then hot swap from the reader application, it is acceptable to change any setting in the OS and MySQL, it is acceptable to add memory if required. It is even acceptable to change the database structure, if necessary.</p>
  
  <p>So I'm really open to ideas here. Anybody knows of anything which could help me?</p>
  
  <p>Edit: I'm currently considering inserting the new rows in a MEMORY table, and then do a SELECT INTO the real table. Hopefully, it will only update and flush the index once after all the rows were inserted. I'll try this on Monday. Has anybody tried something like this before?</p>