Why is this query slow? Should I use MyISAM rather than InnoDB here?

Question

I'm getting these about 5 times an hour in my slow query logs:

# Query_time: 11.420629  Lock_time: 0.000033 Rows_sent: 0  Rows_examined: 0
SET timestamp=1267487708;
INSERT INTO record_lock (record_lock.module_id, record_lock.module_record_id, record_lock.site_id, record_lock.user_id, record_lock.expiration_date_time, record_lock.date_time_created) VALUES ('40', '12581', '940', '155254', '2010-03-02 00:24:57', '2010-03-01 23:54:57');

# Query_time: 2.095374  Lock_time: 0.000031 Rows_sent: 0  Rows_examined: 0
SET timestamp=1267488361;
DELETE
FROM record_lock
WHERE record_lock.user_id = 221659 AND record_lock.expiration_date_time IS NOT NULL;

The record_lock table currently uses InnoDB, and it has under a dozen records in it right now.

We have several thousand active users in our system. Each time they edit a record, we INSERT into this table. And on each and every page load anywhere in the system, we 1) SELECT from the table to see if there are any locks for the current user and 2) run a DELETE query against that table if there are any records for the user, referencing the table's primary keys in the WHERE clause.

Here is the table's schema:

CREATE TABLE IF NOT EXISTS `record_lock` (
  `module_id` int(10) unsigned NOT NULL,
  `module_record_id` int(10) unsigned NOT NULL,
  `site_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `expiration_date_time` datetime NOT NULL,
  `date_time_created` datetime DEFAULT NULL,
  PRIMARY KEY (`module_id`,`module_record_id`),
  KEY `record_lock_site_id` (`site_id`),
  KEY `index_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

Have you tried running an EXPLAIN on the queries?

Answer 2

How many queries are you doing per second?

Could you not just put a locked field in the records themselves? I assume you are getting the record anyway. You could also use something like memcached for storing the locks.

I don't know the specifics off the top of my head, but my understanding is that InnoDB is great for concurrent reads, but sucks for concurrent writes. MyISAM might be better, but my gut tells me the current design is flawed.

Answer 3

Is it possibly too many connections trying to hit the same table? You could try segmenting the table on user_id to help with that.

Answer 4

Switching on the Innodb monitors can help narrow down the causes of poor performance:

SHOW ENGINE INNODB STATUS and the InnoDB Monitors