Monitoring the state of a MySQL® cluster and its hosts

Data on the cluster and host state is available in the management console. You can view them on the Monitoring tab of the cluster management page or in Yandex Monitoring.

Diagnostic information about cluster states is presented as graphs.

Chart update rate:

• Standard hosts and hosts with an increased RAM to vCPU ratio (memory-optimized): 15 seconds.
• Hosts with a guaranteed vCPU share under 100% (burstable): 150 seconds.

You can configure alerts in Yandex Monitoring to receive notifications about cluster failures. In Yandex Monitoring, there are two alert thresholds: Warning and Alarm. If the specified threshold is exceeded, you will receive alerts via the configured notification channels.

Monitoring the cluster stateMonitoring the cluster state

To view detailed information on the state of a Managed Service for MySQL® cluster:

1. Go to Managed Service for MySQL.

2. Click the cluster name and select the Monitoring tab.

3. To get started with Yandex Monitoring metrics, dashboards, or alerts, click Open in Monitoring in the top panel.

The tab displays the following charts:

• Average query time: Average query time for each host, in milliseconds.

• Connections: Number of connections for each host.

• Disk usage: Disk space used for each host and the entire cluster, in bytes.

• Is Alive, [boolean]: Shows the cluster availability as the sum of its hosts' states.

  Each Alive host increases the overall availability by 1. When one of the hosts fails, the overall availability is reduced by 1.

  To increase the cluster’s availability, add more hosts.

• Is Primary, [boolean]: Shows which host is the master and for how long.

• Free space: Free disk space broken down by host, in bytes.

• Queries per second: Total number of queries per second for each host.

• Replication lag: Replica's lag behind the master, in seconds.

• Slow queries per second: Number of SQL queries per second that run longer than the specified long_query_time, for each host.

• Threads running: Number of threads currently running on each host. This value increases rapidly as the cluster workload grows.

The Master overview section shows master details:

• Disk usage: Breakdown of used disk space, in bytes:
  • data: Amount of space used by data.
  • default tablespace: Amount of space used by data in the default tablespace.
  • innodb logs: Amount of space used by InnoDB logs.
  • relaylogs, binlogs: Amount of space used by MySQL® service logs. The MySQL® reference manual provides detailed info on binlogs and relaylogs.
  • temp tablespace: Amount of space used by data in the temporary tablespace.
  • undo tablespace: Amount of space used by data in the InnoDB undo tablespace.
• InnoDB locks: Number of InnoDB table locks. For metric descriptions, see this MySQL® article.
• InnoDB rows operations: Number of operations on InnoDB table rows. For metric descriptions, see this MySQL® article.
• Query quantiles: Quantiles of average query execution time.
• Sorts and joins: Proportions of sort and join operations in the total number of operations. For metric descriptions, see this MySQL® article.
• Table cache: Details of cached tables. For metric descriptions, see this MySQL® article.
• Temp tables: Number of temporary tables. For metric descriptions, see this MySQL® article.
• Thread states: Number of threads in a given state started by the mysqld daemon. For state descriptions, see this MySQL® article.
• Threads: Number of threads started by the mysqld daemon.

  • Threads cached: Number of cached threads.

    During normal cluster operation, mysqld caches most connections.

  • Threads connected: Number of open threads.

    If the chart approaches the maximum value, it may signal that connections are staying active.

    The max_connections setting defines the maximum value.

  • Threads running: Number of running threads.

    This value increases rapidly as the cluster workload grows.

Monitoring the state of hostsMonitoring the state of hosts

To view detailed information on the state of individual Managed Service for MySQL® hosts:

1. Go to Managed Service for MySQL.
2. Click the cluster name and select the Hosts tab.
3. Go to the Monitoring page.
4. Select the host you need from the drop-down list.

This page displays the charts showing workloads of individual cluster hosts:

• CPU usage: Processor core workload. As the workload increases, the idle value goes down.

• Disk read/write bytes: Speed of disk operations, in bytes per second.

• Disk IOPS: Number of disk operations per second.

  The Disk read/write bytes and Disk IOPS charts show the increase in the Read value during database read activity, and in Write, during database write activity.

• Memory usage: Amount of RAM used, in bytes. At high loads, the Free value decreases, while the other values increase.

• Network bytes:Network data transfer rate, in bytes per second.

• Network packets: Network packet rate, in packets per second.

  For Replica hosts, the Received value is normally greater than Sent on the Network Bytes and Network Packets charts.

The MySQL overview section shows detailed information about the DBMS state on the host:

• Disk usage: Breakdown of used disk space, in bytes:
  • data: Amount of space used by data.
  • default tablespace: Amount of space used by data in the default tablespace.
  • innodb logs: Amount of space used by InnoDB logs.
  • relaylogs, binlogs: Amount of space used by MySQL® service logs. The MySQL® reference manual provides detailed info on binlogs and relaylogs.
  • temp tablespace: Amount of space used by data in the temporary tablespace.
  • undo tablespace: Amount of space used by data in the InnoDB undo tablespace.
• Inode usage: Number of inodes used.
• File IO read bytes: Data read rate, in bytes per second.
• File IO read operations: Average number of file read operations per second. For more information about operations, see this MySQL® article.
• File IO write bytes: Data write rate, in bytes per second.
• File IO write operations: Average number of file write operations per second. For more information about operations, see this MySQL® article.
• Handlers: Number of handlers for various operations. For more information, see this MySQL® article.
• InnoDB cache efficiency: Measures of InnoDB buffer performance. For metric descriptions, see this MySQL® article.
• InnoDB data operations: Number of InnoDB operations:
  • innodb data fsyncs: fsync() operations when flushing data to disk.
  • innodb data reads: Disk reads.
  • innodb data writes: Disk writes.
• InnoDB lock time: InnoDB table lock wait time, in seconds.
• InnoDB locks: Number of InnoDB table locks. For metric descriptions, see this MySQL® article.
• InnoDB rows operation: Number of operations on InnoDB table rows. For metric descriptions, see this MySQL® article.
• Queries per second: Total number of queries per second.
• Query quantiles: Quantiles of the average query execution time.
• Replication lag: Replica's lag behind the master, in seconds.
• SemiSync latency: Details of transaction commit latency in semi-synchronous replication, in seconds. For metric descriptions, see this MySQL® article.
• Slow queries per second: Number of SQL queries per second that run longer than the specified long_query_time.
• Sorts and joins: Proportions of sort and join operations in the total number of operations. For metric descriptions, see this MySQL® article.
• Table cache: Details of cached tables. For metric descriptions, see this MySQL® article.
• Temp tables: Number of temporary tables. For metric descriptions, see this MySQL® article.
• Thread states: Number of threads in a given state started by the mysqld daemon. For state descriptions, see this MySQL® article.
• Threads: Number of threads started by the mysqld daemon.

  • Threads cached: Number of cached threads.

    During normal host operation, mysqld caches most connections.

  • Threads connected: Number of open threads.

    If the chart approaches the maximum value, it may signal that connections are staying active.

    The max_connections setting defines the maximum value.

  • Threads running: Number of running threads.

    This value increases rapidly as the host workload grows.

Setting up alerts in Yandex MonitoringSetting up alerts in Yandex Monitoring

To have other cluster health indicators monitored automatically:

Below are the recommended thresholds for some metrics:

Metric	Internal metric name	Alarm	Warning
Replication lag	mysql_replication_lag	600	60
Number of healthy hosts	mysql_is_alive	<number_of_hosts> - 2	<number_of_hosts> - 1
Average query execution time	mysql_latency_query_avg	—	2,000
Storage space used	disk.used_bytes	90% of the storage size	80% of the storage size
CPU usage	cpu.idle	10	20

For the disk.used_bytes metric, the Alarm and Warning thresholds are only set in bytes. For example, the recommended values for a 100 GB disk are as follows:

• Alarm: 96,636,764,160 bytes (90%)
• Warning: 85,899,345,920 bytes (80%)

You can check the current storage size in the cluster details. For a complete list of supported metrics, see this Monitoring article.

Cluster state and statusCluster state and status

The State of a cluster shows the health of its hosts, while the Status shows whether the cluster is started, stopped, or is at an intermediate stage.

To check the cluster’s state and status:

1. Go to Managed Service for MySQL.
2. In the cluster row, hover over the indicator in the Availability column.

Cluster statesCluster states

State	Description	Suggested actions
ALIVE	Cluster is operating normally.	No action is required.
DEGRADED	Cluster is not running at its full capacity: the state of at least one of the hosts is other than ALIVE.	Run the diagnostics: Go to the Hosts tab and see which hosts are not working. Go to the Operations tab and make sure all operations are completed. Make sure the cluster is not under maintenance. If you cannot find the cause yourself, contact support.
DEAD	The cluster is down: none of its hosts are running.	Make a support request stating the following: Cluster ID. IDs of the last operations performed on it. Time the cluster entered the DEAD state according to the availability charts.
UNKNOWN	Cluster state is unknown.	Make a support request stating the following: Cluster ID. IDs of the last operations performed on it. Time the cluster entered the UNKNOWN state according to the availability charts.

Cluster statusesCluster statuses

Status	Description	Suggested actions
CREATING	Preparing for the first start	Wait a while and get started. The time it takes to create a cluster depends on the host class.
RUNNING	The cluster is operating normally	No action is required.
STOPPING	The cluster is stopping	After a while, the cluster status will switch to STOPPED and the cluster will be disabled. No action is required.
STOPPED	The cluster is stopped	Start the cluster to get it running again.
STARTING	Starting the cluster that was stopped earlier	After a while, the cluster status will switch to RUNNING. Wait a while and get started.
UPDATING	Updating the cluster's configuration	Once the update is complete, the cluster will get the status it had prior to the update: RUNNING or STOPPED.
ERROR	Error when performing an operation with the cluster or during a maintenance window	If the cluster remains in this status for a long time, contact support. You can see whether a cluster is available by its status.
STATUS_UNKNOWN	The cluster is unable to determine its status	If the cluster remains in this status for a long time, contact support.