前言
学习 MySQL 数据库技术, 一个非常重要的技能就是性能调优. 通常情况下, 都是自下而上的调优方法, 主要包括运行环境, 配置参数, SQL 性能和系统架构设计调优等.
本文从多线程的角度, 简单描述 MySQL 并发参数及其调优.
MySQL 并发模型
架构
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 的这种两阶段的机制减少了操作系统因为线程之间的上下文切换带来的开销.
Innodb 并发参数
innodb_thread_concurrency
同一时刻能够进入 innodb 层并发执行的线程数量. 如果超过 CPU 核数, 某些线程就会处于就绪状态; 若 Server 层线程数超过这个数值, 多余的线程会被放到 wait queue 队列中等待;
默认值: 0, 表示不限制线程并发执行的数量, 所有请求都会被认为是可调度的. 此时, innodb_thread_sleep_delay 的值会被忽略
范围: 0 ~ 1000
innodb_commit_concurrency
同一时刻允许同时 commit 的线程数量
默认值: 0, 即不限制
范围: 0 ~ 1000
如果 innodb_thread_concurrency 设置的有点大 innodb_commit_concurrency 应该做出相应的调整, 否则会造成大量线程阻塞.
innodb_concurrency_tickets
thread 进入 INNODB 中, 会获得 innodb_concurrency_tickets 次通行, 该线程在接下来的 innodb_concurrency_tickets 次进入到 INNODB 中不需要再进行检查, 可直接进入.
默认值: 5000
范围: 0 ~ 4294967295
innodb_thread_sleep_delay
线程未能进入 INNODB 存储引擎, 需要等待 innodb_thread_sleep_delay 毫秒再次尝试进入; 即进入 wait queue 前 sleep 的时间;
单位: 微妙
默认值: 10000
建议值
如下建议来自 MySQL 官网. 详情请参考 https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_thread_concurrency
如果一个工作负载中, 并发用户线程的数量小于 64, 建议设置 innodb_thread_concurrency=0;
如果工作负载一直较为严重甚至偶尔达到顶峰, 建议先设置 innodb_thread_concurrency=128, 并通过不断的降低这个参数, 96, 80, 64 等等, 直到发现能够提供最佳性能的线程数.
例如, 假设系统通常有 40 到 50 个用户, 但定期的数量增加至 60,70, 甚至 200. 你会发现, 性能在 80 个并发用户设置时表现稳定, 如果高于这个数, 性能反而下降. 在这种情况下, 建议设置 innodb_thread_concurrency 参数为 80, 以避免影响性能.
如果你不希望 InnoDB 使用的虚拟 CPU 数量比用户线程使用的虚拟 CPU 更多 (比如 20 个虚拟 CPU), 建议通过设置 innodb_thread_concurrency 参数为这个值 (也可能更低, 这取决于性能体现), 如果你的目标是将 MySQL 与其他应用隔离, 你可以考虑绑定 mysqld 进程到专有的虚拟 CPU.
但是需要注意的是, 这种绑定, 在 myslqd 进程一直不是很忙的情况下, 可能会导致非最优的硬件使用率. 在这种情况下, 你可能会设置 mysqld 进程绑定的虚拟 CPU, 允许其他应用程序使用虚拟 CPU 的一部分或全部.
在某些情况下, 最佳的 innodb_thread_concurrency 参数设置可以比虚拟 CPU 的数量小.
定期检测和分析系统, 负载量, 用户数或者工作环境的改变可能都需要对 innodb_thread_concurrency 参数的设置进行调整.
观察
可以通过如下命令观察参数及状态.
- mysql> show variables like '%concurrency%';
- +----------------------------+-------+
- | Variable_name | Value |
- +----------------------------+-------+
- | innodb_commit_concurrency | 0 |
- | innodb_concurrency_tickets | 5000 |
- | innodb_thread_concurrency | 0 |
- | thread_concurrency | 10 |
- +----------------------------+-------+
- 4 rows in set (0.00 sec)
mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx ;
- +----------+-----------+--------------------------------------+---------------------+-------------------------+
- | trx_id | trx_state | trx_query | trx_operation_state | trx_concurrency_tickets |
- +----------+-----------+--------------------------------------+---------------------+-------------------------+
- | 45956096 | RUNNING | select count(*) from tb_test | committing | 0 |
- +----------+-----------+--------------------------------------+---------------------+-------------------------+
- 1 row in set (0.38 sec)
- mysql> show engine innodb status \G;
- *************************** 1. row ***************************
- Type: InnoDB
- Name:
- Status:
- =====================================
180612 11:27:51 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
- ----------
- SEMAPHORES
- ----------
OS WAIT ARRAY INFO: reservation count 40092798, signal count 27800089
Mutex spin waits 0, rounds 1448336939, OS waits 27203399
RW-shared spins 5866534, OS waits 2555867; RW-excl spins 16147805, OS waits 6633709
- ------------
- TRANSACTIONS
- ------------
- Trx id counter 0 918627542
Purge done for trx's n:o < 0 918627313 undo n:o < 0 0
- History list length 63
- LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 918627510, not started, process no 13429, OS thread id 1193253184
...
---TRANSACTION 0 918627539, not started, process no 13429, OS thread id 1075824960
- MySQL thread id 29133262, query id 378160869 localhost 127.0.0.1 test
- --------
- FILE I/O
- --------
- I/O thread 0 state: waiting for i/o request (insert buffer thread)
- I/O thread 1 state: waiting for i/o request (log thread)
- I/O thread 2 state: waiting for i/o request (read thread)
- I/O thread 3 state: waiting for i/o request (write thread)
- Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
18053159 OS file reads, 61697040 OS file writes, 34602915 OS fsyncs
- 2.75 reads/s, 16384 avg bytes/read, 4.75 writes/s, 4.75 fsyncs/s
- -------------------------------------
- INSERT BUFFER AND ADAPTIVE HASH INDEX
- -------------------------------------
- Ibuf: size 1, free list len 5, seg size 7,
664776 inserts, 664776 merged recs, 13693 merges
- Hash table size 17393, node heap has 22 buffer(s)
- 37.99 hash searches/s, 36.74 non-hash searches/s
- ---
- LOG
- ---
- Log sequence number 21 621309414
- Log flushed up to 21 621309414
- Last checkpoint at 21 621282806
0 pending log writes, 0 pending chkp writes
- 32082113 log i/o's done, 4.75 log i/o's/second
- ----------------------
BUFFER POOL AND MEMORY
- ----------------------
- Total memory allocated 21774746; in additional pool allocated 1044224
Dictionary memory allocated 639320
- Buffer pool size 512
- Free buffers 0
- Database pages 490
Modified db pages 52
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 70252515, created 255750, written 41909328
2.75 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 991 / 1000
- --------------
- ROW OPERATIONS
- --------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 13429, id 1167427904, state: sleeping
Number of rows inserted 372775, updated 74210855, deleted 4797, read 3912463894
- 0.00 inserts/s, 12.75 updates/s, 0.00 deletes/s, 134.72 reads/s
- ----------------------------
END OF INNODB MONITOR OUTPUT
- ============================
- 1 row in set (0.00 sec)
- ERROR:
- No query specified
- Reference
- http://imysql.com/2014/09/05/mysql-faq-why-close-query-cache.shtml
- http://imysql.com/2014/09/14/mysql-faq-why-innodb-table-using-autoinc-int-as-pk.shtml
- https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
- https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-thread_concurrency.html
- https://bugs.mysql.com/bug.php?id=42101 (Race condition in innodb_commit_concurrency)
- http://www.ovaistariq.net/496/tuning-innodb-configuration/#.WWjWEtOGM_U
- https://www.linuxidc.com/Linux/2017-06/144814.htm
来源: http://www.tuicool.com/articles/r2iuMvB