使用 MySQL5.7实现每秒50万的高性能查询

夜浩同学_8797

夜浩同学_8797

2017-08-10

3109人浏览

原创

[导读] 本文提供mysql5 7实现每秒50w查询一文的细节以及基准测试结果,解释了我早期在mysql connect 发表的谈话。回顾mysql   innodb 的改善历史。你能很容易发现。在mysql 5 6稳定版本中从来没有在read-only这么

本文提供 MySql5.7实现每秒50W查询 一文的细节以及基准测试结果,解释了我早期在Mysql Connect 发表的谈话。

回顾 MySQL / InnoDB 的改善历史。你能很容易发现。在MySQL 5.6稳定版本中从来没有在read-only 这么快的提速,它很容易搞懂,以及在read-only(RO)有着良好的扩张性。也很期待它在read+write(RW)上达到一个较高水平。(特别是在读取数据是数据库主要工作的时候)\


然而。我们对于RO在 MySQL 5.6的表现也十分的高兴,在5.7这个版本中,主要工作集中在 read+write (RW)上, 因为在大数据的处理上还没能达到我们的期望。但是RW依赖RO下。能够再次提高速度。 InnoDB 团队通过不断的改进,强烈的推进优化着5.7这个版本的每秒的性能。 

下面就按顺序为大家讲解

事实上,在MySQL中只读工作量控制内部链接的方式有以下两种:

  • 用单个表:MDL,trx_sys和lock_sys(InnoDB)

  • 多表:trx_sys和lock_sys(主要是InnoDB)

任何很快的单表范围测试的工作量主要由于MDL链接导致锁住。而多表将会由于InnoDB内部构件限制(不同的表将由不同的MDL锁保护,所以这种情况下MDL中的链接瓶颈将会降低)。但是同样,也要看工作量的大小--一个比一般多的只读工作测量将会在MySQL5.6中表现的会更好(如Sysbench OLTP_RO),同时在工作量少而快的查询(如Sysbench Point-Selects(用外键去取一个记录))将会使所有链接变得困难,而且只能在16核-HT中测量,而在32核中表现很差..但是任何如Point-Select测试的工作量将在所有MySQL内部构件一起工作是会让你看到可能达到最大的性能(开始用SQL解析器,终止与取行值)..在你给定的MySQL版本和给定的HW配置下,这也可能达到最大SQL 查询/每秒(QPS)率。

在Mysql5.6上我们获得的最佳结果是25万个查询每秒,这也是那段时间Mysql/InnoDb上使用SQL语句查询得到的最好的结果了。

当然,只有在使用‘只读事务’功能才能达到这么高速度(Mysql5.6上的新功能);另外,需要使用AUTOCOMMIT=1,否则CPU就会被轻易地浪费在启动事务、提交事务上,你会实际上损失系统的整体性能。

因此,在Mysql5.7上介绍的第一个改进是‘只读事务的自动发现’(实际上每个InnoDb事务都被认为是只读的直到有一个DML声明在此之外)功能---,这很大程度上简化了只读事务功能,节省了用户和开发者的时间,他们不用再去管理是否采用只读事务功能。但是,使用这个功能你仍然不能达到Mysql潜在的最佳每秒查询率,因为CPU时间还是浪费在事务的开启、结束状态处理过程当中。

同时,Percona用不同的的方案来解决“事务列表”管理(TRX-列表)及在InnoDB中trx_sys互斥链接慢的问题。Percona的解决方案在用事务处理Point-Selects高负载时能表现良好,但MySQL5.7表现一般(但我不会公布5.7的结果,因为它的代码不公开)...所以,至少我现在可以做一些比较:

\

观察结果:

  • 在MySQL5.6,Percona 5.5和MySQL5.7中的8个表中用同样的Roint-Select-TRX只读测试(用事务)(2013.5月的结果)

  • 同时你也可以看到,在同样的16核-HT配置下我们离峰值25万/s的结果还很远。

  • MySQL5.6在trx_sys互斥访问中延长了链接时间,而且自从64个用户后每秒的请求数将减少。

  • Percona5.5能维持很长的时间的负载,每秒请求在512个用户时才开始减少

  • 当MySQL5.7已经保持一段时间时,每秒请求依然没有减少(对于更多用户并发的情况你在这幅图里是看不到的)...

然而,很明显,如果用MySQL想要得到最大的潜在每秒查询速率,事务应当避免。

让我们来看一看这是2013年5月我们的每秒最大查询速率。

在同一点八张表进行测试,但是没有使用MySQL5.6的事物:

观察:

  • 上面的测试是保持MySQL5.6始终执行在16核上,然后是16芯-HT,32核,32芯-HT.

  • 正如你所看到的,最大的每秒查询速率比预期的还要大 -—— 在MySQL上是每秒27.5万

  • 最大的结果已经达到16芯-HT.

  • 然而在32核上的结果并没有16芯-HT上的好(由于竞争中断,在相同内核中,具有2CPU线程的配置能够更好的管理线程竞争——所以真正的并发性仍保存在16线程,而不是32核上)

而在MySQL5.7上做同样的测试却看起来大有不同,因为在5.7中lock_sys互斥链接的时间段已经很低了,同时trx_sys互斥相关代码也得到第一次变化的情形:

\

观察结果:

  • 首先你可以看到5.7在同样的16核-HT配置下的性能已经比5.6的要好

  • 之后,在32核配置下没有明显的增强!

  • 在32核-HT配置下达到了35万/秒的最大请求!

  • 从上面特殊(具有攻击性)只读负载测试的情况下可以容易看出我们在32核中得到的结果要比16的好,同时我们还没有启动超线程(在32核-HT)...牛吧!;-)

从另一方面来讲,仍然有改进的空间这点还是很清晰的。有关trx_sys的争用仍然在持续。我们没有充分的使用CPU的能力来做有用的工作(仍然有许多CPU周期用在锁的轮转)...不过现在的结果比以前好多了,并且比5.6好很多,因此没有理由继续挖掘来提高这方面的性能,我们主要集中在我们曾经花费了巨大的空间的读写负载的性能提高上。

到了5月底,也就是我们的性能会议期间,Sunny给try_sys互斥争用增加了几个新的更改,从那以后最大的每秒可进行的查询(QPS)可达到375K!这是不是对5.7进行了足够的性能提高,对吗?;-)

同时,我们继续与建议用其他方式管理TRX列表的Percona团队交换了意见,他们的方案看起来非常有趣,不过在5.5上,这样的代码却不能展示出更高的每秒可进行的查询数(QPS),而且在5.6上的这样代码(曾经测试过Percona Server 5.6)最大的每秒可进行的查询数(QPS)也不会比在MySQL 5.6上大。然而,讨论涉及到一个有趣的观点:如果同时有一些读写负载在运行的话,它对只读性能有什么影响呢?...而且,即使在同样的测试条件下MySQL 5.7代码仍然运行的要好一些,效果是非常明显的(你可以在这儿查看我的分析,然而,再次说明一下,这段时间内我不能展示5.7上的结果,因为它的代码还没有对大众公布-也许会在以后的一篇文章中给出)..

由于这儿同时对任何纯粹的读写负载也有影响,因此有足够的动机以Sunnys很长时间所期待的那样重新写整个TRX列表相关的代码,然而,这种经历简直让人痴迷! 

;-)) 日复一日,我们很高兴的看到我们的每秒可进行的查询图逐渐变高,直到在同一个32核的超线程服务器上达到了每秒可进行的查询440K!

5.7开发里程碑发布2上进行的Select 8个表所得到的结果数:

不需要说明..;-))

然而,有一个小小的令人奇怪的地方-我们试图与Sunny通过不同的工具分析所有瓶颈和代码更改所带来的影响。而且在某些测试里,令我吃惊的是Sunny观察到比我更高的每秒可进行的查询数..这个“奇异之处”与下面因素相关:

  • 在高负载下,现在的5.7代码都运行在接近硬件极限(主要是CPU)的位置,因此每条指令都非常重要!

  • 如果使用的Unix套接字或者IP端口,那么区分就会非常明显!

  • Sysbench自身使用了30%的CPU时间,不过同样的测试负载使用的是(具有更短的代码路径的)老版本的Sysbench的话,它将只使用20%CPU,剩余的10%用在MySQL服务器上。

  • 因此,同样测试负载的情况下,使用Unix套接字而不是IP 端口,并且使用Sysbench-0.4.8替代Sysbench-0.4.13的话,我们将得到每秒可进行的查询数超过500K!-很容易,不是吗?;-))

让我们来比较“之前”和“之后”的差异

观察结果:

  • 通过Sysbench降低了CPU的使用率。

    Skybox AI
    Skybox AI

    Skybox AI是一款由 Blockade Labs 推出的 360° 全景和沉浸式环境生成工具。

    下载
  • 在MySQL服务器上具有更高的CPU可用性。

  • 我们实现了50万每秒查询。

还有什么呢?

我可能只提到:kudos Sunny和整个MySQL的开发团队;

让我们看一下现在选择8张表工作负载的情况下的最大每秒查询。

  • MySQL-5.7.2 (DMR2)

  • MySQL-5.6.14

  • MySQL-5.5.33

  • Percona Server 5.6.13-rc60.5

  • Percona Server 5.5.33-rel31.1

  • MariaDB-10.0.4

  • MariaDB-5.5.32

每个引擎都在以下配置下进行测试:

  • CPU taskset: 8核-HT,16核,16核-HT,32核,32核-HT

  • 并发会话数:8,16,32 ... 1024

  • InnoDB自旋等待延时:6,96

最好的结果是来自任意两个特定的组合间的比较。通过对数据库引擎的比较,我得到了下面的一个图表,这个图表我在以前的文章中已经提到过了。

下面是一些评论:

  • 对Mysql5.7的巨大差距结果不需要做过多的评论,因为这是很明显的。

  • 那么,有趣的是基于MySQL5.5的代码库引擎没有任何的接近MySQL5.6的结果。

  • 这已经证实了在使用MySQL5.6的代码库引擎之后,Percona Server达到了MySQL5.6的水平,然而MariaDB-10仍然还在探索的路上。

  • 因此,毫无疑问,MySQL5.6是代码的基石!

  • MySQL5.7是在MySQL5.6基础上的再一次优化扩展。

具有什么样的扩展性呢?

答案是简单的:MySQL5.7是唯一在此基础上进行扩展的。

如果使用ip端口和一个重量级的Sysbench-0.4.13,会得到如下的结果:

QPS只是稍微的略低一点,但是总体的趋势是完全一样的。

可扩展性也是非常的相似:

 

更多的结果将会出来,敬请期待;

注意:对一个单表绑定过多的工作负载是不好的:

  • 减少InnoDB间的争论使得其他的争论更加的明显。

  • 当负载是绑定在一张单表上时候,MDL的争论将变得更加主导。

  • 这是预期希望的,我们在下一个DMRS上将保持不变。

 

还有很多挑战摆在我们面前;-)

作为参考,我上述测试的硬件配置信息如下:

  • Server : 32cores-HT (bi-thread) Intel 2300Mhz, 128GB RAM

  • OS : Oracle Linux 6.2

  • FS : 启用"noatime,nodiratime,nobarrier"挂载的EXT4


my.conf:

max_connections=4000
 key_buffer_size=200M
 low_priority_updates=1
 table_open_cache = 8000
 back_log=1500
 query_cache_type=0
 table_open_cache_instances=16

# files
 innodb_file_per_table
 innodb_log_file_size=1024M
 innodb_log_files_in_group = 3
 innodb_open_files=4000

# buffers
 innodb_buffer_pool_size=32000M
 innodb_buffer_pool_instances=32
 innodb_additional_mem_pool_size=20M
 innodb_log_buffer_size=64M
 join_buffer_size=32K
 sort_buffer_size=32K

# innodb
 innodb_checksums=0
 innodb_doublewrite=0
 innodb_support_xa=0
 innodb_thread_concurrency=0
 innodb_flush_log_at_trx_commit=2
 innodb_max_dirty_pages_pct=50
 innodb_use_native_aio=1
 innodb_stats_persistent = 1
 innodb_spin_wait_delay= 6 / 96

# perf special
 innodb_adaptive_flushing = 1
 innodb_flush_neighbors = 0
 innodb_read_io_threads = 4
 innodb_write_io_threads = 4
 innodb_io_capacity = 4000
 innodb_purge_threads=1
 innodb_adaptive_hash_index=0

# monitoring
 innodb_monitor_enable = '%'
 performance_schema=OFF

如果你需要的话,Linux Sysbench的二进制版本在这里:

  • Sysbench-0.4.13-lux86

  • Sysbench-0.4.8-lux86

使用UNIX socket来运行Point-Selects测试的Sysbench命令如下(在parallel中启动8个进程):

LD_PRELOAD=/usr/lib64/libjemalloc.so /BMK/sysbench-0.4.8 --num-threads=$1 --test=oltp --oltp-table-size=10000000 \
        --oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n \
        --max-requests=0 --max-time=$2 --mysql-socket=/SSD_raid0/mysql.sock \
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
        --mysql-table-engine=INNODB  --db-driver=mysql \
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
        --oltp-read-only=on run  > /tmp/test_$n.log &

使用IP端口来运行Point-Selects测试的Sysbench命令如下(在parallel中启动8个进程):

LD_PRELOAD=/usr/lib64/libjemalloc.so /BMK/sysbench-0.4.13 --num-threads=$1 --test=oltp --oltp-table-size=10000000 \
        --oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n \
        --max-requests=0 --max-time=$2 --mysql-host=127.0.0.1 --mysql-port=5700 \
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
        --mysql-table-engine=INNODB  --db-driver=mysql \
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
        --oltp-read-only=on run  > /tmp/test_$n.log &

相关文章

数码产品性能查询
数码产品性能查询

该软件包括了市面上所有手机CPU,手机跑分情况,电脑CPU,电脑产品信息等等,方便需要大家查阅数码产品最新情况,了解产品特性,能够进行对比选择最具性价比的商品。

下载

相关标签:

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

20

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

20

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

20

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

20

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

120

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

120

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程