This has results for HammerDB tproc-c on a small server using MySQL and Postgres. I am new to HammerDB and still figuring out how to explain and present results so I will keep this simple and just share graphs without explaining the results.
The comparison might favor Postgres for the IO-bound workloads because I used smaller buffer pools than normal to avoid OOM. I have to do this because RSS for the HammerDB client grows over time as it buffers more response time stats. And while I used buffered IO for Postgres, I use O_DIRECT for InnoDB. So Postgres might have avoided some read IO thanks to the OS page cache while InnoDB did not.
tl;dr for MySQL
- With vu=40 MySQL 8.4.8 uses about 2X more CPU per transaction and does more than 2X more context switches per transaction compared to Postgres 18.1. I will get CPU profiles soon.
- Modern MySQL brings us great improvements to concurrency and too many new CPU overheads
- MySQL 5.6 and 8.4 have similar throughput at the lowest concurrency (vu=10)
- MySQl 8.4 is a lot faster than 5.6 at the highest concurrency (vu=40)
tl;dr for Postgres
- Modern Postgres has regressions relative to old Postgres
- The regressions increase with the warehouse count, at wh=4000 the NOPM drops between 3% and 13% depending on the virtual user count (vu).
tl;dr for Postgres vs MySQL
- Postgres and MySQL have similar throughput for the largest warehouse count (wh=4000)
- Otherwise Postgres gets between 1.4X and 2X more throughput (NOPM)
Builds, configuration and hardware
I compiled Postgres versions from source: 12.22, 13.23, 14.20, 15.15, 16.11, 17.7 and 18.1.
I compiled MySQL versions from source: 5.6.51, 5.7.44, 8.0.45, 8.4.8, 9.4.0 and 9.6.0.
I used a 48-core server from Hetzner
- an ax162s with an AMD EPYC 9454P 48-Core Processor with SMT disabled
- 2 Intel D7-P5520 NVMe storage devices with RAID 1 (3.8T each) using ext4
- 128G RAM
- Ubuntu 22.04 running the non-HWE kernel (5.5.0-118-generic)
Postgres configuration files:
- prior to 9.6 the config file is named my.cnf.cz12a50g_c32r128 (z12a50g_c32r128 or z12a50g) and is here for versions 5.6, 5.7, 8.0 and 8.4
- for 9.6 it is named my.cnf.cz13a50g_c32r128 (z13a50g_c32r128 or z13a50g) and is here
For both Postgres and MySQL fsync on commit is disabled to avoid turning this into an fsync benchmark. The server has 2 SSDs with SW RAID and low fsync latency.
Benchmark
The benchmark is tproc-c from HammerDB. The tproc-c benchmark is derived from TPC-C.
The benchmark was run for several workloads:
The benchmark was run for several workloads:
- vu=10, wh=1000 - 10 virtual users, 1000 warehouses
- vu=20, wh=1000 - 20 virtual users, 1000 warehouses
- vu=40, wh=1000 - 40 virtual users, 1000 warehouses
- vu=10, wh=2000 - 10 virtual users, 2000 warehouses
- vu=20, wh=2000 - 20 virtual users, 2000 warehouses
- vu=40, wh=2000 - 40 virtual users, 2000 warehouses
- vu=10, wh=4000 - 10 virtual users, 4000 warehouses
- vu=20, wh=4000 - 20 virtual users, 4000 warehouses
- vu=40, wh=4000 - 40 virtual users, 4000 warehouses
The wh=1000 workloads are less heavy on IO. The wh=4000 workloads are more heavy on IO.
The benchmark for Postgres is run by a variant of this script which depends on scripts here. The MySQL scripts are similar.
- stored procedures are enabled
- partitioning is used because the warehouse count is >= 1000
- a 5 minute rampup is used
- then performance is measured for 60 minutes
Basic metrics: iostat
I am still improving my helper scripts to report various performance metrics. The table here has average values from iostat during the benchmark run phase for MySQL 8.4.8 and Postgres 18.1. For these configurations the NOPM values for Postgres and MySQL were similar so I won't present normalized values (average value / NOPM) and NOPM is throughput.
- average wMB/s increases with the warehouse count for Postgres but not for MySQL
- r/s increases with the warehouse count for Postgres and MySQL
iostat metrics
* r/s = average rate of reads/s from storage
* wMB/s = average MB/s written to storage
my8408
r/s wMB/s
22833.0 906.2 vu=40, wh=1000
63079.8 1428.5 vu=40, wh=2000
82282.3 1398.2 vu=40, wh=4000
pg181
r/s wMB/s
30394.9 1261.9 vu=40, wh=1000
59770.4 1267.8 vu=40, wh=2000
78052.3 1272.9 vu=40, wh=4000
Basic metrics: vmstat
I am still improving my helper scripts to report various performance metrics. The table here has average values from vmstat during the benchmark run phase for MySQL 8.4.8 and Postgres 18.1. For these configurations the NOPM values for Postgres and MySQL were similar so I won't present normalized values (average value / NOPM).
- CPU utilization is almost 2X larger for MySQL
- Context switch rates are more than 2X larger for MySQL
- In the future I hope to learn why MySQL uses almost 2X more CPU per transaction and has more than 2X more context switches per transaction relative to Postgres
vmstat metrics
* cs - average value for cs (context switches/s)
* us - average value for us (user CPU)
* sy - average value for sy (system CPU)
* id - average value for id (idle)
* wa - average value for wa (waiting for IO)
* us+sy - sum of us and sy
my8408
cs us sy id wa us+sy
455648 61.9 8.2 24.2 5.7 70.1 vu=40, wh=1000
484955 50.4 9.2 19.5 21.0 59.6 vu=40, wh=2000
487410 39.5 8.4 19.4 32.6 48.0 vu=40, wh=4000
pg181
cs us sy id wa us+sy
127486 23.5 10.1 63.3 3.0 33.6 vu=40, wh=1000
166257 17.2 11.1 62.5 9.1 28.3 vu=40, wh=2000
203578 13.9 11.3 59.2 15.6 25.2 vu=40, wh=4000
Results
My analysis at this point is simple -- I only consider average throughput. Eventually I will examine throughput over time and efficiency (CPU and IO).
On the charts that follow y-axis does not start at 0 to improve readability at the risk of overstating the differences. The y-axis shows relative throughput. There might be a regression when the relative throughput is less than 1.0. There might be an improvement when it is > 1.0. The relative throughput is:
(NOPM for some-version / NOPM for base-version)
I provide three charts below:
- only MySQL - base-version is MySQL 5.6.51
- only Postgres - base-version is Postgres 12.22
- Postgres vs MySQL - base-version is Postgres 18.1, some-version is MySQL 8.4.8
Results: MySQL 5.6 to 9.6
Legend:
- my5651.z12a is MySQL 5.6.51 with the z12a50g config
- my5744.z12a is MySQL 5.7.44 with the z12a50g config
- my8045.z12a is MySQL 8.0.45 with the z12a50g config
- my8408.z12a is MySQL 8.4.8 with the z12a50g config
- my9500.z13a is MySQL 9.6.0 with the z13a50g config
Summary
- At the lowest concurrency (vu=10) MySQL 8.4.8 has similar throughput as 5.6.51 because CPU regressions in modern MySQL offset the concurrency improvements.
- At the highest concurrency (vu=40) MySQL 8.4.8 is much faster than 5.6.51 and the regressions after 5.7 are small. This matches what I have seen elsewhere -- while modern MySQL suffers from CPU regressions it benefits from concurrency improvements. Imagine if we could get those concurrency improvements without the CPU regressions.
And the absolute NOPM values are here:
Results: Postgres 12 to 18
Legend:
- pg1222 is Postgres 12.22 with the x10a50g config
- pg1323 is Postgres 13.23 with the x10a50g config
- pg1420 is Postgres 14.20 with the x10a50g config
- pg1515 is Postgres 15.15 with the x10a50g config
- pg1611 is Postgres 16.11 with the x10a50g config
- pg177 is Postgres 17.7 with the x10a50g config
- pg181 is Postgres 18.1 with the x10b50g config
Summary
- Modern Postgres has regressions relative to old Postgres
- The regressions increase with the warehouse count, at wh=4000 the NOPM drops between 3% and 13% depending on the virtual user count (vu).
The relative NOPM values are here:
The absolute NOPM values are here:
Results: MySQL vs Postgres
Legend:
- pg181 is Postgres 18.1 with the x10b50g config
- my8408 is MySQL 8.4.8 with the z12a50g config
Summary
- Postgres and MySQL have similar throughput for the largest warehouse count (wh=4000)
- Otherwise Postgres gets between 1.4X and 2X more throughput (NOPM)
The absolute NOPM values are here:
No comments:
Post a Comment