Tuesday, May 14, 2024

The Insert Benchmark: MariaDB, MySQL, new small server, cached workload, some concurrency

This post has results for the Insert Benchmark on a small server with a cached workload. The goal is to compare MariaDB and MySQL and see whether they get better or worse over time. The results here are from the newest small servers in my test cluster -- an ASUS PN53.

This work was done by Small Datum LLC and sponsored by the MariaDB Foundation.

The workload here has low concurrency (1 or 4 clients) and the database is cached. The results might be different when the workload is IO-bound or has more concurrency. Results were recently shared from tests run on an older small server. 

tl;dr

  • Modern MariaDB (11.4.1) is faster than modern MySQL (8.0.36) on all benchmark steps. MariaDB is almost 2X faster in the worst case for MySQL.
  • MariaDB 11.4.1 compared to 10.2.44 is up to 11% slower on the intiial load (l.i0), up to 26% faster on the write heavy steps (l.i1, l.i2) and up to 9% slower on the read-write steps (qr*, qp*). It has done a great job at avoiding regressions.
  • There are large regressions from MySQL 5.6.51 to 5.7.44 and then again from 5.7.44 to 8.0.36. In the worst case 5.6.51 is almost 2X faster than 8.0.36 and in most cases 8.0.36 is 15% to 30% slower than 5.6.51. 
Build + Configuration

This report has results for InnoDB from:
  • MySQL - versions 5.6.51, 5.7.44 and 8.0.36
  • MariaDB - versions 10.2.44, 10.3.39, 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.4.1. Versions 10.2, 10.3, 10.4, 10.5, 10.6 and 10.11 are the most recent LTS releases and 11.4 will be the next LTS release.
All of the my.cnf files are here. I tried to use similar configurations across releases, but isn't always possible. And even when it was possible I favor setting fewer entries especially for options where the default value changes between releases. The my.cnf files for MariaDB and MySQL can be split into two groups:
  • my.cnf.cz11a_c8r32 and my.cnf.cz11b_c8r32
    • these use innodb_purge_threads=4
    • the cz11a[pur1]_c8r32 versions use innodb_flush_method =O_DIRECT_NO_FSYNC while the cz11b[pur1]_c8r32 versions use =O_DIRECT (or its equivalent). All DBMS versions support cz11a_c8r32 except for MariaDB 11.4.1 which removed support for O_DIRECT_NO_FSYNC and uses the equivalent of O_DIRECT.
  • my.cnf.cz11apur1_c8r32 and my.cnf.cz11bpur1_c8r32
    • these use innodb_purge_threads=1 because on some of the older small servers, performance is worse with =4
    • see above for the discussion of cz11a vs cz11b
My previous post has more details on the challenges of trying to use similar my.cnf files across MySQL and MariaDB.

The Benchmark

The benchmark is run with 1 client and then 4 clients, a cached workload and a table per client. It is explained here.

The test server was named v7 here and is an ASUS PN53. It has 8 cores, 32G RAM, Ubuntu 22.04 and XFS using 1 m.2 device.

The benchmark steps are:

  • l.i0
    • insert X million rows per table into Y tables in PK order. Each table has a PK index but no secondary indexes. There is one connection per client. With 1 client the values of X and Y are 50M and 1. While with 4 clients the values are X=12M and Y=4.
  • l.x
    • create 3 secondary indexes per table. There is one connection per client.
  • l.i1
    • use 2 connections/client. One inserts 40M rows and the other does deletes at the same rate as the inserts. Each transaction modifies 50 rows (big transactions). This step is run for a fixed number of inserts, so the run time varies depending on the insert rate.
  • l.i2
    • like l.i1 but each transaction modifies 5 rows (small transactions) and 10M rows are inserted and deleted.
    • Wait for X seconds after the step finishes to reduce variance during the read-write benchmark steps that follow. The value of X is a function of the table size.
  • qr100
    • use 3 connections/client. One does range queries and performance is reported for this. The second does does 100 inserts/s and the third does 100 deletes/s. The second and third are less busy than the first. The range queries use covering secondary indexes. This step is run for 1800 seconds. If the target insert rate is not sustained then that is considered to be an SLA failure. If the target insert rate is sustained then the step does the same number of inserts for all systems tested.
  • qp100
    • like qr100 except uses point queries on the PK index
  • qr500
    • like qr100 but the insert and delete rates are increased from 100/s to 500/s
  • qp500
    • like qp100 but the insert and delete rates are increased from 100/s to 500/s
  • qr1000
    • like qr100 but the insert and delete rates are increased from 100/s to 1000/s
  • qp1000
    • like qp100 but the insert and delete rates are increased from 100/s to 1000/s
Results

The performance reports are here for:
The summary in each performance report has 3 tables. The first shows absolute throughput by DBMS tested X benchmark step. The second has throughput relative to the version from the first row of the table. The third shows the background insert rate for benchmark steps with background inserts and all systems sustained the target rates. The second table makes it easy to see how performance changes over time. The third table makes it easy to see which DBMS+configs failed to meet the SLA.

Below I use relative QPS to explain how performance changes. It is: (QPS for $me / QPS for $base) where $me is my version and $base is the version of the base case. When relative QPS is > 1.0 then performance improved over time. When it is < 1.0 then there are regressions. The Q in relative QPS measures: 
  • insert/s for l.i0, l.i1, l.i2
  • indexed rows/s for l.x
  • range queries/s for qr100, qr500, qr1000
  • point queries/s for qp100, qp500, qp1000
Below I use colors to highlight the relative QPS values with red for <= 0.95, green for >= 1.05 and grey for values between 0.95 and 1.05.

Results: MariaDB vs MySQL

MariaDB is much better at avoiding regressions than MySQL and the result over time means that MariaDB is now faster than MySQL on all of the benchmark steps. The results are similar between innodb_purge_threads =1 and =4 so I focus on =4.

In both cases (1 client, 4 clients) the base case is MariaDB 10.11.7 with the cz11a_c8r32 config (ma101107_rel.cz11a_c8r32). It is compared with:
  • MariaDB 11.4.1 with the cz11b_c8r32 config (ma110401_rel.cz11b_c8r32)
  • MySQL 8.0.36 with the cz11a_c8r32 config (my8036_rel.cz11a_c8r32)
From the summary for 1 client the relative throughput per benchmark step is:
  • l.i0
    • relative QPS is 1.02 in MariaDB 11.4.1
    • relative QPS is 0.62 in MySQL 8.0.36
  • l.x - I ignore this for now
  • l.i1, l.i2
    • relative QPS is 0.92, 0.97 in MariaDB 11.4.1
    • relative QPS is 0.86, 0.78 in MySQL 8.0.36
  • qr100, qr500, qr1000
    • relative QPS is 1.00, 1.02, 1.01 in MariaDB 11.4.1
    • relative QPS is 0.98, 1.00, 0.99 in MySQL 8.0.36
  • qp100, qp500, qp1000
    • relative QPS is 1.01, 1.00, 1.01 in MariaDB 11.4.1
    • relative QPS is 0.84, 0.84, 0.85 in MySQL 8.0.36
From the summary for 4 clients the relative throughput per benchmark step is:
  • l.i0
    • relative QPS is 1.00 in MariaDB 11.4.1
    • relative QPS is 0.80 in MySQL 8.0.36
  • l.x - I ignore this for now
  • l.i1, l.i2
    • relative QPS is 0.83, 0.97 in MariaDB 11.4.1
    • relative QPS is 0.50, 0.52 in MySQL 8.0.36
  • qr100, qr500, qr1000
    • relative QPS is 1.00, 0.99, 1.00 in MariaDB 11.4.1
    • relative QPS is 0.98, 0.98, 0.98 in MySQL 8.0.36
  • qp100, qp500, qp1000
    • relative QPS is 1.00, 1.00, 1.00 in MariaDB 11.4.1
    • relative QPS is 0.85, 0.84, 0.85 in MySQL 8.0.36
Results: MariaDB

MariaDB 11.4.1 compared to 10.2.44 is up to 11% slower on the intiial load (l.i0), up to 26% faster on the write heavy steps (l.i1, l.i2) and up to 9% slower on the read-write steps (qr*, qp*). It has done a great job at avoiding regressions.

The results are similar between innodb_purge_threads =1 and =4 so I focus on =4.

In both cases (1 client, 4 clients) the base case is MariaDB 10.2.44 with the cz11a_c8r32 config (ma100244_rel.cz11a_c8r32). It is compared with:
  • MariaDB 10.11.7 with the cz11a_c8r32 config (ma101107_rel.cz11a_c8r32)
  • MariaDB 11.4.1 with the cz11b_c8r32 config (ma110401_rel.cz11b_c8r32)
From the summary for 1 client the relative throughput per benchmark step is:
  • l.i0
    • relative QPS is 0.93 in MariaDB 10.11.7
    • relative QPS is 0.95 in MariaDB 11.4.1
  • l.x - I ignore this for now
  • l.i1, l.i2
    • relative QPS is 1.13, 1.26 in MariaDB 10.11.7
    • relative QPS is 1.05, 1.22 in MariaDB 11.4.1
  • qr100, qr500, qr1000
    • relative QPS is 0.92, 0.91, 0.93 in MariaDB 10.11.7
    • relative QPS is 0.92, 0.93, 0.93 in MariaDB 11.4.1
  • qp100, qp500, qp1000
    • relative QPS is 0.93, 0.94, 0.94 in MariaDB 10.11.7
    • relative QPS is 0.93, 0.94, 0.95 in MariaDB 11.4.1
From the summary for 4 clients the relative throughput per benchmark step is:
  • l.i0
    • relative QPS is 0.89 in MariaDB 10.11.7
    • relative QPS is 0.89 in MariaDB 11.4.1
  • l.x - I ignore this for now
  • l.i1, l.i2
    • relative QPS is 1.32, 1.22 in MariaDB 10.11.7
    • relative QPS is 1.10, 1.18 in MariaDB 11.4.1
  • qr100, qr500, qr1000
    • relative QPS is 0.94, 0.98, 0.99 in MariaDB 10.11.7
    • relative QPS is 0.94, 0.97, 0.99 in MariaDB 11.4.1
  • qp100, qp500, qp1000
    • relative QPS is 0.93, 0.96, 0.98 in MariaDB 10.11.7
    • relative QPS is 0.93, 0.96, 0.97 in MariaDB 11.4.1
Results: MySQL

There are large regressions from MySQL 5.6.51 to 5.7.44 and then again from 5.7.44 to 8.0.36. In the worst case 5.6.51 is almost 2X faster than 8.0.36 and in most cases 8.0.36 is 15% to 30% slower than 5.6.51. The results are similar between innodb_purge_threads =1 and =4 so I focus on =4.

In both cases (1 client, 4 clients) the base case is MySQL 5.6.51 with the cz11a_c8r32 config (my5651_rel.cz11a_c8r32). It is compared with:
  • MySQL 5.7.44 with the cz11a_c8r32 config (my5744_rel.cz11a_c8r32)
  • MySQL 8.0.36 with the cz11a_c8r32 config (my8036_rel.cz11a_c8r32)
From the summary for 1 client the relative throughput per benchmark step is:
  • l.i0
    • relative QPS is 0.86 in MySQL 5.7.44
    • relative QPS is 0.56 in MySQL 8.0.36
  • l.x - I ignore this for now
  • l.i1, l.i2
    • relative QPS is 1.06, 1.00 in MySQL 5.7.44
    • relative QPS is 0.98, 0.84 in MySQL 8.0.36
  • qr100, qr500, qr1000
    • relative QPS is 0.84, 0.85, 0.83 in MySQL 5.7.44
    • relative QPS is 0.75, 0.76, 0.75 in MySQL 8.0.36
  • qp100, qp500, qp1000
    • relative QPS is 0.89, 0.87, 0.87 in MySQL 5.7.44
    • relative QPS is 0.71, 0.70, 0.70 in MySQL 8.0.36
From the summary for 4 clients the relative throughput per benchmark step is:
  • l.i0
    • relative QPS is 1.05 in MySQL 5.7.44
    • relative QPS is 0.79 in MySQL 8.0.36
  • l.x - I ignore this for now
  • l.i1, l.i2
    • relative QPS is 1.30, 1.31 in MySQL 5.7.44
    • relative QPS is 0.80, 0.77 in MySQL 8.0.36
  • qr100, qr500, qr1000
    • relative QPS is 0.85, 0.86, 0.86 in MySQL 5.7.44
    • relative QPS is 0.76, 0.76, 0.77 in MySQL 8.0.36
  • qp100, qp500, qp1000
    • relative QPS is 0.87, 0.87, 0.87 in MySQL 5.7.44
    • relative QPS is 0.69, 0.69, 0.70 in MySQL 8.0.36


Posted by at
Labels: , ,

2 comments:

  1. AnonymousMay 23, 2024 at 8:46 AM

    I think you should use the latest MySQL 8.0.37 to compare

    ReplyDelete
    Replies
    1. Mark CallaghanMay 23, 2024 at 9:56 AM

      I will eventually get to that, as in within a few months.

      But otherwise, why? What changes in 8.0.37 do you think are significant?

      Delete

Subscribe to: Post Comments (Atom)