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 restart.

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.

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, not waited on. _check_sql_running() checks pgrep -f /usr/sbin/mysqld plus the presence of /run/mysqld/mysqld.sock; if the daemon is down it runs service mysql start and loops on the socket. 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 instead of hanging.
• mysqld is restarted unconditionally after startup-only directives. On the 8.4 path mysql_native_password=ON is written into my.cnf, 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).

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.

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 against detected RAM.

Note the deployed aegir/tools/bin/memorytuner binary is not in this path. It is shipped and chmod'd by BOA.sh.txt but invoked by nothing in the live tree — no lib/, no system tooling, no cron/minute/second launcher calls it. Do not rely on it, configure it, or read its behaviour as authoritative; the active tuner is _tune_sql_memory_limits().

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.

_USE_MYSQLTUNER

The optional upstream MySQLTuner path is gated inside _tune_sql_memory_limits and is off by default (_USE_MYSQLTUNER=NO in barracuda.sh.cnf):

# /root/.barracuda.cnf — opt in to upstream MySQLTuner sizing on upgrade
_USE_MYSQLTUNER=YES

When enabled (and only on an UPGRADE pass), the tuner fetches and runs mysqltuner.pl, parses its MyISAM/InnoDB data-size recommendations, and feeds them into the key_buffer_size (and InnoDB) sizing — falling back to sane BOA defaults if the run fails or the recommendation is unusable.

Leave it off for typical Drupal workloads; enable it on hosts with unusual data-size profiles. This toggle governs the sql.sh.inc tuner only; it has no effect on the memorytuner binary.

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.

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.

Whole-server restart (load + thread). Separately, _mysql_high_load calls _sql_restart (which runs killall php then bash /var/xdrago/move_sql.sh) only 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.

The same _sql_restart is the path the busy-detection (Too many connections) and the down-server health check take. The restart itself routes through move_sql.sh.

The only .cnf gates this monitor honours (all under /etc/boa/) are:

• .instant.busy.mysql.action.cnf
• .mysqladmin.monitor.cnf
• .sql.problematic.users.cnf

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.
• Reference appendix — consolidated _VAR table (_DB_SERIES, _DB_SERVER, _USE_MYSQLTUNER, _RESERVED_RAM, _SQL_MAX_TTL, _SQL_LOW_MAX_TTL, _LOAD_THRESHOLD, _THREAD_THRESHOLD, _CUSTOM_COLLATION_SQL).