MySQL 在 Drop Large Table 中挂起，现在需要很长时间才能连接

Question

MySql 接受连接的速度很慢，并且在尝试删除包含 5000 万条记录的大型表失败后变得昏昏欲睡。以前可以立即建立连接，现在只需 20-30 秒即可建立连接。

运行mysqlcheck结果表正常，但是SHOW ENGINE INNODB STATUS看起来不正确:

它以这样的多行开始:

+--------+------+---------------------------- ------------------------------------------------ -------------- -------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------- ---- -------------------------------------------------- -------------------------- ------------------------ -------------------------------------------------- ------

这会持续很多行，然后:

----------------------------------------------+
| InnoDB |      |
=====================================
2017-01-31 19:03:05 0x7f2be82e5700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 35 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 19 srv_active, 0 srv_shutdown, 1234 srv_idle
srv_master_thread log flush and writes: 1253
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 91
OS WAIT ARRAY INFO: signal count 74
RW-shared spins 0, rounds 58, OS waits 23
RW-excl spins 0, rounds 205, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 58.00 RW-shared, 205.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 40192594
Purge done for trx's n:o < 40190602 undo n:o < 0 state: running but idle
History list length 2280
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421306474252112, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
369979 OS file reads, 61 OS file writes, 15 OS fsyncs
286.85 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 2193, seg size 2195, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1182691, node heap has 0 buffer(s)
Hash table size 1182691, node heap has 3 buffer(s)
Hash table size 1182691, node heap has 3 buffer(s)
Hash table size 1182691, node heap has 6 buffer(s)
Hash table size 1182691, node heap has 2 buffer(s)
Hash table size 1182691, node heap has 2 buffer(s)
Hash table size 1182691, node heap has 1 buffer(s)
Hash table size 1182691, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 310536690314
Log flushed up to   310536690314
Pages flushed up to 310536690314
Last checkpoint at  310536690305
0 pending log flushes, 0 pending chkp writes
16 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4397727744
Dictionary memory allocated 39828543
Buffer pool size   262112
Free buffers       0
Database pages     262095
Old database pages 96806
Modified db pages  0
Pending reads      1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 103, not young 6145557
0.00 youngs/s, 92166.80 non-youngs/s
Pages read 369591, created 34, written 37
286.88 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 368 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 262095, unzip_LRU len: 0
I/O sum[120336]:cur[304], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   32764
Free buffers       0
Database pages     32761
Old database pages 12102
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 12, not young 782981
0.00 youngs/s, 12008.66 non-youngs/s
Pages read 46266, created 0, written 3
36.46 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 369 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 32761, unzip_LRU len: 0
I/O sum[15042]:cur[38], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   32764
Free buffers       0
Database pages     32760
Old database pages 12090
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 23, not young 714985
0.00 youngs/s, 10882.83 non-youngs/s
Pages read 43701, created 0, written 0
33.54 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 361 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 32760, unzip_LRU len: 0
I/O sum[15042]:cur[38], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   32764
Free buffers       0
Database pages     32762
Old database pages 12102
Modified db pages  0
Pending reads      1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 12, not young 826273
0.00 youngs/s, 12544.36 non-youngs/s
Pages read 47653, created 0, written 0
40.11 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 363 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 32762, unzip_LRU len: 0
I/O sum[15042]:cur[38], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   32764
Free buffers       0
Database pages     32763
Old database pages 12103
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11, not young 789399
0.00 youngs/s, 10819.38 non-youngs/s
Pages read 46867, created 0, written 0
34.46 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 364 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 32763, unzip_LRU len: 0
I/O sum[15042]:cur[38], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   32764
Free buffers       0
Database pages     32762
Old database pages 12089
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24, not young 716775
0.00 youngs/s, 10601.24 non-youngs/s
Pages read 45316, created 0, written 0
32.43 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 371 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 32762, unzip_LRU len: 0
I/O sum[15042]:cur[38], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   32764
Free buffers       0
Database pages     32762
Old database pages 12094
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 20, not young 816196
0.00 youngs/s, 12974.26 non-youngs/s
Pages read 47369, created 0, written 0
39.77 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 378 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 32762, unzip_LRU len: 0
I/O sum[15042]:cur[38], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   32764
Free buffers       0
Database pages     32764
Old database pages 12113
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 739215
0.00 youngs/s, 11328.79 non-youngs/s
Pages read 46339, created 34, written 34
34.51 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 373 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 32764, unzip_LRU len: 0
I/O sum[15042]:cur[38], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   32764
Free buffers       0
Database pages     32761
Old database pages 12113
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 759733
0.00 youngs/s, 11007.29 non-youngs/s
Pages read 46080, created 0, written 0
35.60 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 367 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 32761, unzip_LRU len: 0
I/O sum[15042]:cur[38], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=2785, Main thread ID=139826948695808, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 14
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
+--------+------+---------------------------------------------------------------
                                                                                           --------------------------------------------------------------------------------                                                                                                --------------------------------------------------------------------------------                                                                                                --------------------------------------------------------------------------------                                                                                                -------------------------------------------------------------------------------- 

最后，输出像开始一样以多行破折号和加号结束。

请告知我有关故障排除和修复的其他步骤。

Answer 1

如果你怀疑innoDB行为异常，你可以看看引擎在做什么，这很昂贵，所以在正常情况下不要这样做。手册内容如下:

Enable the InnoDB Monitors to obtain information about a problem (see Section 14.17, “InnoDB Monitors”). If the problem is performance-related, or your server appears to be hung, you should enable the standard Monitor to print information about the internal state of InnoDB. If the problem is with locks, enable the Lock Monitor. If the problem is in creation of tables or other data dictionary operations, enable the Table Monitor to print the contents of the InnoDB internal data dictionary. To see tablespace information enable the Tablespace Monitor.

InnoDB temporarily enables standard InnoDB Monitor output under the following conditions:

• A long semaphore wait

• InnoDB cannot find free blocks in the buffer pool

• Over 67% of the buffer pool is occupied by lock heaps or the adaptive hash index

https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html https://dev.mysql.com/doc/refman/5.6/en/innodb-monitors.html