MySql MyISAM INSERT slowness

Question

I am having a performance issue when inserting some data in a mysql table. The table has a bunch of columns, let's say DATE,A,B,C,D,E,F where DATE,A,B,C,D,E is the primary key. Every day, I insert 70k rows in that table (with a different date), and this table contains 18 million rows now. The method I use to insert the rows is just sending 70k INSERT queries.

The problem I am having is that the queries started to take a lot more time than they used to. Going from a few minutes to a few hours. I profiled the inserts and this is the charts I got:

Speed of each insert (in sec) vs. Number of insert for that day:

A few strange facts:

1. Most queries take less than 2 ms to execute
2. The speed of slow queries increases linearly with the number of rows in the table for that date
3. This behavior only happens at night, after a bunch of processes have happened on the database. Inserting during the day is fast, so is weekends
4. The overall speed doesn't depend on what else is running on the database, in fact, nothing else is running on the database when this happens
5. There is nothing in the query that can explain that a query is fast or no, the fast ones are very similar to the slow one, and from one day to another are not the same set.
6. the behavior does not change from one day to the next.

Any idea what could cause this?

** Edit ** the columns in the index are in the following order:

DATE NOT NULL,
DATE NOT NULL,
VARCHAR (10) NOT NULL,
VARCHAR (45) NOT NULL,
VARCHAR (45) NOT NULL,
VARCHAR (3) NOT NULL,
VARCHAR (45) NOT NULL,
DOUBLE NOT NULL,
VARCHAR (10) NOT NULL,
VARCHAR (45) NOT NULL,
VARCHAR (45) NOT NULL,
VARCHAR (45) NOT NULL,

The Dates are either the same as today, or left empty, the double is always the same number (no clue who designed this table)

Answer 1

The short explanation is that you have an index that is non-incremental within the scope of a single day. Non-incremental indices are generally slower to insert/update because they will more often require rebalancing the index tree, and to a greater extent, than an incremental index.

To explain this further - assume the following schema:

a (int) | b (varchar)

And the index is (a, b)

Now we insert:

1, 'foo'
2, 'bar'
3, 'baz'

This will be quite fast because the index will append on each insert. Now lets try the following:

100, 'foo'
100, 'bar'
100, 'baz'

This won't quite be as fast since 'bar' needs to be inserted before 'foo', and 'baz' needs to insert between the other 2. This often requires the index to rewrite the tree to accomodate, and this 'rebalancing' act takes some time. The larger the components involved in the rebalancing (in this case, the subset where a=100), the more time it will take. Note that this rebalancing activity will only occur more often and more extensively, but not necessarily on each insert. This is because the tree will usually leave some room within the leaves for expansion. When the leaves runs out of room, it knows that it's time to rebalance.

In your case, since your index is primarily based on the current date, you are constantly rebalancing your tree within the scope of the single day. Each day starts a new scope, and as such starts rebalancing within that day's scope. Initially this involves just a bit of rebalancing, but this will grow as your scope of existing entries for the day increases. The cycle starts over as you start a new day, which is the result you are seeing.

That this is happening to the primary key may make matters even worse, since instead of shifting some index pointers around, entire rows of data may need to be shifted to accommodate the new entry. (This last point assumes that MyISAM clustering is performed on the primary key, a point that I haven't gotten clarification on to this day, although anectodal evidence does seem to support this. For example, see here and here.)