title: Insertion speed slowdown as the table grows in mysql
body: <p>I am trying to get a better understanding about insertion speed and performance patterns in mysql for a custom product. I have two tables to which I keep appending new rows. The two tables are defined as follows:</p>
  
  <pre><code>CREATE TABLE events (
   added_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   id BINARY(16) NOT NULL,
   body MEDIUMBLOB,
   UNIQUE KEY (id)) ENGINE InnoDB;
  
  CREATE TABLE index_fpid (
   fpid VARCHAR(255) NOT NULL,
   event_id BINARY(16) NOT NULL UNIQUE,
   PRIMARY KEY (fpid, event_id)) ENGINE InnoDB;
  </code></pre>
  
  <p>And I keep inserting new objects to both tables (for each new object, I insert the relevant information to both tables in one transaction). At first, I get around 600 insertions / sec, but after ~ 30000 rows, I get a significant slowdown (around 200 insertions/sec), and then a more slower, but still noticeable slowdown.</p>
  
  <p>I can see that as the table grows, the IO wait numbers get higher and higher. My first thought was memory taken by the index, but those are done on a VM which has 768 Mb, and is dedicated to this task alone (2/3 of memory are unused). Also, I have a hard time seeing 30000 rows taking so much memory, even more so just the indexes (the whole mysql data dir &lt; 100 Mb anyway). To confirm this, I allocated very little memory to the VM (64 Mb), and the slowdown pattern is almost identical (i.e. slowdown appears after the same numbers of insertions), so I suspect some configuration issues, especially since I am relatively new to databases.</p>
  
  <p>The pattern looks as follows: <img src="https://i.stack.imgur.com/tJ54R.png" alt="alt text"></p>
  
  <p>I have a self-contained python script which reproduces the issue, that I can make available if that's helpful.</p>
  
  <p>Configuration:</p>
  
  <ul>
  <li>Ubuntu 10.04, 32 bits running on KVM, 760 Mb allocated to it.</li>
  <li>Mysql 5.1, out of the box configuration with separate files for tables</li>
  </ul>
  
  <p>[EDIT]</p>
  
  <p>Thank you very much to Eric Holmberg, he nailed it. Here are the graphs after fixing the innodb_buffer_pool_size to a reasonable value: <img src="https://i.stack.imgur.com/spYXC.png" alt="alt text"></p>