Monitoring the state of MySQL® clusters and hosts
Data on cluster and host states are 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.
Note
The most appropriate multiple units (MB, GB, and more) are automatically used in charts.
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 state
To view detailed information about the Managed Service for MySQL® cluster state:
-
Go to the folder page
and select Managed Service for MySQL. -
Click the cluster name and open the Monitoring tab.
-
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 running 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 the average query execution time.
- Sorts and joins: Proportion 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 certain status 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 maximum value is set by the
max_connections
parameter. -
Threads running: Number of running threads.
As the load on the cluster increases, this value rapidly grows.
-
Monitoring the state of hosts
To view detailed information about the state of individual Managed Service for MySQL® hosts:
- Go to the folder page
and select Managed Service for MySQL. - Click the cluster name and select the Hosts tab.
- Go to the Monitoring page.
- 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 the MySQL® documentation
. - 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 data is flushed to disk. - innodb data reads: Disk read operations.
- innodb data writes: Write operations.
- innodb data fsyncs:
- InnoDB lock time: InnoDB table lock wait timeout (in seconds).
- InnoDB locks: The 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: The 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 running longer than specified in the
long_query_time
parameter. - Sorts and joins: The proportion 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 certain status 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 maximum value is set by the
max_connections
parameter. -
Threads running: Number of running threads.
As the load on the host increases, this value grows rapidly.
-
Alert settings in Yandex Monitoring
-
In the management console
, select the folder with the cluster you want to configure alerts for. -
In the list of services, select
Monitoring. -
Under Service dashboards, select:
- Managed Service for MySQL® — Cluster Overview to configure cluster alerts.
- Managed Service for MySQL® — Host Overview to configure host alerts.
-
In the chart you need, click
and select Create alert. -
If the chart shows multiple metrics, select a data query to generate a metric and click Continue. You can learn more about the query language in the Yandex Monitoring documentation.
-
Set the
Alarm
andWarning
thresholds to trigger an alert. -
Click Create alert.
To have other cluster health indicators monitored automatically:
- Create an alert.
- Add a status metric.
- In the alert parameters, set up your alert thresholds.
The recommended thresholds are as follows:
Metric | Parameter | Alarm |
Warning |
---|---|---|---|
Replication delay | mysql_replication_lag |
600 |
60 |
Number of healthy hosts | mysql_is_alive |
<host_count>: 2 |
<host_count>: 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 the Monitoring documentation.
Cluster 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:
- Go to the folder page and select Managed Service for MySQL.
- Hover over the indicator in the Availability column in the required cluster row.
Cluster 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:
|
DEAD | The cluster is down: none of its hosts are running. | Make a support request
|
UNKNOWN | Cluster state is unknown. | Make a support request
|
Cluster statuses
Status | Description | Suggested actions |
---|---|---|
CREATING | Preparing for the first launch | Wait a while and get started. The time it takes to create a cluster depends on the host class. |
RUNNING | Cluster is operating normally | No action is required. |
STOPPING | Stopping cluster | After a while, the cluster status will change to STOPPED and the cluster will be disabled. No action is required. |
STOPPED | Cluster stopped | Start the cluster to get it running again. |
STARTING | Starting the cluster that was stopped earlier | After a while, the cluster status will change to RUNNING . Wait a while and get started. |
UPDATING | Updating the cluster status | After the update is completed, the cluster status will change to RUNNING . Wait a while and get started. |
ERROR | An error occurred that does not allow the cluster to continue working | Run the initial diagnostics:
|
STATUS_UNKNOWN | Cluster is unable to determine its own status | Run the initial diagnostics:
|