MySQL多线程并发调优

本文涉及的产品
对象存储 OSS,20GB 3个月
阿里云盘企业版 CDE,企业版用户数5人 500GB空间
云备份 Cloud Backup,100GB 3个月
简介:

前言

学习MySQL数据库技术,一个非常重要的技能就是性能调优。通常情况下,都是自下而上的调优方法,主要包括运行环境、配置参数、SQL性能和系统架构设计调优等。

本文从多线程并发的角度进行的思考,简单描述MySQL并发参数及其调优。

MySQL并发模型

架构

mysql_arch

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 concurrency相关参数

  • 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)
AI 代码解读
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)
AI 代码解读
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 918627501, not started, process no 13429, OS thread id 1191389504
MySQL thread id 29134175, query id 378160830 ...

...

---TRANSACTION 0 918627539, not started, process no 13429, OS thread id 1075824960
MySQL thread id 29133262, query id 378160869 localhost 127.0.0.1 apsara
--------
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
AI 代码解读

Reference

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
1
3038
分享
相关文章
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
207 66
MySQL MVCC全面解读:掌握并发控制的核心机制
【10月更文挑战第15天】 在数据库管理系统中,MySQL的InnoDB存储引擎采用了一种称为MVCC(Multi-Version Concurrency Control,多版本并发控制)的技术来处理事务的并发访问。MVCC不仅提高了数据库的并发性能,还保证了事务的隔离性。本文将深入探讨MySQL中的MVCC机制,为你在面试中遇到的相关问题提供全面的解答。
268 2
【MySQL基础篇】事务(事务操作、事务四大特性、并发事务问题、事务隔离级别)
事务是MySQL中一组不可分割的操作集合,确保所有操作要么全部成功,要么全部失败。本文利用SQL演示并总结了事务操作、事务四大特性、并发事务问题、事务隔离级别。
【MySQL基础篇】事务(事务操作、事务四大特性、并发事务问题、事务隔离级别)
List并发线程安全问题
【10月更文挑战第21天】`List` 并发线程安全问题是多线程编程中一个非常重要的问题,需要我们认真对待和处理。只有通过不断地学习和实践,我们才能更好地掌握多线程编程的技巧和方法,提高程序的性能和稳定性。
231 59
|
2月前
|
线程安全的艺术:确保并发程序的正确性
在多线程环境中,确保线程安全是编程中的一个核心挑战。线程安全问题可能导致数据不一致、程序崩溃甚至安全漏洞。本文将分享如何确保线程安全,探讨不同的技术策略和最佳实践。
50 6
Java 多线程并发控制:深入理解与实战应用
《Java多线程并发控制:深入理解与实战应用》一书详细解析了Java多线程编程的核心概念、并发控制技术及其实战技巧,适合Java开发者深入学习和实践参考。
70 6
Java多线程编程中的并发容器:深入解析与实战应用####
在本文中,我们将探讨Java多线程编程中的一个核心话题——并发容器。不同于传统单一线程环境下的数据结构,并发容器专为多线程场景设计,确保数据访问的线程安全性和高效性。我们将从基础概念出发,逐步深入到`java.util.concurrent`包下的核心并发容器实现,如`ConcurrentHashMap`、`CopyOnWriteArrayList`以及`BlockingQueue`等,通过实例代码演示其使用方法,并分析它们背后的设计原理与适用场景。无论你是Java并发编程的初学者还是希望深化理解的开发者,本文都将为你提供有价值的见解与实践指导。 --- ####
Java线程池调优指南###
本文深入探讨了Java线程池的工作原理与调优策略,旨在帮助开发者理解线程池的核心参数及其对应用性能的影响。通过实例分析,揭示如何根据具体业务场景合理配置线程池,以实现资源高效利用和系统稳定性的平衡。 ###
Java中的多线程编程:并发与并行的深度解析####
在当今软件开发领域,多线程编程已成为提升应用性能、响应速度及资源利用率的关键手段之一。本文将深入探讨Java平台上的多线程机制,从基础概念到高级应用,全面解析并发与并行编程的核心理念、实现方式及其在实际项目中的应用策略。不同于常规摘要的简洁概述,本文旨在通过详尽的技术剖析,为读者构建一个系统化的多线程知识框架,辅以生动实例,让抽象概念具体化,复杂问题简单化。 ####
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
380 1

云存储

+关注
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等