title: MySQL Insert performance degrades on a large table
body: <p>I'm working with a huge table which has 250+ million rows. The schema is simple. </p>
  
  <pre><code>CREATE TABLE MyTable (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          oid INT NOT NULL,
          long1 BIGINT NOT NULL,
          str1 VARCHAR(30) DEFAULT NULL,
          str2 VARCHAR(30) DEFAULT NULL,
          str2 VARCHAR(200) DEFAULT NULL,
          str4 VARCHAR(50) DEFAULT NULL,
          int1 INT(6) DEFAULT NULL,
          str5 VARCHAR(300) DEFAULT NULL,
          date1 DATE DEFAULT NULL,
          date2 DATE DEFAULT NULL,
          lastUpdated TIMESTAMP NOT NULL,
          hashcode INT NOT NULL,
          active TINYINT(1) DEFAULT 1,
          KEY oid(oid),
          KEY lastUpdated(lastUpdated),
          UNIQUE KEY (hashcode, active),
          KEY (active)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1000000000;
  </code></pre>
  
  <p>The performance of insert has dropped significantly. Upto 150 million rows in the table, it used to take 5-6 seconds to insert 10,000 rows.  Now it has gone up by 2-4 times. Innodb's ibdata file has grown to 107 GB. Innodb configuration parameters are as follows.</p>
  
  <pre><code>innodb_buffer_pool_size = 36G # Machine has 48G memory
  innodb_additional_mem_pool_size = 20M
  innodb_data_file_path = ibdata1:10M:autoextend
  innodb_log_file_size = 50M
  innodb_log_buffer_size = 20M
  innodb_log_files_in_group=2
  innodb_flush_log_at_trx_commit = 1
  innodb_lock_wait_timeout = 50
  innodb_thread_concurrency = 8
  innodb_flush_method = O_DIRECT
  expire_logs_days = 4
  </code></pre>
  
  <p>IO wait time has gone up as seen with <code>top</code>. I have tried changing the flush method to O_DSYNC, but it didn't help.  The disk is carved out of hardware RAID 10 setup. In an earlier setup with single disk, IO was not a problem. </p>
  
  <p>Is partitioning the table only option? Can splitting single 100G file into "smaller" files help?  Are there any variables that need to be tuned for RAID?</p>
  
  <p><strong>Update</strong>: This is a test system. I have the freedom to make any changes required. </p>