Insertion speed slowdown as the table grows in mysql

Question

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:

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;

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.

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 < 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.

The pattern looks as follows:

I have a self-contained python script which reproduces the issue, that I can make available if that's helpful.

Configuration:

• Ubuntu 10.04, 32 bits running on KVM, 760 Mb allocated to it.
• Mysql 5.1, out of the box configuration with separate files for tables

[EDIT]

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:

Answer 1

Your indexes may just need to be analyzed and optimized during the insert, they gradually get out of shape as you go along. The other option of course is to disable indexes entirely when you're inserting and rebuild them later which should give more consistent performance.

Great link about insert speed.

ANALYZE. OPTIMIZE

Answer 2

Edit your /etc/mysql/my.cnf file and make sure you allocate enough memory to the InnoDB buffer pool. If this is a dedicated sever, you could probably use up to 80% of your system memory.

# Provide a buffer pool for InnoDB - up to 80% of memory for a dedicated database server
innodb_buffer_pool_size=614M

The primary keys are B Trees so inserts will always take O(logN) time and once you run out of cache, they will start swapping like mad. When this happens, you will probably want to partition the data to keep your insertion speed up. See http://dev.mysql.com/doc/refman/5.1/en/partitioning.html for more info on partitioning.

Good luck!

Answer 3

Verifying that the insert doesn't violate a key constraint takes some time, and that time grows as the table gets larger. If you're interested in flat out performance, using LOAD DATA INFILE will improve your insert speed considerably.