-
PGO or not PGO this is the dilemma. Step 3
Step 3: Why my sysbench-trained build loses and how to do it right.
Given the topic complexity and the length of this article I have split it in 3 three different blog-post:
What is PGO
How PGO it works
Why my sysbench-trained build loses and how to do it right.
Three compounding reasons:
Uncovered code gets pessimized.
Sysbench-tpcc touches a narrow slice of mysqld.
Every function with zero counts is treated as cold: GCC optimizes it for size, skips inlining, and shoves it into cold sections.
But at runtime I still execute plenty of code my training never touched, such as purge, flushing, stats recalculation, error paths, different optimizer plans, connection churn.
All of that is now running de-optimized code, and I pay icache penalties every time hot code calls into “cold” regions.
This is why GCC added -fprofile-partial-training, without it, a narrow profile actively hurts everything outside it.
High-concurrency instrumented runs produce corrupted or skewed profiles.
GCC’s profile counters are non-atomic by default.
With 128–1024 threads hammering the same counters I get lost updates and internally inconsistent counts (I actually had “profile count data file corrupted/inconsistent” warnings at the -fprofile-use compile).
I need to use -fprofile-update=atomic, which almost nobody sets and was at the beginning not aware of.
In short my profile was garbage and a garbage profile is worse than no profile, consistent with my PGO build being slower than plain -O3 at 128 threads.
Instrumentation distorts what “hot” means under contention.
The instrumented binary is 2–10x slower, which shifts where threads pile up. Spin loops in mutexes and rw-locks record enormous counts, so the compiler lavishes optimization on waiting code instead of useful work. While at high thread counts my real bottleneck is lock contention and memory latency things branch layout can’t fix.
Do I have a way to merge the different profiles like MTR + sysbench?
The answer is yes. For GCC it’s simple to do so, because the runtime automatically merges profile data across multiple training runs against the same instrumented binary. I don’t need a separate merge step like Clang does.
What it does is that each time an instrumented binary exits, it writes its counters into files. If those files already exist (from a previous run), GCC’s runtime adds the new counts to the existing ones rather than overwriting them.
So if I run MTR first, then run sysbench against the same instrumented build with the same FPROFILE_DIR, the second run’s counts accumulate on top of the first. The final profile reflects both workloads combined.
Combining MTR with a moderate-thread sysbench run for PGO training is helpful because the two workloads cover different, complementary dimensions of mysqld’s behavior:
MTR sweeps broad functional breadth parser, optimizer, DDL, replication, error paths. However it runs almost entirely single-connection, so it never exercises the branches that only exist under real concurrency, like the contended slow-path of a latch, MVCC visibility checks against in-flight writers, lock-wait queuing, or redo-log group-commit batching;
A moderate-concurrency sysbench run (something like 4–64 threads, enough to create actual simultaneous access without descending into the timing-distortion and counter-corruption problems), fills exactly that gap by giving those concurrency-only branches nonzero execution counts, which keeps the compiler from treating them as cold, since cold code paths get optimized for size instead of speed.
So the merged profile ends up with both the wide code coverage MTR provides and the concurrent-path coverage MTR structurally can’t, at the cost of only a modest, second-order improvement over MTR alone since PGO’s overall gains are already small and this specific slice of the binary is a narrow fraction of total execution.
However even where it does help, I am stacking a small effect on top of a small effect. I have already found PGO vs non-PGO gives me under 5%.
The incremental gain from better-covering a narrow slice of concurrency-only code within that is a second-order refinement.
It is plausibly in the sub-1% range, quite smaller than the run-to-run noise I had seen just from benchmark variance.
Or at least that is what I now think, let me validate it.
Commands to build the code:cmake ../mysql-9.7.2 \
-DCMAKE_INSTALL_PREFIX=/opt/mysql_templates/mysql-9.7.2-PGO-instrument \
-DCMAKE_BUILD_TYPE=Release \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_PACKAGE_FLAGS=OFF \
-DCOMPILATION_COMMENT_SERVER="Marco compile 9.7.2-PGO instrument" \
-DCOMPILATION_COMMENT="Marco compile 9.7.2 PGO instrument" \
-DCMAKE_C_COMPILER=clang-20 \
-DCMAKE_CXX_COMPILER=clang++-20 \
-DCMAKE_C_FLAGS="-fuse-ld=lld" \
-DCMAKE_CXX_FLAGS="-fuse-ld=lld" \
-DFPROFILE_GENERATE=ON \
-DWITH_LTO=OFF \
-DFPROFILE_DIR=/opt/mysql_source/profileRun the mtr:
perl mysql-test-run.pl --force --max-test-fail=0 --parallel=8 --suite=main,innodb,innodb_undo,binlog,rpl,perfschema,sys_vars
Then run the sysbench-tpcc test with 64 threadssysbench /opt/tools/sysbench-tpcc/tpcc.lua --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=app_test --mysql-password=test --mysql-db=tpcc --db-driver=mysql --tables=10 --scale=100 --rand-type=uniform --report-interval=1 --histogram --report_csv=yes --stats_format=csv --db-ps-mode=disable --trx_level=RR --enable_purge=yes --time=600 --threads=64 --mysql-ssl=PREFERRED --mysql-ignore-errors=none --reconnect=0 runAnd got the profile as[root@sm-blade03 profile]# llvm-profdata-20 show -detailed-summary /opt/mysql_source/profile/mysql.profdata
/opt/mysql_source/profile/mysql.profdata
Instrumentation level: IR entry_first = 0 instrument_loop_entries = 0
Total functions: 52403
Maximum function count: 289910820864
Maximum internal block count: 16881288068
Total number of blocks: 619050
Total count: 2473216025729
Detailed summary:
2 blocks (0.00%) with count >= 289910820864 account for 1% of the total counts.
2 blocks (0.00%) with count >= 289910820864 account for 10% of the total counts.
2 blocks (0.00%) with count >= 289910820864 account for 20% of the total counts.
11 blocks (0.00%) with count >= 16777125216 account for 30% of the total counts.
32 blocks (0.01%) with count >= 7806039615 account for 40% of the total counts.
77 blocks (0.01%) with count >= 3839409465 account for 50% of the total counts.
160 blocks (0.03%) with count >= 2273821592 account for 60% of the total counts.
312 blocks (0.05%) with count >= 1111890303 account for 70% of the total counts.
719 blocks (0.12%) with count >= 365989756 account for 80% of the total counts.
2165 blocks (0.35%) with count >= 85858392 account for 90% of the total counts.
4893 blocks (0.79%) with count >= 24359463 account for 95% of the total counts.
16760 blocks (2.71%) with count >= 2841200 account for 99% of the total counts.
45006 blocks (7.27%) with count >= 148881 account for 99.9% of the total counts.
96619 blocks (15.61%) with count >= 8640 account for 99.99% of the total counts.
157023 blocks (25.37%) with count >= 960 account for 99.999% of the total counts.
220001 blocks (35.54%) with count >= 91 account for 99.9999% of the total countsChecking on how counts concentrate: just 2 blocks account for 20% of all executed instructions across the entire training run (almost certainly a tight InnoDB buffer-pool/redo-log/lock_manager loop counts in the hundreds of billions), while 90% of total execution volume is concentrated in only 2,165 blocks (0.35% of all covered blocks).
That’s the “hot core” PGO is designed to find and optimize aggressively. Meanwhile the long tail, the other 96%+ of blocks, still has nonzero counts (this is a sparse profile, so anything appearing here was actually executed at least once).
Meaning a huge amount of MTR’s functional-path breadth got captured even though it’s numerically dwarfed by sysbench’s tight hot loops.
This is the ideal shape for a merged profile: a small, extremely hot core (from sustained sysbench load) sitting on top of broad, low-frequency-but-nonzero coverage (from MTR’s functional sweep).
If MTR had contributed nothing, I would have a much flatter, narrower distribution with far fewer total functions covered.
If sysbench had swamped everything with no MTR contribution, I would have a similar shape but with a much smaller “Total functions” number, since sysbench-tpcc only touches a fraction of mysqld’s total surface.
Command to build the final optimized binaries:llvm-profdata-20 merge -sparse /opt/mysql_source/profile/*.profraw -o /opt/mysql_source/profile/mysql.profdata
cmake ../mysql-9.7.2 \
-DCMAKE_INSTALL_PREFIX=/opt/mysql_templates/mysql-9.7.2-PGO-optimized-MTR-sysbench \
-DCMAKE_BUILD_TYPE=Release \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_PACKAGE_FLAGS=OFF \
-DCOMPILATION_COMMENT_SERVER="Marco compile 9.7.2-PGO optimized MTR+sysbench" \
-DCOMPILATION_COMMENT="Marco compile 9.7.2 PGO optimized MTR+sysbench" \
-DCMAKE_C_COMPILER=clang-20 \
-DCMAKE_CXX_COMPILER=clang++-20 \
-DFPROFILE_USE=ON \
-DFPROFILE_DIR=/opt/mysql_source/profile/mysql.profdata \
-DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LZ4=system -DWITH_ICU=system \
-DWITH_NUMA=ON -DWITH_LTO=ON -DWITH_LD=lld -DWITH_SYSTEMD=1 \
-DWITH_UNIT_TESTS=OFF -DWITH_ROUTER=OFF -DMYSQL_MAINTAINER_MODE=OFFRe-running the test I got this:
As expected the benefit I got was minimal, something was there but that disappeared while the concurrency increased.
Conclusions
Non-PGO vs PGO comparison: we had a 12% increase with low concurrency. But when simulating a more realistic load with higher contention the win becomes smaller and smaller, under 2%.
Training-workload choice mattered a lot: a sysbench-tpcc-trained PGO build ended up slower than a non-PGO.
After switching to a merged MTR + moderate-concurrency-sysbench training profile and re-running, the gain was still minimal, and it shrank further as concurrency increased.
Why the sysbench-only build lost
Sysbench-tpcc only exercises a narrow slice of mysqld, so everything outside that slice (purge, flushing, stats, error paths, alternate optimizer plans) gets pessimized as “cold” code.
At 128–1024 threads, GCC’s non-atomic profile counters get corrupted under contention unless I explicitly set -fprofile-update=atomic,a garbage profile is worse than no profile at all.
Instrumentation overhead (2–10x slowdown) distorts what looks “hot” under load mutex/rwlock spin loops dominate the counts, so the compiler optimizes waiting code instead of real work, while the actual bottleneck (lock contention, memory latency) is something branch layout can’t fix anyway.
Bottom line: PGO for MySQL/Percona Server binaries does work in the sense that the mechanism is sound whole-binary function reordering for something the size of mysqld is a legitimate, often the single biggest, win in PGO generally.
But empirically here the payoff was consistently small (sub-5%, trending toward sub-1% for the concurrency-specific refinement), fragile to training-workload choice, fragile to build-flag correctness (atomic counters, -fprofile-partial-training), and it erodes further as thread count rises, which is exactly what happens in production and what we care about most.
So my practical answer: it’s not a clear “yes, always compile with PGO.” It’s more a “maybe, and only if you get every detail right“. Correct broad-coverage training data (MTR, ideally merged with moderate-concurrency sysbench), atomic profile counters, and realistic expectations that the win is marginal and shrinks under heavy concurrency.
Get any of those wrong and you can end up worse than a plain build.
Given the size of the benefit versus the number of ways to mess up the training methodology, PGO reads more like a niche optimization for a well-controlled build pipeline than a default you’d flip on broadly.
I would be more than happy to prove wrong and I am eager to get other people’s feedback, so please test, test, test and let me know.
Happy MySQL to everyone
Go to:
What is PGO
How PGO it works
The post PGO or not PGO this is the dilemma. Step 3 appeared first on Percona.
-
PGO or not PGO this is the dilemma. Step 2
Step 2: How it works
Given the topic complexity and the length of this article I have split it in 3 three different blog-post:
What is PGO
How PGO it works
Why my sysbench-trained build loses and how to do it right.
How PGO works: PGO is a two-pass build. First pass compiles with instrumentation (-fprofile-generate): every basic block and branch gets a counter.
You run a training workload, counters are dumped to profraw files.
Second pass recompiles using those counts to drive inlining decisions, branch layout (hot path falls through, cold path jumps away), hot/cold function splitting, code ordering for icache/iTLB locality, loop unrolling, and indirect-call promotion.
Crucially, PGO is not “make the trained workload fast” it’s “tell the compiler which code is hot and which is cold, and let it reshape the whole binary accordingly.”
But how does it work?
Phase 1: what actually gets recorded. The instrumented binary has counters injected at compile time one per edge in the control-flow graph, not just per function.
So for every branch, every loop back-edge, every call site, there’s a counter that increments each time execution takes that path.
This is finer-grained than “function X was called N times” it’s “when we reached this branch, we went left 950,000 times and right 50 times.”
That per-edge granularity is what lets the second phase make surgical decisions rather than just “function X is hot, function Y is cold.”
Phase 2: what the compiler does with those counts. Several distinct transformations, all driven by the same counter data:
Inlining.
Normally the compiler inlines based on static heuristics:
function size call-site count
estimated cost/benefit.
With profile data it can override those heuristics: a call site executed millions of times gets inlined even if it looks “too expensive” by static cost rules, because the runtime benefit clearly outweighs the code-size cost.
A call site that’s technically inlinable but sits in dead-cold code gets left as a real call inlining it would only bloat the binary for no benefit.
Branch layout.
Every “if” in our code compiles down to a branch instruction with two possible outcomes:
continue straight to the next instruction
jump somewhere else.
Continuing straight is basically free; the CPU is already fetching instructions in order, so there’s no extra cost.
Jumping is not free: the CPU has to guess in advance which way a branch will go so it can keep fetching ahead of time, and if it guesses wrong, it has to throw away the work it already queued up and start over from the right place.
That is the “pipeline bubble,” a small stall. So the “straight through” path is cheap and the “jump elsewhere” path carries a penalty when the guess is wrong.
The compiler arranges the hot path as the fall-through and pushes the cold path out of line; literally relocated to a separate location in the binary, often into a .text.unlikely section.
So an if (unlikely_error_condition) { …rare handling… } block doesn’t sit inline interrupting the hot path anymore; it is moved somewhere else entirely, and the hot path becomes a straight run of instructions with no diversion.
To be clear, reordering our if/else in the source code usually doesn’t change how the compiler lays out the machine code. Optimizing compilers decide branch layout themselves based on either profile data (PGO) or static heuristics, not on which branch we happened to write first in the source. So swapping the order of our if blocks by hand generally has little to no effect on the compiled result.
Hot/cold function splitting.
This is the same idea applied within a single function.
A function might have a hot core loop and a rarely-hit error-handling tail.
The compiler physically splits the function into two pieces:
the hot part stays in .text.hot
the cold part moves to .text.unlikely.
The function still works identically (a jump connects them when needed), but now the hot part is smaller and denser, so more of it fits in an instruction-cache line, and cold code that’s almost never touched isn’t wasting icache space sitting next to it.
Whole-binary function reordering.
This is where “reshape the whole binary” becomes literal.
At link time (especially with LTO, which MySQL’s PGO build enables), functions get physically reordered in the final executable so that functions which call each other frequently, or execute in sequence during a hot workload, are placed near each other in memory.
This maximizes instruction-cache and iTLB locality; the CPU’s fetch unit is pulling in a tight cluster of hot functions instead of jumping all over a 100+MB binary.
For something the size of mysqld, this is often the single biggest win, because normal builds place functions in whatever order the source files happen to be compiled, which has no relationship to runtime call patterns.
Indirect-call promotion.
If profile data shows a virtual call or function-pointer call resolves to the same target the overwhelming majority of the time (common in C++ with vtables, e.g. a storage-engine interface with basically only InnoDB registered), the compiler inserts a guarded direct call: “if target == this specific address, call it directly and skip the indirect jump; otherwise fall back to the indirect call.”
Direct calls are cheaper and more predictable for the branch predictor than an indirect jump through a table.
Register allocation and code density trade-offs.
Hot code gets compiled favoring speed, more aggressive unrolling, more registers dedicated to hot-path values.
Cold code, especially with -fprofile-partial-training and cold-path treatment, gets compiled favoring size, fewer registers, less unrolling because it barely executes. So runtime cost there is irrelevant but its footprint in the binary is not free (it still occupies disk/page-cache space and can evict hot lines from cache if placed carelessly, which is exactly why it gets segregated into .text.unlikely rather than just left unoptimized in place).
Switch/jump-table lowering.
A switch statement with many cases can be compiled as a jump table (fast, O(1), but requires a full table load and indirect jump) or as a cascade of compares (slower per-case but better branch prediction if one case dominates).
Profile data tells the compiler which case actually dominates in practice and picks accordingly.
Given the above, “reshape the whole binary” is not metaphorical. The compiler is redrawing the physical layout of machine code in the executable: which instructions are adjacent to which, which code sections are hot and packed tightly versus cold and shoved to the side, which calls are direct versus indirect, and where the CPU’s fetch/prediction effort gets spent.
None of this requires the values processed during training to resemble production traffic. It only requires the shape of control flow.
Which branches, functions, and paths are frequently exercised to resemble production traffic.
That’s the core reason MTR’s broad-but-different-data coverage transfers well: it walks nearly every code path in mysqld even though the actual queries and data are nothing like a TPCC workload, and control-flow shape is exactly what PGO optimizes for.
Why my sysbench-trained build loses and how to do it right.
The post PGO or not PGO this is the dilemma. Step 2 appeared first on Percona.
-
PGO or not PGO this is the dilemma. Step 1
Given the topic complexity and the length of this article I have split it in 3 three different blog-post:
What is PGO
How PGO it works
Why my sysbench-trained build loses and how to do it right.
Step 1: What is PGO
PGO (Profile-Guided Optimization) is a two-pass compilation technique.
First, we build the program with instrumentation that adds counters to every branch, loop, and call site.
Next, we run it against a representative training workload so these counters can record which code paths are actually hot versus cold.
We then recompile the program using that data to drive the compiler’s decisions.
With this profiling data, the compiler inlines hot call sites more aggressively and lays out hot paths as straight-line, fall-through code while pushing cold paths out of the way.
Finally, it physically reorders functions in the binary so frequently interacting hot code sits close together for better instruction-cache locality, and converts frequently resolved indirect calls into direct ones.
The result isn’t “make the training run fast” but rather “reshape the entire binary’s layout around real execution patterns,” which is why the quality and representativeness of the training workload matters so much to whether PGO actually helps.
It is nice to be wrong
Not so far ago I was wondering if having Percona Server compiled with PGO default is a good idea or not. Then I started to do some tests and I end up with this:
If I compare non PGO with PGO release I can see an optimization, minimal below 5% but is there.
If I compile MySQL using PGO and use as sample the recording of a specific test say sysbench-tpcc my compile will always be slower than my compile without PGO, no mater how many threads I use during recording, I tried from 128 to 1024.
Given my understanding of PGO was that it should be the other way around I was a bit disoriented. So I decided to read a bit and get a better understanding of what PGO really means/does and if it makes sense or not.
How PGO it works
The post PGO or not PGO this is the dilemma. Step 1 appeared first on Percona.
-
Step by Step: ProxySQL HA with BGP ECMP Anycast
In our previous blog post ProxySQL HA with BGP ECMP Anycast, we established why we would want to use BGP ECMP as a strategy for making our ProxySQL cluster highly available. Now let’s look at how to implement it technically.
For this scenario we assume that we have:
an OPNsense instance as our BGP Router with the IP address 10.5.8.251
Two ProxySQL nodes (proxysql-01 with IP 10.5.20.4 and proxysql-02 with IP 10.5.20.5)
10.5.200.1 as the anycast IP we want both ProxySQL nodes to accept connections on
Setup on the router
Enable BGP on OPNsense
The out-of-the-box installation of OPNsense does not come with BGP support. To add it, install the FRRouting package first (os-frr), which will enable available protocols for dynamic routing.
Install the os-frr plugin in System -> Firmware -> Plugins.
We need to activate the routing service on our OPNsense host.
In Routing -> General check “enable”
Next, assign an Autonomous System (AS) number to the OPNsense BGP peer.
An AS number identifies a network, or a group of routers, under a single administrative domain.
Choose a private AS number from the range 64512-65534.
In Routing -> BGP (advanced mode):
check “enable”.
set the chosen AS number (in this example we use 64512).
set Maximum Paths to 2 to match the number of ProxySQL nodes that we have.
Maximum Paths configures OPNsense to perform Equal-Cost Multi-Path (ECMP) load balancing across the two paths.
Any additional paths are kept as backups and only used if an active route is withdrawn.
Create a firewall rule for BGP traffic
BGP exchanges routing information through a TCP connection on port 179.
Our ProxySQL nodes will establish this connection towards OPNsense, so we need a firewall rule to allow this traffic.
First, we will create an alias for the ProxySQL nodes. This keeps the firewall rule readable, and new nodes only need to be added in one place.
In Firewall -> Aliases, add:
Name: proxysql_nodes
Type: Host(s)
Content: 10.5.20.4, 10.5.20.5 (The IP addresses of our ProxySQL nodes)
With the Alias in place, we can now continue to define the actual Firewall rule:
Navigate to Firewall -> Rules and create a new rule:
Description: Allow ProxySQL network to BGP peer with OPNsense
Interface: Select the interface the ProxySQL nodes are in.
Action: Pass
Direction: in
Version: IPv4
Protocol: TCP
Source: proxysql_nodes (the alias we created in the previous step)
Source port: any
Destination: the OPNsense address the ProxySQL nodes peer with (10.5.8.251)
Destination port: BGP (179)
Note, here we do not explicitly enable logging. It may be helpful to enable logging that if you need to debug the BGP peering.
Set up route filtering with a prefix list
We need to set up an inbound filter using a prefix list.
The prefix list defines the networks that OPNsense will accept routes for, and should be as narrow as possible to prevent a misbehaving peer from advertising routes that could re-route sensitive traffic through it.
Prefix lists are configured in Routing -> BGP -> Prefix Lists.
Create the prefix list:
Description: ProxySQL Anycast IP
Name: ANYCAST-PROXYSQL-IN
IP Version: IPV4
Sequence Number: for this example we use 10. Sequence numbers are used in order to set the order with which to apply the rules, in case you would have multiple rules for the same prefix list. We only need to create one entry for the ProxySQL Anycast IP.
Action: Permit, as we want to allow the ProxySQL Anycast IP.
Network: 10.5.200.1/32, the Anycast IP of our ProxySQL nodes.
Tip: Click the ⓘ icon next to a field in the OPNsense UI for more information.
Configure BGP neighbors
In this step we will configure OPNsense to know about our ProxySQL servers and their intent to peer.
Neighbor configuration defines which IPs are allowed to speak the BGP protocol with OPNsense. In our case, we need to define a neighbor for both of our ProxySQL hosts:
Choose a unique AS number for the cluster. All ProxySQL nodes belonging to this cluster will share this AS number, whilst separate clusters must use different, dedicated AS numbers.
Ensure that the cluster AS number is distinct from the one configured for OPNsense.
For this example we choose the AS number 64513.
Navigate to Routing -> BGP -> Neighbors and add a new one.
Description: ProxySQL-01
Peer-IP: 10.5.20.4
Remote AS: 64513
Local AS: 64512
Prefix-List In: ANYCAST-PROXYSQL-IN:10
Make sure you repeat this for the second ProxySQL.
All configurable BGP options can be found in the OPNsense documentation.
Setup on the ProxySQL nodes
Next, assign the anycast virtual IP (10.5.200.1) to the loopback interface of each ProxySQL node, so that the node accepts traffic intended for that IP address.
bash
Copy
Copied!
# Add the Anycast VIP to the loopback interface
sudo ip addr add 10.5.200.1/32 dev lo
Make the address persistent across reboots by adding it to netplan, systemd-networkd or /etc/network/interfaces.
Install ExaBGP
ExaBGP is a tool that can speak the BGP protocol with OPNsense and is able to announce / withdraw routes.
Install ExaBGP on the ProxySQL hosts with:
bash
Copy
Copied!
sudo apt-get update
sudo apt-get install exabgp
For information on installing ExaBGP on other distributions, you can check the ExaBGP wiki here.
Note that BIRD (BIRD Internet Routing Daemon) or FRR can be used as alternative tools to ExaBGP, but in this example we will go with ExaBGP.
Create the health check
As we only want to route traffic to a ProxySQL host when the ProxySQL process is running, we need a way to tell ExaBGP when to announce the route and when to withdraw it.
We will write a health check script that ExaBGP can execute to determine the state of our ProxySQL process.
If this health check fails, ExaBGP withdraws the node’s route from OPNsense. OPNsense removes that node as an anycast next hop, and routes the traffic to the remaining healthy nodes.
In our example, we will use a simple bash script that uses mysqladmin to send a PING to ProxySQL:
bash
Copy
Copied!
#!/usr/bin/env bash
ANYCAST_IP="10.5.200.1/32"
FAILED=1
while true; do
# Execute the health check command and depending on the return code
# announce or withdraw the route.
mysqladmin --defaults-extra-file=/etc/exabgp/proxysql-monitor.cnf --connect-timeout=2 ping &> /dev/null
STATUS=$?
if [ $STATUS -eq 0 ]; then
if [ $FAILED -ne 0 ]; then
# Recovered: Announce route
echo "announce route $ANYCAST_IP next-hop self"
FAILED=0
fi
else
if [ $FAILED -eq 0 ]; then
# Health check failed: Withdraw route
echo "withdraw route $ANYCAST_IP next-hop self"
FAILED=1
fi
fi
sleep 2
done
Save the health check file as /etc/exabgp/healthcheck-proxysql, and make it executable with chmod +x /etc/exabgp/healthcheck-proxysql.
To avoid passwords in the check script, we tell mysqladmin to load them from a separate file. Create that file (/etc/exabgp/proxysql-monitor.cnf) with the credentials that you want the check to use to connect to ProxySQL:
bash
Copy
Copied!
[client]
user=user
password=password
host=127.0.0.1
port=6033
Ensure that you replace “user” and “password” with your actual user credentials. Restrict the file permissions with:
bash
Copy
Copied!
sudo chown exabgp:exabgp /etc/exabgp/proxysql-monitor.cnf
sudo chmod 600 /etc/exabgp/proxysql-monitor.cnf
In our example, the script only checks if connecting to ProxySQL succeeds, and not whether ProxySQL can reach any backend servers.
The checks you come up with should ideally only test the readiness of the ProxySQL process, regardless of the health of the MySQL nodes behind it. Otherwise you might get unwanted side effects. For example:
you could write a check to verify whether there are hosts with status ONLINE in the runtime_mysql_servers table. At first it might sound like a good idea, as a misconfigured ProxySQL server would be taken offline.
BUT: In case your MySQL cluster is experiencing a downtime, ALL ProxySQLs will withdraw their routes, causing clients to see timeouts instead of potentially helpful error messages. Define your health checks carefully based on your specific architecture.
Configuring ExaBGP
ExaBGP configuration lives in the /etc/exabgp/exabgp.conf file. You can find full details on what can be configured there in the ExaBGP documentation.
For the purpose of this blog post, we will define three blocks.
The process block defines the path to the health check script.
The template block defines the BGP settings and which health check process to run.
Each neighbor block defines the router we want ExaBGP to peer with.
bash
Copy
Copied!
# -------------------------------------------------------------------
# Process Definitions
# -------------------------------------------------------------------
process proxysql-healthcheck {
run "/etc/exabgp/healthcheck-proxysql";
encoder text;
}
# -------------------------------------------------------------------
# Neighbor Templates
# -------------------------------------------------------------------
template {
neighbor opnsense-nodes {
router-id 10.5.20.4;
local-as 64513;
peer-as 64512;
api {
processes [ proxysql-healthcheck ];
}
}
}
# -------------------------------------------------------------------
# OPNsense nodes
# -------------------------------------------------------------------
neighbor 10.5.8.251 {
inherit opnsense-nodes;
local-address 10.5.20.4;
}
Create the file on proxysql-01 and proxysql-02, but make sure to update the neighbor and template block. The router-id and local-address should be set to 10.5.20.5 on proxysql-02.
Once you have created this file, restart ExaBGP.
bash
Copy
Copied!
systemctl restart exabgp
You can check the status of ExaBGP by running exabgpcli show neighbor summary on the ProxySQL hosts.
An overview of the workflow for a healthy ProxySQL node
ExaBGP starts the health check script that we configured in exabgp.conf
The script sees that ProxySQL is running, and outputs announce route 10.5.200.1/32 next-hop self.
ExaBGP receives this output and sends a BGP UPDATE message to OPNsense.
OPNsense adds the ProxySQL node as potential “next hop” to its routing table for 10.5.200.1.
If the health checks detects that ProxySQL is not running, it will output withdraw route 10.5.200.1/32 next-hop self, which tells ExaBGP to send a BGP update for OPNsense to remove that route from its routing table.
The traffic is redistributed to the remaining healthy ProxySQL node.
Check BGP ECMP is configured correctly
Check the firewall
Ensure that the firewall is not blocking traffic by navigating to Firewall -> Log Files -> Live View. Make sure you have enabled logging in the firewall rule you created.
Filter for “address” “is” “10.5.200.1” and check that the connections are not blocked.
Check ExaBGP on the ProxySQL
Run the health check on the ProxySQL to confirm that the ProxySQL reports that it is healthy, and announces the route.
bash
Copy
Copied!
sudo -u exabgp /etc/exabgp/healthcheck-proxysql
You should see output like:
bash
Copy
Copied!
announce route 10.5.200.1/32 next-hop self
Check that ExaBGP announces the route with:
bash
Copy
Copied!
exabgpcli show adj-rib out
You should see output like:
bash
Copy
Copied!
neighbor 10.5.8.251 ipv4 unicast 10.5.200.1/32 next-hop self
Check that ExaBGP has an established BGP session to OPNsense with:
bash
Copy
Copied!
exabgpcli show neighbor summary
You want to see that the state is established. State active means “ready to connect”, but no connection is actually made.
bash
Copy
Copied!
Peer AS up/down state | #sent #recvd
10.5.8.251 64512 0:00:57 established 2 6
Verify the BGP connections and routes on OPNsense.
Navigate to Routing -> Diagnostics -> BGP to check the routing status.
The Anycast IP should appear twice, one entry for each ProxySQL. Both entries should be marked valid.
The path should show the AS number 64513.
BGP always selects a single best path. With Maximum Paths set, the other equal-cost paths are also installed in the routing table and are flagged as multipath.
You can also run vtysh on the OPNsense CLI to check this:
bash
Copy
Copied!
vtysh -c "show ip route 10.5.200.1/32"
You should see an entry like:
bash
Copy
Copied!
Routing entry for 10.5.200.1/32
Known via "bgp", distance 20, metric 0, best
Last update 00:05:12 ago
Flags: Selected
Status: Installed
* 10.5.20.4, via vtnet1, weight 1
* 10.5.20.5, via vtnet1, weight 1
The * next to each result shows that this is an active next-hop path.
Because the two results share the same weight (1), ECMP is active, and OPNsense will load balance the tcp connections equally across the two routes.
Summary
In this blog post we stepped through an example setup of BGP ECMP Anycast for ProxySQL. We configured OPNsense to accept anycast routes from our ProxySQL nodes and load-balance across them. On each node, ExaBGP announces the route whilst ProxySQL is healthy and withdraws the route when ProxySQL fails. To scale the cluster, add the new node to the proxysql_nodes alias, create a BGP neighbor for it, and raise Maximum Paths to match the new node count.
This post is part of the Percona Community Writers Program.
-
Your Galera cluster’s hardest problem was never the replication library
Ask anyone who runs a multi-node MySQL Galera Cluster in production about their worst night. Almost nobody says write-set replication was wrong. They say something closer to this:
“All queries piling up… no error messages in logs.”
Twenty minutes of downtime, ended only when a human picked a node to kill. (codership-team)
Or the flow-control stall where writes time out and wsrep_flow_control_sent still reads 0, so there’s nothing to alert on and nothing to write in the post-mortem. (codership-team)
You have your own version of these. You didn’t need mine.
And here’s the thing about all of them: the root cause is usually not Galera. It’s flashcache, or a shadowed wsrep_provider_options line, or DNS resolving to the wrong interface, or a one-way firewall path. One team diagnosed a Kubernetes crash loop by reading the operator’s source code.
So your MTTR isn’t dominated by repair. It’s dominated by diagnosis. The cluster is not hard to fix once you know what’s wrong, but it’s hard to know what’s wrong at 3am with writes stalled and clean logs.
That’s what changed on 30 September 2026.
What the EOL actually takes away
MariaDB plc acquired Codership in May 2025 and set the end of support for all current MySQL Galera Cluster versions at 30 September 2026. No development, no maintenance, no binary releases after that.
Your cluster will behave on 1 October exactly as it did the day before. Nothing new breaks. What disappears is the two things that used to bound those incidents: someone to escalate to, and a release that eventually contains the fix.
MariaDB Enterprise’s recommended way out is migrating to MariaDB Galera Cluster. That’s a different database: distinct system table structure, a data dictionary that differs fundamentally from MySQL’s, and user privileges that MariaDB’s own migration notes say you have to systematically recreate. You built this HA layer on open source MySQL because you wanted control. Changing database vendor to keep a support contract is the opposite of control.
Two paths that don’t ask you to:
Keep the cluster, buy back the escalation path
If diagnosis is the expensive part, what shortens it is a senior engineer who has seen your failure mode before. Not a new binary.
Percona MySQL Support covers your existing MySQL Galera Cluster as it is. No migration, no re-platforming, no privilege rebuild. Senior MySQL engineers 24x7x365 on follow-the-sun, consultative support as well as incident triage (wsrep tuning, DDL strategy, write distribution, quorum design), and contractual SLAs down to 15-minute initial response for Severity 1 incidents on Premium tier. Details in the support datasheet.
Or move to PXC, where the cluster layer still gets fixed
Percona XtraDB Cluster is Percona Server for MySQL plus the Galera library – our own fork, in a public repo, compatible with MySQL Community Edition.
The difference from the MariaDB path is mechanical: your schema, system tables, and user accounts carry over as they are. Same wsrep behavior, same ecosystem, same tools, same connectors. The ten-step walkthrough is public and free to follow on your own.
The reason to pick PXC isn’t that it’s another Galera build. It’s that the failure modes at the top of this post are what we ship fixes for: IST and SST behavior, node eviction, flow control, DDL under concurrent writes. Public release notes, Jira IDs attached, every quarter:
That cadence goes back to 2012, across 5.5, 5.6, 5.7, 8.0 and now 8.4, with the 8.0 line still shipping alongside. In the last 90 days, nearly 4,000 PXC host instances reported telemetry outside Kubernetes, and the PXC Operator runs across thousands more Kubernetes deployments. The engineers you’d escalate to are in this cluster layer every week.- See the recent work on gcache inspection and cross-site replication in the Operator.
If you’d rather not migrate alone, our migration service includes a test environment on the target cluster with a wsrep behavior comparison against your current one: you watch it handle the same failures before you commit, plus a documented rollback plan, scheduled cutover with off-hours support, and a health audit a few weeks after go-live.
What’s not an option
Running unmaintained. Not because of the date since your cluster won’t notice the date. Because the next time all the queries pile up and the logs say nothing, there’s no ticket to open and no release to wait for.
Support buys time. Migration ends the exposure. Doing both, in that order, is a perfectly good plan. Either way your clustering layer stays open source, stays on MySQL, and stays supported.
Talk to us about which one fits.
The post Your Galera cluster’s hardest problem was never the replication library appeared first on Percona.
|
