1. Home
  2. Docs
  3. ClusterControl
  4. User Guide (GUI)
  5. MySQL/MariaDB
  6. Query Monitor

Query Monitor

Provides a summary of query processing across all nodes in the cluster.

Query Monitor (version 2)

Since the release of ClusterControl 1.9.0, the Query Monitor v2 (version 2) has been introduced. It is our new feature that 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 v2 are the following:

  • Install / remove query monitor agents on the db nodes.
  • Start / stop collecting query stats with the agents.
  • New ‘Query Workload’ overview showing query digests, latency, throughput and concurrency with a scatter chart.
Attention

Starting from ClusterControl 1.9.7 (September 2023), ClusterControl GUI v2 is the default frontend graphical user interface (GUI) for ClusterControl. Note that the GUI v1 is considered a feature-freeze product with no future development. All new developments will be happening on ClusterControl GUI v2. See User Guide (GUI v2).

Query Monitor Agents

When you click the Install Query Monitor Agent button, ClusterControl will setup and install the agent to all of the database nodes assigned to the particular cluster.

 

The agent has the following configuration which you can found in /etc/cmnd.conf. This is for the agent configuration. Then under /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 in order 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. So you can manage the init scripts with systemd to start and stop the service. To determine if the agents are running, you can do the following,

$ systemctl status cmon-daemon

or verify it with 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. These metrics which is collected by a database client and sends the query inquiry to the agent. Then the agent that reads the input data and will produce the query statistics and makes it available to the Query Monitor v2 dashboard query metrics. That means, all collected information from the Query Monitoring shall depend upon the following  condition.

  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 too complicated the query statistics should be produced and made available in a progressive manner so a responsive UI can be implemented.
    This means while the processing of the data happens the client program can access the data that is already processed.

  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 to monitor the input data and keep the data be 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.

Overview (Beta)

For MySQL/MariaDB, it requires performance_schema to be enabled. You cannot further use the capabilities that the Query Monitor v2 until this is enabled.

See also

For more details (including how to enable the Performance Schema), see this blog post, How to use the ClusterControl Query Monitor for MySQL, MariaDB, and Percona Server.

Once it is enabled, you are good to go and allowed to enable or install the agents.

To install the agents, click Install Query Monitor Agent button.

Using The Overview Dashboard

The Overview dashboard contains the following:

  • The drop down lists for the list of your MySQL databases and time range that you can select.
  • The database metrics
    • The database metrics has the following:
      • Throughput which is based on Queries per-second/Query Count
      • Concurrency which is based on Lock time (in seconds)
      • Average Latency which is based on Average query time (s)/Average Latency
      • Errors which are all the database connectivity errors per second
  • The digest queries which contains the list of queries based on the following
    • a query fingerprint format listed under Digest field.
    • Schema
    • Count i.e. the number of queries being detected or found
    • Rows which contains the Sent, Examined, Affected sub-fields
    • Exec Time (Execution Time) with contains Average and Total sub-fields

 

Top Queries

This is an aggregated list of all your top queries running on all the nodes of your database cluster. The list can be ordered by Occurrence or Execution Time, to show the most common or slowest queries respectively. It is also possible to filter and review queries from one particular node.

ClusterControl gets the information in two different ways:

  • Queries are retrieved from Performance Schema.
  • If Performance Schema is disabled or unavailable, ClusterControl will parse the content of the MySQL’s slow query log.

 

Toggle Query Monitor to ON to enable query monitoring. If Performance Schema is enabled, ClusterControl will use it to look for the slow queries. Otherwise, ClusterControl will parse the content of MySQL slow query log based on the following flow:

Start:

1.Start slow log (during MySQL runtime).
2. Run it for a short period of time (a second or a couple of seconds).
3. Stop log.
4. Parse log.
5. Truncate log (new log file).
6. Go to Start.

The collected queries are hashed, calculated, and digested (normalize, average, count, sort) and then stored in ClusterControl.

Attention

By using a slow query log, there is a slight chance some queries will not be captured, especially during “stop log, parse log, truncate log” parts. You can enable Performance Schema if this is not an option.

If you are using the Slow Query Log, only queries that exceed the Long Query Time will be listed here. If the data is not populated correctly and you believe that there should be something in there, it could be:

  • ClusterControl did not collect enough queries to summarize and populate data. Try to lower the Long Query Time.
  • You have configured slow_query_log configuration options in the my.cnf of MySQL server, and Override Local Query is turned off. If you really want to use the value you defined inside my.cnf, probably have to lower the long_query_time value so ClusterControl can calculate a more accurate result.
  • You have another ClusterControl node pulling the slow query log as well (in case you have a standby ClusterControl server). Only allow one ClusterControl server to do this job.

The Long Query Time value can be specified to a resolution of microseconds, for example, 0.000001 (1 x 10-6).

Settings

Click on the Settings to configure the Query Monitor settings, as explained below:

Field Description
Long Query Time
  • Collects queries taking longer than Long Query Time seconds
  • For example, 0.1 – Only queries taking more than 0.1 seconds will be accounted for.
Log queries not using indexes?
  • Configures ClusterControl behavior on sampling queries without indexes:
    • Yes – Logs queries that are using indexes.
    • No – Ignores queries that are not using indexes (will not be accounted for in ClusterControl → Query Monitor → Query Outliers).
MySQL Local Query Override
  • Choose whether you want ClusterControl to override the local MySQL query sampling:
    • Yes – The local MySQL configuration settings inside my.cnf for long_query_time and log_queries_not_using_indexes will be used.
    • No – ClusterControl uses Long Query Time and Log queries not using indexes will be used across all MySQL Servers.
Auto-Purge Queries
  • Automatically purge sampled queries hourly. If PERFORMANCE_SCHEMA is enabled, the table events_statements_summary_by_digest will be auto-purged (using the TRUNCATE TABLE statement) every hour. By default this is disabled.
Purge Query Monitor
  • Wipes out the sampled queries. The sampling will be refreshed again after db_stats_collection_interval.

Top Queries Table

This page is auto-refresh every 30 seconds. You can change the refresh rate by clicking on the Refresh Rate dropdown at the top right. The following describes the Top Queries table columns:

Field Description
Query
  • List of sampled queries.
DB
  • Database name.
Count
  • The total number of query occurrences.
Rows
  • Total rows involved:
    • Sent – The number of rows MySQL returned.
    • Examined – The number of rows MySQL believes it must examine to execute the query.
Tmp tables
  • The number of temporary tables created for this query, on RAM or on disk
Exec Time
  • Execution time in microseconds of:
    • Max – Maximum execution time.
    • Avg – Average execution time.
    • Stdev – Standard deviation time.
Total Exec Time
  • The total amount of execution time of:
    • Absolute – Total execution time of the query.
    • Relative – Percentage of the query execution time over total time.

Running Queries

View current running queries on your database cluster similar to SHOW FULL PROCESSLIST the command in MySQL. You can stop a running query by selecting to kill the connection that started the query. The process list can be filtered out by the host.

This page is auto-refresh every 30 seconds. You can change the refresh rate by clicking on the Refresh Rate dropdown at the top right.

Field  Description
MySQL Server
  • The MySQL server from which the process is retrieved.
Kill [thread ID]
  • Click to kill the specific MySQL thread ID.
ID
  • Connection identifier number.
DB
  • Database name.
User
  • The MySQL user who issued the statement.
Exec T
  • The time in seconds that the thread has been in its current state.
Client
  • The hostname (or port if TCP/IP) of the client issuing the statement.
Info
  • The statement the thread is executing, or NULL if it is not executing any statement.
Command
  • The type of command the thread is executing.
State
  • An action, event, or state that indicates what the thread is doing, as explained on the MySQL Documentation page.

Query Outliers

Shows queries that are outliers. An outlier is a query taking a longer time than the normal query of that type. Use this feature to filter out the outliers for a certain time period. After a number of samples and when ClusterControl has had enough stats, it can determine if latency is higher than normal (2 sigmas + average_query_time) then it is an outlier and will be added into the Query Outlier.

This feature is dependent on the Top Queries feature above. If Query Monitoring is enabled and Top Queries are captured and populated, the Query Outliers will summarize these and provide a filter based on timestamp. You can view the query history as old as one year ago.

Field  Description
Time
  • The exact time when the query is captured.
Query
  • The SQL query.
Query Time
  • Query’s execution time in microseconds.
Avg Query Time
  • Query’s average execution time in microseconds.
Stdev
  • Query’s standard deviation execution time in microseconds.
Max Query Time
  • Query’s maximum execution time in microseconds.
Max Lock Time
  • Query’s lock time in microseconds.
Was this article helpful to you? Yes No