本文描述了MySQL性能突然降低的情况，连接数增多到230多个。问题分析指出，可能是由于`innodb_thread_concurrency`设置不当，导致线程过多进入等待队列。解决方案是调整该参数至CPU核心数。通过检查`show engine innodb status`确认问题，并调高参数后解决了问题。
一：问题描述：
mysql性能突然降低，连接数突然增加，正常也就不到50个，如下;
root@localhost : (none) 18:34:48>select count(user) from information_schema.processlist group by user;
+-------------+
| count(user) |
+-------------+
| 230 |
| 150 |
| 3 |
| 8 |
| 6 |
| 2 |
| 1 |
| 17 |
+-------------+
8 rows in set (0.00 sec)
二：问题分析：
Innodb用自己的线程调度机制来控制线程如何进入innodb内核工作，并执行相关的操作。
当一个线程需要进入到Innodb存储引擎层(以下简称Innodb)，Innodb会检查已经进入到Innodb存储引擎层的线程总数是否超过innodb_thread_concurrency;如果超过了，则该线程需要等待innodb_thread_sleep_delay毫秒再次进行尝试;如果尝试仍然失败，该线程将会进入到FIFO的队列中进行等待唤醒(此时状态为sleeping)。 一旦该thread进入到INNODB中，该线程将会获得innodb_concurrency_tickets次通行证，即该线程在接下来的innodb_concurrency_tickets次进入到INNODB中都不需要再进行检查，可直接进入。
线程尝试两次进入INNODB存储引擎层的目的是，减少等待线程的数量以及减少上下文切换。
Innodb并发参数：
1) innodb_thread_concurrency
同一时刻能够进入innodb层并发执行的线程数量。如果超过CPU核数，某些线程就会处于就绪状态;若Server层线程数超过这个数值，多余的线程会被放到wait queue队列中等待;
默认值：0，表示不限制线程并发执行的数量，所有请求都会被认为是可调度的。此时，innodb_thread_sleep_delay的值会被忽略
范围：0 ~ 1000
2) innodb_commit_concurrency
同一时刻允许同时commit的线程数量
默认值：0，即不限制
范围：0 ~ 1000
如果 innodb_thread_concurrency 设置的有点大innodb_commit_concurrency应该做出相应的调整，否则会造成大量线程阻塞。
3) innodb_concurrency_tickets
thread进入INNODB中，会获得innodb_concurrency_tickets次通行，该线程在接下来的innodb_concurrency_tickets次进入到INNODB中不需要再进行检查，可直接进入。
默认值：5000
范围：0 ~ 4294967295
4) innodb_thread_sleep_delay
线程未能进入INNODB存储引擎，需要等待innodb_thread_sleep_delay毫秒再次尝试进入;即进入wait queue前sleep的时间;
单位：微妙
默认值：10000
在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：
1. 如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0;
2.如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，建议设置innodb_thread_concurrency参数为80，以避免影响性能。
3. 如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多(比如20个虚拟CPU)，建议通过设置innodb_thread_concurrency 参数为这个值(也可能更低，这取决于性能体现)，如果你的目标是将MySQL与其他应用隔离，你可以考虑绑定mysqld进程到专有的虚拟CPU。但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。
4.innodb_thread_concurrency设置的过高，可能增加系统内部和资源的争用，可能导致性能倒退。
5 在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。
6.定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要innodb_thread_concurrency参数的设置进行调整。
innodb_thread_concurrency参数设置的原则，一般等于cpu个数即可，如果不设置的话，当并发量
太高的时候 ，可以会导致mysql 耗尽cpu导致 数据库崩溃!
三：解决问题：
调高参数后问题解决了：
set global innodb_thread_concurrency=cpu个数
四：问题定位：
当出现问题的时候：如下查看：show engine innodb status，查看有没有线程由于没有线程由于重试后也没有进入innodb内核工作，而进入队列中等待，进而导致数据库出现性能问题;该线程将会进入到FIFO的队列中进行等待唤醒(此时状态为sleeping)
如下红色部分代表 有没有 线程进入了FIFO的队列中，如果有，则表示该innodb_thread_concurrency参数设置的小了，
root@localhost : (none) 18:27:15>show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2019-09-23 18:27:49 7f64ae3e2700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 67 srv_active, 0 srv_shutdown, 2254311 srv_idle
srv_master_thread log flush and writes: 2254345
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 404
OS WAIT ARRAY INFO: signal count 348
Mutex spin waits 494, rounds 3764, OS waits 84
RW-shared spins 297, rounds 9659, OS waits 312
RW-excl spins 160, rounds 805, OS waits 5
Spin rounds per wait: 7.62 mutex, 32.52 RW-shared, 5.03 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 25755
Purge done for trx's n:o < 25755 undo n:o < 0 state: running but idle
History list length 940
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 2892, OS thread handle 0x7f64ae3e2700, query id 30275 localhost root init
show engine innodb status
--------
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, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
493 OS file reads, 3985 OS file writes, 1228 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 4812030
Log flushed up to 4812030
Pages flushed up to 4812030
Last checkpoint at 4812030
Max checkpoint age 1738632684
Checkpoint age target 1684300413
Modified age 0
Checkpoint age 0
0 pending log writes, 0 pending chkp writes
1334 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2235564032; in additional pool allocated 0
Total memory allocated by read views 200
Internal hash tables (constant factor + variable factor)
Adaptive hash index 35438784 (35401816 + 36968)
Page hash 1107208 (buffer pool 0 only)
Dictionary cache 9298091 (8851984 + 446107)
File system 894592 (812272 + 82320)
Lock system 5313776 (5313416 + 360)
Recovery system 0 (0 + 0)
Dictionary memory allocated 446107
Buffer pool size 131070
Buffer pool size, bytes 2147450880
Free buffers 130075
Database pages 993
Old database pages 206
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 459, created 538, written 2187
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 993, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 65535
Buffer pool size, bytes 1073725440
Free buffers 65018
Database pages 516
Old database pages 206
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 24, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 248, created 272, written 1237
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 516, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 65535
Buffer pool size, bytes 1073725440
Free buffers 65057
Database pages 477
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 211, created 266, written 950
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 477, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
0 out of 1000 descriptors used
Main thread process no. 30859, id 140070431688448, state: sleeping
Number of rows inserted 11, updated 0, deleted 0, read 97
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
