Boost MySQL Performance 30‑50% with Profile‑Guided Optimization (PGO)

What is PGO?

Profile‑Guided Optimization (PGO) is a compiler technique that uses runtime execution data to generate a profile, which then guides a second compilation to produce code optimized for the actual workload. Unlike manual branch‑prediction tricks, PGO requires no source modifications and works for any program written in languages supported by the compiler, such as C++ and Rust.

Why PGO over Traditional Branch Prediction?

Traditional branch‑prediction optimizations rely on macros like UNIV_EXPECT that wrap GCC’s __builtin_expect. They demand source changes and risk mismatches between predicted and real execution paths, potentially causing performance regressions. PGO eliminates these issues by automatically learning the most likely branches from real runs.

PGO Workflow

Instrument (first compile) : Compile the program with profiling enabled, e.g., g++ -o test test.cpp -fprofile-generate=/tmp/pgo. This inserts instrumentation code.

Train (run program) : Execute the instrumented binary to collect execution data, producing .gcda files.

Optimize (second compile) : Re‑compile using the collected profile, e.g., g++ -o test test.cpp -fprofile-use=/tmp/pgo, allowing the compiler to generate code tuned to the observed behavior.

Applying PGO to MySQL

MySQL is written in C++, so it can benefit from PGO. Starting with MySQL 8.0.19, the CMake build system includes PGO support. The typical steps are:

Enable profile generation: cmake .. -DFPROFILE_GENERATE=ON Run the resulting mysqld with a realistic workload (e.g., sysbench) to collect .gcda files in the build_release‑profile‑data directory.

Re‑compile using the collected profiles: cmake .. -DFPROFILE_USE=ON After the optimized build, restart mysqld and rerun the same benchmark.

Benchmark Results

Using a 16‑core cloud instance, the baseline MySQL build achieved roughly 250 k QPS on a read‑only sysbench test. After applying PGO, the same test reached about 350 k QPS, an improvement of over 40 %.

For comparison, a non‑PGO build with the same hardware showed QPS around 65 k when the profile collection phase was still running, illustrating the impact of the profiling data on performance.

Pros and Cons of PGO

PGO delivers substantial speedups when the workload is stable and well‑defined, such as a microservice handling a specific type of transaction. However, its effectiveness diminishes for mixed workloads (both TP and AP) or when the query pattern changes frequently, because the generated profile may no longer represent the current execution path.

In summary, PGO is a powerful, low‑overhead way to “squeeze” extra performance out of MySQL and other C++ programs, provided that the target workload is consistent and that regular database tuning has already been performed.