Database PGO gains fade under heavier load in Percona testing
The open-source relational database’s performance gains from profile-guided optimization faded as competing database activity increased in tests reported by Percona’s Marco Tusa. One narrowly trained build actually performed worse than the baseline. His findings illustrate a practical limit of compiler tuning: making frequently used code faster does not necessarily solve the bottlenecks that dominate a busy database.
Profile-guided optimization (PGO) uses measurements from training runs to guide a compiler, the software that turns source code into executable programs. An instrumented build records which functions and execution paths run most often; a subsequent build uses those records to prioritize optimization. Tusa’s third instalment on PGO examines why training the database with sysbench-tpcc, a transaction-processing benchmark, produced a disappointing result and whether broader training could help.
The reported gains depend heavily on the workload. Tusa’s conclusions cite a 12% improvement at low concurrency, meaning relatively few activities running simultaneously, but less than 2% with higher contention, when those activities compete for shared resources. Elsewhere, he describes gains as below 5% and suggests the additional benefit of better concurrency coverage could plausibly be below 1%. That last figure is an expectation, not a separately established measurement. These numbers describe different comparisons and should not be presented as one universal performance uplift.
Why narrow training can make the database slower
Tusa identifies three compounding problems. First, sysbench-tpcc exercises only part of the database’s server program. In his GCC compiler discussion, functions with no recorded executions become “cold”: the compiler favors smaller code, avoids inlining (placing a function’s code directly inside its caller), and moves it into less-frequently-used regions. Yet a running database still performs purge work to remove obsolete data versions, flushing to write changed data to storage, statistics recalculation, error handling, alternative query-execution plans and repeated connection setup and teardown.
Those operations can therefore run through less speed-optimized code. Moving between frequently and infrequently used regions can also hurt the instruction cache, the processor’s fast store of executable instructions. Tusa points to GCC’s partial-profile-training option as a safeguard against penalizing code that training did not cover.
Second, he reports unreliable training counts at 128 to 1024 threads, the parallel workers executing database activity. GCC’s default profile counters, in the setup he describes, are not atomic: simultaneous updates need not be recorded as indivisible operations. Updates can be lost and counts can become inconsistent. Tusa encountered corrupted or inconsistent profile-data warnings when compiling with profile use enabled and identifies atomic profile updates as the necessary safeguard. His sysbench-trained PGO build was slower than a conventional optimization-level-3 build, using a high compiler optimization level, at 128 threads.
Third, measurement changes the behavior being measured. Tusa describes instrumentation slowing execution by 2 to 10 times. That changes where threads accumulate under contention. Mutexes and read-write locks, mechanisms that coordinate access to shared data, can spend large amounts of time in spin loops that repeatedly check whether access is available. Their enormous execution counts may encourage the compiler to optimize waiting rather than productive work. Rearranging code branches cannot remove the underlying lock contention or delays accessing memory.
Combining breadth with concurrent activity
Tusa’s alternative combines the database test runner (MTR), the database’s testing tool, with a moderate-concurrency sysbench workload. MTR covers broad functionality: interpreting queries, selecting execution plans, changing database structures, replication (copying changes between database servers), and error handling. But he describes its workloads as operating almost entirely through single connections, leaving important concurrent execution paths underrepresented.
Those paths include contended latches, lightweight coordination mechanisms; multiversion concurrency control (MVCC), which determines which data versions a transaction can see while others write; queues for lock waits; and group commit, which batches transaction commits through the redo log used for recovery. Tusa proposes roughly 4 to 64 sysbench threads to exercise these paths without the severe distortions of very high concurrency. Giving them nonzero counts helps prevent the compiler from classifying them as unused.
The compiler distinction matters. Tusa explains that GCC’s runtime can accumulate counts when successive training runs use the same instrumented executable and profile directory, adding to existing files when the program exits. However, the published build commands use Clang 20, not GCC. His demonstrated workflow explicitly merges raw profiles with version 20 of LLVM’s profile-data tool in sparse merge mode into the database profile-data file in the source tree’s profile directory under the optional-software directory. The GCC safeguards should not be mistaken for options shown as enabled in those Clang commands.
What the demonstrated experiment contains
The instrumented build uses the database server version 9.7.2, Release configuration, Clang’s C and C++ compilers version 20, the lld linker that assembles compiled program components, and profile generation enabled. It disables link-time optimization (LTO), optimization performed across compiled components during linking. Training runs MTR with eight parallel test workers across the main, transactional storage engine, transactional storage engine undo, binary log, replication, performance schema and system variables test suites, continuing despite test failures.
The subsequent sysbench-tpcc run uses 64 threads for 600 seconds, 10 tables and scale 100. It selects data uniformly, uses repeatable-read transaction isolation to govern which changes transactions can see, enables purge and disables prepared statements, reusable preprocessed database requests. Connections remain open rather than repeatedly reconnecting. These choices describe the training workload, not a recommended production configuration.
Tusa’s profile summary lists 52,403 functions, 619,050 blocks of code and a total count of 2,473,216,025,729. The maximum function count is 289,910,820,864 and the maximum internal block count is 16,881,288,068. Just two blocks account for 20% of recorded counts; 2,165 blocks, or 0.35%, account for 90%. At the broader end, 220,001 blocks, or 35.54%, account for 99.9999%.
He interprets this concentrated core plus a broad, low-frequency tail as evidence that sustained sysbench activity and MTR’s functional coverage both contributed.