即使正在使用主索引和良好的EXPLAIN计划，MySQL也会慢速查询

I have a highly trafficked table with 1.3M rows which is seeing a spate of slow queries of the following type:

我有一个高度流量的表,有1.3M行,看到一连串的慢速查询类型如下:

UPDATE app_info SET data1=269223, data2=0, data3=164, last_update='2012-08-30'
WHERE slice_id=7636 AND app_id=375 AND user_id=21012286 AND mode_id=1;

Yet, the explain plan for this query indicates an optimal plan (we're using the primary key):

然而,此查询的解释计划表明了一个最佳计划(我们使用的是主键):

explain select * from app_info
where slice_id=7636 and app_id=375 and user_id=21012286 and mode_id=1\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: app_info
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 18
          ref: const,const,const,const
         rows: 1
        Extra: 

Here's the slow query log:

这是慢查询日志:

 Time: 120830  3:23:37
# User@Host: rest_service[rest_service] @ app01.peak.mindjolt.com [10.0.0.174]
# Thread_id: 10091395  Schema: platform  Last_errno: 0  Killed: 0
# Query_time: 68.559347  Lock_time: 0.000045  Rows_sent: 0  Rows_examined: 1 Rows_affected: 1  Rows_read: 2
# Bytes_sent: 52  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 575CBF3B9
UPDATE app_info SET data1=269223, data2=0, data3=164, last_update='2012-08-30' WHERE slice_id=7636 AND app_id=375 AND user_id=21012286 AND mode_id=1;

About 30% of all queries are taking >1s, and about 10% of all queries are taking >10s (!)

大约30%的查询占用> 1s,大约10%的查询占用> 10s(!)

What could be causing this query to run slow? As far as I can tell, the plan is perfect, only one row was scanned, and no time was spent acquiring the lock. So, what goes?

什么可能导致此查询运行缓慢?据我所知,该计划是完美的,只扫描了一行,没有时间花在获取锁上。那么,怎么回事?

Update: forgot to include server specs, this is on a 64G Quad Xeon X5650 2.66GHz (24 cores), Mysql 5.1.52-rel11.6-log Percona server 11.6, 12 disk PERC H700 RAID Array. This server has worked perfectly fine for a long time (uptime indicate 565 days up).

更新:忘记包含服务器规格,这是在64G Quad Xeon X5650 2.66GHz(24核),Mysql 5.1.52-rel11.6-log Percona服务器11.6,12磁盘PERC H700 RAID阵列。这台服务器长时间工作正常(正常运行时间为565天)。

Update 2: This table has only one index, which is a primary index consisting of the tuple (app_id, user_id, slice_id, mode_id). Additionally, this is a write-only master, three other slave servers handle all reads.

更新2:此表只有一个索引,它是由元组(app_id,user_id,slice_id,mode_id)组成的主索引。此外,这是一个只写主机,其他三个从机服务器处理所有读取。

1 个解决方案

#1

I suspect there are indexes on one or more of the columns you are updating, and with that large of an index, it could take some time to flush those indexes and rebuild the necessary parts. I would do an audit of the indexes and remove any which are not giving you highly valuable select performance gains. A quick search also turned up the delay-key-write option (MyISAM only :-/), which may help. The last frontier (if that's actually the issue) would be to look into master-for-write/slave-for-read architecture and then do significantly fewer indexes on your write master server.

我怀疑您正在更新的一个或多个列上有索引,并且使用那么大的索引,可能需要一些时间来刷新这些索引并重建必要的部分。我会对索引进行审核,并删除任何没有给你带来非常有价值的选择性能提升的索引。快速搜索还调出了delay-key-write选项(仅限MyISAM: - /),这可能有所帮助。最后一个边界(如果这实际上是问题)将是查看master-for-write / slave-for-read架构,然后在写主服务器上显着减少索引。

edit I searched a bit for InnoDB options and found this SO question on how to temporarily disable keys for them for write operations.

编辑我搜索了一下InnoDB选项,发现了这个问题,关于如何暂时禁用它们的键进行写入操作。