2

Yesterday, our MySQL's response time increased 20 times- select queries which earlier used to take 0.6-0.7 ms took 20ms, and updates even more- 60ms.

The MySQL running threads count which usually stays around 10-15 shot up to 300-400, for a good five hours (before coming down when traffic cooled).

Server version: 5.6.25-log MySQL Community Server (GPL), with 32 core machine, 128GB machine (90 GB buffer pool)

Storage engine is Innodb.

Some output from show engine innodb status captured by ptstalk-

SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 159291194 OS WAIT ARRAY INFO: signal count 1074930257 Mutex spin waits 1220535470, rounds 2921695607, OS waits 26779994 RW-shared spins 291539762, rounds 1856674850, OS waits 31197621 RW-excl spins 300274870, rounds 5700270052, OS waits 95339247 Spin rounds per wait: 2.39 mutex, 6.37 RW-shared, 18.98 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 20746118705 Purge done for trx's n:o < 20746118666 undo n:o < 5 state: running but idle History list length 1154

Also,there are large number of os waits, specifically at

InnoDB dict0dict.cc:2606 os_waits=60628 InnoDB dict0dict.cc:2606 os_waits=131984 InnoDB dict0dict.cc:2606
os_waits=81342 InnoDB dict0dict.cc:2606 os_waits=230958

Also at:

InnoDB trx0rseg.cc:196 os_waits=491 InnoDB trx0rseg.cc:196 os_waits=458 InnoDB trx0rseg.cc:196 os_waits=489 InnoDB trx0rseg.cc:196 os_waits=483 InnoDB trx0rseg.cc:196 os_waits=459

Some other information: 1. Query Cache:

| have_query_cache             | YES                               |
| long_query_time              | 0.500000                          |
| query_alloc_block_size       | 8192                              |
| query_cache_limit            | 10485                             |
| query_cache_min_res_unit     | 4096                              |
| query_cache_size             | 0                                 |
| query_cache_type             | OFF                               |
| query_cache_wlock_invalidate | OFF                               |
| query_prealloc_size          | 8192                     
  1. Adaptive Hash Index is also ON.

What could the issue be? Please let me know if you need more details.

Vmstats snapshot from that time:

procs   -----------memory----------   ---swap--  -----io----  --system-------cpu-----
 r  b   swpd   free    buff   cache    si   so    bi    bo    in     cs us sy id wa st
17  0 685588 2254064 310852 23913820    0    0    96   404     0      0  3  1 97  0  0     
 5  0 685588 2256376 310856 23915576    0    0  6816 14092 53915 142794 18  3 79  0  0  
 7  0 685588 2256284 310860 23916752    0    0  6288 13136 51433 143163 15  2 82  0  0  
 7  0 685588 2254044 310860 23917792    0    0  7904 18572 52465 142161 16  2 82  0  0  
 8  0 685588 2254044 310860 23918800    0    0  6512 18244 51958 146916 15  2 82  0  0  
 6  0 685584 2252828 310860 23919844    4    0  5444 16820 51750 147625 16  2 82  0  0  
 5  0 685584 2251820 310860 23920884    0    0  6864 14084 53609 146373 15  2 82  0  0  
 6  0 685584 2251952 310860 23921996    0    0  6016 13112 52862 147601 15  2 82  0  0  
10  0 685584 2250164 310864 23923056    0    0  6096 14468 52406 147109 15  2 82  0  0  
 7  0 685584 2248936 310864 23924112    0    0  6576 13664 51638 142051 15  2 83  0  0
2

Query cache off -- good

Large history list -- Innodb stumbling over itself.

Latency suddenly shot up -- ditto

Thread count suddenly shot up -- ditto.

Possible causes:

  • A long-running UPDATE or ALTER blocking lots of other threads. Or even a SELECT that was poorly written. If the slowlog was on, you may be able to discover what it was. long_query_time = 0.5 implies that (if the slowlog was turned on) it will have caught the naughty query. And there would be a lot of normally-fast queries in the log, too.
  • Too many threads. What is "too many"? When this happens. How to control it? Decrease the limit on number of connections, either in the clients, or with max_connections. I know that sounds like a bad remedy, but when MySQL gets into the state you described, it may take a restart to uncork it. Sharing resources among 300-400 threads means that no one will 'ever' get finished. How many clients? Web server(s)? What is the limit on each? Example: Apache's MaxClients.
  • Swapping. Do you have any monitoring that would say that there was swapping? 90G/128G sounds fine, but perhaps something else was eating into RAM. Swapping is terrible for MySQL.
  • Currently, we have a lot of web-servers (~30) connecting to MySQL, but with each limited to 20 max connections (Hikari maxConnections). Adding vmstat snapshot from that time in the question. – user20507 Nov 17 '16 at 8:14
  • Well, I guess it is not swapping. – Rick James Nov 17 '16 at 22:20
  • @RickJames, don't you think I/O, CPU could have been an issue? – Nawaz Sohail Nov 18 '16 at 12:02
  • High CPU and/or I/O implies lack of indexes and/or poorly phrased queries. Show us the typical queries. And use pt-query-digest on the slowlog. – Rick James Nov 18 '16 at 18:16
  • Additional information request, please. Post on pastebin.com or here. complete (not edited) my.cnf-ini Text results of: A) SHOW GLOBAL STATUS; B) SHOW GLOBAL VARIABLES; C) complete MySQLTuner.com report if readily available - 7 or more days uptime is helpful D) SHOW ENGINE INNODB STATUS; Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for IOPS by device, cat/proc/meminfo will include context switch data - vmalloc used for server analysis. – Wilson Hauck May 9 '18 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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