MySQL Insert Query Randomly Takes a Long Time

Question

I am using MySQL to manage session data for my PHP application. When testing the app, it is usually very quick and responsive. However, seemingly randomly the response will stall before finally completing after a few seconds. I have narrowed the problem down to the session write query which looks something like this:

INSERT INTO Session VALUES('lvg0p9peb1vd55tue9nvh460a7', '1275704013', '') ON DUPLICATE KEY UPDATE sessAccess='1275704013',sessData='';

The slow query log has this information:

Query_time: 0.524446  Lock_time: 0.000046 Rows_sent: 0  Rows_examined: 0

This happens about 1 out of every 10 times. The query usually only takes ~0.0044 sec.

The table is InnoDB with about 60 rows. sessId is the primary key with a BTREE index.

Since this is accessed on every page view, it is clearly not an acceptable execution time. Why is this happening?

Update: Table schema is: sessId:varchar(32), sessAccess:int(10), sessData:text

Answer 1

Note that inserting into the middle of a BTree index does require a release of the page quite often, and a rebuild of a portion of the index. For a clustered index (your primary key is probably your clustered index), the actual row data must be moved too when the page is rebuilt.

If the row data is large, it will take some time.

For your case, it might be best to use an autoincrement primary key, and just use a unique index on sessId, so you're not inserting records into the middle of a clustered index.

Answer 2

Tried using a surrogate autoincrement key as suggested, but still had problems with speed when executing the query.

Solution was to switch the table's engine to MyISAM which does quicker inserts.

Answer 3

Reproduce the problem on something that isn't a VM, then you can complain.

Virtual machines, particularly ones shared with arbitrary third parties, have behaviour which cannot be relied upon, in my experience.

In all likelihood, innoDB is trying to do a fdatasync(). This needs to do some actual physical IO, which is blocked by another task on the host box (perhaps another VM) doing something. If you don't control them, you cannot predict what its behaviour will be.

If the sessions table does not need to be persistent across database shutdowns, consider ENGINE=Memory.

If you have no strong data durability requirement, then reduce the innodb durability settings (but this will affect the entire server not just the table)