为什么有时候查询需要3秒才能完成工作

Question

The application is real time site that need a speed of access on every query (select, update, insert) Low latency must less than 200ms every query

Well, On average 98% each query. time is less than 0.001-0.005 secound on for complete job query.

the problem is Sometime around 5 time per hour, they can take up to 1s and may be 5s, i don't know real cause of problem

i found to reducing the problem are move that from INNODB to MEMORY.

THE QUESTION are Why sometime the query can take time up to 5 secound ?

Example query in sometime query more than 1 secound

# Time: 130328 20:27:40
# User@Host: ferge572w[ferge572w] @ localhost []
# Query_time: 1.339712  Lock_time: 0.000026 Rows_sent: 0  Rows_examined: 1
SET timestamp=1364477260;
UPDATE log_product SET credit=credit+1 WHERE id_product='149721921' and id_user='2029275' LIMIT 1;

table index: id_product, id_user
No.row: 33,491   table size: 5Mb

# Time: 130329  7:25:37
# User@Host: ferge572w[ferge572w] @ localhost []
# Query_time: 1.439856  Lock_time: 0.000031 Rows_sent: 0  Rows_examined: 1
SET timestamp=1364516737;
UPDATE product SET lastuser='hello',picperson='1',lastid='2030505',country='thailand',price=price+0.01,time=DATE_ADD(time, INTERVAL 3 SECOND) WHERE id='349721227' LIMIT 1;

table index: id
No.row: 35   table size: 2.1Mb

i ensure EXPLAIN to optimize query and update use index. also checked data types and their lengths of the fields of the table but still problem

---

UPDATE i find the cause of problem are when system High IOwait. the slow query heppen Immediate. How to fix it

Slow query when IO wait is high.

Show from iotop command

-- TID -- PRIO -- USER -- DISK READ -- DISK WRITE -- SWAPIN -- IO> -- COMMAND

-- 2311 -- be/4 -- mysql -- 0.00 B/s -- 0.00 B/s -- 0.00% -- 96.25% -- mysql~l.sock

-- 2311 -- be/4 -- mysql -- 0.00 B/s -- 0.00 B/s -- 0.00% -- 96.25% -- mysql~l.sock

-- 2311 -- be/4 -- mysql -- 0.00 B/s -- 0.00 B/s -- 0.00% -- 96.24% -- mysql~l.sock

High IO wait start when 6:13:28 PM - 6:13:29 PM (sar command)

--------------------- CPU -- %usr -- %nice -- %sys -- %iowait -- %steal

-- 6:13:28 PM --- all -- 2.53 -- 0.00 -- 2.02 -- 39.39 -- 0.00

-- 6:13:29 PM --- all -- 1.99 -- 0.00 -- 1.00 -- 49.25 -- 0.00

Got slow query between that time

Time: 130329 18:13:29

User@Host: wdwdwd[wdwdwd] @ localhost []

Query_time: 2.007902 Lock_time: 0.000025 Rows_sent: 0 Rows_examined: 1 SET timestamp=1364555609;

UPDATE log_product SET credit=credit+1 WHERE id_product='349721228' and id_user='2021841' LIMIT 1;

Answer 1

There could be a number of reasons, the more data you have (and check) the closer you'll get to finding your answer.

Things to check:

• Are you on shared hosting, they can be unreliable.
• Have you checked mySQL slow logs
• Have you done a mysql explain on your queries?
• Are you getting lots of requests at the time your SQL queries are too slow, disk IO could be an issue
• Do your tables need to be optimised?
• Are you running out of memory on the machine?

http://newrelic.com/ can be very helpful in these situations (and they have a free version which you can take advantage of)

Potential Reasons:

• Disk I/O: You are on shared hosting and/or your server's disk is
  unreliable/saturated at times
• Peak Usage: Lots of traffic/queries at specific times causing delays
• Other Server related slowdowns: e.g AntiVirus or another process is running and chewing up resources

If you're able to do more analysis of your data as I and others have suggested it could prove very valuable in getting to the bottom of your particular issue