This blog compares how PostgreSQL and mysql handle millions of queries per second.

Anastasia : Can open source databases cope with millions of queries per second? Many open source advocates would answer “yes.” However, assertions aren’t enough for well-grounded proof. That’s why in this blog post, we share the benchmark testing results from Alexander Korotkov (CEO of Development, Postgres Professional ) and Sveta Smirnova (Principal Technical Services Engineer, Percona). The comparative research of PostgreSQL 9.6 and MySQL 5.7 performance will be especially valuable for environments with multiple databases.

The idea behind this research is to provide an honest comparison for the two popular RDBMSs. Sveta and Aleksander wanted to test the most recent versions of both MySQL and PostgreSQL with the same tool, under the same challenging workloads and using the same configuration parameters (where possible). However, because both PostgreSQL and MySQL ecosystems evolved independently, with standard testing tools ( pgbench and SysBench ) used for each database, it wasn’t an easy journey.

The task fell to database experts with years of hands-on experience. Sveta has worked as Senior Principal Technical Support Engineer in the Bugs Verification Group of the MySQL Support Group at Oracle for more than eight years, and since 2015 has worked as a Principal Technical Services Engineer at Percona. Alexander Korotkov is a PostgreSQL major contributor, and the developer of a number PostgreSQL features including the CREATE ACCESS METHOD command, generic WAL interface, lockfree Pin/UnpinBuffer, index-based search for regular expressions and much more. So we have a pretty decent cast for this particular play!

Sveta : Dimitri Kravtchuk regularly publishes detailed benchmarks for MySQL, so my main task wasn’tconfirming that MySQL can do millions of queries per second. As our graphs will show, we’ve passed that mark already. As a Support Engineer, I often work with customers who have heterogeneous database environments in their shops, and want to know about the impact of migrating jobs from one database to another. So instead, I found the chance to work with the Postgres Professional company and identify both the strong and weak points of the two databases an excellent opportunity.

We wanted to test both databases on thesame hardware, using thesame tools and tests. We expected to test base functionality, and then work on more detailed comparisons. That way we could compare different real-world use case scenarios and popular options.

Spoiler : We are far from the final results. This is the start of a blog series.

OpenSource Databases on Big Machines, Series 1: “That Was Close…”

PostgreSQL Professional together with Freematiq provided two modern, powerful machines for tests.

Hardware configuration:

Processors:physical = 4, cores = 72, virtual = 144, hyperthreading = yes
Memory: 3.0T
Disk speed: about 3K IOPS
OS: CentOS 7.1.1503
File system: XFS

I also used a smaller Percona machine.

Hardware configuration:

Processors:physical = 2, cores = 12, virtual = 24, hyperthreading = yes
Memory: 251.9G
Disk speed: about 33K IOPS
OS: Ubuntu 14.04.5 LTS
File system: EXT4

Note that machines with smaller numbers of CPU cores and faster disks are more common for MySQL installations than machines with larger numbers of cores.

The first thing we needed to agree on is which tool to use. A fair comparison only makes sense if the workloads are as close as possible.

The standard PostgreSQL tool for performance tests is pgbench , while for MySQL it’s SysBench . SysBench supports multiple database drivers and scriptable tests in the Lua programming language, so we decided to use this tool for both databases.

The initial plan was to convert pgbench tests into SysBench Lua syntax, and then run standard tests on both databases. After initial results, we modified our tests to better examine specific MySQL and PostgreSQL features.

I converted pgbench tests into SysBench syntax, and put the tests into an open-database-bench GitHub repository.

And then we both faced difficulties.

As I wrote already, I also ran the tests on a Percona machine. For this converted test, the results were almost identical:

Percona machine:

OLTPtest statistics: transactions:1000000 (28727.81 persec.) read/write requests:5000000 (143639.05 persec.) otheroperations:2000000 (57455.62 persec.)

Freematiq machine:

OLTPtest statistics: transactions:1000000 (29784.74 persec.) read/write requests:5000000 (148923.71 persec.) otheroperations:2000000 (59569.49 persec.)

I started investigating. The only place where the Percona machine was better than Freematiq’s was disk speed. So I started running thepgbench read-only test, which was identical to SysBench’s point select test with full dataset in memory. But this time SysBench used 50% of available CPU resources:

PIDUSERPRNIVIRTRESSHR S%CPU %MEMTIME+ COMMAND 4585 smirnova2000,157t 0,041t9596 S72261,412:27.16 mysqld 8745 smirnova200 1266212 6291481824 S71260,09:22.78 sysbench

Alexander, in turn, had issues with SysBench, which could not create ahigh load on PostgreSQL when prepared statements were used:

93087 korotkov200 9289440 3,718g2964 S 242,60,10:32.82 sysbench 93161 korotkov200 32,904g8161280208 S4,00,00:00.47 postgres 93116 korotkov200 32,904g8082879424 S3,60,00:00.46 postgres 93118 korotkov200 32,904g8042479020 S3,60,00:00.47 postgres 93121 korotkov200 32,904g8072079312 S3,60,00:00.47 postgres 93128 korotkov200 32,904g7793676536 S3,60,00:00.46 postgres 93130 korotkov200 32,904g8160480204 S3,60,00:00.47 postgres 93146 korotkov200 32,904g8111279704 S3,60,00:00.46 postgres

We contacted SysBench author Alexey Kopytov, and he fixed MySQL issue. The solution is:

Use SysBench with options -- percentile = 0 -- max - requests = 0 (reasonable CPU usage) Use concurrency_kit branch (better concurrency and Lua processing) Rewrite Lua scripts to support prepared statements (pull request:

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

本文标题:Millions of Queries per Second: PostgreSQL and MySQL’s Peaceful Battle at Today ...

技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(18)