This blog shows a comparison of 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’t confirming 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 the same hardware, using the same 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:
Freematiq machine:
I started investigating. The only place where the Percona machine was better than Freematiq’s was disk speed. So I started running the pgbench 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:
Alexander, in turn, had issues with SysBench, which could not create a high load on PostgreSQL when prepared statements were used:
We contacted SysBench author Alexey Kopytov, and he fixed MySQL issue. The solution is:
Use SysBench with options
(reasonable CPU usage)
Use concurrency_kit branch (better concurrency and Lua processing)
Rewrite Lua scripts to support prepared statements (pull request: https://github.com/akopytov/sysbench/pull/94)
Start both SysBench and mysqld with the jemalloc or tmalloc library pre-loaded
A fix for PostgreSQL is on the way. For now, Alexander converted a standard SysBench test into pgbench format and we stuck with it. Not much new for MySQL, but at least we had a baseline for comparison.
The next difficulty I faced was the default operating system parameters. To make the long story short, I changed them to the recommended ones (described below):
The same parameters were better for PostgreSQL performance as well. Alexander set his machine similarly.
After solving these issues we learned and implemented the following:
We cannot use a single tool (for now)
Alexander wrote a test for pgbench, imitating the standard SysBench tests
We are still not able to write custom tests because we use different tools
But we could use these tests as a baseline. After work done by Alexander, we stuck with the standard SysBench tests. I converted them to use prepared statements, and Alexander converted them into pgbench format.
I should mention that I was not able to get the same results for the Read Only and Point Select tests as Dimitri. They are close, but slightly slower. We need to investigate if this is the result of different hardware, or my lack of performance testing abilities. The results from the Read-Write tests are similar.
Another difference was between the PostgreSQL and MySQL tests. MySQL users normally have many connections. Setting the value of the variable
, and limiting the total number of parallel connections to thousands is not rare nowadays. While not recommended, people use this option even without the thread pool plugin. In real life, most of these connections are sleeping. But there is always a chance they all will used in cases of increased website activity.
For MySQL I tested up to 1024 connections. I used powers of two and multiplies of the number of cores: 1, 2, 4, 8, 16, 32, 36, 64, 72, 128, 144, 256, 512 and 1024 threads.
For Alexander, it was more important to test in smaller steps. He started from one thread and increased by 10 threads, until 250 parallel threads were reached. So you will see a more detailed graph for PostgreSQL, but no results after 250 threads.
Here are our comparison results.
Point SELECTs
pgsql-9.6 is standard PostgreSQL
pgsql-9.6 + pgxact-align is PostgreSQL with this patch (more details can be found in this blog post)
MySQL-5.7 Dimitri is Oracle’s MySQL Server
MySQL-5.7 Sveta is Percona Server 5.7.15
OLTP RO
OLTP RW
Sync commit in PostgreSQL is a feature, similar to
in InnoDB, and async commit is similar to
.
You see that the results are very similar: both databases are developing very fast and work with modern hardware well.
MySQL results which show 1024 threads for reference.
Point SELECT and OLTP RO
OLTP RW with innodb_flush_log_at_trx_commit set to 1 and 2
After receiving these results, we did a few feature-specific tests that will be covered in separate blog posts.
More Information
MySQL Options for OLTP RO and Point SELECT tests:
MySQL Options for OLTP RW:
MySQL SysBench parameters.
PostgreSQL pgbench parameters:
Features in MySQL 5.7 that significantly improved performance:
InnoDB: transaction list optimization
https://blogs.oracle.com/mysqlinnodb/entry/transaction_life_cycle_improvements_in
WL #6047
InnoDB: Reduce lock_sys_t::mutex contention
WL #6899
InnoDB: fix index->lock contention
WL #6326
InnoDB: faster and parallel flushing
Multiple page cleaner threads: WL #6642
Reduced number of pages which needs to be flushed: WL #7047
Improved adaptive flushing: WL #7868
MDL (Meta-Data Lock) scalability
Remove THR_LOCK::mutex for InnoDB: Wl #6671
Partitioned LOCK_grant
Number of partitions is constant
Thread ID used to assign partition
Wl #8355
Bug #72829
Lock-free MDL lock acquisition for DML
WL #7306
WL #7305
Anastasia: The initial findings of this research were announced at Percona Live Amsterdam 2016. More findings were added to the second version of the same talk given at Moscow HighLoad++ 2016. Hopefully the third iteration of this talk will be available at Percona Live Open Source Database Conference 2017 in Santa Clara. Stay tuned: the Percona Live Committee is working on the program!
Save
Save
Save
Save
Save
Save
Save
Save
Save
Save
Save
Save
Save
Save
Save
Save
Save
Save