Operational Reports

Generates or schedules operational reports, as well as incident reports. An operational report is a comprehensive way to understand the state of your database cluster over time. The reports can be generated in place or can be delivered to you via email, which makes things conveniently easy if you have a reporting silo. For incident reports, ClusterControl will auto-generate an incident report via a feature called MySQL Freeze Frame when these two states are triggered:

• CLUSTER_DEGRADED
• CLUSTER_FAILURE

For operational reports, there are 9 types of reports available:

• System report
• Database availability report
• Backup report
• Schema change report – MySQL/MariaDB-based clusters only.
• Daily system report
• Database growth report
• Upgrade report
• Capacity report
• Incident report

See Types of Operational Reports for more details. Check out the operational report examples in this blog post, Become a ClusterControl DBA: Operational Reports for MySQL, MariaDB, PostgreSQL & MongoDB.

Reports

Provides a list of generated operational reports. Click on any of the entries that will open the generated operational report in a new window.

Schedules

List of scheduled operational reports and incident reports (if any).

Types of Operational Reports

Database Availability Report

The report generates the uptime/downtime and availability report for each cluster managed by ClusterControl. You can see information about the availability statistics of your databases, the cluster type, total uptime and downtime, the current state of the cluster, and when that state last changed.

Backup Report

The report summarizes backups for each cluster managed by ClusterControl. It contains two sections – backup summary and backup details, where the former basically gives you a short summary of when the last backup was created, if it was completed successfully or failed, backup verification status, success rate, and retention period. The latter section provides the list of backups executed on the cluster with the state, type, and size within the specified interval.

ClusterControl also provides examples of the backup policy if it finds any of the monitored database clusters running without any scheduled backup or delayed slave configured.

Schema Change Report

This report compares the selected MySQL/MariaDB database changes in table structure which happened between two different generated reports. In the MySQL/MariaDB older versions, the DDL operation is a non-atomic operation (pre 8.0) and requires full table copy (pre 5.6 for most operations) – blocking other transactions until it completes. Schema changes could become a huge pain once your tables get a significant amount of data and must be carefully planned especially in a clustered setup. In a multi-tiered development environment, we have seen many cases where developers silently modify the table structure, resulting in a significant impact on query performance.

In order for ClusterControl to produce an accurate report, special options must be configured inside the CMON configuration file for the respective cluster:

• schema_change_detection_address – Checks will be executed using SHOW TABLES or SHOW CREATE TABLE to determine if the schema has changed. The checks are executed at the address specified and are of the format {HOSTNAME}:{PORT}. The schema_change_detection_databases must also be set. A differential of a changing table is created (using diff).
• schema_change_detection_databases – Comma-separated list of databases to monitor for schema changes. If empty, no checks are made.

Supposedly we have a MariaDB Galera Cluster with cluster ID 27 and we would like to monitor schema changes for the database “myapp” and “sbtest”. Pick one of the database nodes as the value of schema_change_detection_address. For MySQL replication, this should be the master host or any slave host that holds the databases (in case of partial replication is active). Then, inside, /etc/cmon.d/cmon_27.cnf, add the two following lines:

schema_change_detection_address=10.0.0.30:3306
schema_change_detection_databases=myapp,sbtest

Restart the CMON service to load the change:

$ systemctl restart cmon # systemd
$ service cmon restart # SysVinit

Take note only new tables or changing tables are printed in the report. The first report is only for metadata collection for comparison in the subsequent rounds, thus we have to run it at least twice to see the difference. For the first and foremost report, ClusterControl only returns the result of metadata collection. With the first report as the baseline, the subsequent reports will return the output that we are expecting.

System Report

The current default report shows a cluster’s health and performance at the time it was generated compared to one day ago. Under the summary section, it lists the nodes in the cluster, their type, role (master or slave), the status of the node, uptime, and the OS. It also reports the top query summary as well as node status overview in histogram formats like CPU usage, data throughput, load average, disk usage, and throughput, RAM usage, network throughput, server load, and handler.

Upgrade Report

This report gives a summary of packages available for an upgrade by the repository manager on the monitored hosts. It summarizes the total number of packages available for upgrade as well as the related managed service for the cluster like a load balancer, virtual IP address, and arbitrator. This report can greatly help us plan our maintenance window efficiently. For critical upgrades like security and database packages, we could prioritize them over non-critical upgrades, which could be consolidated with other less-priority maintenance windows.

For accurate reporting, ensure you always use stable and trusted repositories on every host. In some undesirable occasions, the monitored hosts could be configured with an outdated repository after an upgrade (e.g, every MariaDB major version uses a different repository), an incomplete internal repository (e.g, partially mirrored from the upstream), or a bleeding edge repository (commonly for unstable nightly-build packages).

Database growth report

This report provides information about the growth and changes in a database over a specific period of time. It includes data such as the size of the database, the number of records or rows, the number of tables or collections, and other relevant metrics.

A database growth report can be useful for database administrators and analysts to understand the trends and patterns in data growth, identify areas of concern or potential bottlenecks, and make informed decisions regarding database management and optimization.