Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Nov 16 '11 at 6:40

This question came from our site for system and network administrators.

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

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
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 '11 at 8:16
    
Thanks guys. Results: gist.github.com/1370496. It definitely looks like the main problem is memory. –  jrallison Nov 16 '11 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.

RECOMMEDATION

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.

share|improve this answer
    
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 '11 at 16:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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