Evaluate using Profile-Guided Optimization (PGO) and Post-Link Optimization (PLO)

[zamazan4ik]

Hi!

Recently I did many Profile-Guided Optimization (PGO) benchmarks on multiple projects (including many databases like PostgreSQL, ClickHouse, Redis, MongoDB, etc.) - the results are available here, database-related results could be checked here. That's why I think it's worth trying to apply PGO to HeavyDB to improve the database performance.

I can suggest the following things to do:

• Evaluate PGO's results on HeavyDB.
• If PGO helps to achieve better performance - add a note to HeavyDB's documentation about that. In this case, users and maintainers will be aware of another optimization opportunity for HeavyDB.
• Provide PGO integration into the build scripts. It can help users and maintainers easily apply PGO for their workloads.
• Optimize prebuilt HeavyDB binaries with PGO.

Here are some examples of how PGO is already integrated into other projects' build scripts:

• Rustc: a CI script for the multi-stage build
• GCC:
  • Official docs, section "Building with profile feedback" (even AutoFDO build is supported)
  • A part in a "wonderful" configure script
• Clang: Docs
• Python:
  • CPython: README
  • Pyston: README
• Go: Bash script
• V8: Bazel flag
• ChakraCore: Scripts
• Chromium: Script
• Firefox: Docs
  • Thunderbird has PGO support too
• PHP - Makefile command and old Centminmod scripts
• MySQL: CMake script
• YugabyteDB: GitHub commit
• FoundationDB: Script
• Zstd: Makefile
• Foot: Scripts
• Windows Terminal: GitHub PR
• Pydantic-core: GitHub PR
• file.d: GitHub PR
• OceanBase: CMake flag
• ISPC: CMake scipts
• NodeJS: Configure script
• Android Open Source Project (AOSP):
  • Official documentation
  • Committed PGO profiles: repository
• DMD: Custom build rule
• LDC: GitHub action
• tsv-utils: Makefile
• Erlang OTP: Makefile
• Clingo (PGO enabled only in Spack): Package recipe
• SWI-Prolog:
  • Script
  • CMake module
• hck: Justfile

Here are some examples how PGO-related documentation could look like in the project:

• ClickHouse: https://clickhouse.com/docs/en/operations/optimizing-performance/profile-guided-optimization
• Databend: https://databend.rs/doc/contributing/pgo
• Vector: https://vector.dev/docs/administration/tuning/pgo/
• Nebula: https://docs.nebula-graph.io/3.5.0/8.service-tuning/enable_autofdo_for_nebulagraph/
• GCC: Official docs, section "Building with profile feedback" (even AutoFDO build is supported)
• Clang:
  • https://llvm.org/docs/HowToBuildWithPGO.html
  • https://llvm.org/docs/AdvancedBuilds.html
• tsv-utils: https://github.com/eBay/tsv-utils/blob/master/docs/BuildingWithLTO.md

After PGO, I can suggest evaluating PLO with LLVM BOLT as an additional optimization step after PGO.

Below are listed some BOLT results:

• Rustc:
  • Rustc itself (GitHub PR)
  • LLVM in Rustc (Reddit)
• CPython: GitHub PR
• YDB: GitHub comment
• Clang:
  • Slides
  • Results on building Clang
  • Linaro results
  • on AMD 7950X3D
  • GitHub comment from LLVM
• LDC: GitHub comment
• HHVM, Proxygen and others: Facebook paper
• NodeJS: Blog
• Chromium: Blog
• MySQL, MongoDB, memcached, Verilator: Paper

I am not familiar with HeavyDB (yet) but I guess at first we can try to train PGO on the HeavyDB benchmarks and then compare before and after PGO performance with HeavyDB.

[cdessanti]

Hi @zamazan4ik,

I'm sorry for the late response. Each time I tried to reply to this message, I've been distracted by something else.

I have carefully reviewed your work and, in particular, the project you completed using ClickHouse. I found it to be very interesting. I just wanted to let you know that our database employs the GPU as an accelerator for operations that require high bandwidth and parallelism. Specifically, it handles data aggregations, filters and joins, and generates LLVM code to optimize these operations. This can also be done for CPU execution. As a result, the CPU is mainly responsible for coordination and memory management.

I haven't had the chance to use PGO on the project yet due to time constraints. However, if you're interested in running benchmarks with HeavyDB, I'd be happy to guide you through the process of setting up a development environment, including DDLs and data for standard or internal benchmarking purposes.

Let me know how I can assist you. Meanwhile, have a nice weekend.
Candido