Monitoring the state of a MySQL® cluster and 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 cluster stateMonitoring cluster state

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

1. Navigate to the folder dashboard and select Managed Service for MySQL.

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

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

The following charts are displayed on the tab:

• Average query time: Average time it takes to execute queries on each host (in milliseconds).

• Connections: Number of connections on each host.

• Disk usage: Shows how much disk space (in bytes) is used on each host and in the entire cluster.

• Is Alive, [boolean]: Indicates cluster accessibility 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 availability of a cluster, add hosts.

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

• Free space: Free disk space for each host (in bytes).

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

• Replication lag: Replica's lag behind the master (seconds).

• Slow queries per second: Number of SQL queries per second executed longer than specified in the long_query_time parameter for each host.

• Threads running: Number of active threads on each host. As the load on the cluster increases, this value rapidly grows.

The Master overview section shows detailed information about the master:

• Disk usage: Details of the disk space used (in bytes):
  • data: Volume used by data.
  • default tablespace: Volume used by data in the default tablespace.
  • innodb logs: Volume used by InnoDB logs.
  • relaylogs, binlogs: Volume used by MySQL® service logs. For more information about binlogs and relaylogs, see the MySQL® documentation.
  • temp tablespace: Volume used by data in the temporary tablespace.
  • undo tablespace: Volume used by data in the InnoDB undo tablespace.
• InnoDB locks: Number of InnoDB table locks. For more information about metrics, see the MySQL® documentation.
• InnoDB rows operations: Number of operations with InnoDB table rows. For more information about metrics, see the MySQL® documentation.
• Query quantiles: Quantiles of average query execution time.
• Sorts and joins: Proportions of sort and join operations in the total number of operations. For more information about metrics, see the MySQL® documentation.
• Table cache: Cached table characteristics. For more information about metrics, see the MySQL® documentation.
• Temp tables: Number of temporary tables. For more information about metrics, see the MySQL® documentation.
• Thread states: Number of threads in a given state started by the mysqld daemon. For more information about states, see the MySQL® documentation.
• Threads: Number of threads started by the mysqld daemon.

  • Threads cached: Number of cached threads.

    During normal cluster operation, the mysqld process caches most of the connections.

  • Threads connected: Number of open threads.

    If the chart is close to the maximum value, it may mean that open connections cannot be closed.

    The max_connections parameter sets the maximum value.

  • Threads running: Number of running threads.

    As the load on the cluster increases, this value rapidly grows.

Monitoring the state of hostsMonitoring the state of hosts

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

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

This page displays charts showing the load on an individual host in the cluster:

• CPU usage: Usage of processor cores. As the load goes up, 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 the Disk IOPS charts show that the Read property increases when active database reads are in progress, and that Write increases when database writes are in progress.

• Memory usage: Use of RAM, in bytes. At high loads, the value of the Free parameter goes down while those of other parameters go up.

• Network bytes: Speed of data exchange over the network, in bytes per second.

• Network packets: Number of packets exchanged over the network, per second.

  For hosts with the Replica role, Received 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: Details of the disk space used (in bytes):
  • data: Volume used by data.
  • default tablespace: Volume used by data in the default tablespace.
  • innodb logs: Volume used by InnoDB logs.
  • relaylogs, binlogs: Volume used by MySQL® service logs. For more information about binlogs and relaylogs, see the MySQL® documentation.
  • temp tablespace: Volume used by data in the temporary tablespace.
  • undo tablespace: Volume used by data in the InnoDB undo tablespace.
• Inode usage: Number of inodes used.
• File IO read bytes: Data read rate (bytes per second).
• File IO read operations: Average number of file read operations (per second). For more information about operations, see the MySQL® documentation.
• File IO write bytes: Data write rate (bytes per second).
• File IO write operations: Average number of file write operations (per second). For more information about operations, see the MySQL® documentation.
• Handlers: Number of handlers of various operations. For more information, see this MySQL® article.
• InnoDB cache efficiency: InnoDB cache performance indicators. For more information about metrics, see the MySQL® documentation.
• InnoDB data operations: Number of InnoDB operations:
  • innodb data fsyncs: fsync() operations when flushing data to disk.
  • innodb data reads: Disk read operations.
  • innodb data writes: Write operations.
• InnoDB lock time: InnoDB table lock wait timeout (in seconds).
• InnoDB locks: Number of InnoDB table locks. For more information about metrics, see the MySQL® documentation.
• InnoDB rows operation: Number of operations with InnoDB table rows. For more information about metrics, see the MySQL® documentation.
• 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 (seconds).
• SemiSync latency: Transaction's commit delay under semisynchronous replication (in seconds). For more information about metrics, see the MySQL® documentation.
• Slow queries per second: Number of SQL queries per second executed longer than specified in the long_query_time parameter.
• Sorts and joins: Proportions of sort and join operations in the total number of operations. For more information about metrics, see the MySQL® documentation.
• Table cache: Cached table characteristics. For more information about metrics, see the MySQL® documentation.
• Temp tables: Number of temporary tables. For more information about metrics, see the MySQL® documentation.
• Thread states: Number of threads in a given state started by the mysqld daemon. For more information about states, see the MySQL® documentation.
• Threads: Number of threads started by the mysqld daemon.

  • Threads cached: Number of cached threads.

    During normal host operation, the mysqld process caches most of the connections.

  • Threads connected: Number of open threads.

    If the chart is close to the maximum value, it may mean that open connections cannot be closed.

    The max_connections parameter sets the maximum value.

  • Threads running: Number of running threads.

    As the load on the host increases, this value grows rapidly.

Alert settings in Yandex MonitoringAlert settings in Yandex Monitoring

To have other cluster health indicators monitored automatically:

The recommended thresholds are as follows:

Metric	Parameter	Alarm	Warning
Replication delay	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 view the current storage size in the detailed information about the cluster. 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 view a cluster's state and status:

1. Go to the folder page and select Managed Service for MySQL.
2. Hover over the indicator in the cluster row of 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.