Query Monitoring

The Query Monitoring feature is a powerful tool designed to provide real-time visibility into the queries running on your database. It helps administrators identify performance bottlenecks, optimize query execution, and ensure the overall health of the database. This feature includes real-time query monitoring, top queries analysis, and query outlier detection, all of which work together to provide actionable insights into database performance.

Every database cluster managed by ClusterControl has it is own Query Monitoring. This will help monitor queries to the specific database cluster. Ideally, after the database cluster has been provisioned, administrators can set up the Slow Query Log. The purpose of the Slow Query Log is to capture slow queries based on a configured threshold.

Configure Slow Query Log

ClusterControl GUIClusterControl CLI

In the ClusterControl UI, go to the cluster and choose the tab Performance → Query Monitor, go to the ettings.
Enable the Query Monitor toggle and set the thresholds for long query time. The unit for long query time is in seconds. For example, we can set the threshold to 5 seconds.
In addition to the slow query threshold, we can enable the option to log queries that do not use indexes. This means any query performing a full table scan will also be logged.

Change the value of the long query time in s9s CLI is very straightforward. Define the --cluster-id, and the value of new slow query threshold. The long query time defined as seconds.
```
s9s cluster --change-config --cluster-id=<clusterID> --opt-name=long_query_time --opt-value=5
```

Verify the config by executing the following command:

s9s cluster --list-config --cluster-id=<id> | grep -i long_query_time

The clusterID is the unique identifier belong to the cluster.

Slow Query Monitor

Queries that exceed the slow query threshold and those that do not use indexes will appear in the Top Queries section.

ClusterControl GUI

In the ClusterControl UI, go to the cluster and choose the tab Performance → Query Monitor, go to the Top Queries.
It provides detailed information about each query, including the query text, execution time, user, database, and state (example: running, idle, waiting, or completed).

The next step after identifying the slow queries is to manually check the execution plan for each query (example: missing indexes, inefficient joins, and other issues) and make the necessary corrections.

RealTime Query Monitor

Administrators can monitor queries running in the database in real time, which is beneficial in the event of an incident. We can immediately check which queries are causing performance issues. Key aspects to watch include the query execution time, query status (active, idle, or waiting), the schema, and the user executing the queries.

ClusterControl GUI

In the ClusterControl UI, go to the cluster and choose the tab Performance → Query Monitor, go to the DB Connections.
It provides detailed information about each query, including the query text, execution time, user, database, and state (example: running, idle, waiting, or completed).

ProxySQL for Query Monitor

Administrators can also monitor queries in real time through ProxySQL. It monitors queries from the perspective of the database load balancer. The Top Queries section in ProxySQL provides insights such as the number of queries executed (count_star), total query time (sum_time), and the timestamps for when queries were first and last seen. The Process List provides information about the queries being executed, including the host group, session ID, thread ID, user, and execution time.

ClusterControl GUI

In the ClusterControl UI, go to the cluster and choose the tab Nodes → Node List, go to the ProxySQL node.
There is Actions menu in the right side, just click the menu and choose Node details.
There are several tabs in the ProxySQL node list, and administrators can monitor the queries in the Top Queries.

Note

ProxySQL is database load balancer that is currently supported for MySQL (Replication and Percona XtraDB Cluster), MariaDB (Replication & Galera Cluster)

Reset Query Monitoring

After the queries have been fixed, they may still appear in the Top Query Monitor. Administrators can reset the query monitoring to start fresh.

ClusterControl GUI

In the ClusterControl UI, go to the cluster and choose the tab Performance → Query Monitor, go to the Top Queries.
There is button Clear all queries to reset the query monitoring or enable Auto Clear Queries toggle in the Settings.