Percona install + tuning

The DB server is Percona Server, version-pinned per Devuan codename and installed by barracuda install with a BOA-shipped my.cnf and BOA-managed client-library set. There is one server per host.

This page covers:

• version selection,
• the staged in-place upgrade and its recovery guards,
• the upgrade-time memory tuner, and
• the monitor stack's high-load protection and its auto-heal.

One server per host also means one endpoint per host: each Octopus instance merely records where that shared server lives, in auto-managed variables — _THIS_DB_HOST / _THIS_DB_PORT in /root/.<user>.octopus.cnf, mirrored as $oct_db_host / $oct_db_port in /data/disk/<user>/.<user>.pass.php.

Finding _THIS_DB_PORT there is not a per-instance DB split and not an operator knob: it defaults to 3306, and BOA flips it to 6033 only when a ProxySQL marker file exists (per-instance /data/conf/<user>_use_proxysql.txt; host-wide /var/aegir/use_proxysql.txt) — a remnant of the discontinued Simple Cluster.

On a supported single-server host it is always 3306; do not hand-edit it — BOA recomputes the value from the marker and rewrites it (see octopus cnf).

Supported versions per OS

The Percona series (_DB_SERIES) is bounded by the host's Devuan codename:

On Excalibur, BOA installs 8.4 regardless of the install argument — 5.7 and 8.0 are not available there. The OS-pairing path is in the OS lifecycle section.

The table shows the effective series per OS, not the shipped default. The config baseline is a flat _DB_SERIES=5.7 in barracuda.sh.cnf regardless of OS, so a fresh /root/.barracuda.cnf literally reads _DB_SERIES=5.7.

The install/upgrade logic then rewrites it in place (sed into the .cnf): forced to 8.4 on Excalibur, and stepped 5.7 → 8.0 when a 5.7 host is upgraded. Read the running value from .barracuda.cnf after install, not the shipped default.

Initial install

At install time the Percona version is taken from the trailing argument:

The client-library set BOA installs alongside the server is version-specific (see the soname ladder below), not a single fixed package.

Staged in-place upgrade

Percona is upgraded in place, one major step at a time:

barracuda up-lts percona-8.0   # 5.7 -> 8.0
barracuda up-lts percona-8.4   # 8.0 -> 8.4

There is no 5.7 → 8.4 jump — go through 8.0. Each step:

1. backs up every database with mydumper,
2. stops the old daemon,
3. swaps the Percona packages,
4. runs mysqlcheck --auto-repair (plus mysql_upgrade --force on the 5.7 step),
5. starts the new daemon, and
6. reloads Drupal connections.

MySQL is down only while the daemon is stopped, packages swap, and the table fix/upgrade runs; the window tracks total DB size.

Requesting a series without an argument

The three /root/.percona.5.7.cnf, /root/.percona.8.0.cnf and /root/.percona.8.4.cnf markers are standing equivalents of the percona-X.Y token on the command line. barracuda reads them in _up_action on every upgrade run and, on a match, rewrites _DB_SERIES in /root/.barracuda.cnf and in the settings copy under /var/backups — exactly what the argument does, except that a file keeps saying it on every later run. Requests are tested in order 8.4, then 8.0, then 5.7, so the highest one present wins, whether it came from an argument or from a file.

Each arm is guarded on the installed Percona version (mysql -V), which is what makes the markers safe to leave in place:

None of them can downgrade a server, and none is a way round the staged path. On a 5.7 box .percona.8.4.cnf does not produce a direct jump: _sql_strict_upgrade_path() in sql.sh.inc sees a running 5.7 with a target that is neither 5.7 nor 8.0, and rewrites _DB_SERIES back to 8.0 for that run. The box therefore lands on 8.0 on the first upgrade run and on 8.4 on the next — the same two hops as the two commands above, spread over two runs.

Nothing in BOA removes these files. A marker left in place keeps asking on every later run; at the target series its guard no longer matches, so it sits there as a no-op until an operator deletes it.

Recovery guards

The staged path carries several guards in lib/functions/sql.sh.inc that make it recover cleanly rather than wedge:

• Directive sync is decoupled from InnoDB sizing. _sql_directives_sync() normalises only the version-compatibility directives (logging, binlog, performance_schema, removed options) for the target series, without touching InnoDB sizing or restarting mysqld. This is what keeps the file from ever carrying a directive the target version rejects (e.g. a stale log_syslog from 5.7 that 8.x aborts on) and removes the unbounded restart/resize recursion the old frequent-update band-aid masked. The per-version directive sets are detailed in my.cnf lifecycle.
• A down mysqld is started once, then the wait is bounded. _check_sql_running() samples pgrep -f /usr/sbin/mysqld plus /run/mysqld/mysqld.sock every 5 seconds. If the daemon is down it makes one start attempt — bash /var/xdrago/move_sql.sh start, because a bare service mysql start is a no-op on a stale pidfile, falling back to service mysql start only when that script is missing. After the package install on a 5.7 → 8.0 step nothing else brings mysqld back up, so this explicit start is what lets the upgrade proceed. The attempt is skipped while another operation holds a fresh SQL-maintenance marker at /run/boa_sql_maintenance.pid, so it does not fight a concurrent xoct, xmass or mysql_repair; a marker this run wrote itself, one whose writer has since died, and one older than 240 minutes all count as unheld and do not suppress the start. The wait itself is bounded two ways: roughly two minutes of consecutive dead samples (a mysqld that died on its config or datadir) or roughly one hour in total (a flapping one). A live process with no socket yet is normal — the first 8.x start runs the data-dictionary upgrade, minutes on a large datadir — and it resets the consecutive counter, so such a start is held only by the total ceiling. On either ceiling the run aborts with a FATAL naming both the down time and the total wait, rather than hanging; the maintenance marker is deliberately left in place, because this generic wait cannot know who owns it. _check_mysqld_running() carries the same shape ahead of memory tuning, kept as a separate copy by design. A box carrying /root/.proxy.cnf skips the whole check.
• mysqld is restarted unconditionally after startup-only directives. On the 8.4 path mysql_native_password=ON and authentication_policy=mysql_native_password,, are written into my.cnf (the plugin for legacy users, the policy so the handshake greeting itself is native — a PHP 5.6 client cannot answer a caching_sha2 greeting at all), but startup-only options take effect only on a (re)start. BOA restarts mysqld unconditionally before mysqlcheck / mysql_upgrade so the plugin loads and the root-credentials update can reconnect (a pgrep guard here raced the package's async start and never fired).
• The package phase is fail-closed for every series. On the Percona APT path, once the package attempts are done, BOA asserts that the server package is fully installed and that its dpkg version starts with the target _DB_SERIES before anything else runs. The package name BOA installs is the unversioned percona-server-server across all 8.x, so a name-only test would pass an 8.0 → 8.4 step that delivered nothing, and the _mrun wrapper swallows the installer's exit code by design. On a mismatch the run prints the observed dpkg state for percona-server-server*, removes /run/boa_sql_maintenance.pid and aborts — before any mysqld liveness wait and before any my.cnf tuning — so a died package phase cannot leave a half-flipped box waiting on a server that cannot exist. Re-run the upgrade after fixing the apt errors the log records above the abort.

Client-library soname ladder

The Percona client library is version-specific, selected by _DB_SERIES in sql.sh.inc:

The staged client path is therefore 20 → 21 → 24 — there is no 22 in the ladder. The libperconaserverclient22 / 22-dev purge you may see in upgrade logs is a one-off orphan-soname cleanup inside the generic _run_aptitude_full_upgrade() (paired with removing /usr/local/bin/mytop), not an 8.4-upgrade step and not a 22 → 24 bump.

The Ægir/Provision DB layer is 8.4-aware on the application side too:

• the PDO connect path requests the server public key for caching_sha2_password over TCP (guarded so it is a no-op on older client libraries), and
• user cleanup (revoke/dump) treats SHOW GRANTS as the source of truth — silently skipping REVOKE/DROP for absent users so 8.4's stricter behaviour does not abort a task.

That application-side detail is covered where the Provision DB service is documented; at this layer it matters when debugging 8.4 connection or auth failures.

Service-password rotation on install and upgrade

BOA rotates the MySQL root password and the Valkey and Redis passwords on its own schedule, and four /root/ markers decide whether a given pass does it. All four are empty files — no password value passes through any of them. The secrets themselves live in /root/.my.pass.txt, /root/.my.cnf, /root/.valkey.pass.txt and /root/.redis.pass.txt.

boa in-dev, in-pro and in-lts create the suppression trio (.mysql.no.new.password.cnf, .valkey.no.new.password.cnf, .redis.no.new.password.cnf) as one block, so a long install is not interrupted by a password change half-way through. They are cleared as one block too, but only once per box: the first autoupboa run that finds the install settled — three Percona lines in boa info, CSF present — deletes all three and stamps the immutable /var/log/boa/reset_no_new_password.pid, after which that cleanup never runs again. The same stamp is what tells the database watchdog the box is fully installed.

Polarity differs across the family, and the two cache markers are weaker than they look. valkey.sh.inc and redis.sh.inc rotate when the marker is absent or the stored password file is missing, so a host without /root/.valkey.pass.txt rotates whatever the marker says. On the MySQL side sql.sh.inc is a plain suppressor: the rotation runs only when .mysql.no.new.password.cnf is absent and one of /root/.mysql.yes.new.password.cnf or /root/.my.cluster_root_pwd.txt exists.

.mysql.yes.new.password.cnf is the opt-in twin — but only on 5.7 and 8.0. On a host whose _DB_SERIES is 8.4, _os_detection() calls _turn_on_password_update() on every barracuda run: it clears any immutable flag on .mysql.no.new.password.cnf and deletes it, then re-creates .mysql.yes.new.password.cnf and locks it with chattr +i. That call sits early in the run; the rotation gate that would have honoured the opt-out is reached much later, in _sql_root_credentials_update. Rotation is BOA-computed state on 8.4, not an operator choice — a hand-made opt-out there never takes effect: the next barracuda run deletes it before the gate is reached, and rotates in that same run. Deleting the locked file by hand needs a chattr -i first. Nothing in BOA ever removes it.

The Valkey rotation ends in a service valkey-server restart. That is the cache restart the memory-tuning note below refers to: on a standard upgrade run it happens minutes before the tune pass, which is why the measured Valkey ceiling depends on the sqlprobe sampler's stored peak rather than on live uptime at that moment.

Memory tuning

Memory sizing is done at install and on every upgrade pass by _tune_sql_memory_limits() (sql.sh.inc), called from the system-setup path. It re-deploys my.cnf.txt (unless _CUSTOM_CONFIG_SQL=YES), copies the live my.cnf aside to the pre-upgrade vault first, and sizes the buffers from detected RAM and what the box actually hosts (_sql_budget_derive() in system.sh.inc):

• InnoDB buffer pool — grows towards the measured table-data size (du over the datadir, excluding redo/undo/ibtmp1/binlogs, cached against measurement timeouts) under a tiered ceiling: 3/8 of usable RAM on big boxes (32 GiB+ after _RESERVED_RAM), 3/10 mid, 1/4 small — never below the historical RAM/8 floor, snapped to 1024M (the pool-instances × chunk granularity), and left at the historical value on the memory-fragile OpenVZ/VS families or on any measurement failure.
• tmp_table_size / max_heap_table_size — clamp(RAM/64, 32M..256M) per session (historically these granted the whole SQL budget per implicit temp table).
• key_buffer_size — 64M once a positive-evidence scan proves no user-schema MyISAM table exists; any MyISAM presence or scan failure keeps the historical 1024M.
• Valkey maxmemory — sized by a hit-rate control loop rather than by occupancy: a long-TTL cache fills whatever it is granted, so peak-based sizing only ever ratchets upward. Each tune pass reads the demand window the sqlprobe sampler maintains (/var/log/boa/.valkey.demand.txt — hits, misses, evictions and the observed peak accumulated between trusted samples) and moves the ceiling one bounded step: down by an eighth (at least 256M) while the hit rate holds at 90 % or better; down by a quarter (at least 512M) toward peak × 1.5 on an idle or over-granted cache; up by a quarter (at least 512M) only on a hit rate under that floor with evictions. A low hit rate without evictions is the workload, not the ceiling, and moves nothing. The result is never above RAM/3 and never below RAM/16 or 512M, and a move smaller than 128M is treated as noise rather than an action. The loop's state lives in its own store, /var/log/boa/.valkey.ceiling.txt (v1 <ceiling_mb> <action_epoch>), not in valkey.conf, which reinstall paths re-template earlier in the same run; the conf is consulted once only, to seed the first run, and only when it carries a plausible BOA-written value inside the floor/cap band. The loop acts only on a mature window (at least 96 accumulated samples spanning at least 24 hours), holds if it acted within the last hour or if the running instance still enforces a visibly higher ceiling than the store, and resets the window whenever it acts, so the next step is judged on post-action evidence. A shrink is applied to the running instance only when it sits at or above current occupancy; a deeper cut is written to the conf and takes effect at the next natural restart. A valid _VALKEY_MAXMEM_FORCE pin wins outright and disables the loop. A box with no usable demand window (fresh, or opted out of sqlprobe) falls back to the older measured guard — the cache's own used_memory_peak at ≥1h uptime, cached across the upgrade's own cache restarts, with 4× headroom, the same floors, and only ever below RAM/3 — and then to RAM/3. Redis-legacy boxes keep RAM/3 untouched.

All the my.cnf targets sit in the restart-suppressor's ignore list, so corrected values are written at upgrade time and take effect at each box's next natural database restart — there is no fleet-wide restart wave.

Note the deployed memorytuner binary is not in this path — it is invoked by nothing in the live tree, and the active tuner is _tune_sql_memory_limits(). Run by hand, memorytuner is advisory only, and its evidence comes from the sqlprobe monitor's corpus: a log-only sampler (on by default, _USE_SQLPROBE=NO opts a box out) that records per-second rate deltas between its own ~5-minute samples — buffer pool miss ratio, tmp-table disk spill, MyISAM key reads, connection headroom — alongside MemAvailable and the mysqld resident size. From warm samples (an hour or more after the last database restart) the advisory reports the observed distributions and, only where the evidence clears a stated threshold, emits the exact _SQL_*_FORCE pin lines to paste into /root/.barracuda.cnf, each with its reason. Its budget formulas run on the same reserved-adjusted RAM the tuning itself uses. Without a corpus (fresh box, opted out, or short uptime) it reports the running values and recommends nothing. It never edits my.cnf, never changes a running server, and never restarts anything; applying its advice is entirely the operator's call. (It used to apply and restart on its own — that behaviour is gone.)

The report has a second half below the pin recommendations, the needs ledger — observation only, and entirely separate from the advice above it. It reports what each consumer on this box actually uses, from the sqlprobe records that carry the per-consumer measurements: steady (p50) and peak (p95) resident size per consumer, each with a stated margin (need = peak + 20 %, a reporting convention for this phase rather than a ruling); each Solr instance against its own -Xmx; the PHP-FPM pool and worker topology beside the aggregate and per-process figures; Valkey's need on an occupancy basis, with the cache's own converging ceiling shown as context where one is in force and never added to the total; growth slopes per account from the dated /var/log/boa/usage series; and an OS reserve derived from the residual non-BOA resident size, floored at the larger of 1024M and a thirty-second of RAM. A sum check closes it: the consumer needs plus that reserve against total RAM, with file cache taken as the remainder rather than as demand, flagged when the remainder falls below an eighth of RAM. It names no pin, applies nothing and changes no recommendation. A box whose sqlprobe samples predate the per-consumer measurements simply reports that it has no ledger-capable samples, and nothing else degrades.

memorytuner --days N sets the corpus horizon for the whole report (default 7; a value that is not a positive number falls back to 7). The growth slopes are the exception — they always scan up to 92 days, so a short advisory window cannot flatten a real trend.

The sampler also keeps the derivation's two measurement stores fresh between weekly upgrades: it re-measures the table-data footprint about once a day, and it records the Valkey memory peak once the cache has an hour of uptime — which lets the measured Valkey ceiling engage on ordinary runs (previously it could only engage when a tune pass happened to run an hour after a cache restart, which standard upgrade runs never do — the upgrade's own password rotation restarts the cache minutes before the tune pass).

System RAM available to MySQL is reduced by _RESERVED_RAM first: the host-wide tuning logic in system.sh.inc subtracts _RESERVED_RAM from total RAM before sizing (defaulting it to one quarter of RAM when unset). It is written to /root/.barracuda.cnf and, per Octopus instance, to /root/.<USER>.octopus.cnf. Set it to hold RAM back from MySQL for non-BOA workloads on the box.

Correcting the derived values: per-knob pins

The derivation is dataset-aware, but a formula still cannot know everything about a workload. When a box needs a different value than the model produces — in either direction — set the matching pin in /root/.barracuda.cnf (_SQL_BUFFER_POOL_FORCE and friends) instead of hand-editing my.cnf: pins override the derivation both ways, BOA keeps managing everything else, and the pin survives upgrades, including Percona majors. See barracuda-cnf for the full pin list and my.cnf lifecycle for how pins compare with _CUSTOM_CONFIG_SQL=YES.

An earlier optional MySQLTuner integration (_USE_MYSQLTUNER) has been removed entirely: it had been off by default for years, could only lower values below the derived ceiling, and a full MySQLTuner run on a busy host was itself a load spike. The memorytuner advisory works from the sqlprobe corpus instead and never invoked MySQLTuner meaningfully. The retired toggle is stripped from existing barracuda.cnf files automatically; see Discontinued features for the record.

Custom my.cnf and collation

Persistent custom tuning goes in /etc/mysql/my.cnf (or, more safely, conf.d/ drop-ins). BOA re-deploys the template over my.cnf on every config pass unless _CUSTOM_CONFIG_SQL=YES — see my.cnf lifecycle for the protection model and the per-version directive sets.

Default server collation is utf8mb4_unicode_ci. Override host-wide with:

# /root/.barracuda.cnf
_CUSTOM_COLLATION_SQL=utf8mb4_general_ci

Per-site collation is a separate _SITES_COLLATION_SQL knob in the Octopus config (see Control files & INI).

High-load protection

The monitor (monitor/check/mysql.sh) acts at two distinct levels, and they must not be conflated. Neither of them restarts a database that is still answering.

Per-process kill (TTL ceilings). _mysql_proc_control walks the live process list and, in _mysql_proc_kill, runs mysqladmin -u root kill <id> on any single query that has run longer than its ceiling. This kills the one runaway query — it does not restart the server. The two ceilings are read from the host config:

_SQL_MAX_TTL=3600       # kill ceiling (seconds) for a normal MySQL process
_SQL_LOW_MAX_TTL=60     # kill ceiling for a "problematic" user's process

_SQL_MAX_TTL defaults to 3600 and _SQL_LOW_MAX_TTL to 60 when unset. A user listed in /etc/boa/.sql.problematic.users.cnf is held to the lower ceiling. Root-owned processes are skipped.

Load and saturation response. Separately, _mysql_high_load fires when the 1-minute load and the mysqld thread count both exceed their own thresholds — _LOAD_THRESHOLD (default 33.0) and _THREAD_THRESHOLD (default 99), not the TTL knobs above — and _sql_busy_detection fires on a Too many connections flood. Both hand off to _sql_restart, and on a server that is merely overloaded both stop there: _sql_restart first asks the server whether it is alive with a mysqladmin ping, and any reply at all — including Too many connections or an authentication error — means it is. A saturated server is a server that is up. The remedies that actually help — flush-hosts, and the per-process TTL kill above — run instead.

Recovery (down or wedged). Only the health check can reach a restart, and only after a mysqladmin ping has failed repeatedly across a short grace, which is what keeps a mysqld_safe respawn from being mistaken for an outage. The recovery is then database-only, through the dbrestart verb of move_sql.sh when mysqld is present but silent, or start when it is gone. It does not stop Nginx or PHP-FPM, wipe the cache, or drop the page cache — a database fault heals the database.

Repeated recoveries are paced and then stopped. _SQL_COOLDOWN_SECS (120) spaces two heals; a ledger at /var/log/boa/mysql.restart.ledger counts them inside _SQL_FLAP_WINDOW_SECS (3600); and once _SQL_FLAP_MAX (3) have run the circuit breaker latches /run/boa_mysql_restart_latched.pid, logs CIRCUIT OPEN, sends one e-mail and refuses to restart the database again until an operator deletes the latch or _SQL_FLAP_LATCH_MINS (60) passes. See Auto-healing watchdogs for the full mechanism.

The only .cnf gates this monitor honours (all under /etc/boa/) are .instant.busy.mysql.action.cnf, .mysqladmin.monitor.cnf and .sql.problematic.users.cnf. BOA never creates one from scratch — they exist only if an operator makes them. The single exception is history: a one-time shim copies a legacy /root/ copy of each into /etc/boa/, once per box and stamped by /etc/boa/.migrated.misc-toggles.txt, leaving the /root/ original in place and inert. /etc/boa/ is the only path the monitor reads.

.instant.busy.mysql.action.cnf — a second saturation detector. Without it, _sql_busy_detection decides a server is saturated from the system log alone: more than 111 Too many connections lines in the last 333, and no provision process running. Touch the file and the same function also asks the server directly on every pass — mysql -u root -e "status" — treating a Too many connections reply as the trigger. That live arm carries its own co-guards, a running mysqld and a non-empty /root/.my.pass.txt, and since 2026-08 it also stands down while a provision task is in flight, as the log arm always did.

What it does not do is restart. The trigger is the server replying Too many connections, and a reply of any kind proves it is answering, so the hand-off to _sql_restart returned without acting every single time — the marker bought nothing at all. It now relieves the saturation instead: flush the host cache, then the long-query sweep. That is not redundant with the sweep the monitor already runs each pass, because the later one is suppressed while /run/max_load.pid or /run/critical_load.pid exists — precisely the state that produces the saturation this marker exists to catch.

.mysqladmin.monitor.cnf — absence is the active state. Presence sets the monitor's internal _SQLMONITOR=YES. It then appends a full mysqladmin -u root proc -v process list to /var/log/boa/mysqladmin.monitor.log on every pass and, on the first pass after the flip, turns slow_query_log on with long_query_time = 5, writing to /var/log/mysql/sql-slow-query.log.

Absence is not a no-op. The else arm turns slow_query_log off on the running server and deletes both /var/log/boa/mysqladmin.monitor.log and /var/log/mysql/sql-slow-query.log — including slow-query logging an operator enabled by hand, and the evidence already collected. Each arm acts once per transition, stamped by /var/log/boa/.debug_slow_query.pid while the marker is present and /var/log/boa/.nodebug_slow_query.pid while it is absent, so removing the marker is what schedules the next switch-off, and the process-list log keeps growing for as long as the marker stays.

The monitor sources /root/.barracuda.cnf before it tests the marker, so a stray _SQLMONITOR=YES line there switches the same behaviour on. The marker is the supported surface — do not add that line by hand.

There is no .no.sql.cpu.limit.cnf disable — that marker survives only in the retiring reference docs and is inert; touching it does nothing.

Verifying the server

service mysql status
mysql -e "SELECT VERSION();"     # 5.7.x-... | 8.0.x-... | 8.4.x-...
boa info | grep -i db            # BOA's abbreviated DBV/DBS tags

As a quick completion check, boa info | grep -c Percona returns 3 once the install/upgrade chain has fully settled (including the cron-triggered background phase).

Dropped: Simple Cluster

The legacy Simple Cluster (Percona XtraDB Cluster + ProxySQL + Galera) is not supported — see Discontinued features. For DB-layer redundancy run two BOA hosts and flip via DNS, backed by the off-site backups suite.

Related

• my.cnf lifecycle + mycnfup — the template lifecycle, per-version directives, and _CUSTOM_CONFIG_SQL.
• Dumps with mydumper — the per-DB backup chain.
• Graceful MySQLD control — the clean restart the upgrade and self-heal paths drive.
• Auto-healing watchdogs — how the database watchdog decides the server is down, and the flap circuit breaker that stops it retrying for ever.
• Reference appendix — consolidated _VAR table (_DB_SERIES, _DB_SERVER, _USE_MYSQLTUNER, _RESERVED_RAM, _SQL_MAX_TTL, _SQL_LOW_MAX_TTL, _LOAD_THRESHOLD, _THREAD_THRESHOLD, _SQL_COOLDOWN_SECS, _SQL_FLAP_MAX, _SQL_FLAP_WINDOW_SECS, _SQL_FLAP_LATCH_MINS, _CUSTOM_COLLATION_SQL).