1. Home
  2. Docs
  3. ClusterControl
  4. User Guide (GUI v2)
  5. Clusters
  6. Performance

Performance

DB Growth

Provides a summary of your database and table growth daily for the last 31 days so that you can track the dataset growth on your databases. Click on the Top largest tables will open the details per database on table count, row count, data size, index size, and database size. Click on Actions → View a specific database to further detail the growth summary per table.

Click on Update data in controller to trigger a job to re-calculate the database size.

Query Monitor

Available for: MySQL/MariaDB ReplicationMySQL/MariaDB Galera ClusterPostgreSQLTimescaleDBMariaDB Server

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:

Subsection Description
DB connections Provides information on the current database connections and processes, similar to SHOW FULL PROCESSLIST (MySQL/MariaDB) or pg_stat_activity (PostgreSQL/TimescaleDB).
Top queries Top queries aggregated from PERFORMANCE_SCHEMA (MySQL/MariaDB) or pg_stat_statements (PostgreSQL/TimescaleDB).
Query outliers Provides information on queries that take a longer time than the normal query time of that type. This feature is dependent on the Top queries feature. If Query Monitor is enabled and Top queries are captured and populated, the Query outliers will summarize these and provide a filter based on timestamp.
Settings Configures query monitoring-related options.

Query Monitor/Agents

Available for: MySQL/MariaDB ReplicationMySQL/MariaDB Galera ClusterPostgreSQLTimescaleDBMariaDB Server

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.

 

Was this article helpful to you? Yes No