MySQL UPDATE Performance Issue

Question

I'm running out of ideas on a performance bottleneck I'm seeing on one of my projects.

The table in question is pretty small, with only 5396 records. However, I'm seeing queries like this show up in our slow query log:

# Query_time: 17.482010  Lock_time: 0.000088  Rows_sent: 0  Rows_examined: 0
UPDATE `submissions` SET `updated_at` = '2011-11-15 20:12:44', `status` = 'eligible',   `published_at` = '2011-11-15 20:12:44' WHERE `submissions`.`id` = 5334

Submissions.id is the primary key.

We are relatively read heavy, so we had query_cache enabled. I've turned that off and it helped somewhat, but the updates still seem to be way too slow.

It only seems to effect this particular update, and the database overall has fairly light load with an average of 20 queries per second.

Any ideas on places to look would be greatly appreciated!

Clarifications

Mysql version: mysql  Ver 14.12 Distrib 5.0.51, for pc-linux-gnu (x86_64) using readline 5.2

Applications talking to the database are built with Ruby on Rails. No explicit table locks.

Answer 1

What does EXPLAIN show for that query? What does mysqltuner show? What does free -m show?

If turning the query cache off helped, I'd suspect you are running out memory and your machine is starting to swap. Swapping is a pretty effective way to kill MySQL's performance. The solution would be to either increase the memory of the machine, or tune MySQL to require less memory.

Answer 2

The Lock_time = 0.000088. Very small number. My guess is that the submissions table uses the MyISAM storage engine. What is the MyISAM Storage Engine notorious for? Table Level Locking for any INSERT, UPDATE or DELETE !!!

In a ready-heavy environment, a MyISAM table behaves a like a prioritized queue.

• SELECTs will make all DDL, INSERTs, UPDATEs, & DELETEs, wait until all SELECTs are done
• A single write operation makes all SELECT wait. The exception to this rule is Concurrent INSERT. The environment for Concurrent INSERTs:
  • Only INSERTs and SELECTs
  • MyISAM tables must not contain any gaps
• Since your query is an UPDATE, Concurrent INSERTs are ruled out as an option

Some long running query (such as an UPDATE), even just one, can bottleneck INSERTs, UPDATEs, DELETEs and SELECTs.

RECOMMEDATION

• Convert the submissions table to InnoDB: ALTER TABLE submissions ENGINE=InnoDB;
• Create a decent-sized InnoDB Buffer Pool
• Create decent-sized InnoDB Log Files and ibdata1

UPDATE 2011-11-16 11:19 EDT

I was afraid you'd say it was InnoDB. As I mentioned, you need to have a decent-sized InnoDB Buffer Pool. That way, all data and index pages of your working dataset would reside in memory. You may also need to run SHOW ENGINE INNODB STATUS\G at the time these long running queries run. Why? Even with InnoDB, deadlocking can be a problem (Please read this and its links).

Most people overlook the fact the the clustered index gets locked at the page level. Your query is hitting the clustered by reason of the primary key.