HF PanelDocsv1.0.0

Database tools

Switch MariaDB's slow query log and PostgreSQL's query statistics on, see the slowest queries of every account and failed dumps.

The Database tools screen in Server Admin holds the server-wide part of the database tools. Customers use their own Database tools screen for their databases: slow queries with EXPLAIN, maintenance, dumps and growth.

Where: Server Admin › Hosting › Database tools

Slow query log (MySQL/MariaDB)

  1. Tick Record slow MySQL queries.
  2. Set the Threshold (milliseconds), from 100 to 60000 (default 1000).
  3. Click Save.

The setting takes effect at once and is kept in /etc/mysql/mariadb.conf.d/61-hostfog-slow.cnf, so it survives a restart of MariaDB. The log is /var/log/mysql/mariadb-slow.log (rotated with MariaDB's other logs). HF Panel reads what is new in it every hour (hf-api dbtools), keeps only the queries of customer databases and adds them up per database and query; Read now reads it straight away. Customers only ever see the queries of their own databases.

PostgreSQL query statistics

PostgreSQL numbers come from its pg_stat_statements module. When the card says Query statistics (pg_stat_statements) are off, click Switch on: the module is added to shared_preload_libraries, PostgreSQL restarts once, and the extension is created. Statements whose longest run reached the threshold above are shown.

Note: for a PostgreSQL query with $1 placeholders, EXPLAIN needs PostgreSQL 16 or newer (it uses a generic plan).

Slowest queries and failed dumps

Slowest queries lists the 50 queries with the most time in total across every account, with the account and database. Failed dumps lists the scheduled dumps whose last run failed, with the error.

Packages: scheduled dumps

Each package has Scheduled dumps (0 = unlimited) in Packages: how many databases an account may dump on a schedule (default 5). Dumps made by hand are not limited; they count towards the account's disk space like its other backups.

How it works

  • EXPLAIN and maintenance run through the server agent as a temporary database user whose rights end at that one database (EXPLAIN: read only), and the client runs as the hosting account — never as the database root user.
  • Dumps are stored in /var/backups/hostfog/<account>/dbdumps/, readable by root only, and restored with the account's database rights.
  • Hook events: db.dump_finished and db.optimised (see Event hooks). Token API: dumps and slow queries under /v1/accounts/{username}/db-tools/ (see API: MySQL and PostgreSQL).