It looks like I accidentally solved my problem on the development site, but I don't understand why. I switched the table's type from InnoDB to MyISAM and it immediately fixed the issue, with 100ms response times.

My question is, why?

By all the comparisons I've seen MyISAM is the slower storage engine and doesn't support row-level locking or full ACID-compliant transactions. InnoDB has all these things. I still suspect that it's because of the PRIMARY KEY being a VARCHAR, or the table has a corrupt index.

Any MySQL gurus able to shed some light on this?

First, try to pinpoint the real issue. What happen when you insert a fake session row inside this table using phpMyAdmin (for example).

Secondly, if it's really this table that is slow, then you can try to delete it and recreate it.

By the way try to gather as much information as possible such as size (index and data) and how many rows are in it. In short, try to find anything that can help you understand what is the root cause.

Edit: After some thinking, maybe try to drop the index, optimise the table then recreate the index? I'm not an expert, but that's what I would have done.

Can you dump your query log for a session. That will help you troubleshoot !

Just your queries in real-time to pin-point the problem.

Well, I use Sequel Pro as a database tool. It lets me connect using a native app over SSH to the remote server and run queries that way rather than via phpMyAdmin. If you're on a Mac it's the best DB tool I've ever seen for MySQL (not connected to the project in any way, BTW).

This issue has to be table/storage-engine related.

In InnoDB format the table contains 10 rows, totals 16kB storage, and 8MB free space. Insertion of a fake session row took the same average 2 - 15 seconds.

In MyISAM format the table also contains 10 rows, 1.6kB storage, 0B free space, and 2kB index space. Insertion of a fake session row is sub-second, roughly the average 0.24 - 0.3 second duration.

mcraz said:

Can you dump your query log for a session. That will help you troubleshoot !

I'm using Laravel 3, and yes database profiling is enabled on the staging site. The actual INSERT/UPDATE queries are taking 2 - 15 seconds long to execute, which is about the same duration as the request itself. That's how I found out that it was a MySQL problem to begin with.