The insert benchmark on a small server, IO-bound workload : Postgres 19 beta1

This has results for Postgres versions 19 beta1, 18.4 and 17.10 with the Insert Benchmark on a small server using a cached and CPU-bound workload. I also used MySQL 8.4.8 to see where performance was different.

Postgres continues to be boring in a good way. It is hard to find performance regressions.

 tl;dr

• create index (the l.x step) is faster in Postgres 19beta1. A Postgres expert told me that the sort algorithm was changed to be more CPU efficient
• the write heavy steps (l.i1, l.i2) are 15% and 9% faster in 19 beta1 vs Postgres 17.10
• the second write heavy step (l.i2) is more than 20X faster in MySQL 8.4.8 vs Postgres thanks to the CPU overhead from get_actual_variable_range. I have written about this before.

Builds, configuration and hardware

I compiled Postgres from source using -O2 -fno-omit-frame-pointer for versions 19 beta1, 18.4 and 17.10.

I compiled MySQL 8.4.8 from source as well.

The server is an Beelink SER7 with a Ryzen 7 7840HS CPU with 8 cores and AMD SMT disabled, 32G of RAM. Storage is one SSD for the OS and an NVMe SSD for the database using ext-4 with discard enabled. The OS is Ubuntu 24.04.

For 17.10 the config file is named conf.diff.cx10a_c8r32 (cx10a) and is here.

For Postgres 18 and 19 the config file is conf.diff.cx10b_c8r32 (cx10b) which is as similar as possible to the config for version 17.

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

The Benchmark

The benchmark is explained here and is run with 1 client.

The point query (qp100, qp500, qp1000) and range query (qr100, qr500, qr1000) steps are run for 3600 seconds each.

The benchmark steps are:

• l.i0
• insert 800M 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 4M rows per table 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 1M rows are inserted and deleted per table.
• Wait for S seconds after the step finishes to reduce variance during the read-write benchmark steps that follow. The value of S 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. 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. This step is frequently not IO-bound for the IO-bound workload.
• 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 summary with charts is here.

This table lists relative QPS per benchmark step and relative QPS is:
    (QPS for my version / QPS for Postgres 17.10)

The background in the table cells is blue for big improvements and yellow for regressions. There are no regressions here. 

The improvements here for Postgres 19 beta1 are similar to what I reported for the cached workload.

The index create (l.x) step is much faster in 19.10. I usually ignore results on this step but I am curious if something was done in 19.10 to improve index create. A Postgres expert told me that the sort algorithm for index create was changed in version 19 to be more CPU efficient.

For the write-heavy steps (l.i1, l.i2):

• there are large improvements in 19 beta1 (15% and 9%). The CPU overhead is lower in 19 beta1 compared to 17.10 (see cpupq here).
• throughput for the l.i2 step is more than 20X larger for MySQL than for Postgres. From vmstat I see that the CPU overhead (cpupq here) is more than 10X larger with Postgres vs MySQL. From flamegraphs the problem is the CPU overhead in get_actual_variable_range. I have written about this before (see here). The Postgres query planner uses too much CPU skipping old versions to figure out selectivity for a query and there are too many old versions because Postgres doesn't collect them ASAP, vacuum takes time. The flamegraphs are in subdirectories here.

For the range query steps (qr100, qr500, qr1000) throughput is ~3% less in 19 beta1 vs 17.10 and ~1% less in 18.4 vs 17.10. For 19 beta1 there is a small increase in CPU overhead (see cpupq here, here and here). I already have flamegraphs for MySQL 8.4.8 and Postgres 19 beta1, soon I will have them for Postgres 17.10 and 18.4 to try and explain this.

dbms	l.i0	l.x	l.i1	l.i2	qr100	qp100	qr500	qp500	qr1000	qp1000
PG 17.10	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00
PG 18.4	1.01	1.03	1.00	1.00	0.98	1.00	0.99	0.99	0.99	0.99
PG 19 beta1	1.01	1.15	1.05	1.09	0.97	1.01	0.96	1.01	0.97	1.00
MySQL 8.4.8	0.77	0.89	0.76	21.62	0.61	1.07	0.66	0.93	0.85	0.84

The insert benchmark on a small server, cached workload : Postgres 19 beta1

This has results for Postgres versions 19 beta1, 18.4 and 17.10 with the Insert Benchmark on a small server using a cached and CPU-bound workload.

Postgres continues to be boring in a good way. It is hard to find performance regressions.

 tl;dr

• I don't see regressions here in 19 beta1
• I see some improvements here in 19 beta1
• index create (l.x) is faster but the step is short-running so I don't assume much from this
• the write-heavy steps (l.i1, l.i2) are faster and CPU overhead is lower in 19 beta1, I hope to explain why the CPU overhead is lower, but that waits for another day.

Builds, configuration and hardware

I compiled Postgres from source using -O2 -fno-omit-frame-pointer for versions 19 beta1, 18.4 and 17.10.

The server is an Beelink SER7 with a Ryzen 7 7840HS CPU with 8 cores and AMD SMT disabled, 32G of RAM. Storage is one SSD for the OS and an NVMe SSD for the database using ext-4 with discard enabled. The OS is Ubuntu 24.04.

For 17.10 the config file is named conf.diff.cx10a_c8r32 (cx10a) and is here.

For Postgres 18 and 19 the config file is conf.diff.cx10b_c8r32 (cx10b) which is as similar as possible to the config for version 17.

The Benchmark

The benchmark is explained here and is run with 1 client.

The point query (qp100, qp500, qp1000) and range query (qr100, qr500, qr1000) steps are run for 3600 seconds each.

The benchmark steps are:

• l.i0
• insert 30M 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 per table 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 per table.
• Wait for S seconds after the step finishes to reduce variance during the read-write benchmark steps that follow. The value of S 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. 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. This step is frequently not IO-bound for the IO-bound workload.
• 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 summary with charts is here.

This table lists relative QPS per benchmark step and relative QPS is:
    (QPS for my version / QPS for Postgres 17.10)

The background in the table cells is blue for big improvements and yellow for regressions. There are no regressions here. 

The index create (l.x) step is much faster in 19.10. I usually ignore results on this step but I am curious if something was done in 19.10 to improve index create. But this step takes between 1 and 2 minutes and I am reluctant to assume too much from a short running step.

For the write-heavy steps (l.i1, l.i2)

• there are small improvements in 18.4
• there are large improvements in 19 beta1. The CPU overhead is lower in 19 beta1 compared to 17.10, ~15% lower for l.i1 and ~10% lower for l.i2. Hopefully I can explain why. But the lower CPU overhead might explain the improved performance in 19 beta1. Some of the metrics from iostat and vmstat are here.

dbms	l.i0	l.x	l.i1	l.i2	qr100	qp100	qr500	qp500	qr1000	qp1000
17.10	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00
18.4	1.00	1.03	1.02	1.07	0.99	1.00	1.00	1.00	1.01	1.00
19 beta1	1.01	1.16	1.23	1.22	0.99	1.00	0.99	0.99	1.00	1.00

The Insert Benchmark vs MariaDB 10.2 to 13.0 on a 32-core server

This has results for MariaDB versions 10.2 through 13.0 vs the Insert Benchmark on a 32-core server. The goal is to see how performance changes over time to find regressions or highlight improvements. My previous post has results from a 24-core server.  Differences between these servers include:

• RAM - 32-core server has 128G, 24-core server has 64G
• fsync latency - 32-core has an SSD with high fsync latency, while it is fast on the 24-core server
• sockets - 32-core server has 1 CPU socket, 24-core server has two
• CPU maker  - 32-core server uses an AMD Threadripper, 24-core server has an Intel Xeon
• cores - obviously it is 32 vs 24, Intel HT and AMD SMT are disabled

The results here for modern MariaDB are great for the CPU-bound workload but not for the IO-bound workload.. They were great for both on the 24-core server. The regressions are likely caused by the extra fsync calls that are done because the equivalent of equivalent of innodb_flush_method =O_DIRECT_NO_FSYNC was lost with the new options that replace innodb_flush_method starting in MariaDB 11.4. I created MDEV-33545 to request support for it. The workaround is to use an SSD that doesn't have high fsync latency, which is always a good idea, but not always possible.

tl;dr

• for a CPU-bound workload
• the write-heavy steps are much faster in 13.0.0 than 10.2.30
• the read-heavy steps get similar QPS in 13.0.0 and 10.2.30
• this is similar to the results on the 24-core server
• for an IO-bound workload
• the initial load (l.i0) is much faster in 13.0.0 than 10.2.30
• the random write step (l.i1) is slower in 13.0.0 than 10.2.30 because fsync latency
• the range query step (qr100) gets similar QPS in 13.0.0 and 10.2.30
• the point query step (qp100) is much slower in 13.0.0 than 10.2.30 because fsync latency

Builds, configuration and hardware

I compiled MariaDB from source for versions 10.2.30, 10.2.44, 10.3.39, 10.4.34, 10.5.29, 10.6.25, 10.11.16, 11.4.10, 11.8.6, 12.3.1 and 13.0.0.

The server has 24-cores, 2-sockets and 64G of RAM. Storage is 1 NVMe device with ext-4 and discard enabled. The OS is Ubuntu 24.04. Intel HT is disabled.

The my.cnf files are here for: 10.2, 10.3, 10.4, 10.5, 10.6, 10.11, 11.4, 11.8, 12.3 and 13.0. 

For MariaDB 10.11.16 I used both the z12a config, as I did for all 10.x releases, and also used the z12b config. The difference is that the z12a config uses innodb_flush_method =O_DIRECT_NO_FSYNC while the z12b config uses =O_DIRECT. And the z12b config is closer to the configs used for MariaDB because with the new variables that replaced innodb_flush_method, we lose support for the equivalent of =O_DIRECT_NO_FSYNC.

And I write about this because the extra fsync calls that are done when the z12b config is used have a large impact on throughput on a server that uses an SSD with high fsync latency, which causes perf regressions for all DBMS versions that used the z12b config -- 10.11.16, 11.4, 11.8, 12.3 and 13.0.

The Benchmark

The benchmark is explained here and is run with 12 clients with a table per client. I repeated it with two workloads:

• CPU-bound
• the values for X, Y, Z are 10M, 16M, 4M
• IO-bound
• the values for X, Y, Z are 300M, 4M, 1M

The point query (qp100, qp500, qp1000) and range query (qr100, qr500, qr1000) steps are run for 1800 seconds each.

The benchmark steps are:

• l.i0
• insert X 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 Y rows per table 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 Z rows are inserted and deleted per table.
• Wait for S seconds after the step finishes to reduce variance during the read-write benchmark steps that follow. The value of S 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. 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. This step is frequently not IO-bound for the IO-bound workload.
• 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: overview

The performance reports are here for the CPU-bound and IO-bound workloads.

The summary sections from the performances report have 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. 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 the result for some version. The base version is MariaDB 10.2.30.

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

This statement doesn't apply to this blog post, but I keep it here for copy/paste into future posts. 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: CPU-bound

The performance summary is here.

The summary per benchmark step, where rQPS means relative QPS.

• l.i0
• MariaDB 13.0.0 is faster than 10.2.30, rQPS is 1.47
• CPU per insert (cpupq) and KB written to storage per insert (wKBpi) are much smaller in 13.0.0 than 10.2.30 (see here)
• l.x
• I will ignore this
• l.i1, l.i2
• MariaDB 13.0.0 is faster than 10.2.30, rQPS is 1.50 and 1.37
• CPU per write (cpupq) is much smaller in 13.0.0 than 10.2.30 (see here)
• qr100, qr500, qr1000
• MariaDB 13.0.0 and 10.2.30 have similar QPS (rQPS is close to 1.0)
• CPU per query (cqpq) is similar in 13.0.0 and 10.2.30 (see here)
• qp100, qp500, qp1000
• MariaDB 13.0.0 and 10.2.30 have similar QPS (rQPS is close to 1.0)
• CPU per query (cqpq) is similar in 13.0.0 and 10.2.30 (see here)

Results: IO-bound

The performance summary is here.

The summary per benchmark step, where rQPS means relative QPS.

• l.i0
• MariaDB 13.0.0 is faster than 10.2.30, rQPS is 1.25
• CPU per insert (cpupq) and KB written to storage per insert (wKBpi) are much smaller in 13.0.0 than 10.2.30 (see here)
• l.x
• I will ignore this
• l.i1, l.i2
• MariaDB 13.0.0 is slower than 10.2.30 for l.i1, rQPS is 0.68
• MariaDB 13.0.0 is faster than 10.2.30 for l.i2, rQPS is 1.31. I suspect it is faster on l.i2 because it inherits less MVCC GC debt from l.i1 because it was slower on l.i1. So I won't celebrate this result and will focus on l.i1.
• From the normalized vmstat and iostat metrics I don't see anything obvious. But I do see a reduction in storage reads/s (rps) and storage read MB/s (rMBps). And this reduction starts in 10.11.16 with the z12b config and continues to 13.0.0. This does not occur on the earlier releases that are eable to use the z12a config. So I am curious if the extra fsyncs are the root cause.
• From the iostat summary for l.i1 that includes average values for all iostat columns, and these are not divided by QPS, what I see a much higher rate for fsyncs (f/s) as well as an increase in read latency. For MariaDB 10.11.16 the value for r_await is 0.640 with the z12a config vs 0.888 with the z12b config. I assume that more frequent fsync calls hurt read latency. The iostat results don't look great for either the z12a or z12b config and the real solution is to avoid using an SSD with high fsync latency, but that isn't always possible.
• qr100, qr500, qr1000
• no DBMS versions were able to sustain the target write rate for qr500 or qr1000 so I ignore them. This server needs more IOPs capacity -- a second SSD, and both SSDs needs power loss protection to reduce fsync latency.
• MariaDB 13.0.0 and 10.2.30 have similar performance, rQPS is 0.96. The qr100 step for MariaDB 13.0.0 might not suffer from fsync latency like the qp100 step because it does less read IO per query than qp100 (see rpq here).
• qp100, qp500, qp1000
• no DBMS versions were able to sustain the target write rate for qp500 or qp1000 so I ignore them. This server needs more IOPs capacity -- a second SSD, and both SSDs needs power loss protection to reduce fsync latency.
• MariaDB 13.0.0 is slower than 10.2.30, rQPS is 0.62
• From the normalized vmstat and iostat metrics there are increases in CPU per query (cpupq) and storage reads per query (rpq) for all DBMS versions that use the z12b config (see here).
• From the iostat summary for qp100 that includes average values for all iostat columns the read latency increases for all DBMS versions that use the z12b config. I blame interference from the extra fsync calls.

The Insert Benchmark vs MariaDB 10.2 to 13.0 on a 24-core server

This has results for MariaDB versions 10.2 through 13.0 vs the Insert Benchmark on a 24-core server. The goal is to see how performance changes over time to find regressions or highlight improvements.

MariaDB 13.0.0 is faster than 10.2.30 on most benchmark steps and otherwise as fast as 10.2.30. This is a great result.

tl;dr

• for a CPU-bound workload
• the write-heavy steps are much faster in 13.0.0 than 10.2.30
• the read-heavy steps get similar QPS in 13.0.0 and 10.2.30
• for an IO-bound workload
• most of the write-heavy steps are much faster in 13.0.0 than 10.2.30
• the point-query heavy steps get similar QPS in 13.0.0 and 10.2.30
• the range-query heavy steps get more QPS in 13.0.0 than 10.2.30

Builds, configuration and hardware

I compiled MariaDB from source for versions 10.2.30, 10.2.44, 10.3.39, 10.4.34, 10.5.29, 10.6.25, 10.11.16, 11.4.10, 11.8.6, 12.3.1 and 13.0.0.

The server has 24-cores, 2-sockets and 64G of RAM. Storage is 1 NVMe device with ext-4 and discard enabled. The OS is Ubuntu 24.04. Intel HT is disabled.

The my.cnf files are here for: 10.2, 10.3, 10.4, 10.5, 10.6, 10.11, 11.4, 11.8, 12.3 and 13.0.

The Benchmark

The benchmark is explained here and is run with 8 clients with a table per client. I repeated it with two workloads:

• CPU-bound
• the values for X, Y, Z are 10M, 16M, 4M
• IO-bound
• the values for X, Y, Z are 250M, 4M, 1M

The point query (qp100, qp500, qp1000) and range query (qr100, qr500, qr1000) steps are run for 1800 seconds each.

The benchmark steps are:

• l.i0
• insert X 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 Y rows per table 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 Z rows are inserted and deleted per table.
• Wait for S seconds after the step finishes to reduce variance during the read-write benchmark steps that follow. The value of S 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. 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. This step is frequently not IO-bound for the IO-bound workload.
• 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: overview

The performance reports are here for the CPU-bound and IO-bound workloads.

The summary sections from the performances report have 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. 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 the result for some version. The base version is MariaDB 10.2.30.

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

This statement doesn't apply to this blog post, but I keep it here for copy/paste into future posts. 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: CPU-bound

The performance summary is here.

The summary per benchmark step, where rQPS means relative QPS.

• l.i0
• MariaDB 13.0.0 is faster than 10.2.30 (rQPS is 1.22)
• KB written to storage per insert (wKBpi) and CPU per insert (cpupq) are smaller in 13.0.0 than 10.2.30, see here
• l.x
• I will ignore this
• l.i1, l.i2
• MariaDB 13.0.0 is faster than 10.2.30 (rQPS is 1.21 and 1.45)
• for l.i1, CPU per insert (cpupq) is smaller in 13.0.0 than 10.2.30 but KB written to storage per insert (wKBpi) and the context switch rate (cspq) are larger in 13.0.0 than 10.2.30, see here
• for l.i2, CPU per insert (cpupq) and KB written to storage per insert (wKBpi) are smaller in 13.0.0 than 10.2.30 but the context switch rate (cspq) is larger in 13.0.0 than 10.2.30, see here
• qr100, qr500, qr1000
• MariaDB 13.0.0 and 10.2.30 have similar QPS (rQPS is close to 1.0)
• the results from vmstat and iostat are less useful here because the write rate in 10.2 to 10.4 was much larger than 10.5+. While the my.cnf settings are as close as possible across all versions, it looks like furious flushing was enabled in 10.2 to 10.4 and I need to figure out whether it is possible to disable that.
• qp100, qp500, qp1000
• MariaDB 13.0.0 and 10.2.30 have similar QPS (rQPS is close to 1.0)
• what I wrote above for vmstat and iostat with the qr* test also applies here

Results: IO-bound

The performance summary is here.

The summary per benchmark step, where rQPS means relative QPS.

• l.i0
• MariaDB 13.0.0 is faster than 10.2.30 (rQPS is 1.16)
• KB written to storage per insert (wKBpi) and CPU per insert (cpupq) are smaller in 13.0.0 than 10.2.30, see here
• l.x
• I will ignore this
• l.i1, l.i2
• MariaDB 13.0.0 and 10.2.30 have the same QPS for l.i1 while 13.0.0 is faster for l.i2 (rQPS is 1.03 and 3.70). It is odd that QPS drops from 12.3.1 to 13.0.0 on the l.i1 step.
• for l.i1, CPU per insert (cpupq) and the context switch rate (cspq) are larger in 13.0.0 than 12.3.1, see here. The flamegraphs, that I have not shared, look similar. From iostat results there is much more discard (TRIM, SSD GC) in progress with 13.0.0 than 12.3.1 and the overhead from that might explain the difference.
• for l.i2, almost everything looks better in 13.0.0 than 10.2.30. Unlike what occurs for the l.i1 step, the results for 13.0.0 are similar to 12.3.1, see here.
• qr100, qr500, qr1000
• no DBMS versions were able to sustain the target write rate for qr1000 so I ignore that step
• MariaDB 13.0.0 and 10.2.30 have similar QPS (rQPS is close to 1.0)
• the results from vmstat and iostat are less useful here because the write rate in 10.2 to 10.4 was much larger than 10.5+. While the my.cnf settings are as close as possible across all versions, it looks like furious flushing was enabled in 10.2 to 10.4 and I need to figure out whether it is possible to disable that.
• qp100, qp500, qp1000
• no DBMS versions were able to sustain the target write rate for qr1000 so I ignore that step
• MariaDB 13.0.0 is faster than 10.2.30 (rQPS is 1.17 and 1.56)
• what I wrote above for vmstat and iostat with the qr* test also applies here

The insert benchmark on a small server : Postgres 12.22 through 18.3

This has results for Postgres versions 12.22 through 18.3 with the Insert Benchmark on a small server. My previous post for the same hardware with results up to Postgres 18.1 is here. This post also has results for:

• all 17.x releases from 17.0 through 17.9 
• 18.2 with and without full page writes enabled
• both 1 and 4 users

Postgres continues to be boring in a good way. It is hard to find performance regressions. Performance wasn't always stable, but I am reluctant to expect it to show no changes because there are sources of variance beyond the DBMS, especially HW (a too-hot SSD or CPU will run slower). Sometimes perf changes because there are obvious perf bugs, sometimes it changes for other reasons.

 tl;dr for a  CPU-bound workload

• performance is stable from Postgres 12 through 18
• performance is stable from Postgres 17.0 through 17.9
• disabling full-page writes improves throughput on write-heavy benchmark steps

tl;dr for an IO-bound workload

• performance is mostly stable from Postgres 12 through 18
• performance is stable from Postgres 17.0 through 17.9
• disabling full-page writes improves throughput on write-heavy benchmark steps
• in a few cases there are large improvements to point-query throughput on the qp1000 benchmark step. I will try to explain that soon.

Builds, configuration and hardware

I compiled Postgres from source using -O2 -fno-omit-frame-pointer for versions 12.22, 13.23, 14.22, 15.17, 16.13, 17.0 to 17.9, 18.2 and 18.3.

The server is an Beelink SER7 with a Ryzen 7 7840HS CPU with 8 cores and AMD SMT disabled, 32G of RAM. Storage is one SSD for the OS and an NVMe SSD for the database using ext-4 with discard enabled. The OS is Ubuntu 24.04.

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

For Postgres 18 in most cases I used a config named conf.diff.cx10b_c8r32 (aka cx10b) which is as similar as possible to the configs for versions 17 and earlier. But for tests with full-page writes disabled I used additional configs to compare with results from the cx10b config.

• cx10b_fpw0
• this adds full_page_writes=off while cx10b has =on
• cx10b_wallz4
• this adds wal_compression=lz4 while cx10b has =off
• cx10b_fpw0_wallz4
• this adds full_page_writes=off and wal_compression=lz4

The Benchmark

The benchmark is explained here and is run with 1 and 4 clients. In each case each client uses a separate table. I repeated it with two workloads:

• CPU-bound
• for 1 user the values for X, Y, Z are 30M, 40M, 10M
• for 4 users the values for X, Y, Z are 10M, 16M, 4M
• IO-bound
• for 1 user the values for X, Y, Z are 800M, 4M, 1M
• for 4 users the values for X, Y, Z are 200M, 4M, 1M

The point query (qp100, qp500, qp1000) and range query (qr100, qr500, qr1000) steps are run for 1800 seconds each.

The benchmark steps are:

• l.i0
• insert X 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 Y rows per table 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 Z rows are inserted and deleted per table.
• Wait for S seconds after the step finishes to reduce variance during the read-write benchmark steps that follow. The value of S 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. 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. This step is frequently not IO-bound for the IO-bound workload.
• 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: overview

The performance reports are here for:

• CPU-bound
• Latest point releases: 1 user and 4 users
• All 17.x releases: 1 user and 4 users
• Full-page writes enabled and disabled: 1 user and 4 users
• IO-bound
• Latest point releases: 1 user and 4 users
• All 17.x releases: 1 user and 4 users
• Full-page writes enabled and disabled: 1 user and 4 users

The summary sections from the performances report have 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. 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 the result for some version. The base version is Postgres 12.22 for the latest point releases comparison, 17.0 for the 17.x releases comparison and 18.2 with the cx10b config for the full-page writes comparison. 

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

This statement doesn't apply to this blog post, but I keep it here for copy/paste into future posts. 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: CPU-bound

The performance summaries are here for:

• 1 user: latest point releases, all 17.x releases and full-page writes
• 4 users: latest point releases, all 17.x releases and full-page writes

For latest point releases at 1 user

• there is either no change or a small improvement for l.i0 (load in PK order), l.x (create indexes) and the read-write tests (qr*, qp*).
• for l.i1 and l.i2 (random write-only) throughput drops by 5% to 10% from 12.22 to 13.23 and has been stable since then (throughput in 18.2 is similar to 13.23. The CPU per operation overhead (cpupq here) increases after 12.22 for the l.i2 step but there wasn't an obvious increase for the l.i1 step - but the way I measure this is far from perfect. The results I share here are worse than what I measured in December 2025.

For latest point releases at 4 users

• there might be a small (3%) regression for l.i0 (load in PK order) in 18.2 vs 12.22. Perhaps this is noise. From vmstat and iostat metrics there aren't obvious changes.
• throughput in 18.2 is better than 12.22 for all other benchmark steps

For all 17.x releases at 1 user

• throughput is stable from 17.0 to 17.9 for all benchmark steps except l.i1 and l.i2 (random writes) where there might be a 5% regression late in 17.x. This might be from new CPU overhead - see cpupq here.

For all 17.x releases at 4 users

• throughput is stable with small improvements from 17.0 to 17.9

For full-page writes at 1 user

• throughput improves by ~5% for l.i1 and l.i2 (random writes) when full-page writes are disabled  and KB written to storage per commit drops by ~20% -- see wkbpi here.
• enabling wal_compression=lz4 decreases write throughput for all write-heavy steps when full-page writes are enabled. The impact is smaller when full page writes are disabled.

For full-page writes at 4 users

• throughput improves by <= 5% for all write-heavy steps when full-page writes are disabled
• the impact from wal_compression=lz4 isn't obvious

Results: IO-bound

The performance summaries are here for:

• 1 user:  latest point releases, all 17.x releases and full-page writes
• 4 users: latest point releases, all 17.x releases and full-page writes

For latest point releases at 1 user

• there are small (<= 10%) improvements for l.i0 (load in PK order) and l.x (create index). I don't see anything obvious in vmstat and iostat metrics to explain this.
• there are small (<= 10%) regressions for l.i1 and l.i2 (random writes) that might be from a sequence of small regressions from 13.x through 18.x. I don't see anything obvious in vmstat and iostat metrics to explain this.
• throughput is unchanged for the range-query read+write tests (qr*)
• throughput improves by ~1.4X for the point-query read+write tests (qp*). This improvement arrived in 13.x. This can be explained by large drops in CPU overhead (cpupq) and context switch rates (cspq) -- see here.
• the results here are similar to what I measured in December 2025

For latest point releases at 4 users

• there are small (~10%) regressions for l.i0 (load in PK order) that arrived in 17.x. The context switch rate (cspq) increases in 17.x. 
• there are small (<= 20%) improvements for l.x (create index) that arrived in 13.x
• there are large regressions for l.i1 and l.i2 (random writes) that arrive in 15.x through 18.x. There are large increases in CPU overhead (cpupq) -- see here.
• throughput is unchanged for the range-query read+write tests (qr*)
• throughput improves for the point-query read+write tests (qp*) at higher write rates (qp500, qp1000).

For all 17.x releases at 1 user

• throughput is stable with a few exceptions
• for qp1000 (point-query, read+write) it improves by ~5% in 17.1 and is then stable to 17.9
• in 17.9 there are large (~1.4x) improvements for all of the point-query, read+write tests
• the changes in throughput for qp1000 might be explained by a small drop in CPU overhead per query (cpupq) that arrived in 17.1 and a large drop that arrived in 17.9 -- see here.

For all 17.x releases at 4 users

• throughput for most steps (l.i0, l.x, qr*, qp100, qp500) is stable
• throughput for l.i1 and l.i2 (random writes) has more variance
• throughput for qp1000 drops by up to 10% from 17.3 through 17.8 and in those cases the CPU overhead increased -- see cpupq here.

For full-page writes at 1 user

• throughput improves by 6% for l.i1 (random writes) when full-page writes are disabled
• throughput improved for qp* tests when either full-page writes were disabled or lz4 was used for log_compression. That is harder to explain, perhaps it is noise.

For full-page writes at 4 users

• throughput improves by 20% for l.i1 (random writes) when full-page writes are disabled
• throughput improved for qp* tests when either full-page writes were disabled or lz4 was used for log_compression. That is harder to explain, perhaps it is noise.

n_dead_tup vs n_live_tup

The tables below show the ratio: n_dead_tup / (n_dead_tup + n_live_tup) for the CPU-bound and IO-bound workloads using 1 user (and one table). These were measured at the end of each benchmark step.

CPU-bound

        12.22   18.3

l.i0    0.000   0.000

l.x     0.000   0.000

l.i1    0.065   0.035

l.i2    0.045   0.020

qr100   0.006   0.006

qp100   0.012   0.012

qr500   0.040   0.040

qp500   0.021   0.024

qr1000  0.031   0.036

qp1000  0.040   0.003

IO-bound

        12.22   18.3

l.i0    0.000   0.000

l.x     0.000   0.000

l.i1    0.005   0.005

l.i2    0.006   0.006

qr100   0.000   0.000

qp100   0.000   0.000

qr500   0.002   0.002

qp500   0.003   0.003

qr1000  0.005   0.005

qp1000  0.007   0.007

 

MariaDB innovation: binlog_storage_engine, 32-core server, Insert Benchmark

MariaDB 12.3 has a new feature enabled by the option binlog_storage_engine. When enabled it uses InnoDB instead of raw files to store the binlog. A big benefit from this is reducing the number of fsync calls per commit from 2 to 1 because it reduces the number of resource managers from 2 (binlog, InnoDB) to 1 (InnoDB). This work was done by Small Datum LLC and sponsored by the MariaDB Foundation.

My previous post had results for sysbench with a small server. This post has results for the Insert Benchmark with a large (32-core) server. Both servers use an SSD that has has high fsync latency. This is probably a best-case comparison for the feature. If you really care, then get enterprise SSDs with power loss protection. But you might encounter high fsync latency on public cloud servers.

While throughput improves with the InnoDB doublewrite buffer disabled, I am not suggesting people do that for production workloads without understanding the risks it creates.

tl;dr for a CPU-bound workload

• throughput for write-heavy steps is larger with the InnoDB doublewrite buffer disabled
• throughput for write-heavy steps is much larger with the binlog storage engine enabled
• throughput for write-heavy steps is largest with both the binlog storage engine enabled and the InnoDB doublewrite buffer disabled. In this case it was up to 8.9X larger.

tl;dr for an IO-bound workload

• see the tl;dr above
• the best throughput comes from enabling the binlog storage engine and disabling the InnoDB doublewrite buffer and was 3.26X.

Builds, configuration and hardware

I compiled MariaDB 12.3.1 from source.

The server has 32-cores and 128G of RAM. Storage is 1 NVMe device with ext-4 and discard enabled. The OS is Ubuntu 24.04. AMD SMT is disabled. The SSD has high fsync latency.

I tried 4 my.cnf files:

• z12b_sync
• my.cnf.cz12b_sync_c32r128 (z12b_sync) uses sync-on-commit for the binlog and InnoDB
• z12c_sync
• my.cnf.cz12c_sync_c32r128 (z12c_sync) is like z12b_sync and then enables the binlog storage engine
• z12b_sync_dw0
• my.cnf.cz12b_sync_dw0_c32r128 (z12b_sync_dw0) is like z12b_sync and then disables the InnoDB doublewrite buffer
• z12c_sync_dw0
• my.cnf.cz12c_sync_dw0_c32r128 (z12c_sync_dw0) is like z12c_sync and then disables the InnoDB doublewrite buffer

The Benchmark

The benchmark is explained here. It was run with 12 clients for two workloads:

• CPU-bound - the database is cached by InnoDB, but there is still much write IO
• IO-bound - most, but not all, benchmark steps are IO-bound

The benchmark steps are:

• l.i0
• insert XM rows per table in PK order. The table has a PK index but no secondary indexes. There is one connection per client. X is 10M for CPU-bound and 300M for IO-bound.
• l.x
• create 3 secondary indexes per table. There is one connection per client.
• l.i1
• use 2 connections/client. One inserts XM rows per table 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. X is 16M for CPU-bound and 4M for IO-bound.
• l.i2
• like l.i1 but each transaction modifies 5 rows (small transactions) and YM rows are inserted and deleted per table. Y is 4M for CPU-bound and 1M for IO-bound.
• Wait for S seconds after the step finishes to reduce MVCC GC debt and perf variance during the read-write benchmark steps that follow. The value of S 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. 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. This step is frequently not IO-bound for the IO-bound workload. This step runs for 1800 seconds.
• 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: summary

The performance reports are here for CPU-bound and IO-bound.

The summary sections from the performance reports have 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. 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. And from the third table for the IO-bound workload I see that there were failures to meet the SLA for qp500, qr500, qp1000 and qr1000.

I use relative QPS to explain how performance changes. It is: (QPS for $me / QPS for $base) where $me is the result for some version $base is the result from the base version.

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 yellow for regressions and blue for improvements.

I often use context switch rates as a proxy for mutex contention.

Results: CPU-bound

The summary is here.

Some of the improvements here are huge courtesy of storage with high fsync latency.

Throughput is much better with the binlog storage engine enabled when the InnoDB doublewrite buffer is also enabled. Comparing z12b_sync and z12c_sync (z12c_sync uses the binlog storage engine):

• throughput for l.i0 (load in PK order) is 3.63X larger for z12c_sync
• throughput for l.i1 (write-only, larger transactions) is 2.80X larger for z12c_sync
• throughput for l.i2 (write-only, smaller transactions) is 8.13X larger for z12c_sync

There is a smaller benefit from only disabling the InnoDB doublewrite buffer. Comparing z12b_sync and z12b_sync_dw0:

• throughput for l.i0 (load in PK order) is the same for z12b_sync and z12b_sync_dw0
• throughput for l.i1 (write-only, larger transactions) is 1.14X larger for z12b_sync_dw0
• throughput for l.i2 (write-only, smaller transactions) is 1.93X larger for z12b_sync_dw0

The largest benefits come from using the binlog storage engine and disabling the InnoDB doublewrite buffer. Comparing z12b_sync and z12c_sync_dw0:

• throughput for l.i0 (load in PK order) is 3.61X larger for z12c_sync_dw0
• throughput for l.i1 (write-only, larger transactions) is 3.03X larger for z12b_sync_dw0
• throughput for l.i2 (write-only, smaller transactions) is 8.90X larger for z12b_sync_dw0

Results: IO-bound

The summary is here.

For the read-write steps the insert SLA was not met for qr500, qp500, qr1000 and qp1000 as those steps needed more IOPs than the storage devices can provide. So I ignore those steps.

Some of the improvements here are huge courtesy of storage with high fsync latency.

Throughput is much better with the binlog storage engine enabled when the InnoDB doublewrite buffer is also enabled. Comparing z12b_sync and z12c_sync (z12c_sync uses the binlog storage engine):

• throughput for l.i0 (load in PK order) is 3.05X larger for z12c_sync
• throughput for l.i1 (write-only, larger transactions) is 1.22X larger for z12c_sync
• throughput for l.i2 (write-only, smaller transactions) is 1.58X larger for z12c_sync

There is a smaller benefit from only disabling the InnoDB doublewrite buffer. Comparing z12b_sync and z12b_sync_dw0:

• throughput for l.i0 (load in PK order) is the same for z12b_sync and z12b_sync_dw0
• throughput for l.i1 (write-only, larger transactions) is 2.06X larger for z12b_sync_dw0
• throughput for l.i2 (write-only, smaller transactions) is 1.59X larger for z12b_sync_dw0

The largest benefits come from using the binlog storage engine and disabling the InnoDB doublewrite buffer. Comparing z12b_sync and z12c_sync_dw0:

• throughput for l.i0 (load in PK order) is 3.01X larger for z12c_sync_dw0
• throughput for l.i1 (write-only, larger transactions) is 3.26X larger for z12b_sync_dw0
• throughput for l.i2 (write-only, smaller transactions) is 2.78X larger for z12b_sync_dw0

MariaDB innovation: binlog_storage_engine, 48-core server, Insert Benchmark

MariaDB 12.3 has a new feature enabled by the option binlog_storage_engine. When enabled it uses InnoDB instead of raw files to store the binlog. A big benefit from this is reducing the number of fsync calls per commit from 2 to 1 because it reduces the number of resource managers from 2 (binlog, InnoDB) to 1 (InnoDB). See this blog post for more details on the new feature. This work was done by Small Datum LLC and sponsored by the MariaDB Foundation.

My previous post had results for sysbench with a small server. This post has results for the Insert Benchmark with a large (48-core) server. Storage on this server has a low fsync latency while the small server has high fsync latency.

In this test throughput doesn't improve with the InnoDB doublewrite buffer disabled. Even if it did I am not suggesting people do that for production workloads without understanding the risks it creates.

tl;dr

• binlog storage engine makes some things better without making other things worse
• binlog storage engine doesn't make all write-heavy steps faster because the commit path isn't the bottleneck in all cases on a server with storage that has low fsync latency

tl;dr for a CPU-bound workload

• the l.i0 step (load in PK order) is ~1.3X faster with binlog storage engine
• the l.i2 step (write-only with smaller transactions) is ~1.5X faster with binlog storage engine

tl;dr for an IO-bound workload

• the l.i0 step (load in PK order) is ~1.08X faster with binlog storage engine

Builds, configuration and hardware

I compiled MariaDB 12.3.1 from source.

The server has 48-cores and 128G of RAM. Storage is 2 NVMe device with ext-4, discard enabled and RAID. The OS is Ubuntu 22.04. AMD SMT is disabled. The SSD has low fsync latency.

I tried 4 my.cnf files:

• z12b_sync
• my.cnf.cz12b_sync_c32r128 (z12b_sync) is like z12b except it enables sync-on-commit for the binlog and InnoDB
• z12c_sync
• my.cnf.cz12c_sync_c32r128 (z12c_sync) is like cz12c except it enables sync-on-commit for InnoDB. Note that InnoDB is used to store the binlog so there is nothing else to sync on commit.
• z12b_sync_dw0
• my.cnf.cz12b_sync_dw0_c32r128 (z12b_sync_dw0) is like z12b_sync but disables the InnoDB doublewrite buffer
• z12c_sync_dw0
• my.cnf.cz12c_sync_dw0_c32r128 (z12c_sync_dw0) is like cz12c_sync but disables the InnoDB doublewrite buffer

The Benchmark

The benchmark is explained here. It was run with 20 clients for two workloads:

• CPU-bound - the database is cached by InnoDB, but there is still much write IO
• IO-bound - most, but not all, benchmark steps are IO-bound

The benchmark steps are:

• l.i0
• insert XM rows per table in PK order. The table has a PK index but no secondary indexes. There is one connection per client. X is 10M for CPU-bound and 200M for IO-bound.
• l.x
• create 3 secondary indexes per table. There is one connection per client.
• l.i1
• use 2 connections/client. One inserts XM rows per table 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. X is 40M for CPU-bound and 4M for IO-bound.
• l.i2
• like l.i1 but each transaction modifies 5 rows (small transactions) and YM rows are inserted and deleted per table. Y is 10M for CPU-bound and 1M for IO-bound.
• Wait for S seconds after the step finishes to reduce MVCC GC debt and perf variance during the read-write benchmark steps that follow. The value of S 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. 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. This step is frequently not IO-bound for the IO-bound workload. This step runs for 3600 seconds.
• 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: summary

The performance reports are here for CPU-bound and IO-bound.

The summary sections from the performance reports have 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. 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. And from the third table for the IO-bound workload I see that there were failures to meet the SLA for qp500, qr500, qp1000 and qr1000.

I use relative QPS to explain how performance changes. It is: (QPS for $me / QPS for $base) where $me is the result for some version $base is the result from the base version.

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 yellow for regressions and blue for improvements.

I often use context switch rates as a proxy for mutex contention.

Results: CPU-bound

The summary is here. 

• Disabling the InnoDB doublewrite buffer doesn't improve performance.

With and without the InnoDB doublewrite buffer enabled, enabling the binlog storage engine improves throughput a lot for two of the write-heavy steps while there are only small changes on the other two write-heavy steps:

• l.i0, load in PK order, gets ~1.3X more throughput
• when the binlog storage engine is enabled (see here)
• storage writes per insert (wpi) are reduced by about 1/2
• KB written to storage per insert (wkbpi) is a bit smaller
• context switches per insert (cspq) are reduced by about 1/3
• l.x, create secondary indexes, is unchanged
• when the binlog storage engine is enabled (see here)
• storage writes per insert (wpi) are reduced by about 4/5
• KB written to storage per insert (wkbpi) are reduced almost in half
• context switches per insert (cspq) are reduced by about 1/4
• l.i1, write-only with larger tranactions, is unchanged
• l.i2, write-only with smaller transactions, gets ~1.5X more throughput

Results: IO-bound

The summary is here.

• Disabling the InnoDB doublewrite buffer doesn't improve performance.
• For the read-write steps the insert SLA was not met for qr500, qp500, qr1000 and qp1000 as those steps needed more IOPs than the storage devices can provide. So I ignore those steps.
• Enabling the InnoDB doublewrite buffer improves throughput by ~1.25X on the l.i2 step (write-only with smaller transactions) but doesn't change performance on the other steps.
• as expected there is a large reduction in KB written to storage (see wkbpi here)
• Enabling the binlog storage engine improves throughput by 9% and 8% on the l.i0 step (load in PK order) but doesn't have a significant impact on other steps.
• with the binlog storage engine there is a large reduction in storage writes per insert (wpi), a small reduction in KB written to storage per insert (wkbpi) and small increases in CPU per insert (cpupq) and contex switches per insert (cspq) -- see here

Explaining why throughput varies for Postgres with a CPU-bound Insert Benchmark

Throughput for the write-heavy steps of the Insert Benchmark look like a distorted sine wave with Postgres on CPU-bound workloads but not on IO-bound workloads. For the CPU-bound workloads the chart for max response time at N-second intervals for inserts is flat but for deletes it looks like the distorted sine wave. To see the chart for deletes, scroll down from here. So this looks like a problem for deletes and this post starts to explain that.

tl;dr

• Once again, blame vacuum

History of the Insert Benchmark

Long ago (prior to 2010) the Insert Benchmark was published by Tokutek to highlight things that the TokuDB storage engine was great at. I was working on MySQL at Google at the time and the benchmark was useful to me, however it was written in C++. While the Insert Benchmark is great at showing the benefits of an LSM storage engine, this was years before MyRocks and I was only doing InnoDB at the time, on spinning disks. So I rewrote it in Python to make it easier to modify, and then the Tokutek team improved a few things about my rewrite, and I have been enhancing it slowly since then.

Until a few years ago the steps of the benchmark were:

• load - insert in PK order
• create 3 secondary indexes
• do more inserts as fast as possible
• do rate-limited inserts concurrent with range and point queries

The problem with this approach is that the database size grows forever and that limited for how long I could run the benchmark before running out of storage. So I changed it and the new approach keeps the database at a fixed size after the load. The new workflow is:

• load - insert in PK order
• create 3 secondary indexes
• do inserts+deletes at the same rate, as fast as possible
• do rate-limited inserts+deletes at the same rate concurrent with range and point queries

The insert and delete statements run at the same rate to keep the table from changing size. The Insert Benchmark client uses Python multiprocessing, there is one process doing Insert statements, another doing Delete statements and both get their work from queues. Another process populates those queues and that other process controlling what is put on the queue is what keeps them running at the same rate.

The benchmark treats the table like a queue, and when ordered by PK (transactionid) there are inserts at the high end and deletes at the low end. The delete statement currently looks like:
    delete from %s where transactionid in

        (select transactionid from %s where transactionid >= %d order by transactionid asc limit %d)

The delete statement is written like that because it must delete the oldest rows -- the ones that have the smallest value for transactionid. While the process that does deletes has some idea of what that smallest value is, it doesn't know it for sure, thus the query. To improve performance it maintains a guess for the value that will be <= the real minimum and it updates that guess over time.

I encountered other performance problems with Postgres while figuring out how to maintain that guess and get_actual_variable_range() in Postgres was the problem. Maintaining that guess requires a resync query every N seconds where the resync query is: select min(transactionid) from %s. The problem for this query in general is that is scans the low end of the PK index on transactionid and when vacuum hasn't been done recently, then it will scan and skip many entries that aren't visible (wasting much CPU and some IO) before finding visible rows. Unfortunately, there will be some time between consecutive vacuums to the same table and this problem can't be avoided. The result is that the response time for the query increases a lot in between vacuums. For more on how get_actual_variable_range() contributes to this problem, see this post.

I assume the sine wave for delete response time is caused by one or both of:

• get_actual_varable_range() CPU overhead while planning the delete statement
• CPU overhead from scanning and skipping tombstones while executing the select subquery

The structure of the delete statement above reduces the number of tombstones that the select subquery might encounter by specifying where transactionid >= %d. Perhaps that isn't sufficient. Perhaps the Postgres query planner still has too much CPU overhead from get_actual_variable_range() while planning that delete statement. I have yet to figure that out. But I have figured out that vacuum is a frequent source of problems.