Sysbench on a medium server: MariaDB is faster than MySQL

Modern MariaDB is significantly faster than modern MySQL on a medium server with cached sysbench because MySQL suffers from too many CPU performance regressions (code bloat, etc) over time.

This post has results for sysbench with a cached database and medium server (16 cores) using MariaDB and MySQL. The benchmark is run with 12 threads. A full writeup for MySQL on this HW is here and for MariaDB on a smaller server is here.

Note that the great results for MariaDB only occur when MDEV-33894 has been fixed in versions 10.11 or greater -- it isn't a bug prior to 10.11. I reported that bug a few months ago and then worked with the MariaDB core team to explain the problem and validate the fix.

The gap between MariaDB and MySQL is likely to grow. While MySQL 8.0.40 might have fixes that undo some of the regressions that arrived in InnoDB after 8.0.28 (see PS-8822 and MySQL 111538), I expect to see improvements in MariaDB as well. I opened three perf bugs for MariaDB earlier this year. The great results here are due in part to fixing MDEV-33894 and there will be even better results in the future when the two other bugs are fixed: MDEV-34178 and MDEV-33966.

tl;dr

• The fix for MDEV-33894 is important
• MariaDB has done great at avoiding CPU performance regressions over time, MySQL has not
• Modern MariaDB is faster than modern MySQL
• MariaDB was faster on 41 microbenchmarks and slower on one (update-inlist)
• A typical result is MariaDB is ~11% faster than MySQL

Builds, configuration and hardware

For MySQL I compiled 8.0.38 from source.

For MariaDB I compiled these versions from source:

• ma100244_rel_withdbg
• MariaDB 10.2.44
• ma100339_rel_withdbg
• MariaDB 10.3.29
• ma100434_rel_withdbg
• MariaDB 10.4.34
• ma100525_rel_withdbg
• MariDB 10.5.25
• ma100618_rel_withdbg
• MariaDB 10.6.18
• ma101108_rel_withdbg
• MariaDB 10.11.8
• ma1011_240723_c944cd6e_ilwas
• MariaDB 10.11 compiled on 24/07/23 at git hash c944cd6f with innodb_log_writeahead_size =4096. Note the git hash in ma1011_240723_c944cd6e_ilwas is a typo (should end in 6f)
• ma110402_rel_withdbg
• MariaDB 11.4.2
• ma1104_240723_2ee061c2_ilwas
• MariaDB 11.4 compiled on 24/07/23 at git hash 2ee061c2 with innodb_log_writeahead_size =4096
• ma110501_rel_withdbg
• MariaDB 11.5.1
• ma1105_240723_2f4b0ba3_ilwas
• MariaDB 11.5 compiled on 24/07/23 at git hash 2f4b0ba3 with innodb_log_writeahead_size =4096
• ma110600_rel_withdbg
• MariaDB 11.6.0
• ma1106_240723_d8374262_ilwas
• MariaDB 11.6 compiled on 24/07/23 at git hash d8374262 with innodb_log_writeahead_size =4096

The server is a c2d-highcpu-32 instance type on GCP (c2d high-CPU) with 32 vCPU, 64G RAM and SMT disabled so there are 16 cores. It uses Ubuntu 22.04 and storage is ext4 (data=writeback) using SW RAID 0 over 2 locally attached NVMe devices.

The my.cnf file is here for MySQL 8.0.38.

The my.cnf files for MariaDB are here in per-version subdirectories. For 10.x the cz11a_c24r64 variant was used. For 11.x the cz11b_c24r64 variant was used. For the *_ilwas binaries above I added innodb_log_writeahead_size =4096 to the my.cnf file, although that wasn't needed because it was =4096 when I didn't set it in my.cnf.

Benchmark

I used sysbench and my usage is explained here. There are 42 microbenchmarks and most test only 1 type of SQL statement. The database is cached by InnoDB.

The benchmark is run with 12 threads, 8 tables and 10M rows per table.. Ech microbenchmark runs for 300 seconds if read-only and 600 seconds otherwise. Prepared statements were enabled.

The command lines for my helper scripts were:

#  8 tables, 10M rows/table, 12 threads
bash r.sh 8 10000000 300 600 md0 1 1 12

Results

For the results below I split the 42 microbenchmarks into 5 groups -- 2 for point queries, 2 for range queries, 1 for writes. For the range query microbenchmarks, part 1 has queries that don't do aggregation while part 2 has queries that do aggregation. The spreadsheet with all data is here. For each microbenchmark group there is a table with summary statistics. I don't have charts because that would use too much space, but the results per microbenchmark are in the spreadsheets.

The numbers in the spreadsheets and the tables below are the relative QPS which is (QPS for my version) / (QPS for base case). When the relative throughput is > 1 then that version is faster than the base case.

For all results below the base case is InnoDB from MySQL 8.0.26

Results: iostat and vmstat

This section has data from vmstat and iostat normalized by throughput to understand HW consumption per database operation. When MySQL is slower than MariaDB the usual culprit is CPU overhead (see the cpu/o column) and one example is the result for scans (see here) where cpu/o is 0.059403 for MySQL vs 0.043163 for MariaDB which is ~1.38X larger for MySQL.

But the results also show new problems for MariaDB and MDEV-34178 and MDEV-33966 are open for these problems. The usual signal is an increase in the context switch rate (cs/o). Examples of that occur on:

• The problem is new in MariDB 10.11
• random-points (see here)
• range-notcovered (see here)
• scan (see here)
• The problem is new in MariaDB 10.5
• update-inlist (see here) - the problem arrives in 10.5
• update-index (see here) - the problem arrives in 10.5

The full results are here for MariaDB vs MySQL and here for MariaDB.

Results: MariaDB vs MySQL, summary statistics

Each table has summary statistics per microbenchmark group. The numbers are the relative QPS.

• Relative QPS is: (QPS for MariaDB) / (QPS for MySQL)
• MariaDB is ma1104_240723_2ee061c2_ilwas (11.4 with MDEV-33894 fix)
• MySQL is 8.0.38
• MariaDB is faster than MySQL on all microbenchmarks except for update-inlist

MariaDB	min	max	avg	median
point-1	1.05	1.27	1.09	1.08
point-2	1.06	1.09	1.07	1.07
range-1	1.03	1.35	1.12	1.11
range-2	1.10	1.37	1.19	1.11
writes	0.83	2.06	1.36	1.37

Results: MariaDB, summary statistics

Each table has summary statistics per microbenchmark group. The numbers are the relative QPS.

• Relative QPS is: (QPS for modern MariaDB) / (QPS for older MariaDB)
• modern MariaDB is 11.4 without and with the MDEV-33894 fix -- ma110402_rel_withdbg and ma1104_240723_2ee061c2_ilwas
• older MariaDB is 10.2.44 -- ma100244_rel_withdbg

Summary

• Writes are faster in 11.4 than in 10.2 with the MDEV-33894 fix
• Reads are between 13% slower and 23% faster in 11.4 than in 10.2
• MariaDB has done great at avoiding regressions over time

For 11.4.2 ( ma110402_rel_withdbg) relative to 10.2.44 (ma100244_rel_withdbg)

11.4.2	min	max	avg	median
point-1	0.86	0.98	0.92	0.93
point-2	0.87	0.91	0.89	0.88
range-1	0.87	1.18	0.95	0.93
range-2	0.92	1.04	0.97	0.94
writes	0.43	0.75	0.62	0.61

For 11.4 with the fix for MDEV-33894 (ma1104_240723_2ee061c2_ilwas) relative to 10.2.44 (ma100244_rel_withdbg)

11.4.ilwas	min	max	avg	median
point-1	0.86	0.96	0.92	0.93
point-2	0.87	0.91	0.89	0.88
range-1	0.87	1.25	0.95	0.93
range-2	0.92	1.04	0.97	0.94
writes	0.74	1.29	1.06	1.12

Results: MariaDB, charts

The y-axis starts at 0.85 for the read microbenchmark groups (point-1, point-2, range-1, range-2) to improve readability.

Summary:

• MariaDB has done much better than MySQL at avoiding CPU perf regressions over time
• For the scan microbenchmark modern MariaDB is faster than older MariaDB
• For write microbenchmarks the MDEV-33894 fix is important as QPS is almost 2X larger with it

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

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

Evaluating vector indexes in MariaDB and pgvector: part 1

This post has results for vector index support in MariaDB and Postgres. I am new to vector indexes so I will start small and over time add more results.  This work was done by Small Datum LLC and sponsored by the MariaDB Corporation.

I compare MariaDB with pgvector because I respect the work that the Postgres community has done to support vector search workloads. And I am happy to report that MariaDB has also done a great job on this. While I don't know the full story of the development effort, this feature came from the MariaDB Foundation and the community and it is wonderful to see that collaboration.

tl;dr

• Performance for MariaDB is excellent
• peak QPS for a given recall target is much better than pgvector
• time to create the index to achieve a given recall target is almost always much better than pgvector
• MariaDB is easier to tune than pgvector
• MariaDB needs more documentation

The good and the less good

The good for MariaDB starts with performance. The results I get are great and match the results in this blog post from upstream. Another good thing about MariaDB is that it is easier to tune. With pgvector I need to set M and ef_construction while creating an index and then ef_search while running a query, With MariaDB there is no option to set ef_construction. And evaluations are easier when there are fewer options to tune. Note that the M with pgvector isn't the same thing as the M with MariaDB, but they are similar (at least in spirit).

The less good for MariaDB is the documentation. We need more, but that is easy to fix. I have been using:

• this blog post on benchmarks
• this overview
• this syntax description

Things that need more documentation

• the algorithm is described as a variation of HNSW (MHNSW) and the community will benefit from more details on what has been changed. For example, I can't set ef_construction and it always uses float16 in the index. But with pgvector I can use either float32 (default) or float16 (via halfvec).
• What transaction isolation level is supported? I get that workloads will be read-heavy but I assume that some won't be read-only so I won't to know whether repeatable read and read committed are provided.
• What concurrent operations are supported? Obviously, reads can be concurrent with other reads. But can writes be concurrent with reads or with other writes on the same table?

Hardware

The hardware is a Beelink SER7 7840HS with a Ryzen 7 7840HS CPU, 32G of RAM and Ubuntu 22.04.

While I already installed some dependencies on this server long ago, to run this benchmark I did:

sudo apt install libmariadb3 libmariadb-dev

pip3 install mariadb

pip3 install pgvector psycopg

Database software

For Postgres I compiled version 17.2 and pgvector 0.8.0 from source. Files that I used include:

• build script
• configuration file
• initialization script

For MariaDB I compiled version 11.7.1 from source. Files that I used include:

• build script
• configuration file
• initialization script

Benchmark

I am using the ann-benchmarks via my fork of a fork of a fork at this commit. These forks have changes to run the benchmark for MariaDB and pgvector without using Docker containers. A request I have for anyone writing a benchmark client is to limit dependencies, or at least make them optional. I just want to point the benchmark client at an existing installation.

The ann-benchmarks configuration files are here for MariaDB and for pgvector. I am open to feedback that I should try different parameters. I added support to use float16 (halfvec) for the pgvector index (but only the index, not for the vector stored in the table).

In this post I use the fashion-mnist-784-euclidean dataset in non-batch mode where non-batch mode doesn't run concurrent queries and batch mode does. Note that I don't set --count when running the benchmark which means that all of the queries use LIMIT 10.

Files related to these tests are archived here.

The command lines for non-batch mode are:

POSTGRES_CONN_ARGS=root:pw:127.0.0.1:5432 POSTGRES_DB_NAME=ib \
    python3 -u run.py  --algorithm pgvector --dataset fashion-mnist-784-euclidean --local

POSTGRES_CONN_ARGS=root:pw:127.0.0.1:5432 POSTGRES_DB_NAME=ib \
    python3 -u run.py  --algorithm pgvector_halfvec --dataset fashion-mnist-784-euclidean --local

MARIADB_CONN_ARGS=root:pw:127.0.0.1:3306 MARIADB_DB_NAME=test \
    python3 -u run.py  --algorithm mariadb --dataset fashion-mnist-784-euclidean --local

Results: QPS vs recall graphs

The recall vs QPS graph is created by: python3 plot.py --dataset fashion-mnist-784-euclidean

The results below for MariaDB are excellent. It gets more QPS than pgvector at a given recall target.

Results: create index

I am still trying to figure out how to present this data. All of the numbers are here for the time to create an index and the size of the index. The database configs for Postgres and MariaDB are shared above, and parallel index create is disabled by the config for Postgres (and not supported yet by MariaDB). The summary is:

• Indexes have a similar size with MariaDB and Postgres with halfvec. The Postgres index without halfvec is about 2X larger.
• Time to create the index for Postgres is similar with and without halfvec
• Time to create the index for MariaDB is less than for pgvector. Perhaps the best way to compare this is the time to create the index for a similar point on the QPS/recall graph (see the last section of this blog post)

Results: best QPS for a given recall

Many benchmark results are marketed via peak performance (max throughput or min response time) but these are usually constrained optimization problems -- determine peak performance that satisfies some SLA. And the SLA might be response time or efficiency (cost).

With ann-benchmarks the constraint might be recall where you seek the best QPS that satisfies a recall target. Below I share the best QPS for a given recall target along with the configuration parameters (M, ef_construction, ef_search) at which that occurs for each of the algorithms (MariaDB, pgvector with float32, pgvector with float16).

For all cases below except the first (recall = 1.000) the time to create the index is about 2X or more longer with pgvector than with MariaDB.

For all cases below the best QPS at a given recall target is much better, sometimes 3X better, with MariaDB than with pgvector.

Legend

* recall & QPS - results from the benchmark

* isecs - number of seconds to create the index for M and ef_construction (ef_cons)

Best QPS with recall >= 1.000

recall  QPS  isecs  algorithm

1.000   920  97.4   PGVector(m=16, ef_cons=192, ef_search=120)

1.000   991  89.8   PGVector_halfvec(m=16, ef_cons=256, ef_search=120)

1.000  3226 111.8   MariaDB(m=32, ef_search=10)

Best QPS with recall >= 0.99

recall  QPS  isecs  algorithm

0.990  2337  70.7   PGVector(m=16, ef_cons=96, ef_search=20)

0.991  2558  78.3   PGVector_halfvec(m=16, ef_cons=192, ef_search=20)

0.995  4745  27.4   MariaDB(m=12, ef_search=10)

Best QPS with recall >= 0.98

recall  QPS  isecs  algorithm

0.985  2383  50.4   PGVector(m=16, ef_cons=32, ef_search=20)

0.988  2608  52.8   PGVector_halfvec(m=16, ef_cons=64, ef_search=20)

0.984  5351  18.1   MariaDB(m=8, ef_search=10)

Best QPS with recall >= 0.97

recall  QPS  isecs  algorithm

0.972  3033  42.3   PGVector(m=8, ef_cons=96, ef_search=20)

0.973  3185  89.8   PGVector_halfvec(m=16, ef_cons=256, ef_search=10)

0.984  5351  18.1   MariaDB(m=8, ef_search=10)

Best QPS with recall >= 0.96

recall  QPS  isecs  algorithm

0.961  3044   50.4  PGVector(m=16, ef_cons=32, ef_search=10)

0.967  3250   52.8  PGVector_halfvec(m=16, ef_cons=64, ef_search=10)

0.961  5691   14.8  MariaDB(m=6, ef_search=10)

Best QPS with recall >= 0.95

recall  QPS  isecs  algorithm

0.961  3044  50.4   PGVector(m=16, ef_cons=32, ef_search=10)

0.967  3250  52.8   PGVector_halfvec(m=16, ef_cons=64, ef_search=10)

0.961  5691  14.8   MariaDB(m=6, ef_search=10)

Vector indexes, MariaDB & pgvector, large server, dbpedia-openai dataset

This post has results from ann-benchmarks to compare MariaDB and Postgres with a larger dataset, dbpedia-openai at 100k, 500k and 1M rows. It has 1536 dimensions and uses angular (cosine) as the distance metric. By larger I mean by the standards of what is in ann-benchmarks. This work was done by Small Datum LLC and sponsored by the MariaDB Corporation.

tl;dr

• Index create time was much less for MariaDB in all cases except the result for recall >= 0.95
• For a given recall, MariaDB gets between 2.1X and 2.7X more QPS than Postgres

Benchmark

This post has much more detail about my approach in general. I ran the benchmark for 1 session. I use ann-benchmarks via my fork of a fork of a fork at this commit.  The ann-benchmarks config files are here for MariaDB and for Postgres.

I used a larger server (Hetzner ax162-s) with 48 cores, 128G of RAM, Ubuntu 22.04 and HW RAID 10 using 2 NVMe devices. The database configuration files are here for MariaDB and for Postgres. Output from the benchmark is here.

I had ps and vmstat running during the benchmark and confirmed there weren't storage reads as the table and index were cached by MariaDB and Postgres.

The command lines to run the benchmark using my helper scripts are:
    bash rall.batch.sh v1 dbpedia-openai-100k-angular c32r128

    bash rall.batch.sh v1 dbpedia-openai-500k-angular c32r128

    bash rall.batch.sh v1 dbpedia-openai-1000k-angular c32r128

Results: QPS vs recall

These charts show the best QPS for a given recall. MariaDB gets about 2X more QPS than Postgres for a specific recall level 

With 100k rows

With 500k rows

With 1M rows

Results: create index

The database configs for Postgres and MariaDB are shared above, and parallel index create is disabled by the config for Postgres and not supported yet by MariaDB. The summary is:

• index sizes are similar between MariaDB and pgvector with halfvec
• time to create the index varies a lot and it is better to consider this in the context of recall which is done in next section

Legend

• M - value for M when creating the index
• cons - value for ef_construction when creating the index
• secs - time in seconds to create the index
• size(MB) - index size in MB

Table sizes:

* Postgres is 7734M

* MariaDB is 7856M

                -- pgvector --          -- pgevector --

                -- float32 --           -- halfvec   --

M       cons    secs    size(MB)        secs    size(MB)

 8       32       458   7734             402    3867

16       32       720   7734             655    3867

 8       64       699   7734             627    3867

16       64      1144   7734            1029    3867

32       64      2033   7734            1880    3867

 8       96       934   7734             843    3867

16       96      1537   7734            1382    3867

32       96      2730   7734            2482    3867

48       96      4039   7734            3725    3867

 8      192      1606   7734            1409    3867

16      192      2778   7734            2435    3867

32      192      4683   7734            4154    3867

48      192      6830   7734            6106    3867

64      192      8601   7734            7831    3958

 8      256      2028   7734            1764    3867

16      256      3609   7734            3151    3867

32      256      5838   7734            5056    3867

48      256      8224   7734            7283    3867

64      256     11031   7734            9931    3957

mariadb

M       secs    size(MB)

 4        318   3976

 5        372   3976

 6        465   3976

 8        717   3976

12       1550   3976

16       2887   3976

24       7248   3976

32      14120   3976

48      36697   3980

Results: best QPS for a given recall

Many benchmark results are marketed via peak performance (max throughput or min response time) but these are usually constrained optimization problems -- determine peak performance that satisfies some SLA. And the SLA might be response time or efficiency (cost).

With ann-benchmarks the constraint is recall. Below I share the best QPS for a given recall target along with the configuration parameters (M, ef_construction, ef_search) at which that occurs for each of the algorithms (MariaDB, pgvector with float32, pgvector with float16/halfvec).

Summary

• Postgres does not get recall=1.0 for the values of M, ef_construction and ef_search I used
• Index create time was much less for MariaDB in all cases except the result for recall >= 0.95
• For a given recall target, MariaDB gets between 2.1X and 2.7X more QPS than Postgres

Legend:

• recall, QPS - best QPS at that recall
• isecs - time to create the index in seconds
• m= - value for M when creating the index
• ef_cons= - value for ef_construction when creating the index
• ef_search= - value for ef_search when running queries

Best QPS with recall >= 1.000, pgvector did not reach the recall target

recall  QPS     isecs

1.000    20     36697   MariaDB(m=48, ef_search=40)

Best QPS with recall >= 0.99, MariaDB gets >= 2.2X more QPS than Postgres

recall  QPS     isecs

0.990   287     8224    PGVector(m=48, ef_cons=256, ef_search=40)

0.990   321     7283    PGVector_halfvec(m=48, ef_cons=256, ef_search=40)

0.992   731     7248    MariaDB(m=24, ef_search=10)

Best QPS with recall >= 0.98, MariaDB gets >= 2.7X more QPS than Postgres

recall  QPS     isecs

0.984    375    4683    PGVector(m=32, ef_cons=192, ef_search=40)

0.984    418    4154    PGVector_halfvec(m=32, ef_cons=192, ef_search=40)

0.981   1130    2887    MariaDB(m=16, ef_search=10)

Best QPS with recall >= 0.97, MariaDB gets >= 2.3X more QPS than Postgres

recall  QPS     isecs

0.974    440    6830    PGVector(m=48, ef_cons=192, ef_search=20)

0.973    483    6106    PGVector_halfvec(m=48, ef_cons=192, ef_search=20)

0.981   1130    2887    MariaDB(m=16, ef_search=10)

Best QPS with recall >= 0.96, MariaDB gets >= 2.2X more QPS than Postgres

recall  QPS     isecs

0.962    568    4683    PGVector(m=32, ef_cons=192, ef_search=20)

0.961    635    4154    PGVector_halfvec(m=32, ef_cons=192, ef_search=20)

0.965   1433    1550    MariaDB(m=12, ef_search=10)

Best QPS with recall >= 0.95, MariaDB gets >= 2.1X more QPS

recall  QPS     isecs

0.953    588    2730    PGVector(m=32, ef_cons=96, ef_search=20)

0.957    662    1382    PGVector_halfvec(m=16, ef_cons=96, ef_search=40)

0.965   1433    1550    MariaDB(m=12, ef_search=10)