Time to compile Postgres, MySQL and MyRocks

I measured the time it takes to build Postgres, upstream MySQL and FB MySQL from source on my home servers (Beelink, see here) using Postgres 15.2, upstream MySQL 8.0.32 and FB MySQL 8.0.28. 

tl;dr

• Build times in seconds: 619 for Postgres, 5294 for upstream MySQL, 8443 for FB MySQL
• Postgres is by far the fastest to compile. Reasons for that include that it uses C and has fewer things to compile
• FB MySQL spends ~2700 seconds compiling RocksDB related things. Without that overhead the FB MySQL build would be 5601 seconds vs 5294 for upstream MySQL. For reasons I don't understand yet, most of the RocksDB source files are compiled 3 times (for the storage engine, ldb binary and sst_dump binary). If that could be avoided then the build time would be reduced by ~20%.

Setup

While the server has 8 cores I did a non-parallel make (make -j1). 

The build configuration (configure & CMake command lines) for each DBMS is here.

Methods

For Postgres I just measure the total time to build. For upstream and FB MySQL I also measure the time for each build target. That is done in two steps.

1. Run make and add timestamps to the output. Smart people on Twitter explained how to do that.
2. Parse the output from make to get per-target compile times

To run make I did:

make 2>&1 | ts '[%s]' | tee o.time

To parse the output I did:

grep "Built target" o.time | tr '[]' ' ' | \

awk '{ $2=""; $3=""; $4=""; print $0 }' | \

awk '{ if (NR==1) { printf "%s\t%s\n", $1 - itime, $2 } else { printf "%s\t%s\n", $1 - lastTS, $2 };  lastTS=$1 } ' itime=$firstTS | \

sort -rnk 1,1 | head -10

Results: summary

Legend

* Time(s) - total time for build in seconds

* Targets - number of build targets, only printed for MySQL

Time(s) Targets DBMS
619     NA      Postgres
5294    255     upstream MySQL
8443    251     FB MySQL

Results: detailed

The time in seconds for the top-20 build targets

For upstream MySQL 8.0.32:

1035    sql_main
636     innobase
433     sql_gis
393     sql_dd
301     group_replication
294     perfschema
209     icui18n
190     mysqld
153     libprotoc
128     icuuc
123     libprotobuf
99      slave
96      binlog
65      mysql_server_component_services
65      mysqlgcs
54      mysys_objlib
49      myisam_library
44      mysqlpump_lib
33      libprotobuf-lite
30      mysqlbinlog

For FB MySQL:

The RocksDB related build targets are rocksdb_se, ldb and sst_dump. From the output it looks like each of those repeats the same work -- as all or most of the RocksDB source files are recompiled for each. That explains why each target takes ~900 seconds. If the recompile could be avoided then the build time would be reduced by ~20%.

1201    sql_main
993     rocksdb_se
932     ldb
917     sst_dump
598     innobase
462     sql_dd
411     sql_gis
334     group_replication
332     perfschema
227     mysqld
212     icui18n
153     libprotoc
131     slave
129     icuuc
123     libprotobuf
97      binlog
65      mysqlgcs
57      mysys_objlib
54      mysql_server_component_services
47      myisam_library

Perf regressions in MySQL/InnoDB, a big server & sysbench, part 2

My last post has results for MySQL/InnoDB on a big server and the database fit in the InnoDB buffer pool. Here I have results where the database fits in the OS page cache but not the InnoDB buffer pool.

The context for the results is short-running queries, in-memory (cached by the OS) with high-concurrency (20 clients) on a big server (30-cores). The goals are:

1. Understand the impact of compiler optimizations
2. Document how performance has changed from MySQL 5.6 to 5.7 to 8.0
3. Document performance with fast storage (reading from the OS page cache is fast)

tl;dr

• The rel_lto build improves QPS by up to 3%
• 8.0 releases look much better here with a big server & high-concurrency than on the small server with low-concurrency.
• For changes from 5.6 to 8.0
• Point queries - version 8.0.32 gets about 3X more QPS than 5.6.51 on most of the microbenchmarks. This is much better than the previous result where the database fits in the buffer pool.
• Range queries - version 8.0.32 gets about 14% more QPS than 5.6.51. This is much better than the previous result where the database fits in the buffer pool.
• Writes - version 8.0.32 gets the same QPS as version 5.6.51. This is much worse than the previous result where the database fits in the buffer pool.

Benchmark

A description of how I run sysbench is here. Tests use the a c2-standard-60 server on GCP with 30-cores, hyperthreading disabled, 240G RAM and 3TB of local attached NVMe. The sysbench tests were run for 20 clients, 600 seconds per microbenchmark using 4 tables with 50M rows per table. All tests use the InnoDB storage engine. The test database fits in the InnoDB buffer pool.

I used a similar configuration (my.cnf) for all versions which is here for 5.6, 5.7, 8.0.2x and 8.0.3x.

Builds

I tested MySQL versions 5.6.51, 5.7.40, 8.0.22, 8.0.28, 8.0.31 and 8.0.32 using multiple builds for each version. For each build+version the full set of sysbench microbenchmarks was repeated. More details on the builds are in the previous post. To save time I only tested all builds for 8.0.31 and for other versions used the rel_lto build.

Results: all versions

The spreadsheet is here. See the 56_to_80.redo.4g tab.

The graphs use relative throughput which is throughput for me / throughput for base case. When the relative throughput is > 1 then my results are better than the base case. When it is 1.10 then my results are ~10% better than the base case. The base case here is MySQL 5.6.51 using the rel_lto build.

There are three graphs per version which group the microbenchmarks by the dominant operation: one for point queries, one for range queries, one for writes. There is much variance within each of the microbenchmark groups:

• Point queries - most of the microbenchmarks get about 3X more QPS in 8.0 than 5.6. The exceptions are hot-points_range=100, point-query.pre_range=100 and point-query.range=100. Two of the exceptions select one row per query while the microbenchmarks that are 3X faster tend to have a large in-list.
• Range queries - most of the microbenchmarks have a relative throughput between 0.8 and 1.2 with 8.0 compared to 5.6.51. There are two outliers that are more than 3X faster in 8.0 -- range-notcovered-si.pre_range=1000 and range-notcovered-si_range=1000 which use oltp_points_covered.lua. The two exceptions do a range scan on a non-covering secondary index so there will be more reads from the OS page cache for these.
• Writes - there is not much variance in the microbenchmarks except for read-write* which use the classic sysbench transaction that includes range queries. Perhaps their improvement in 8.0 vs 5.6 is mostly do to the improvements in range queries, but their cousin (read-only*) which uses the same SQL excluding the writes doesn't show such an improvement. This is a mystery.

Summary statistics:

my5651_rel	my5740_rel_lto	my8022_rel_lto	my8028_rel_lto	my8031_rel_lto	my8032_rel_lto
Point: avg	1.71	2.67	2.75	2.71	2.70
Point: median	1.58	2.88	2.98	3.15	3.19
Point: min	1.03	1.02	1.01	0.99	0.98
Point: max	5.11	3.73	3.76	3.70	3.37
Point: stddev	0.897	0.794	0.861	0.844	0.840
Range: avg	1.29	1.47	1.50	1.42	1.42
Range: median	1.25	1.24	1.19	1.15	1.14
Range: min	0.81	0.71	0.73	0.68	0.62
Range: max	2.29	3.47	3.60	3.44	3.44
Range: stddev	0.368	0.834	0.897	0.847	0.858
Write: avg	1.22	1.09	1.07	1.06	1.06
Write: median	1.18	1.02	1.01	1.00	1.01
Write: min	0.98	0.89	0.95	0.94	0.95
Write: max	1.52	1.55	1.33	1.33	1.33
Write: stddev	0.200	0.220	0.142	0.134	0.132

Results: MySQL 8.0.31

The spreadsheet is here. See the my8031.redo.4g tab.

The graphs use relative throughput which is throughput for me / throughput for base case. When the relative throughput is > 1 then my results are better than the base case. When it is 1.10 then my results are ~10% better than the base case. The base case here is MySQL 8.0.31 using the rel_withdbg build.

There are three graphs per version which group the microbenchmarks by the dominant operation: one for point queries, one for range queries, one for writes. For each group of microbenchmarks:

• Point queries - there is little variance across the microbenchmarks
• Range queries - the full table scan test (scan_range=10) shows the best improvement from the rel_lto build. I don't understand the noisy result for the read-only_range=10000 microbenchmark. Perhaps buffer pool writeback was still in progress as that microbenchmark is run shortly after the write-heavy microbenchmarks.
• Writes -  there is little variance across the microbenchmarks

Summary statistics:

rel_withdbg	rel_o2	rel_native	rel	rel_o2_lto	rel_native_lto	rel_lto
Point: avg	0.98	1.02	1.02	1.01	1.03	1.03
Point: median	0.99	1.02	1.02	1.01	1.03	1.03
Point: min	0.95	0.96	0.97	1.00	1.01	1.01
Point: max	1.01	1.03	1.03	1.02	1.06	1.05
Point: stddev	0.017	0.021	0.018	0.007	0.011	0.011
Range: avg	0.98	0.98	0.98	1.00	1.00	1.01
Range: median	0.97	0.98	0.98	1.01	1.01	1.02
Range: min	0.96	0.89	0.86	0.93	0.90	0.81
Range: max	1.07	1.06	1.03	1.02	1.04	1.11
Range: stddev	0.028	0.040	0.038	0.021	0.034	0.061
Write: avg	0.98	0.98	0.99	0.99	0.99	1.00
Write: median	0.98	0.98	0.99	0.98	0.98	0.99
Write: min	0.97	0.98	0.98	0.98	0.98	0.99
Write: max	0.98	0.99	1.00	1.00	1.01	1.01
Write: stddev	0.003	0.005	0.006	0.007	0.011	0.008

Perf regressions in MySQL/InnoDB, a big server & sysbench

I used sysbench to test MySQL/InnoDB performance on a big server. This is similar to the results I shared for InnoDB vs sysbench on a small server. The context for the results is short-running queries, in-memory (cached by InnoDB) with high-concurrency (20 clients) on a big server (30-cores). The goals are:

1. Understand the impact of compiler optimizations
2. Document how performance has changed from MySQL 5.6 to 5.7 to 8.0

tl;dr

• The rel_lto build gets 4%, 0% and 3% more QPS for point query, range query and write microbenchmarks compared to the rel_withdbg build for MySQL 8.0.31. This is similar to the benefit measured on the small server. Link-time optimization is nice.
• 8.0 releases look much better here with a big server & high-concurrency than on the small server with low-concurrency.
• For changes from 5.6 to 8.0
• Point queries - version 8.0.32 gets about 4% more QPS (on average) versus version 5.6.51. But microbenchmarks that use the PK index do better than average while ones that use the secondary index do much worse than average where much worse means getting about 25% less QPS than 5.6.51.
• Range queries - version 8.0.32 gets about 22% less QPS versus version 5.6.51. The regressions have been gradual from 5.6 to 5.7 to 8.0.
• Writes - version 8.0.32 gets almost 3X more QPS versus version 5.6.51. All of that improvement is between 5.6.51 and 5.7.40.

Benchmark

A description of how I run sysbench is here. Tests use the a c2-standard-60 server on GCP with 30-cores, hyperthreading disabled, 240G RAM and 3TB of local attached NVMe. The sysbench tests were run for 20 clients, 600 seconds per microbenchmark using 4 tables with 50M rows per table. All tests use the InnoDB storage engine. The test database fits in the InnoDB buffer pool.

I used a similar configuration (my.cnf) for all versions which is here for 5.6, 5.7, 8.0.2x and 8.0.3x.

Builds

I tested MySQL versions 5.6.51, 5.7.40, 8.0.22, 8.0.28, 8.0.31 and 8.0.32 using multiple builds for each version. For each build+version the full set of sysbench microbenchmarks was repeated. More details on the builds are in the previous post. To save time I only tested all builds for 8.0.31 and for other versions used the rel_lto build.

Results: all versions

The spreadsheet is here. See the 56_to_80.redo tab.

The graphs use relative throughput which is throughput for me / throughput for base case. When the relative throughput is > 1 then my results are better than the base case. When it is 1.10 then my results are ~10% better than the base case. The base case here is MySQL 5.6.51 using the rel_lto build.

There are three graphs per version which group the microbenchmarks by the dominant operation: one for point queries, one for range queries, one for writes. There is much variance within each of the microbenchmark groups:

• Point queries - most of the regressions, where the relative throughput is much less than 1, occur on microbenchmarks that use the secondary index. See the spreadsheet for the full microbenchmark names as they are cutoff on the graphs below. So on average 8.0.32 gets about 4% more QPS than 5.6.51 but that can hide something. For microbenchmarks that use the PK index the QPS from 8.0.32 is usually much more than 4% better than 5.6.51. For microbenchmarks that use the secondary index the QPS from 8.0.32 is usually about 25% less than 5.6.51. 
• Range queries - results are in three classes. 
• The first class gets about 22% less QPS versus 5.6.51. These do a variety of range scans using the PK or secondary index. For some the index is covering, for others it is not.
• The second class gets about 12% more QPS versus 5.6.51. The Lua script for all of these is oltp_read_only.lua which is the classic sysbench transaction excluding writes.
• The final class has but one microbenchmark that does a full table scan (scan_range*) and 5.6.51 will soon be 2X faster than modern MySQL for that microbenchmark.  
• Writes - while there is much variance in the relative throughput across the microbenchmarks in this group, in all cases the throughput with 8.0 is much better than 5.6.51. The read-write* microbenchmarks have the least improvement in 8.0 versus 5.6.51 but those use oltp_read_write.lua which is the classic sysbench transaction and that includes range queries in addition to the writes.

Summary statistics:

my5651_rel	my5740_rel_lto	my8022_rel_lto	my8028_rel_lto	my8031_rel_lto	my8032_rel_lto
Point: avg	1.10	0.97	0.94	0.96	0.95
Point: median	1.23	0.93	0.91	1.06	1.04
Point: min	0.81	0.75	0.72	0.72	0.72
Point: max	1.36	1.29	1.15	1.19	1.18
Point: stddev	0.201	0.169	0.153	0.170	0.165
Range: avg	1.04	0.97	0.95	0.90	0.88
Range: median	0.89	0.86	0.81	0.78	0.78
Range: min	0.74	0.76	0.76	0.63	0.60
Range: max	1.40	1.23	1.21	1.16	1.14
Range: stddev	0.253	0.203	0.199	0.210	0.208
Write: avg	3.19	2.94	2.95	2.89	2.81
Write: median	3.15	2.95	3.03	2.96	2.90
Write: min	1.41	1.32	1.28	1.24	1.21
Write: max	5.83	4.77	4.79	4.66	4.35
Write: stddev	1.251	1.075	1.076	1.071	1.023

Results: version 8.0.31

The spreadsheet is here. See the my8031.redo tab.

The graphs use relative throughput which is throughput for me / throughput for base case. When the relative throughput is > 1 then my results are better than the base case. When it is 1.10 then my results are ~10% better than the base case. The base case here is MySQL 8.0.31 using the rel_withdbg build.

There are three graphs per version which group the microbenchmarks by the dominant operation: one for point queries, one for range queries, one for writes. For each group of microbenchmarks:

• point queries show little variance
• range queries show little variance except on the full scan (scan_range=10). I suspect that is noise from the microbenchmark rather than from compiler optimizations
• writes show little variance

Summary statistics:

rel_withdbg	rel_o2	rel_native	rel	rel_o2_lto	rel_native_lto	rel_lto
Point: avg	0.98	1.00	1.01	1.01	1.04	1.04
Point: median	0.98	1.00	1.02	1.01	1.04	1.04
Point: min	0.96	0.98	0.97	0.99	1.01	1.01
Point: max	0.99	1.02	1.03	1.03	1.05	1.06
Point: stddev	0.008	0.011	0.020	0.009	0.010	0.014
Range: avg	0.98	0.98	0.99	1.00	0.99	1.01
Range: median	0.98	0.97	0.97	1.00	0.99	1.00
Range: min	0.97	0.94	0.96	0.97	0.94	0.97
Range: max	1.06	1.14	1.14	1.01	1.03	1.04
Range: stddev	0.023	0.048	0.046	0.010	0.025	0.019
Write: avg	0.98	0.99	0.99	1.01	1.03	1.03
Write: median	0.98	0.99	0.99	1.00	1.03	1.03
Write: min	0.97	0.96	0.96	1.00	1.00	1.01
Write: max	1.00	1.03	1.02	1.05	1.05	1.06
Write: stddev	0.008	0.020	0.018	0.021	0.018	0.019