MariaDB performance analytics
Portable MariaDB Performance Schema helpers for Frappe benches. Snapshots statement digests, wait events, and table I/O into tables in the site database, then reports deltas between snapshots.
Runs under bench --site … console or bench execute. Not a Frappe app hook or patch — call enable() once per site after installing test_utils.
Prerequisites
MariaDB with Performance Schema enabled. If it is off, enable() prints guidance and exits without failing.
On Debian/Ubuntu, put server-only settings in mariadb.conf.d under [mysqld] — not in shared sections of mariadb.cnf (the client will reject them):
sudo nano /etc/mysql/mariadb.conf.d/99-performance-schema.cnf
[mysqld]
performance_schema=ON
event_scheduler=ON
Restart MariaDB after editing my.cnf.
Instruments and consumers are turned on at runtime by enable() (superuser). You do not need the MySQL-style performance-schema-instrument / performance-schema-consumer-* lines in my.cnf on MariaDB.
For the optional MariaDB EVENT scheduler, event_scheduler=ON in [mysqld] is required if SET GLOBAL event_scheduler=ON is blocked. enable() tries the global SET using the superuser connection.
Credentials
Two MariaDB roles, same pattern as bench new-site:
| Role | Connection | Used for |
|---|---|---|
| Site user | frappe.db (db_name / db_password in site config) | Snapshot tables, summary reads, snapshot(), top_*, most of status() |
| Superuser | Frappe get_root_connection() | enable() only — instrumentation, CREATE EVENT, and GRANT SELECT on Performance Schema summary tables for the site user |
During enable(), the superuser grants the site database user SELECT on the Performance Schema tables used by snapshot() and status(). Without that grant, Frappe site users cannot read events_statements_summary_by_digest and similar tables.
enable() uses Frappe’s get_root_connection():
root_login/root_passwordkwargs, ormariadb_root_*/root_*fromcommon_site_config.json- Username defaults to
rootif unset - Password is prompted (
MySQL root password:) if unset — same asbench new-site
If your bench already has root credentials for bubble backup, no prompt appears:
bench set-config -g root_login root
bench set-config -g root_password your_password
Pass credentials explicitly for non-interactive bench execute:
bench --site your_site execute test_utils.utils.mariadb_analytics.enable \
--kwargs "{'root_login': 'root', 'root_password': 'your_password'}"
Prompted passwords are not written back to site config.
One-time setup
# bench --site your_site console
from test_utils.utils.mariadb_analytics import (
enable, status, snapshot, top_statements, top_waits, top_table_io
)
enable() # default: EVENT every 15 min, retain 7 days
enable(schedule_minutes=0) # skip MariaDB EVENT; use manual snapshot or hooks
status()
Or:
bench --site your_site execute test_utils.utils.mariadb_analytics.enable
Ongoing use
snapshot() # collect now (also runs on EVENT schedule if enabled)
top_statements(20) # delta between last two snapshots (includes first-seen SQL)
top_waits(20)
top_table_io(20)
status()
top_* uses a left join from the newer snapshot, so statements/waits that first appear in the interval are included. Timer columns are picoseconds; reports are seconds (/ 1e12). top_statements also shows rows_examined, select_scan, and no_index_used deltas. Ranked by wait time. If counters reset, the newer absolute value is used for that interval.
Snapshot tables
Created in the site schema without the tab prefix (Frappe does not sync them):
ps_snapshot— snapshot id, time, server uptimeps_snapshot_digest— statement digest countersps_snapshot_wait— wait event countersps_snapshot_table_io— per-table I/O waits
Older snapshots are pruned after retain_days (default 7, baked into the MariaDB EVENT body).
Optional Frappe scheduler
If MariaDB EVENT creation fails or you prefer Frappe's scheduler:
# hooks.py — in a consuming app, not part of test_utils
scheduler_events = {
"cron": {"*/15 * * * *": ["test_utils.utils.mariadb_analytics.snapshot"]}
}
Instrumentation scope
Consumers enabled (not everything):
global_instrumentation,thread_instrumentation,statements_digestevents_statements_current,events_statements_history,events_waits_current
Instruments: wait/% and statement/% only (ENABLED and TIMED).
Digest table full
When events_statements_summary_by_digest fills, Performance Schema adds a row with DIGEST IS NULL. status() reports this (or says SELECT was denied). snapshot() and the MariaDB EVENT capture the overflow row first, then TRUNCATE the digest table. The Python path is best-effort as the site user; the EVENT runs as the superuser DEFINER.
Out of scope
AWS, CloudWatch, Performance Insights, parameter groups, Desk reports, and Greensight-specific patches. After installing test_utils on a bench, run enable() once on each site that needs analytics.