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!


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.

  • Please add to your question the version of MySQL you are using. Nov 16, 2011 at 16:09
  • Please specify if you are using any software (such as Drupal or WordPress) whose modules may possibly impose explicit table locks. Nov 16, 2011 at 16:10

2 Answers 2


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.

  • If running on Linux, running vmstat 2 100 will give you 100 entries two seconds apart of some useful stats - especially the "swap-si" and "swap-so" numbers - these are the "swap ins" and "swap outs". High numbers here are a big no-no for performance and show you have over-allocated memory on the box and it is having to resort to the swap file...
    – Dave Rix
    Nov 16, 2011 at 8:16
  • Thanks guys. Results: gist.github.com/1370496. It definitely looks like the main problem is memory.
    – jrallison
    Nov 16, 2011 at 16:12

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.


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.

  • Thanks Rolando. When I look at show create table submissions; it shows ENGINE=InnoDB. Any thoughts on this issue with the InnoDB storage engine?
    – jrallison
    Nov 16, 2011 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.