Showing posts with label tproc-c. Show all posts
Showing posts with label tproc-c. Show all posts

Friday, June 12, 2026

HammerDB tproc-c on a large server, Postgres 14 to 19 beta1

This has results for HammerDB tproc-c on a large 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.

tl;dr

    • There are small regressions in versions 16, 17 and 18
    • NOPM usually improves a small amount in 19 beta1 relative to 18
    Builds, configuration and hardware

    I compiled Postgres versions from source: 14.22, 14.23, 15.17, 15.18, 16.13, 16.14, 17.9, 17.10, 18.0, 18.1, 18.2, 18.3, 18.4 and 19 beta1.

    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 24.04
    Postgres configuration files:
    • prior to version 18 the config file is named conf.diff.cx10a50g_c32r128 (x10a_c32r128) and is here for versions 14, 15, 16 and 17.
    • for Postgres 18 and 19 I used conf.diff.cx10b_c32r128 (x10b_c32r128) with io_method=sync to be similar to the config used for versions 14 through 17.
    Benchmark

    The benchmark is tproc-c from HammerDB. The tproc-c benchmark is derived from TPC-C.

    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.
    • 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
    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)

    The base version is Postgres 14.22.

    A spreadsheet with absolute and relative values for NOPM is here.

    Results: vu=10, wh=1000

    Summary:

    • There are small regressions in versions 16, 17 and 18 while NOPM improves is 19 beta1

    Results: vu=20, wh=1000

    Summary:

    • There are small regressions in versions 16, 17 and 18 while NOPM improves is 19 beta1

    Results: vu=40, wh=1000

    Summary:

    • There are small regressions in versions 17 and 18 while NOPM improves is 19 beta1

    Results: vu=10, wh=2000

    Summary:

    • There are small regressions in version 18 while NOPM improves is 19 beta1

    Results: vu=20, wh=2000

    Summary:

    • There are small regressions in versions 16, 17 and 18 while NOPM improves is 19 beta1

    Results: vu=40, wh=2000

    Summary:

    • There are small regressions in versions 16, 17 and 18 while NOPM improves is 19 beta1
    • There is no result for 18.1 because of a bug in my test scripts

    Results: vu=10, wh=4000

    Summary:

    • There are small regressions in versions 16, 17 and 18 while NOPM improves is 19 beta1

    Results: vu=20, wh=4000

    Summary:

    • There are small regressions in versions 16, 17 and 18

    Results: vu=40, wh=4000

    Summary:

    • There are small regressions in versions 16, 17 and 18 while NOPM improves is 19 beta1


    at No comments:
    Labels: , , ,

    Monday, March 16, 2026

    CPU efficiency for MariaDB, MySQL and Postgres on TPROC-C with a small server

    I started to use TPROC-C from HammerDB to test MariaDB, MySQL and Postgres and published results for MySQL and Postgres on small and large servers. This post provides more detail on CPU overheads for MariaDB, MySQL and Postgres on a small server.

    tl;dr

    • Postgres get the most throughput and the difference is large.
    • MariaDB gets more throughput than MySQL
    • Throughput improves for MariaDB and MySQL but not for Postgres when stored procedures are enabled. It is possible that the stored procedure support in MariaDB and MySQL is more CPU efficient than in Postgres. The HammerDB author explained that HammerDB uses server-side functions with Postgres when stored procs are disabled. That might explain why there isn't much of a benefit.
    • Postgres uses ~2X to ~4X more CPU for background tasks than InnoDB but it is doing between 1.5X and 3X more writes so were I to normalize that CPU overhead (from vacuum) it might be similar to MySQL and MariaDB. Regardless, the total amount of CPU for background tasks is not significant relative to other CPU consumers.
    Builds, configuration and hardware

    I compiled everything from source: MariaDB 11.8.6, MySQL 8.4.8 and Postgres 18.2.

    The server is an ASUS ExpertCenter PN53 with an AMD Ryzen 7 7735HS CPU, 8 cores, SMT disabled, and 32G of RAM. Storage is one NVMe device for the database using ext-4 with discard enabled. The OS is Ubuntu 24.04. More details on it are here.

    For Postgres 18 the config file is named conf.diff.cx10b_c8r32 and adds io_mod='sync' which matches behavior in earlier Postgres versions.

    For MySQL the config file is named my.cnf.cz12a_c8r32.

    For MariaDB the config file is named my.cnf.cz12b_c8r32.

    For all DBMS fsync on commit is disabled to avoid turning this into an fsync benchmark. The server has an SSD with high fsync latency.

    Benchmark

    The benchmark is tproc-c from HammerDB. The tproc-c benchmark is derived from TPC-C.

    The benchmark was run for one workload, the working set is cached and there is only one user:
    • vu=1, w=100 - 1 virtual user, 100 warehouses
    The test was repeated with stored procedure support in HammerDB enabled and then disabled. For my previous results it was always enabled. I did this to understand the impact of stored procedures. While they are great for workloads with much concurrency because they reduce lock-hold durations, the workload here did not have much concurrency. That helps me understand the CPU efficiency of stored procedures.

    The benchmark for Postgres is run by this script which depends on scripts here. The MySQL scripts are similar.
    • stored procedures are enabled
    • partitioning is used for when the warehouse count is >= 1000
    • a 5 minute rampup is used
    • then performance is measured for 120 minutes
    Results: NOPM

    The numbers in the table below are the NOPM (throughput) for TPROC-C.

    Summary
    • Postgres sustains the most throughput with and without stored procedures
    • MariaDB sustains more throughput than MySQL
    • Stored procedures help MariaDB and MySQL, but do not improve Postgres throughput
    Legend:
    * sp0 - stored procedures disabled
    * sp1 - stored procedures enabled

    sp0     sp1
    11975   19281   MariaDB 11.8.6
     9400   16874   MySQL 8.4.8
    33261   33679   Postgres 18.2

    Results: vmstat

    The following is computed from a sample of ~1000 lines of vmstat output collected from the middle of the benchmark run. 
    • The ratio of us to sy is almost 2X larger in Postgres than in MariaDB and MySQL with stored procedures disabled. But the ratios are similar with stored procedures enabled.
    • The context switch rate is about 5X larger in MariaDB and MySQL vs Postgres with stored procedures disabled before normalizing by thoughput, with normalization the difference would be even larger. But the difference is smaller with stored procedures enabled.
    • Postgres has better throughput because MariaDB and MySQL use more CPU per NOPM. The diference is larger with stored procedures disabled. Perhaps the stored prcoedure evaluator in MariaDB and MySQL is more efficient than in Postgres.
    Legend:
    * r - average value for the r column, runnable tasks
    * cs - average value for the cs column, context switches/s
    * us, sy - average value for the us and sy columns, user and system CPU utilization/s
    * us+sy - average value for the sum of us and sy
    * cpuPer - ((us+sy) / NOPM) * 1000, smaller is better

    --- sp0
    r       cs      us      sy      us+sy   cpuPer
    1.112   54786   10.0    3.1     13.2    1.102   MariaDB 11.8.6
    1.130   65413   10.8    2.9     13.7    1.457   MySQL 8.4.8
    1.206   11266   12.2    1.9     14.1    0.423   Postgres 18.2

    --- sp1
    r       cs      us      sy      us+sy   cpuPer
    1.079   11739   12.0    1.2     13.1    0.679   MariaDB 11.8.6
    1.043   14698   12.0    1.0     13.0    0.770   MySQL 8.4.8
    1.107    9776   12.4    1.4     13.8    0.409   Postgres 18.2

    Results: flamegraphs with stored procedures

    The flamegraphs are here.'

    The following tables summarize CPU time based on the percentage of samples that can be mapped to various tasks and processes. Note that these are absolute values. So both MySQL and Postgres have similar distributions of CPU time per area even when Postgres gets 2X or 3X more throughput.

    Summary
    • the CPU distributions by area are mostly similar for MariaDB, MySQL and Postgres
    • Postgres uses 2X to 4X more CPU for background work (vacuum)
    Legend
    * client - time in the HammerDB benchmark client
    * swap - time in kernel swap code
    * db-fg - time running statements in the DBMS for the client
    * db-bg - time doing background work in the DBMS

    - Total
            MariaDB MySQL   Postgres
    client   4.62    5.70    6.82
    swap     5.15    7.09    5.55
    db-fg   86.83   83.89   79.43
    db-bg    1.43    3.00   ~6.x

    - Limited to db-fg, excludes Postgres because the data is messy
            MariaDB MySQL
    update  22.39   21.49
    insert   6.17    5.82
    select  23.43   18.04
    commit   ~4.0    ~5.0
    parse   ~10.0   ~10.0

    Results: flamegraphs without stored procedures

    The flamegraphs are here.

    Summary
    • the CPU distributions by area are mostly similar for MariaDB and MySQL
    • Postgres uses 2X to 4X more CPU for background work (vacuum)
    Legend
    * client - time in the HammerDB benchmark client
    * swap - time in kernel swap code
    * db-fg - time running statements in the DBMS for the client
    * db-bg - time doing background work in the DBMS

    - Total
            MariaDB MySQL   Postgres
    client  14.29   11.92    7.52
    swap    13.18   15.58    5.80
    db-fg   70.39   70.14   77.24
    db-bg   ~1.0    ~2.0     6.55

    - Limited to db-fg, excludes Postgres because the data is messy
            MariaDB MySQL
    update  14.19   12.04
    insert   4.29    3.57
    select  18.14   11.96
    prepare   NA     6.73
    commit  ~2.0     2.64
    parse    8.86    9.73
    network 15.47   13.73

    For MySQL parse, 2.5% was from pfs_digest_end_vc and children.












    at 2 comments:
    Labels: , , , ,

    Sunday, February 15, 2026

    HammerDB tproc-c on a large server, Postgres and MySQL

    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 v18 the config file is named conf.diff.cx10a50g_c32r128 (x10a_c32r128) and is here for versions 12, 13, 14, 15, 16 and 17.
    • for Postgres 18 I used conf.diff.cx10b_c32r128 (x10b_c32r128) with io_method=sync to be similar to the config used for versions 12 through 17.
    MySQL 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:
    • 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:

    my5651my5744my8045my8408my9600
    vu=10, wh=1000163059183268156039155194151748
    vu=20, wh=1000210506321670283282281038279269
    vu=40, wh=1000216677454743439589435095433618
    vu=10, wh=2000107492130229111798110161108386
    vu=20, wh=2000155398225068193658190717189847
    vu=40, wh=2000178278302723297236307504293217
    vu=10, wh=400081242103406894148931688458
    vu=20, wh=4000131241179112155134152998152301
    vu=40, wh=4000146809228554234922229511230557

    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:

    pg1222pg1323pg1420pg1515pg1611pg177pg181
    vu=10, wh=10001.0001.0001.0541.0421.0041.0100.968
    vu=20, wh=10001.0001.0351.0371.0281.0281.0010.997
    vu=40, wh=10001.0001.0400.9881.0001.0270.9980.970
    vu=10, wh=20001.0001.0261.0591.0751.0681.0811.029
    vu=20, wh=20001.0001.0221.0461.0430.9790.9720.934
    vu=40, wh=20001.0001.0141.0321.0360.9791.0100.947
    vu=10, wh=40001.0001.0271.0321.0350.9930.9980.974
    vu=20, wh=40001.0001.0051.0491.0480.9400.9270.876
    vu=40, wh=40001.0000.9911.0190.9831.0010.9790.937

    The absolute NOPM values are here:

    pg1222pg1323pg1420pg1515pg1611pg177pg181
    vu=10, wh=1000353077353048372015367933354513356469341688
    vu=20, wh=1000423565438456439398435454435288423986422397
    vu=40, wh=1000445114462851439728445144457110444364431648
    vu=10, wh=2000223048228914236231239868238117241185229549
    vu=20, wh=2000314380321380328688328044307728305452293627
    vu=40, wh=2000320347324769330444331896313553323454303403
    vu=10, wh=4000162054166461167320167761160962161716157872
    vu=20, wh=4000244598245804256593256231230037226844214309
    vu=40, wh=4000252931250634257820248584253059247610236986

    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:

    pg181my8408
    vu=10, wh=1000341688155194
    vu=20, wh=1000422397281038
    vu=40, wh=1000431648435095
    vu=10, wh=2000229549110161
    vu=20, wh=2000293627190717
    vu=40, wh=2000303403307504
    vu=10, wh=400015787289316
    vu=20, wh=4000214309152998
    vu=40, wh=4000236986229511









    at 3 comments:
    Labels: , , , , ,

    Saturday, February 14, 2026

    HammerDB tproc-c on a small server, Postgres and MySQL

    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.

    tl;dr

    • Modern Postgres is faster than old Postgres
    • Modern MySQL has large perf regressions relative to old MySQL, and they are worst at low concurrency for CPU-bound worklads. This is similar to what I see on other benchmarks.
    • Modern Postgres is about 2X faster than MySQL at low concurrency (vu=1) and when the workload isn't IO-bound (w=100). But with some concurrency (vu=6) or with more IO per transaction (w=1000, w=2000) they have similar throughput. Note that partitioning is used at w=1000 and 2000 but not at w=100.

    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.44, 8.4.7, 9.4.0 and 9.5.0.

    The server is an ASUS ExpertCenter PN53 with an AMD Ryzen 7 7735HS CPU, 8 cores, SMT disabled, and 32G of RAM. Storage is one NVMe device for the database using ext-4 with discard enabled. The OS is Ubuntu 24.04. More details on it are here.

    For versions prior to 18, the config file is named conf.diff.cx10a_c8r32 and they are as similar as possible and here for versions 12, 13, 14, 15, 16 and 17.

    For Postgres 18 the config file is named conf.diff.cx10b_c8r32 and adds io_mod='sync' which matches behavior in earlier Postgres versions.

    For MySQL the config files are named my.cnf.cz12a_c8r32 and are here: 5.6.51, 5.7.44, 8.0.4x, 8.4.x, 9.x.0.

    For both Postgres and MySQL fsync on commit is disabled to avoid turning this into an fsync benchmark. The server has an SSD with high 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:
    • vu=1, w=100 - 1 virtual user, 100 warehouses
    • vu=6, w=100 - 6 virtual users, 100 warehouses
    • vu=1, w=1000 - 1 virtual user, 1000 warehouses
    • vu=6, w=1000 - 6 virtual users, 1000 warehouses
    • vu=1, w=2000 - 1 virtual user, 2000 warehouses
    • vu=6, w=2000 - 6 virtual users, 2000 warehouses
    The w=100 workloads are less heavy on IO. The w=1000 and w=2000 workloads are more heavy on IO.

    The benchmark for Postgres is run by this script which depends on scripts here. The MySQL scripts are similar.
    • stored procedures are enabled
    • partitioning is used for when the warehouse count is >= 1000
    • a 5 minute rampup is used
    • then performance is measured for 120 minutes
    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.7

    Results: MySQL 5.6 to 9.5

    Legend:

    • my5651.z12a is MySQL 5.6.51 with the z12a_c8r32 config
    • my5744.z12a is MySQL 5.7.44 with the z12a_c8r32 config
    • my8044.z12a is MySQL 8.0.44 with the z12a_c8r32 config
    • my8407.z12a is MySQL 8.4.7 with the z12a_c8r32 config
    • my9400.z12a is MySQL 9.4.0 with the z12a_c8r32 config
    • my9500.z12a is MySQL 9.5.0 with the z12a_c8r32 config

    Summary

    • Perf regressions in MySQL 8.4 are smaller with vu=6 and wh >= 1000 -- the cases where there is more concurrency (vu=6) and the workload does more IO per transaction (wh=1000 & 2000). Note that partitioning is used at w=1000 and 2000 but not at w=100.
    • Perf regressions in MySQL 8.4 are larger with vu=1 and even more so with wh=100 (low concurrency, less IO per transaction).
    • Performance has mostly been dropping from MySQL 5.6 to 8.4. From other benchmarks the problem is from new CPU overheads at low concurrency.
    • While perf regressions in modern MySQL at high concurrency have been less of a problem on other benchmarks, this server is too small to support high concurrency.

    Results: Postgres 12 to 18

    Legend:

    • pg1222.x10a is Postgres 12.22 with the x10a_c8r32 config
    • pg1323.x10a is Postgres 13.23 with the x10a_c8r32 config
    • pg1420.x10a is Postgres 14.20 with the x10a_c8r32 config
    • pg1515.x10a is Postgres 15.15 with the x10a_c8r32 config
    • pg1611.x10a is Postgres 16.11 with the x10a_c8r32 config
    • pg177.x10a is Postgres 17.7 with the x10a_c8r32 config
    • pg181.x10b is Postgres 18.1 with the x10b_c8r32 config

    Summary

    • Modern Postgres is faster than old Postgres

    Results: MySQL vs Postgres

    Legend:

    • pg181.x10b is Postgres 18.1 with the x10b_c8r32 config
    • my8407.z12a is MySQL 8.4.7 with the z12a_c8r32 config

    Summary

    • MySQL and Postgres have similar throughput for vu=6 at w=1000 and 2000. Note that partitioning is used at w=1000 and 2000 but not at w=100.
    • Otherwise Postgres is 2X faster than MySQL

    at No comments:
    Labels: , , , , ,
    Subscribe to: Posts (Atom)

    CPU-bound sysbench on a large server: Postgres 12 to 19 beta1

    This has results from sysbench on a small server with Postgres versions 12 through 19 beta1. Sysbench is run with high concurrency (40 conne...