Simple MySQL query taking far too long

Environment: Rails 2.3.11, MySQL 5.1 (InnoDB)

My Rails app has been sporadically encountering issues with simple queries taking far too long to complete and bottlenecking the entire application's ability to be updated. These queries typically are related to开发者_运维知识库 the forum, the highest traffic section of the site and the one with (by far) the most updates. Here is an example of a sample query pulled from the MySQL slow log:

# Query_time: 46.900202  Lock_time: 0.000030 Rows_sent: 0  Rows_examined: 0
SET timestamp=1302172666;
UPDATE `forum_topics`
SET `views` = 153, `updated_at` = '2011-04-07 10:36:59'
WHERE `id` = 1213305;

This is a very simple query and should be extremely fast, though in this case it took almost 47 seconds to complete. The load average on this server never exceeds 2, so that is not the issue. Some other points of interest are:

• Neither views nor updated_at are indexed.
• While the auto-increment value may be 1.2M, there are actually only 70K records in this table.
• Over 90% of all queries in the slow query log are similar to this one.

What I am looking for here are some recommendations regarding next steps to take to resolve this issue.

Thanks.

P.S. Schema/indexes are as follows:

CREATE TABLE `forum_topics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `forum_category_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `sticky` tinyint(1) DEFAULT '0',
  `views` int(11) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `last_post_created_at` datetime DEFAULT NULL,
  `slug` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_forum_topics_on_created_at` (`created_at`),
  KEY `index_forum_topics_on_forum_category_id` (`forum_category_id`),
  KEY `index_forum_topics_on_sticky` (`sticky`)
) ENGINE=InnoDB AUTO_INCREMENT=1215414 DEFAULT CHARSET=utf8;

1) Perhaps there is a hardware error - disk reset. 2) Is the id key indexed? 3) communications problem. 4) is timestamp a local variable? should it not have an '@' before it?

There are a lot of causes for sudden stalls in many database products - and MySQL is not alone. Here are a few examples:

• You need to load the table description from storage (no free table cache entries on forum_topics). This operation is serialized - you could be queued behind another user on another table.

• You are using the query cache, and the query cache is (possibly) fragmented.

• InnoDB is currently performing an activity like extending the size of a data file, which causes a brief pause (maybe you have auto_extend_increment set to high?)

These are just examples - there are certainly more causes. What you need to do is use a profiling tool such as poor man's profiler. It will give you a stack trace as to exactly where the query is blocked. Search bugs.mysql.com for the stack trace, or try asking on something like the Percona forums.

Try to add LIMIT to your query. Think this should look this way:

... WHERE `id` = 1213305
    LIMIT 1

The where clause is usually the culprit in long running queries. I can see that in this query, only the 'id' column is being referenced. I would check if it is indexed. If it is, I'd check if it is indexed properly. I would even try re-indexing the 'id' column if an index is already present on it

Running optimize on the table might help, especially if there's a lot of deleted rows still on disk.

Beyond that, what does EXPLAIN on that query reveal about how it's running (well, the select version of that query)

There's not really enough information here to tell you exactly why your query is slowly performing, but:

If this is a MYISAM table, these updates could take a very long time because MYISAM lacks row-level-locking on tables. This affects your query performance because every SELECT you need to run obtains a READ LOCK on the table (not allowing further writes). Also, an UPDATE on a MYISAM table requires another lock, which blocks all other UPDATES/INSERTS/SELECTS from running. So if you had 10 of these queries running, they're not running concurrently, and have to wait for each other to be finished.

I don't think indexes are your problem here, as you mentioned Auto-Increment and use the ID field for your update, so I'm sure that's your primary key.

This is a little old but I just stumbled upon it, and maybe it´s helpful for others...

With problems like this you can use the internal profiler:

mysql> SET PROFILING=1;
mysql> [your query]
mysql> SHOW PROFILE FOR QUERY 1;

If your problem occurs randomly, another process might have locked the table. You can use SHOW FULL PROCESSLIST and SHOW OPEN TABLES to see this.

Also, with big tables you can run into memory problems for the buffers - there are a lot of good pages about this. http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html and http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/ are a great start