首页 ›存档› 技术 › 查看内容

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

2018-3-30 13:00 |来自: 互联网 314 0

摘要: DBAplus社群译者：高强原标题：Millions of Queries per Second: PostgreSQL and MySQL's Peaceful Battle at Today's Demanding Workloads 作者：Anastasia Raspopina 和 Sveta Smirnova 地址：https://www.percon ...

DBAplus社群译者：高强

原标题：Millions of Queries per Second: PostgreSQL and MySQL's Peaceful Battle at Today's Demanding Workloads

作者：Anastasia Raspopina 和 Sveta Smirnova

地址：https://www.percona.com/blog/2017/01/06/millions-queries-per-second-postgresql-and-mysql-peaceful-battle-at-modern-demanding-workloads/

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中很多功能模块的性能明显提升了。

参考链接

InnoDB：事务列表优化

InnoDB小组的博客

（https://blogs.oracle.com/mysqlinnodb/entry/transaction_life_cycle_improvements_in）
WL #6047

（http://dev.mysql.com/worklog/task/?id=6047）

InnoDB：减少lock_sys_t::mutex争用

WL #6899

（http://dev.mysql.com/worklog/task/?id=6899）

InnoDB：解决index-

声明：文章版权归原作者所有部分文章转自互联网如有侵权请联系 [邮箱地址] 删除

路过

雷人

握手

鲜花

鸡蛋

收藏分享邀请

上一篇：【Nature】意外流行算法给群体智慧带来新解，可用于多数投票决策下一篇：通过 WSL 在 Windows 下愉快的玩耍 Linux

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

相关分类