Space vs CPU tradeoffs with universal compaction in RocksDB

This post explains the impact of compression for write-heavy workloads with universal compaction in RocksDB. With an LSM you can spend on CPU to save on space. For leveled compaction I normally use no compression for L0, L1 and L2 because compressing them has a small impact on space but costs a lot in CPU and write stalls. My advice for universal compaction is similar -- don't compress everything.

tl;dr

• For fillseq (load in key order) the insert rates are better with less compression, so no compression beats LZ4 and LZ4 beats ZSTD because with stronger compression there are more write stalls. But for overwrite the insert rate is similar regardless of compression for reasons I haven't fully explained.
• There is more CPU used by compaction when there is more compression. The difference in CPU overhead can exceed 3X when comparing no compression (m1.none) and ZSTD for everything (m1.zstd).
• There is less write-amp when there is more compression because with a smaller database there is less to write.

Workload

I used a hacked version of my db_bench helper scripts to run the fillseq and overwrite benchmark steps on a small server (see v5 here, Beelink SER7 with 8 cores, Ryzen7 CPU, 32G RAM, m.2 storage, Ubuntu 22.04 and XFS).

Note that db_bench was configured to generate values that can compress in half, thus the numbers below show that an uncompressed database can be ~2X larger than a compressed database.

The sequence of benchmark steps is below. I look at performance results for fillseq and overwriteandwait. The command lines are here for the m1, 80 and 90 config groups.

• fillseq - load 100M KV pairs in key order
• overwritesome - overwrite 10M KV pairs to fragment the LSM tree
• waitforcompaction - wait for compaction to finish so that all configs start from a similar state for the next benchmark step
• overwriteandwait - overwrite keys as fast as possible for 20 minutes

There were 3 types of configurations that I call m1, 80 and 90 below. They differ in the value used with --universal_compression_size_percent. The configs set it to -1, 80 and 90 for the m1, 80 and 90 configuration types. 

• m1 - all SSTs are either compressed or not compressed (see below)
• 80 - 80% of the SSTs are compressed, the 20% smallest (newest) are not compressed.
• 90 - 90% of the SSTs are compressed, the 90% smallest (newest) are not compressed

Then within each configuration type I try a mix of no, lz4 and zstd compression. The configs descriped with except the largest sorted run get that by setting bottommost_compression. The full set of configs tested are:

• m1.none
• nothing is compressed
• m1.lz4
• uses LZ4 compression for all SSTs
• m1.zstd
• uses ZSTD compression for all SSTs
• m1.lz
• uses LZ4 compression for all SSTs except the largest sorted run which uses ZSTD
• 80.none
• nothing is compressed, just like m1.none
• 80.lz4
• uses LZ4 to compress the largest (oldest) 80% of the SSTs
• 80.zstd
• uses ZSTD for the largest (oldest) 80% of the SSTs
• 90.nnl
• uses LZ4 for the largest sorted run and no compression elsewhere
• 90.nnz
• uses ZSTD for the largest sorted run and no compression elsewhere
• 90.nlz
• uses ZSTD for the largest sorted run, LZ4 for ~90% of the next largest SSTs and then no compression for everything else (the newest, smallest SSTs)

Results

Legend for the tables below:

• config
• name of the tested configuration and the values are explained above
• ips
• the average rate for inserts/s
• stall%
• percentage of time for which there are write stalls. Note that tests are run with fsync on commit disabled which can make write stalls more likely
• w_amp
• write amplification. Without compression the minimum should be 2 (write once to WAL, once to L0 on memtable flush) but with compression it can be less.
• c_wsecs
• wall-clock seconds doing compaction
• c_csecs
• CPU seconds doing compaction. In some cases this is larger than c_wsecs because subcompactions were enabled (set to 2) and one compaction job can use 2 threads.
• sizeGB
• size in GB of the database. For fillseq this is the total database size but for overwriteandwait it is only the size of the largest sorted run (which was stored in L39) because there is too much variance on the total database size with universal compaction for benchmark steps other than fillseq.

Results from the fillseq benchmark step that does inserts in key order. I use green to indicate the best results from each column and red for the worst. All of the results are here.

• The insert rates are better with less compression, so no beats LZ4 and LZ4 beats ZSTD. 
• With more compression there are more write stalls.
• There is more CPU used by compaction when there is more compression. The difference in CPU overhead is larger than 3X when comparing no compression (m1.none) and ZSTD for everything (m1.zstd).
• There is less write-amp when there is more compression because with a smaller database there is less to write.

config	ips	stall%	w_amp	c_wsecs	c_csecs	sizeGB
m1.none	1124117	44.0	2.0	83	71	39.9
m1.lz4	834739	57.8	1.1	114	105	21.9
m1.zstd	401282	78.2	0.9	244	236	18.0
m1.lz	818534	57.8	1.1	116	108	21.9
80.none	995011	50.2	2.0	89	70	39.9
80.lz4	820899	57.8	1.1	114	103	21.9
80.zstd	399171	78.2	0.9	245	236	18.0
90.nnl	1010723	49.3	2.0	92	77	39.9
90.nnz	1010318	49.1	2.0	92	77	39.9
90.nlz	808058	58.5	1.1	118	109	21.9

Results from the overwriteandwait benchmark step. I use green to indicate the best results from each column and red for the worst.

• All configs provide a similar insert rate because there isn't a large difference in write stall percentage between the configs that use more compression and the configs that use less. The benchmark client uses more CPU per request here than for fillseq because key generation here uses a RNG while above it just increments a counter. Were I able to test something that sustains a higher request rate there might be differences here similar to what I see with fillseq.
• There is a large difference in CPU overhead between configs that use less compression and configs that use more. But this CPU overhead is consumed by background threads that do compaction so it doesn't change the insert rate.
• There is less write-amp when there is more compression because with a smaller database there is less to write.

config	ips	stall%	w_amp	c_wsecs	c_csecs	sizeGB
m1.none	307477	0.6	6.9	1880	1232	40.0
m1.lz4	312220	0.1	3.7	1797	1915	21.6
m1.zstd	300634	2.2	3.1	4565	5307	17.8
m1.lz	312476	0.1	3.8	2776	3549	17.8
80.none	307114	0.5	6.9	1864	1219	40.0
80.lz4	308854	0.1	5.9	1733	1620	21.6
80.zstd	310717	0.1	5.6	2451	2656	17.8
90.nnl	307881	0.4	6.9	2061	1900	21.6
90.nnz	310731	0.2	6.2	2431	2793	17.8
90.nlz	308793	0.2	5.7	2470	2996	17.8

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:

• all DBMS: 1 client, 4 clients
• MySQL: 1 client, 4 clients
• MariaDB
• innodb_purge_threads=1: 1 client, 4 clients 
• innodb_purge_threads=4: 1 client, 4 clients
• MariaDB vs MySQL
• innodb_purge_threads=1: 1 client, 4 clients
• innodb_purge_threads=4: 1 client, 4 clients

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

The Insert Benchmark: MariaDB, MySQL, 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.

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

The workload here has some concurrency (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 for a workload with low concurrency (1 client),

The results here are similar to the results on the low-concurrency benchmark.

tl;dr

• Modern MariaDB (11.4.1) is faster than modern MySQL (8.0.36) on all benchmark steps except for qr* (range query) and l.x (create index) where they have similar performance.
• Modern MariaDB (11.4.1) was at most 12% slower than older MariaDB (10.2.44). MariaDB has done a great job of avoiding performance regressions over time.
• There are significant performance regressions from older MySQL (5.6) to modern MySQL (8.0)
• Configuring MariaDB 10.4 to make it closer to 10.5 (only one InnoDB buffer pool, only one redo log file) reduces throughput by ~5% on several benchmark steps

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.

I started with the my.cnf.cz11a_bee config and then began to make small changes. For all configs I set these values to limit the size of the history list which also keeps the database from growing larger than expected. I rarely did this in the past.

innodb_max_purge_lag=500000

innodb_max_purge_lag_delay=1000000

Some of the changes were challenging when trying to make things comparable. 

• the InnoDB change buffer was removed in MariaDB 11.4. 
• I disable it in all my.cnf files for all MariaDB versions except for the my.cnf.cz11d and my.cnf.cz11d1 configs.
• I don't disable it for the MySQL configs named my.cnf.cz11[abc]_bee but I do disable it for the my.cnf.cz11d_bee config used by MySQL. The result is that for MariaDB the my.cnf.cz11d_bee config enables the change buffer while for MySQL it disables it. Sorry for the confusion.
• innodb_buffer_pool_instances was removed in MariaDB 10.5 (assume it is =1). 
• I don't set it to =1 in the my.cnf.cz[abc]_bee configs for MariaDB 10.2, 10.3, 10.4
• innodb_flush_method was removed in MariaDB 11.4 and there is a new way to configure this.
• In 11.4.1 there is an equivalent of =O_DIRECT but not of =O_DIRECT_NO_FSYNC

For MariaDB the typical my.cnf files were:

• my.cnf.cz11a_bee - uses innodb_flush_method=O_DIRECT_NO_FSYNC
• Two configs that are only used with MariaDB 10.4 to make it closer to 10.5+
• my.cnf.cz11abpi1_bee - like my.cnf.cz11a_bee except sets innodb_buffer_pool_instances and innodb_page_cleaners to =1. This config is used for  makes MairaDB 10.4
• my.cnf.cz11aredo1_bee - like my.cnf.cz11a_bee except uses one big InnoDB redo log instead of 10 smaller ones
• my.cnf.cz11b_bee - uses innodb_flush_method=O_DIRECT
• my.cnf.cz11c_bee - uses innodb_flush_method=fsync
• my.cnf.cz11d_bee - uses innodb_flush_method=O_DIRECT_NO_FSYNC and enables the InnoDB change buffer

For MySQL the typical my.cnf files were:

• my.cnf.cz11a_bee - uses innodb_flush_method=O_DIRECT_NO_FSYNC
• my.cnf.cz11b_bee - uses innodb_flush_method=O_DIRECT
• my.cnf.cz11c_bee - uses innodb_flush_method=fsync
• my.cnf.cz11d_bee - uses innodb_flush_method=O_DIRECT_NO_FSYNC and disables the InnoDB change buffer. Note the my.cnf.cz11[abc]_bee configs for MySQL enabled it. This is the opposite of what is done for MariaDB.

The Benchmark

The benchmark is run with 4 clients (some concurrency), a cached workload and 4 tables (table per client). It is explained here. The initial load (l.i0) inserts 30M rows and the database fits in memory.

The test server was named v4 here and is a Beelink SER4. It has 8 cores, 16G RAM, Ubuntu 22.04 and XFS using 1 m.2 device.

The benchmark steps are:

• l.i0
• insert 8 million rows per table in PK order (32M rows in total). Each table has a PK index but no secondary indexes. There is one connection per client.
• 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:

• MariaDB 10.11,  MariaDB 11.4, MySQL 8.0 release+config variations
• most MariaDB release+config variations
• MariaDB 10.4 release+config variations
• MariaDB 10.11 release+config variations
• MariaDB 11.4 release+config variations
• most MySQL release+config variations
• all DBMS

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

Modern MariaDB (11.4.1) is faster than modern MySQL (8.0.36) on all benchmark steps except for qr* (range query) and l.x (create index) where they have similar performance. This matches the low concurrency results.

From the summary:

• The base case is MariaDB 10.11.7 with the cz11a_bee config (ma101107_rel.cz11a_bee). It is compared with
• MariaDB 11.4.1 with the cz11b_bee config (ma110401_rel.cz11b_bee)
• MySQL 8.0.36 with the cz11a_bee config (my8036_rel.cz11a_bee)
• Relative throughput per benchmark step
• l.i0
• relative QPS is 0.99 in MariaDB 11.4.1
• relative QPS is 0.75 in MySQL 8.0.36
• l.x - I ignore this for now
• l.i1, l.i2
• relative QPS is 0.88, 0.98 in MariaDB 11.4.1
• relative QPS is 0.52, 0.54 in MySQL 8.0.36
• qr100, qr500, qr1000
• relative QPS is 0.99, 1.01, 1.00 in MariaDB 11.4.1
• relative QPS is 1.08, 1.05, 1.06 in MySQL 8.0.36
• qp100, qp500, qp1000
• relative QPS is 0.98, 1.00, 1.00 in MariaDB 11.4.1
• relative QPS is 0.77, 0.79, 0.77 in MySQL 8.0.36

Results: MariaDB

Modern MariaDB (11.4.1) was at most 12% slower than older MariaDB (10.2.44). This matches the low concurrency results. MariaDB has done a great job of avoiding performance regressions over time.

From the summary:

• The base case is MariaDB 10.2.44 with the cz11a_bee config (ma100244_rel.cz11a_bee). It is compared with more recent LTS releases from 10.3, 10.4, 10.5, 10.6, 10.11 and 11.4.
• Throughput per benchmark step for 11.4.1 relative to 10.2.44
• l.i0
• relative QPS is 0.88 in MariaDB 11.4.1
• l.x - I ignore this for now
• l.i1, l.i2
• relative QPS is 0.99, 1.09 in MariaDB 11.4.1
• qr100, qr500, qr1000
• relative QPS is 0.88, 0.93, 0.92 in MariaDB 11.4.1
• qp100, qp500, qp1000
• relative QPS is 0.93, 0.98, 1.00 in MariaDB 11.4.1

Results: MariaDB 10.4

From the summary:

• The base case is MariaDB 10.4.33 with the cz11a_bee config (ma100433_rel.cz11a_bee).
• It is compared with MariaDB 10.4.33 using the cz11abpi1_bee and cz11aredo1 configs
• MariaDB with the cz11abpi1_bee config does worse on the l.i1 and l.i2 benchmark steps
• There might be more contention on the one buffer pool instance
• From the metrics with the l.i1 benchmark step I see larger values for context switches per query (cspq) and CPU per query (cpupq). I also see a lower CPU utilization (cpups) and write back rate (wmbps is storage MB/s written)
• MariaDB with the cz11aredo1_bee config does worse on the qp* benchmark steps
• From the metrics with the qp* benchmark steps I see a larger value for CPU per query (cpupq)
• Throughput per benchmark step relative to the base case
• l.i0
• relative QPS is 0.99 with cz11abpi1_bee
• relative QPS is 1.01 with cz11aredo1_bee
• l.x - I ignore this for now
• l.i1, l.i2
• relative QPS is 0.90, 0.94 with cz11abpi1_bee
• relative QPS is 1.00, 1.00 with cz11aredo1_bee
• qr100, qr500, qr1000
• relative QPS is 1.00, 1.00, 1.01 with cz11abpi1_bee
• relative QPS is 0.99, 0.98, 0.99 with cz11aredo1_bee
• qp100, qp500, qp1000
• relative QPS is 0.99, 1.01, 1.00 with cz11abpi1_bee
• relative QPS is 0.95, 0.95, 0.95 with cz11aredo1_bee

Results: MariaDB 10.11

From the summary:

• The base case is MariaDB 10.11.7 with the cz11a_bee config (ma101107_rel.cz11a_bee) and 10.11.7 releases with other configs are compared to it.
• The results here are similar to the low-concurrency results except that the results here for the cz11b_bee and cz11c_bee configs are much worse for several of the write-heavy steps than they are in the low-concurrency test. 

Results: MariaDB 11.4

From the summary:

• The base case is MariaDB 11.4.1 with the cz11b_bee config (ma110401.cz11b_bee) and 11.4.1 releases with the cz11c_bee config is compared to it. Note that 11.4.1 does not support the equivalent of O_DIRECT_NO_FSYNC for innodb_flush_method.
• For the cz11c_bee config
• Performance for the write heavy steps (l.i0, l.i1, l.i2) is ~10% worse than the base case. This issue doesn't repeat on the low-concurrency results.
• Performance for qp100 is ~7% worse than the base case. This is similar to the low-concurrency results.

Results: MySQL

There are significant performance regressions from MySQL 5.6 to 8.0 and the results here are similar to the low-concurrency results.

From the summary:

• The base case is MySQL 5.6.51 with the cz11a_bee config (my5651_rel.cz11a_bee) and it is compared to MySQL 5.7.44 and 8.0.36.
• Relative throughput per benchmark step
• l.i0
• relative QPS is 0.84 in MySQL 5.7.44
• relative QPS is 0.60 in MySQL 8.0.36
• l.x - I ignore this for now
• l.i1, l.i2
• relative QPS is 1.33, 1.17 in MySQL 5.7.44
• relative QPS is 0.78, 0.72 in MySQL 8.0.36
• qr100, qr500, qr1000
• relative QPS is 0.79, 0.80, 0.80 in MySQL 5.7.44
• relative QPS is 0.69, 0.68, 0.69 in MySQL 8.0.36
• qp100, qp500, qp1000
• relative QPS is 0.82, 0.86, 0.81 in MySQL 5.7.44
• relative QPS is 0.64, 0.67, 0.64 in MySQL 8.0.36

Changes to managing turbo boost in Ubuntu 22.04 and Linux 6.5

I often use HWE kernels with Ubuntu and currently use Ubuntu 22.04. Until recently that meant I ran Linux 6.2 but after a recent update I am now on Linux 6.5.

I am far from an expert on this topic and what I write here might just be notes to myself. Be wary of following my advice.

Disabling turbo boost yesterday

I have been disabling turbo boost for many years on my home test servers to reduce performance variance from hardware, especially as the weather gets warm because I don't have a server room with AC.  The problem with turbo boost on some of my servers was cyclical behavior:

1. CPU cools, turbo boost does its thing
2. benchmark runs faster
3. CPU gets hot
4. turbo boost stops doing its thing
5. benchmark runs slower
6. repeat

On my Intel servers I disable turbo boost via BIOS settings. On my AMD servers that used to be done via a script because I was using acpi-cpufreq: echo 0 > /sys/devices/system/cpu/cpufreq/boost

My goal is repeatable performance and I am willing to sacrifice peak HW performance to get that. Avoiding the cycle described above helps to achieve that. Alas this is a spectrum -- I tolerate other things (CPU cache, database cache) that improve performance while adding variance. I assume that I want CPU frequency to stay within a narrow range. It isn't clear that even when using acpi-cpufreq that I was getting a narrow range, but it did help. 

From the Ryzen 7 7840HS CPU I am use on these servers the AMD specs state that the base speed is 3.8GHz and the max boost is up to 5.1GHz. With acpi-cpufreq the CPU cores can be in one of three frequency levels, and from cpupower frequency-info they are:

available frequency steps:  3.80 GHz, 2.20 GHz, 1.60 GHz

So even with turbo boost disabled (see the echo command above) there is still room for variance. But I don't know enough to determine whether I need to do more tuning.

Disabling turbo boost today

After a recent update on Ubuntu 22.04 with HWE kernels I now run 6.5.0-27-generic and acpi-cpufreq has been replaced by amd-pstate. I am sure there are many benefits from this change, alas, it also brings complexity and confusion from users who now have server cooling problems (because things are running faster) and are trying to figure out how to fix them. Notes on setting up the server are here.

I noticed this change because with the the default (amd-pstate in active mode) this file doesn't exist:

/sys/devices/system/cpu/cpufreq/boost

On a Ryzen 7 CPU I get the amd-pstate-epp driver in active mode. Output from /proc/cpuinfo and cpupower frequency-info from this state is below. Note that  /sys/devices/system/cpu/cpufreq/boost doesn't exist when in active mode. It does exist when in guided or passive mode. So I either need to switch to guided or passive mode or rollback to using the acpi-cpufreq driver. Which means I need to understand a bit more.

There is a lot of documentation for the amd-pstate driver. It isn't meant for the casual user.

• from AMD
• useful comments (grep for linuxlion)
• other workarounds
• more details
• notes on CPU frequency scaling
• generic notes on turbo boost
• another good thread on Reddit
• my post on the Beelink support forum

There is a big difference between acpi-cpufreq and amd-pstate and amd-pstate is the future but perhaps not today (for me). While with acpi-cpufreq and turbo boost disabled I should only get one of three CPU frequencies, I can get many more with amd-pstate. From cpupower frequency-info output

analyzing CPU 7:
  driver: amd-pstate-epp
  CPUs which run at the same hardware frequency: 7
  CPUs which need to have their frequency coordinated by software: 7
  maximum transition latency:  Cannot determine or is not supported.
  hardware limits: 400 MHz - 5.61 GHz
  available cpufreq governors: performance powersave
  current policy: frequency should be within 400 MHz and 5.61 GHz.
                  The governor "powersave" may decide which speed to use
                  within this range.
  current CPU frequency: Unable to call hardware
  current CPU frequency: 2.97 GHz (asserted by call to kernel)
  boost state support:
    Supported: yes
    Active: no

For now I will just rollback to using the acpi-cpufreq driver while figuring this out and possibly waiting for Linux 6.6 to show up on Ubuntu 22.04. I am not sure how mature amd-pstate is, and I won't get support for cpupower set --turbo-boost 1 until 6.6 arrives.

I now have this in /etc/default/grub: 

GRUB_CMDLINE_LINUX_DEFAULT="pcie_aspm=off nosmt amd_pstate=disable"

• pcie_aspm=off is there to avoid correctable PCI errors (maybe Beelink BIOS needs an update)
• nosmt disables hyperthreads because BIOS doesn't have an option for that
• amd_pstate=disable lets me use the acpi-cpufreq driver 

CPU frequencies with acpi-cpufreq

This shows the CPU frequencies I get from an idle server with the acpi-cpufreq driver. Note that I mostly get only 3 values when boost is disabled (set to 0).

With /sys/devices/system/cpu/cpufreq/boost set to 0

  current CPU frequency: 2.18 GHz (asserted by call to kernel)
  current CPU frequency: 1.50 GHz (asserted by call to kernel)
  current CPU frequency: 3.80 GHz (asserted by call to kernel)
  current CPU frequency: 1.60 GHz (asserted by call to kernel)
  current CPU frequency: 1.60 GHz (asserted by call to kernel)
  current CPU frequency: 1.60 GHz (asserted by call to kernel)
  current CPU frequency: 1.60 GHz (asserted by call to kernel)
  current CPU frequency: 3.80 GHz (asserted by call to kernel)

With /sys/devices/system/cpu/cpufreq/boost set to 1

  current CPU frequency: 1.60 GHz (asserted by call to kernel)
  current CPU frequency: 2.04 GHz (asserted by call to kernel)
  current CPU frequency: 2.11 GHz (asserted by call to kernel)
  current CPU frequency: 1.60 GHz (asserted by call to kernel)
  current CPU frequency: 1.60 GHz (asserted by call to kernel)
  current CPU frequency: 1.57 GHz (asserted by call to kernel)
  current CPU frequency: 1.60 GHz (asserted by call to kernel)
  current CPU frequency: 3.21 GHz (asserted by call to kernel)

Appendix

Note that cpupower frequency-info only shows frequencies for one core, to see them all use cpupower -c all frequency-info.

Output from  cpupower frequency-info with active mode

analyzing CPU 7:
  driver: amd-pstate-epp
  CPUs which run at the same hardware frequency: 7
  CPUs which need to have their frequency coordinated by software: 7
  maximum transition latency:  Cannot determine or is not supported.
  hardware limits: 400 MHz - 5.61 GHz
  available cpufreq governors: performance powersave
  current policy: frequency should be within 400 MHz and 5.61 GHz.
                  The governor "powersave" may decide which speed to use
                  within this range.
  current CPU frequency: Unable to call hardware
  current CPU frequency: 2.97 GHz (asserted by call to kernel)
  boost state support:
    Supported: yes
    Active: no

Output from  cpupower frequency-info with guided mode

analyzing CPU 7:
  driver: amd-pstate
  CPUs which run at the same hardware frequency: 7
  CPUs which need to have their frequency coordinated by software: 7
  maximum transition latency: 20.0 us
  hardware limits: 400 MHz - 5.61 GHz
  available cpufreq governors: conservative ondemand userspace powersave performance schedutil
  current policy: frequency should be within 400 MHz and 5.61 GHz.
                  The governor "schedutil" may decide which speed to use
                  within this range.
  current CPU frequency: Unable to call hardware
  current CPU frequency: 1.44 GHz (asserted by call to kernel)
  boost state support:
    Supported: yes
    Active: yes
    AMD PSTATE Highest Performance: 214. Maximum Frequency: 5.61 GHz.
    AMD PSTATE Nominal Performance: 145. Nominal Frequency: 3.80 GHz.
    AMD PSTATE Lowest Non-linear Performance: 42. Lowest Non-linear Frequency: 1.10 GHz.
    AMD PSTATE Lowest Performance: 16. Lowest Frequency: 400 MHz.

Output from  cpupower frequency-info with passive mode

analyzing CPU 7:
  driver: amd-pstate
  CPUs which run at the same hardware frequency: 7
  CPUs which need to have their frequency coordinated by software: 7
  maximum transition latency: 20.0 us
  hardware limits: 400 MHz - 5.61 GHz
  available cpufreq governors: conservative ondemand userspace powersave performance schedutil
  current policy: frequency should be within 400 MHz and 5.61 GHz.
                  The governor "schedutil" may decide which speed to use
                  within this range.
  current CPU frequency: Unable to call hardware
  current CPU frequency: 2.74 GHz (asserted by call to kernel)
  boost state support:
    Supported: yes
    Active: yes
    AMD PSTATE Highest Performance: 214. Maximum Frequency: 5.61 GHz.
    AMD PSTATE Nominal Performance: 145. Nominal Frequency: 3.80 GHz.
    AMD PSTATE Lowest Non-linear Performance: 42. Lowest Non-linear Frequency: 1.10 GHz.
    AMD PSTATE Lowest Performance: 16. Lowest Frequency: 400 MHz.

Output from /proc/cpuinfo

processor : 7
vendor_id : AuthenticAMD
cpu family : 25
model : 116
model name : AMD Ryzen 7 7840HS w/ Radeon 780M Graphics
stepping : 1
microcode : 0xa704103
cpu MHz : 3800.000
cache size : 1024 KB
physical id : 0
siblings : 8
core id : 7
cpu cores : 8
apicid : 14
initial apicid : 14
fpu : yes
fpu_exception : yes
cpuid level : 16
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt pdpe1gb rdtscp lm constant_tsc rep_good amd_lbr_v2 nopl nonstop_tsc cpuid extd_apicid aperfmperf rapl pni pclmulqdq monitor ssse3 fma cx16 sse4_1 sse4_2 x2apic movbe popcnt aes xsave avx f16c rdrand lahf_lm cmp_legacy svm extapic cr8_legacy abm sse4a misalignsse 3dnowprefetch osvw ibs skinit wdt tce topoext perfctr_core perfctr_nb bpext perfctr_llc mwaitx cpb cat_l3 cdp_l3 hw_pstate ssbd mba perfmon_v2 ibrs ibpb stibp ibrs_enhanced vmmcall fsgsbase bmi1 avx2 smep bmi2 erms invpcid cqm rdt_a avx512f avx512dq rdseed adx smap avx512ifma clflushopt clwb avx512cd sha_ni avx512bw avx512vl xsaveopt xsavec xgetbv1 xsaves cqm_llc cqm_occup_llc cqm_mbm_total cqm_mbm_local avx512_bf16 clzero irperf xsaveerptr rdpru wbnoinvd cppc arat npt lbrv svm_lock nrip_save tsc_scale vmcb_clean flushbyasid decodeassists pausefilter pfthreshold v_vmsave_vmload vgif x2avic v_spec_ctrl vnmi avx512vbmi umip pku ospke avx512_vbmi2 gfni vaes vpclmulqdq avx512_vnni avx512_bitalg avx512_vpopcntdq rdpid overflow_recov succor smca flush_l1d
bugs : sysret_ss_attrs spectre_v1 spectre_v2 spec_store_bypass srso
bogomips : 7585.46
TLB size : 2560 4K pages
clflush size : 64
cache_alignment : 64
address sizes : 48 bits physical, 48 bits virtual
power management: ts ttp tm hwpstate cpb eff_freq_ro [13] [14] [15]

Status as of April 18, 2024

The problem isn't resolved. I tried both the current HWE and non-HWE kernels with a variety of kernel boot params but the result was that one or both of the new SER7 servers were slower. I tried all of these:

GRUB_CMDLINE_LINUX_DEFAULT="pcie_aspm.policy=performance"

GRUB_CMDLINE_LINUX_DEFAULT="pcie_aspm=off nosmt amd_pstate=disable"

GRUB_CMDLINE_LINUX_DEFAULT="pcie_aspm=off"

GRUB_CMDLINE_LINUX_DEFAULT="amd_pstate=disable"

GRUB_CMDLINE_LINUX_DEFAULT="nosmt"
GRUB_CMDLINE_LINUX_DEFAULT=""

Results from tests are here. In many cases the CPU overhead (user+system) is significantly different on the new servers compared to the old one.

The selection of small servers (with small TDP) that don't have a mix of performance and efficiency cores is limited (the few available use AMD Ryzen CPUs). I might replace the Beelink SER7 with ASUS PN53.

I asked Beelink support for a copy of the v28 BIOS that the old (good) server uses. They provided it, I installed it but the errors remain. 

From dmidecode -t bios the BIOS versions are:

v28 (good one used by old server)

BIOS Information

Vendor: American Megatrends International, LLC.

Version: SER7PRO_P5C8V28

Release Date: 08/14/2023

v38 (used by both new servers that have the errors)

BIOS Information

Vendor: American Megatrends International, LLC.

Version: SER7PRO_P5C8V38

Release Date: 01/10/2024

An example of the errors is:

[Fri Apr 19 17:14:03 2024] pcieport 0000:00:01.2: AER: Corrected error received: 0000:01:00.0

[Fri Apr 19 17:14:03 2024] nvme 0000:01:00.0: PCIe Bus Error: severity=Corrected, type=Physical Layer, (Receiver ID)

[Fri Apr 19 17:14:03 2024] nvme 0000:01:00.0:   device [144d:a80a] error status/mask=00000001/0000e000

[Fri Apr 19 17:14:03 2024] nvme 0000:01:00.0:    [ 0] RxErr                  (First)

The Insert Benchmark: MariaDB, MySQL, small server, cached workload

This post has results for the Insert Benchmark on a small server with a cached workload. The goal is to compare MariaDB and MySQL.

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

The workload here has low concurrency and the database is cached. The results might be different when the workload is IO-bound or has more concurrency.

tl;dr

• Modern MariaDB (11.4.1) is faster than modern MySQL (8.0.36) on all benchmark steps except for qr* (range query) and l.x (create index) where they have similar performance.
• Modern MariaDB (11.4.1) was at most 15% slower than older MariaDB (10.2.44). MariaDB has done a great job of avoiding performance regressions over time.
• There are significant performance regressions from older MySQL (5.6) to modern MySQL (8.0)
• Performance with innodb_flush_method set to =O_DIRECT_NO_FSYNC is better than with =O_DIRECT is better than =fsync.

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.

I started with the my.cnf.cz11a_bee config and then began to make small changes. For all configs I set these values to limit the size of the history list which also keeps the database from growing larger than expected. I rarely did this in the past.

innodb_max_purge_lag=500000

innodb_max_purge_lag_delay=1000000

Some of the changes were challenging when trying to make things comparable. 

• the InnoDB change buffer was removed in MariaDB 11.4. 
• I disable it in all my.cnf files for all MariaDB versions except for the my.cnf.cz11d and my.cnf.cz11d1 configs.
• I don't disable it for the MySQL configs named my.cnf.cz11[abc]_bee but I do disable it for the my.cnf.cz11d_bee config used by MySQL. The result is that for MariaDB the my.cnf.cz11d_bee config enables the change buffer while for MySQL it disables it. Sorry for the confusion.
• innodb_buffer_pool_instances was removed in 10.5 (assume it is =1). 
• I don't set it to =1 in the my.cnf.cz[abc]_bee configs for MariaDB 10.2, 10.3, 10.4
• innodb_flush_method was removed in 11.4 and there is a new way to configure this.
• In 11.4.1 there is an equivalent of =O_DIRECT but not of =O_DIRECT_NO_FSYNC

For MariaDB the typical my.cnf files were:

• my.cnf.cz11a_bee - uses innodb_flush_method=O_DIRECT_NO_FSYNC
• my.cnf.cz11a1_bee - like my.cnf.cz11a_bee but reduces the sizes of the buffer pool and redo log so that both fit in memory
• my.cnf.cz11b_bee - uses innodb_flush_method=O_DIRECT
• my.cnf.cz11b1_bee - like my.cnf.cz11b_bee but reduces the sizes of the buffer pool and redo log so that both fit in memory
• my.cnf.cz11c_bee - uses innodb_flush_method=fsync
• my.cnf.cz11d_bee - uses innodb_flush_method=O_DIRECT_NO_FSYNC and enables the InnoDB change buffer
• my.cnf.cz11d1_bee - like my.cnf.cz11d_bee but reduces the sizes of the buffer pool and redo log so that both fit in memory

For MySQL the typical my.cnf files were:

• my.cnf.cz11a_bee - uses innodb_flush_method=O_DIRECT_NO_FSYNC
• my.cnf.cz11b_bee - uses innodb_flush_method=O_DIRECT
• my.cnf.cz11c_bee - uses innodb_flush_method=fsync
• my.cnf.cz11d_bee - uses innodb_flush_method=O_DIRECT_NO_FSYNC and disables the InnoDB change buffer. Note the my.cnf.cz11[abc]_bee configs for MySQL enabled it. This is the opposite of what is done for MariaDB.

The Benchmark

The benchmark is run with 1 client. It is explained here and was run in two setups with a cached workload. The initial load (l.i0) inserts 30M rows and the database fits in memory. I recently updated the Insert Benchmark to change how deletes are done and the new delete SQL is here.

The test server was named v4 here and is a Beelink SER4. It has 8 cores, 16G RAM, Ubuntu 22.04 and XFS using 1 m.2 device.

The benchmark steps are:

• l.i0
• insert 30 million rows per table in PK order. The table has a PK index but no secondary indexes. There is one connection per client.
• 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:

• MariaDB 10.11,  MariaDB 11.4, MySQL 8.0 release+config variations
• most MariaDB release+config variations
• MariaDB 10.11 release+config variations
• MariaDB 11.4 release+config variations
• most MySQL release+config variations
• all DBMS

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

Modern MariaDB (11.4.1) is faster than modern MySQL (8.0.36) on all benchmark steps except for qr* (range query) and l.x (create index) where they have similar performance.

From the summary:

• The base case is MariaDB 10.11.7 with the cz11a_bee config (ma101107_rel.cz11a_bee). It is compared with
• MariaDB 11.4.1 with the cz11b_bee config (ma110401_rel.cz11b_bee)
• MySQL 8.0.36 with the cz11a_bee config (my8036_rel.cz11a_bee)
• Relative throughput per benchmark step
• l.i0
• relative QPS is 0.98 in MariaDB 11.4.1
• relative QPS is 0.68 in MySQL 8.0.36
• l.x - I ignore this for now
• l.i1, l.i2
• relative QPS is 0.93, 0.97 in MariaDB 11.4.1
• relative QPS is 0.88, 0.78 in MySQL 8.0.36
• qr100, qr500, qr1000
• relative QPS is 1.03, 1.02, 1.01 in MariaDB 11.4.1
• relative QPS is 1.02, 1.04, 1.04 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.76, 0.74, 0.75 in MySQL 8.0.36

Results: MariaDB

Modern MariaDB (11.4.1) was at most 15% slower than older MariaDB (10.2.44). MariaDB has done a great job of avoiding performance regressions over time.

From the summary:

• The base case is MariaDB 10.2.44 with the cz11a_bee config (ma100244_rel.cz11a_bee). It is compared with more recent LTS releases from 10.3, 10.4, 10.5, 10.6, 10.11 and 11.4.
• Throughput per benchmark step for 11.4.1 relative to 10.2.44
• l.i0
• relative QPS is 0.86 in MariaDB 11.4.1
• l.x - I ignore this for now
• l.i1, l.i2
• relative QPS is 1.08, 1.07 in MariaDB 11.4.1
• qr100, qr500, qr1000
• relative QPS is 0.89, 0.86, 0.85 in MariaDB 11.4.1
• qp100, qp500, qp1000
• relative QPS is 0.91, 0.91, 0.88 in MariaDB 11.4.1

Results: MariaDB 10.11

From the summary:

• The base case is MariaDB 10.11.7 with the cz11a_bee config (ma101107_rel.cz11a_bee) and 10.11.7 releases with other configs are compared to it.
• Performance does not change with the cz11a1_bee config that uses a smaller size for the InnoDB redo log and buffer pool so that they fit in memory. In workloads that are more IO-bound I see that MariaDB 10.11+ does read IO for the redo log while MySQL does not. Shrinking the buffer pool and redo log isn't good for performance but avoids the read IO.
• Performance is worse with the cz11b_bee config on the random write (l.i1, l.i2) benchmark steps. This indicates that setting innodb_flush_method to O_DIRECT_NO_FSYNC is better than setting it to O_DIRECT.
• Performance is worse with the cz11c_bee config on the random write (l.i1, l.i2) benchmark steps. This indicates that setting innodb_flush_method to O_DIRECT_NO_FSYNC is better than setting it to fsync.
• Performance does not change with the cz11d_bee config which enabled the InnoDB change buffer. Given that the working set fits in the InnoDB buffer pool the result is expected.
• Performance is worse with the cz11d1_bee config on the random write (l.i1, l.i2) benchmark steps. See the comments above for cz11a1_bee.

Results: MariaDB 11.4

From the summary:

• The base case is MariaDB 11.4.1 with the cz11b_bee config (ma110401.cz11b_bee) and 11.4.1 releases with other configs are compared to it. Note that 11.4.1 does not support the equivalent of O_DIRECT_NO_FSYNC for innodb_flush_method.
• Performance is worse with the cz11b1_bee config on the random write (l.i1, l.i2) benchmark steps. See the comments in the previous section on the cz11a1_bee config.
• Performance is slightly worse with the cz11c_bee config on the random write (l.i1, l.i2) benchmark steps. This indicates that setting innodb_flush_method to O_DIRECT_NO_FSYNC is better than setting it to fsync.

Results: MySQL

There are significant performance regressions from MySQL 5.6 to 8.0.

From the summary:

• The base case is MySQL 5.6.51 with the cz11a_bee config (my5651_rel.cz11a_bee) and it is compared to MySQL 5.7.44 and 8.0.36.
• Relative throughput per benchmark step
• l.i0
• relative QPS is 0.84 in MySQL 5.7.44
• relative QPS is 0.57 in MySQL 8.0.36
• l.x - I ignore this for now
• l.i1, l.i2
• relative QPS is 1.17, 0.89 in MySQL 5.7.44
• relative QPS is 1.07, 0.73 in MySQL 8.0.36
• qr100, qr500, qr1000
• relative QPS is 0.73, 0.72, 0.71 in MySQL 5.7.44
• relative QPS is 0.63, 0.63, 0.63 in MySQL 8.0.36
• qp100, qp500, qp1000
• relative QPS is 0.82, 0.79, 0.80 in MySQL 5.7.44
• relative QPS is 0.62, 0.60, 0.62 in MySQL 8.0.36