Performance diagnostics in Managed Service for MySQL
Managed Service for MySQL has a built-in tool for collecting session and query statistics. These metrics can be useful when analyzing the performance and optimizing the settings of a cluster.
Enabling statistics collection
When creating a cluster or updating its settings:
- Enable Statistics sampling (disabled by default).
- Configure Sessions sampling interval and Statements sampling interval. The values may range from
1
to86400
seconds.
If you do not have the Yandex Cloud command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To enable and configure statistics collection, specify the --performance-diagnostics
parameter in the update cluster command:
yc managed-mysql cluster update <cluster_name_or_ID> \
...
--performance-diagnostics enabled=true,`
`sessions-sampling-interval=<session_sampling_interval>,`
`statements-sampling-interval=<statement_sampling_interval> \
...
The values of the sessions-sampling-interval
and the statements-sampling-interval
parameters may range from 1
to 86400
seconds.
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating a MySQL cluster.
-
To enable and configure statistics collection, add the
performance_diagnostics
section to the cluster configuration:resource "yandex_mdb_mysql_cluster" "<cluster_name>" { ... performance_diagnostics { enabled = true sessions_sampling_interval = <session_sampling_interval> statements_sampling_interval = <statement_sampling_interval> } ... }
The values of the
sessions_sampling_interval
and thestatements_sampling_interval
parameters may range from1
to86400
seconds. -
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
Time limits
A Terraform provider sets the timeout for Managed Service for MySQL cluster operations:
- Creating a cluster, including by restoring one from a backup: 15 minutes.
- Editing a cluster, including the MySQL version update: 60 minutes.
- Deleting a cluster: 15 minutes.
Operations exceeding the set timeout are interrupted.
Add the timeouts
block to the cluster description, for example:
resource "yandex_mdb_mysql_cluster" "<cluster_name>" {
...
timeouts {
create = "1h30m" # 1 hour 30 minutes
update = "2h" # 2 hours
delete = "30m" # 30 minutes
}
}
-
API
To enable statistics collection, use the create or update REST API method for the Cluster resource or the ClusterService/Create or ClusterService/Update gRPC API call and provide the following in the request:
- Cluster ID in the
clusterId
parameter. You can get the ID with a list of clusters in the folder. true
value in theconfigSpec.performanceDiagnostics.enabled
parameter.- Sessions sampling interval in the
configSpec.performanceDiagnostics.sessionsSamplingInterval
parameter. Acceptable values are between1
and86400
seconds. - Statement sampling interval in the
configSpec.performanceDiagnostics.statementsSamplingInterval
parameter. Acceptable values are between1
and86400
seconds. - List of fields to update in the
updateMask
parameter.
Warning
This API method resets any cluster settings that are not provided explicitly in the request to their defaults. To avoid this, make sure to provide the names of the fields to be changed in the
updateMask
parameter. - Cluster ID in the
Getting session statistics
-
In the management console
, go to the folder page and select Managed Service for MySQL. -
Click the cluster name and select the Performance diagnostics → Sessions tab.
To view session statistics or the history of queries executed within a session, select the appropriate tab.
StatisticsHistoryTo view session statistics:
- Specify the required time interval.
- (Optional) Set filters.
- Select the desired data segment
.
To show or hide individual categories, click the category name in the chart legend.
To view the history of queries run during a session:
- Specify the required time interval.
- (Optional) Set filters.
Getting query statistics
-
In the management console
, go to the folder page and select Managed Service for MySQL. -
Click the cluster name and select the Performance diagnostics → Queries tab.
To view query statistics or compare them in two time intervals, select the appropriate tab.
IntervalTwo intervalsTo view query statistics:
- Select the time interval you need.
- (Optional) Set filters.
To get information about the relative change in the query statistics:
- In the Interval 1 field, select the time interval to be used as a calculation basis for statistics.
- In the Interval 2 field, select the time interval to compare the statistics for interval 1 with.
- (Optional) Set filters.
For example, 10
SELECT * FROM cities
queries were executed in the first interval and 20 in the second. When comparing statistics, the difference by thenumber of queries
metric (theCalls
column in the table) will be+100%
.
For more information about what statistics you can get, see the MySQL documentation