HF PanelDatabase tools
Find slow queries and missing indexes, optimise or repair tables, make scheduled dumps and follow how your databases grow.
The Database tools screen works on one of your MySQL or PostgreSQL databases at a time: its slowest queries, maintenance of its tables, dumps (now and on a schedule) and a chart of its size.
Where: Control Panel › Databases › Database tools
Pick the database in the Database list at the top; the tabs below show that database.
Slow queries
The Slow queries tab lists the queries of this database that took longer than the server's threshold (shown
above the list, for example "Queries slower than 1000 ms are recorded"). Queries that differ only in their values
count as one: WHERE id = 5 and WHERE id = 7 both show as WHERE id = ?.
| Column | What it shows |
|---|---|
| Query | the query with its values replaced by ? |
| Calls | how many times it was slow |
| Average (ms) / Longest (ms) | its average and longest time |
| Rows examined | the rows the database read for it, on average — many rows for a small answer usually means a missing index |
The list is updated every hour. Clear list starts it again from empty.
Note: if the screen says Slow queries are not being recorded on this server, your hosting provider has to switch the slow query log (MySQL) or query statistics (PostgreSQL) on first.
Explain a query
Click Explain next to a query. The database works out how it would run the query — without running it — and the dialog shows Suggestions, for example:
- Table wp_posts is read in full (about 250000 rows). — every row is read to find a few.
- An index on the columns used to filter or sort may help: post_status, post_type, post_date.
- Rows of wp_posts are sorted without an index or a temporary table is made — an index on the
ORDER BYorGROUP BYcolumns may help.
Query plan shows the database's own answer. EXPLAIN runs as a temporary database user that can only read this database, and is stopped after 10 seconds.
Maintenance
The Maintenance tab lists the tables with their rows and size, and runs one Operation on one table or on All tables:
| Operation | What it does |
|---|---|
| Optimise (MySQL) | gives back free space and rebuilds the indexes |
| Repair (MySQL) | mends a damaged table (MyISAM and Aria tables; InnoDB tables answer that they don't need it) |
| Analyse | updates the statistics the database uses to plan queries |
| Check (MySQL) | checks a table for errors |
| VACUUM ANALYZE (PostgreSQL) | gives back the space of deleted rows and updates the statistics |
Click Run, or the button in a table's row for that table only. The result per table is shown under the form, and earlier results stay under Earlier results.
Warning: tables are locked while an operation runs. For a database or table of 500 MB or more you are asked to confirm first; run it when your website is quiet. Only one operation or dump of a database runs at a time.
Dumps
A dump is a compressed .sql.gz copy of the database. MySQL dumps use one consistent snapshot
(--single-transaction), so your website keeps running while it is made. Dumps are kept with your account's backups
on the server.
- Dump now makes one straight away.
- Schedule: choose How often (Every hour, Daily, Weekly or Off) and how many to Keep (the newest dumps of this database; older ones are deleted when a new one is made). If you have your own backup storage, Also copy to copies every scheduled dump there too. The pill shows how many schedules you use of your package's limit.
- In the list: Download, Restore and Delete.
Restore a dump
- Click Restore next to the dump.
- Under Into, choose the same database, another database of the same kind, or A new database and type its Name.
- Click Restore and confirm.
MySQL: the tables in the dump replace the tables of the same name; other tables stay. PostgreSQL: the database is made again from the dump (its user and password stay). A new database counts towards your package's database limit; for a new PostgreSQL database its password is shown once.
Size & growth
The Size & growth tab charts the database's size, recorded once a day, over 30 days, 90 days or 365 days, with the change over that time.