title: MySQL slow query even though primary index is being used and good EXPLAIN plan
body: <p>I have a highly trafficked table with 1.3M rows which is seeing a spate of slow queries of the following type:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Yet, the explain plan for this query indicates an optimal plan (we're using the primary key):</p>
  
  <pre><code>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: 
  </code></pre>
  
  <p>Here's the slow query log:</p>
  
  <pre><code> 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;
  </code></pre>
  
  <p>About 30% of all queries are taking >1s, and about 10% of all queries are taking >10s (!)</p>
  
  <p>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?</p>
  
  <p>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).</p>
  
  <p>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.</p>