Database server PGO reshapes code around its busiest execution pa
The open-source relational database's PGO, or profile-guided optimisation, uses evidence from a training run to change how database software is compiled into executable machine instructions. In its explanation of the process, Percona describes a technique that does more than accelerate the queries used for training: it reorganises code throughout the database program. The important match with production is which execution paths run frequently, not whether the training data contains the same values.
Percona's account is the second part of a three-part series, between an introduction to PGO and a discussion of why a build trained with sysbench, a workload-testing tool, loses performance. This instalment explains the mechanisms rather than presenting comparative benchmark results. It therefore provides no measured speed increase that a database operator can assume will apply to their own installation.
Two builds, with a training run between them
PGO starts by compiling an instrumented build, a version of the program with extra counters that record execution activity. Percona describes using the profile-generation compiler option for this stage, running a training workload and writing the measurements into raw profile files. A second compilation uses the recorded activity to choose how to arrange and optimise the final program. Exact profiling details depend on the compiler, the tool that translates source code into machine instructions.
The useful evidence is more detailed than a count of function calls, where a function is a reusable piece of code. Percona describes counters on connections in the control-flow graph, the map of possible routes through a program. These capture branch choices, repeated trips through loops and activity at call sites, the places where one function invokes another. Its illustrative example distinguishes a branch taken 950,000 times from an alternative taken just 50 times. Those are explanatory counts, not reported database benchmark results.
Inlining: spending code space where it helps
Inlining means putting a function's instructions directly at its call site instead of making a separate call. Without runtime evidence, compilers rely on rules such as function size, the number of call sites and estimated costs and benefits. Profiles can justify inlining a call executed millions of times even when ordinary size rules would reject it. Conversely, a call in rarely executed code may remain separate because copying its instructions would enlarge the program with little benefit.
Branch layout changes which route follows directly into the next instructions and which requires a jump elsewhere. Processors predict branch outcomes to fetch instructions ahead of execution. A wrong prediction forces them to discard speculative work, causing a brief stall. Making the common route continue straight through can help instruction fetching, although actual costs depend on the processor and whether its prediction is correct. Percona describes moving rare error handling away from that route, often into the unlikely-code section, a section of the executable reserved for infrequently used instructions.
Changing the order of an if-or-else statement in source code is not an equivalent optimisation. The compiler chooses the machine-code arrangement using profiles or its own rules; the order in which a developer writes the alternatives generally has little or no effect on the final layout.
Keeping frequently used instructions together
Hot/cold function splitting separates frequently executed, or hot, instructions from rarely executed, or cold, ones within the same function. A busy loop can remain in the hot-code section, a section for frequently used code, while its unusual error-handling tail moves to the unlikely-code section. A jump still connects the pieces when necessary. The smaller hot portion makes better use of the instruction cache, the processor's small, fast store of upcoming instructions, rather than sharing that space with seldom-used code.
Reordering also happens across entire functions when the executable is assembled at link time. Percona says the database server's PGO build enables LTO, link-time optimisation, which lets the toolchain optimise across separately compiled parts of the program. Functions that often call each other or execute in succession can be placed close together. This improves instruction-cache locality and iTLB locality: the instruction translation lookaside buffer is a processor cache that helps locate instruction memory.
Percona describes this whole-program rearrangement as potentially one of the largest gains for the database server executable, discussing movement around a binary larger than 100 MB. Ordinary build ordering does not necessarily reflect the paths used at runtime. Packing related hot functions together reduces the need to fetch instructions from widely separated locations, but the article does not quantify that benefit for a particular deployment.
More predictable calls and different code-size choices
Indirect-call promotion addresses calls whose destination is selected while the program runs, such as function-pointer calls or C++ virtual calls. Virtual calls commonly use vtables, tables that select an object's implementation of an operation. Percona gives a storage-engine interface with effectively only the database's data-storage engine registered as an example where one destination may dominate. The compiler can insert a check: call that frequent destination directly when it matches, otherwise retain the original indirect call. This preserves the fallback while making the common destination easier to predict.
Profiles also influence register allocation, the assignment of values to the processor's fastest working storage, and loop unrolling, repeating loop instructions to reduce loop-control overhead. Hot paths can favour speed, with more aggressive unrolling and more registers devoted to frequently needed values. Cold paths can favour compactness. Percona mentions the partial-profile-training compiler option in this discussion; the treatment of untrained code depends on compiler settings. Rarely used instructions still consume disk space and the page cache, memory holding file contents, so their size and placement matter even when they seldom execute.
A switch statement, which selects among several alternatives, presents another trade-off. A compiler can use a jump table, a lookup structure offering order-one, or constant-time, selection, but requiring a table lookup and indirect jump. Alternatively, it can generate successive comparisons. Profile evidence can make comparisons attractive when one case dominates and is predictable, rather than treating every case as equally likely.