Performance

DB Growth

Provides a summary of your database and table growth daily so that you can track the dataset growth on your databases.

DB Status

Available for: MySQL-based clustersMariaDB-based clustersMongoDB-based clusters

Provides a comprehensive overview of your database’s current state. It offers insights into various aspects of the database, such as connection status, data usage, and server performance. This feature is particularly useful for database administrators and developers as it allows them to monitor the health and performance of the database, identify potential issues, and make informed decisions to ensure optimal database operation.

By default, ClusterControl performs status collection every 30 seconds. This is configurable using the db_stats_collection_interval parameter inside the controller’s configuration file.

DB Variables

Available for: MySQL/MariaDB ReplicationMySQL/MariaDB Galera ClusterPostgreSQLTimescaleDB

Provides detailed information about the configuration of your database. It allows you to view and manage various parameters that control the behavior of your database system. These variables can include settings related to memory allocation, data storage, query optimization, and more. This feature is particularly beneficial for database administrators and developers, enabling them to customize the database environment to best suit their specific needs and application requirements.

By default, ClusterControl performs variable collection every 30 seconds. This is configurable using the db_stats_collection_interval parameter inside the controller’s configuration file.

Query Monitor

Available for: MySQL/MariaDB ReplicationMySQL/MariaDB Galera ClusterPostgreSQLTimescaleDB

ClusterControl supports 2 types of query monitoring:

1. Query monitoring via SSH sampling (default).
2. Agent-based query monitoring using an agent called cmnd. See Query Monitor/Agents.

The default query monitoring is performed via SSH sampling in a pull mechanism. This means, ClusterControl connects to the database node, performs various query sampling tasks, and stores it in the CMON database before digesting and visualizing it into the ClusterControl GUI. This method is a legacy method and requires no additional configuration in most environments. However, in some cases, SSH sampling can be counterintuitive, excessive, and may not be accurate from the ClusterControl point-of-view. This is why ClusterControl offers another option called Query Monitor/Agents, as an alternative to the default query monitoring method, as shown in the next section.

The Query Monitor provides more subsections on the following:

Query Monitor/Agents

Available for: MySQL/MariaDB ReplicationMySQL/MariaDB Galera ClusterPostgreSQLTimescaleDB

Deploys agents for monitoring the database nodes, particularly the database queries. It is only available for MySQL and PostgreSQL database systems.

Features you can use with Query Monitor/Agent are the following:

• Install/remove query monitor agents on each database node.  You can remove these agents later on.
• Start/stop collecting query stats with the agents.
• New ‘Query Workload’ overview showing query digests, latency, throughput, and concurrency with a scatter chart.

Query Monitor Agents

When you click the Install monitoring agent button, ClusterControl will set up and install an agent called cmnd to all of the database nodes in the particular cluster.

The agent has the following configuration which you can find in /etc/cmnd.conf. This is for the agent configuration.  /etc/cmnd.d directory for the Top Query configuration. The agent configuration file /etc/cmnd.conf has all the details you can find here. By default, these are the following options that are set:

$ cat /etc/cmnd.conf | sed -e 's/#.*$//' -e '/^$/d' -e '/^$/N;/^\n$/D' | sed '/^\t*$/d'
[cmnd]
data_directory = "/var/lib/cmnd"
log_file = "/var/log/cmnd.log"
pid_file = "/var/run/cmnd.pid"
plugin_names = [
"libpluginMySql.so",
"libpluginSqlite.so",
"libpluginPgSql.so"
]
port = 4433
cert_file = "/etc/ssl/cmnd/cert.pem"
key_file = "/etc/ssl/cmnd/key.pem"

You can change the port to your desired port number based on the firewall policy you have in your organization. So take note, you need to have port 4433 open by default to make it work properly.

You can find more of its configuration files under these files/directories:

$ find /etc/ -name "cmn*"
/etc/ssl/cmnd
/etc/logrotate.d/cmnd
/etc/cmnd.conf
/etc/cmnd.d
/etc/cmn.passwd

The Query Monitor Agents are installed using systemd, allowing you to start and stop the service using the systemctl command. To determine if the agents are running, you can do the following:

$ systemctl status cmon-daemon

Or verify it with the netstat just like below:

$ netstat -ntlvp46 | grep cmnd
tcp 0 0 0.0.0.0:4433 0.0.0.0:* LISTEN 8456/cmnd

Query Monitoring Workflow

The agent collects samples from the performance_schema (MySQL/MariaDB) or pg_stat_statements (PostgreSQL/TimescaleDB). These metrics which is collected by a database client and send the query inquiry to the agent. Then, the agent that reads the input data will produce the query statistics and make it available to the Query Monitor’s Overview dashboard query metrics. That means all collected information from the Query Monitoring shall depend upon the following conditions:

1. The reply should be sent in a timely fashion no matter how big the input data is. Should the computer be very slow or the processing is too complicated, the query statistics should be produced and made available in a progressive manner so a responsive UI can be implemented.

2. The agent should be able to update the query statistics on itself when the input data is updated by the database server. A simple query or RPC call is invoked so that the agent keeps monitoring the input data and keeps the data updated.

3. The query statistics should be according to the query inquiry and the input data. In simpler words, the produced statistical data should be in sync with the input data and the query the user sent. Some query statistics may be derived from the input data.

Advisors

Lists of scheduled advisors’ results created in Manage → Advisors and Manage → Scripts using ClusterControl DSL. You can think of it as a ‘scheduled mini-program’ that executes a script created inside ClusterControl and produces a result containing status, advice, and justification. You can get detailed schedules by rolling over to the Advisor’s name.

Schema Analyzer

Analyze your database schemas for missing primary keys, redundant indexes, and tables using the MyISAM storage engine. Galera Cluster needs an explicitly defined primary key on each table (unique key does not count). MyISAM tables are not recommended in Galera. ClusterControl will periodically check the schemas for these (default every 8 hours or every CMON restart), and raise an alert if necessary.

 

Transaction Deadlocks

Available for: MySQL-based clustersMariaDB-based clusters

Lists out long-running transactions and deadlocks across database clusters where you can easily view what transactions are causing the deadlocks. The default query time threshold is 30 seconds. This is configurable in the CMON configuration file under <span class="pre">db_long_query_time_alarm</span> configuration option.

Click on the listed query to see the output of InnoDB status for detailed debugging.

Running Operations

Available for: MongoDB-based clusters

Provides a view of current running operations similar to db.currentOp() command.