每秒百万查询:MySQL与PG在苛刻负载下的和平之战

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

Anastasia:开源数据库能应付每秒数百万次的查询吗?许多开源倡导者会回答“是的”,但是,断言是不够有理有据的证明。这就是为什么在这篇文章中,我们将分享Alexander Korotkov(CEO,Postgres专家)和Sveta Smirnova(首席技术服务工程师,Percona)的基准测试结果。而且,PostgreSQL 9.6和MySQL 5.7的性能对比研究对于多数据库环境特别有价值。

这项研究背后的想法是客观地比较两个流行的数据库。Sveta和Alexander想在相同的具有挑战性的工作负载中,并使用相同配置参数(如果可能的话)的情况下,用同一个工具分别对最新版本的MySQL和PostgreSQL进行测试。然而,由于PostgreSQL和MySQL的生态系统发展的独立性,所以要使用标准的测试工具(pgbench和SysBench)来测试这两种数据库的话,将会是一次不轻松的测试之旅。

这个任务交给了有多年实践经验的数据库专家手上。Sveta作为首席高级技术支持工程师,在Oracle MySQL技术支持小组的bug验证组工作了八年以上,2015年以来一直担任Percona的首席技术服务工程师。Alexander Korotkov是PostgreSQL的主要贡献者,助力于PostgreSQL功能的开发,包括CREATE ACCESS METHOD命令、通用WAL接口、lockfreePin/unpinbuffer、基于索引的正则表达式搜索等等。所以,在我们这个特别的游戏中有着相当不错的参与者。

Sveta:Dimitri Kravtchuk定期出版对于MySQL的详细测试基准,所以我的主要任务不是确认MySQL是否可以查询每秒百万。正如我们的图表显示,我们早已突破了这个限度。作为一个技术支持工程师,我经常在客户的多种异构数据库环境中工作,并想知道从一个数据库迁移到另一个的影响。所以,我找到了机会跟专业的Postgres公司合作,并发现这对于了解这两种数据库的优缺点来说是极好的机会。

我们想在相同的硬件上使用相同的工具和方法测试这两个数据库。我们想测试基础功能,然后进行更详细的比较。这样就可以比较出现实中不同的案例场景和流行的配置。

 

剧透:我们离最终的结果还很远,这只是一个系列文章的开始。

 

开源数据库在Big Machine上

 

系列1:“很相近...”

 

Postgres专家使用Freematiq提供的两个新型号,为测试提供了强大的机器。

硬件配置:

处理器:数量=4,核数=72,虚拟核数=144,超线程已开启
内存:3.0T
硬盘速度:大概3K IOPS
OS:CentOS 7.1.1503
文件系统:XFS

 
 
 
 
 

 

我也使用了一台较小的Percona服务器。

硬件配置:
处理器:数量=2,核数=12,虚拟核数=24,超线程已开启
内存:251.9G
硬盘速度:大概33K IOPS
OS:Ubuntu 14.04.5 LTS
文件系统:EXT4

 
 
 
 
 

 

注意,部署MySQL的设备中,使用配备CPU核数较少并且硬盘更快的服务器比配备CPU核数高的服务器更普遍。

 

首先我们需要对使用哪种工具达成共识,公平地比较只有在工作负载尽可能接近时才更有意义。

 

标准的PostgreSQL性能测试工具是pgbench,在MySQL中用SysBench。SysBench支持多种数据库驱动和Lua编程语言脚本测试,所以我们决定使用SysBench作为两种数据库的测试工具。

最初的计划是将pgbench测试转换成SysBench lua语法,然后在两种数据库上运行标准测试。经过初步的测试结果,我们修改了测试方法来更好地研究特定的MySQL和PostgreSQL功能。

 

我把pgbench测试转换成了SysBench语法,并把测试上传到了一个开源数据库测试的GitHub知识库中。

然后我们都面临了一些困难。

当我写好之后,我在Percona服务器上也进行了测试。对于这个转换测试,结果几乎相同:

 

Percona服务器:

 

 

Freematiq服务器:

 

 

我开始研究,Percona的机器比Freematiq要好的唯一之处在于磁盘速度,所以我开始运行pgbench只读测试,这跟SysBench测试的内存中全表扫描的结果一致,但这一次SysBench使用了可用CPU资源50%:

 

 

Alexander,相应的,遇到了SysBench的问题,在使用准备好的语句时无法创造出PostgreSQL上的高负荷:

 

 

我们联系了SysBench的作者Alexey Kopytov,他修正了MySQL的问题。解决办法是:

  • 使用SysBench的参数 --percentile=0 --max-requests=0 (合理的CPU使用率)

  • 使用concurrency_kit分支(更好的并发和Lua处理)

  • 重写了Lua脚本来支持的准备好的语句(pull请求:https://github.com/akopytov/sysbench/pull/94

  • 启动SysBench和mysqld的时候使用jemalloc或tmalloc库预加载

 

PostgreSQL的修正方案在准备中。现在,Alexander将一个标准的SysBench测试转换成了pgbench格式,并且我们坚持做下来了。没有太多对于MySQL方面的调整,但至少我们有一个比较的基线。

我面临的下一个困难是默认操作系统参数。长话短说,我把它们改成了推荐的值:

 

 

相同的参数同样也对PostgreSQL的性能更好。Alexander同样设置了他的机器。

解决这些问题后,我们学习并继续进行测试:

  • 我们不能使用单一的工具(现在)

  • Alexander写了pgbench测试,模仿标准的SysBench测试

  • 我们仍然不能编写自定义测试,因为我们使用不同的工具

 

但是我们可以使用这些测试作为基线。由Alexander完成工作后,我们坚持做了标准的SysBench测试。我把它们转换成使用事先准备好的语句,Alexander将其转化为pgbench格式。

 

应该注意的是,我不能得到和Dimitri做的只读和point select选择测试相同的结果。它们接近,但稍微有点慢。我们需要调查这是由于硬件不同而导致的结果,还是我缺乏性能测试能力的结果。从读写测试返回的结果是相似的。

另一个区别是PostgreSQL和MySQL测试。MySQL用户通常有许多连接。设置变量max_conenctions的值,并且限制并行连接总数在上千的级别并不少见。虽然不建议,但是人们使用这个选项,即使没有线程池插件。在真实生活中,绝大多数的这种连接大多是休眠状态。但总会遇到机会,在网站活动增长的时候他们都会被使用。

MySQL我测试了1024个连接。我用2的幂次方和多核:1,2,4,8,16,32,36,64,72,128,144,256,512和1024线。

对于Alexander来说,更重要的是在线程较小的步骤中进行测试。他从一个线程开始,增加了10个线程,直到达到250个并行的线程。所以你会看到一个更详细的关于PostgreSQL的图,但250个线程之后没有结果。

以下是我们的比较结果:

Point SELECTs

 

 

  • pgsql-9.6 是标准的PostgreSQL

  • pgsql-9.6 + pgxact-align是PostgreSQL的补丁包(更多的细节可在本文中看到)

  • MySQL-5.7 Dimitri是Oracle's MySQL服务器

  • MySQL-5.7 Sveta是Percona服务器,版本5.7.15

 

OLTP RO

 

 

OLTP RW

 

 

Sync commit是PostgreSQL的一个功能,类似于InnoDB中的innodb_flush_log_at_trx_commit = 1,async commit类似innodb_flush_log_at_trx_commit = 2。

结果非常相似:两个数据库都发展很快,并且与现代硬件很好地兼容。

MySQL使用1024个线程测试的参考结果。
 

Point SELECT and OLTP RO

 

 

OLTP RW当参数innodb_flush_log_at_trx_commit分别设置为1和2时

 

 

收到这些结果后,我们做了一些特定功能的测试,将涵盖在单独的博客帖子中。
 

补充信息
 
 

 

MySQL选项OLTP RO和Point SELECE试验:

 

 

MySQL OLTP RW的配置参数:

 

 

MySQL SysBench的参数:

 

 

PostgreSQL pgbench配置参数:

 

 

MySQL 5.7中很多功能模块的性能明显提升了。
 原文发布时间为:2017-02-13

本文来自云栖社区合作伙伴DBAplus

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5天前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
23小时前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
|
2天前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
10 1
|
2天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
6 0
|
25天前
|
存储 SQL 关系型数据库
MySQL 给查询结果增列并自定义列数据
MySQL 给查询结果增列并自定义列数据
228 2
|
25天前
|
存储 SQL 关系型数据库
MySQL查询数据库锁表的SQL语句
MySQL查询数据库锁表的SQL语句
68 1
|
25天前
|
SQL 存储 关系型数据库
MySQL能否查询某张表的操作记录
MySQL能否查询某张表的操作记录
183 1
|
18天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
78 6
|
16天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
50 3
Mysql(4)—数据库索引
|
1天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
17 2