MySQL多线程并发调优

本文涉及的产品
对象存储 OSS,20GB 3个月
对象存储 OSS,恶意文件检测 1000次 1年
对象存储 OSS,内容安全 1000次 1年
简介:

前言

学习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)
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 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

Reference

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
12天前
|
数据采集 存储 Java
高德地图爬虫实践:Java多线程并发处理策略
高德地图爬虫实践:Java多线程并发处理策略
|
1天前
|
分布式计算 Java Hadoop
NameNode 处理线程配置(心跳并发)
NameNode线程池处理客户端和数据节点请求,如读写文件及心跳、块报告。通过调整`dfs.namenode.handler.count`(默认10,示例设为21)在`hdfs-site.xml`中可控制并发处理能力。线程数过多或过少都可能影响性能,需平衡资源使用并进行基准测试以确定最佳值。合理线程数可通过公式`int(math.log(N) * 20)`计算,N为服务器数量。例如,3台服务器的计算结果为21。
|
1天前
|
安全 Java 开发者
探索Java中的多线程编程与并发控制
多线程编程是Java编程中不可或缺的一部分,它允许程序同时执行多个任务,从而显著提高程序的整体性能。然而,多线程编程也带来了诸如数据不一致、死锁等并发问题。本文将深入探讨Java中的多线程编程技术,包括线程的创建、同步与通信,并介绍几种常用的并发控制策略,旨在帮助读者更好地理解并发编程的复杂性和挑战,并学会如何编写高效、安全的并发程序。
|
3天前
|
缓存 安全 Java
JAVA多线程编程与并发控制
```markdown Java多线程编程与并发控制关键点:1) 通过Thread或Runnable创建线程,管理线程状态;2) 使用synchronized关键字和ReentrantLock实现线程同步,防止数据竞争;3) 利用线程池(如Executors)优化资源管理,提高系统效率。并发控制需注意线程安全,避免死锁,确保程序正确稳定。 ```
|
3天前
|
安全 Go 对象存储
C++多线程编程:并发与同步的实战应用
本文介绍了C++中的多线程编程,包括基础知识和实战应用。C++借助`&lt;thread&gt;`库支持多线程,通过`std::thread`创建线程执行任务。文章探讨了并发与同步的概念,如互斥锁(Mutex)用于保护共享资源,条件变量(Condition Variable)协调线程等待与通知,以及原子操作(Atomic Operations)保证线程安全。实战部分展示了如何使用多线程进行并发计算,利用`std::async`实现异步任务并获取结果。多线程编程能提高效率,但也需注意数据竞争和同步问题,以确保程序的正确性。
|
4天前
|
关系型数据库 MySQL Java
实时计算 Flink版产品使用合集之同步MySQL数据到Hologres时,配置线程池的大小该考虑哪些
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
5天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之mysql cdc支持全量的时候并发读取,该如何配置
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
9天前
|
算法 Java 程序员
Java中的线程同步与并发控制
【5月更文挑战第18天】随着计算机技术的不断发展,多核处理器的普及使得多线程编程成为提高程序性能的关键。在Java中,线程是实现并发的一种重要手段。然而,线程的并发执行可能导致数据不一致、死锁等问题。本文将深入探讨Java中线程同步的方法和技巧,以及如何避免常见的并发问题,从而提高程序的性能和稳定性。
|
10天前
|
Java 测试技术 Python
Python的多线程允许在同一进程中并发执行任务
【5月更文挑战第17天】Python的多线程允许在同一进程中并发执行任务。示例1展示了创建5个线程打印&quot;Hello World&quot;,每个线程调用同一函数并使用`join()`等待所有线程完成。示例2使用`ThreadPoolExecutor`下载网页,创建线程池处理多个URL,打印出每个网页的大小。Python多线程还可用于线程间通信和同步,如使用Queue和Lock。
34 1
|
11天前
|
关系型数据库 MySQL Java
实时计算 Flink版产品使用合集之mysql通过flink cdc同步数据,有没有办法所有表共用一个dump线程
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。

推荐镜像

更多