FAQ about Managed Service for MySQL®®
General questions
-
What part of database management and maintenance is Managed Service for MySQL® responsible for?
-
Which tasks are best addressed using Managed Service for MySQL®, and which using VMs with databases?
-
How can I change the computing resources and storage size for a database cluster?
-
When are backups performed? Is a database cluster available during a backup?
Questions about MySQL®
-
What MySQL® versions does Managed Service for MySQL® support?
-
Why is the cluster slow even though the computing resources are not used fully?
Connection
Cluster read/write issues
-
Why is the cluster size much greater than the amount of data stored?
-
What should I do if I encounter any other application error?
Performance issues
-
How do I figure out the cause of performance degradation at peak loads?
-
How do I figure out the cause of general performance degradation?
Updating a cluster
Monitoring and logs
Migration/transfer
Configuring MySQL®
General questions
What is Managed Service for MySQL®?
Managed Service for MySQL® is a service that helps you create, operate, and scale MySQL® databases in a cloud infrastructure.
With Managed Service for MySQL®, you can:
- Create a database with the required performance characteristics.
- Scale processing power and storage dedicated for your databases as needed.
- Get database logs.
Managed Service for MySQL® takes on time-consuming MySQL® infrastructure administration tasks:
- Monitors resource usage.
- Automatically creates DB backups.
- Provides fault tolerance through automatic failover to backup replicas.
- Keeps database software updated.
You interact with database clusters in Managed Service for MySQL® the same way you interact with regular databases in your local infrastructure. This allows you to manage internal database settings to meet your app requirements.
What part of database management and maintenance is Managed Service for MySQL® responsible for?
When you create clusters, Managed Service for MySQL® allocates resources, installs the DBMS, and creates databases.
For the created and running databases, Managed Service for MySQL® automatically creates backups and applies fixes and updates to the DBMS.
Managed Service for MySQL® also allows you to replicate data between database hosts (both within and across availability zones) and automatically routes the load to a backup replica in the event of a failure.
Which tasks are best addressed using Managed Service for MySQL®, and which using VMs with databases?
Yandex Cloud offers two ways to work with databases:
- Managed Service for MySQL® allows you to operate template databases with no need to worry about administration.
- Yandex Compute Cloud virtual machines allow you to create and configure your own databases. This approach allows you to use any database management systems, access databases via SSH, etc.
What is a database host and database cluster?
A database host is an isolated database environment in the cloud infrastructure with dedicated computing resources and reserved data storage.
A database cluster is one or more database hosts between which replication can be configured.
How do I get started with Managed Service for MySQL®?
Managed Service for MySQL® is available to any registered Yandex Cloud user.
To create a database cluster in Managed Service for MySQL®, you need to define its parameters:
- Host class (performance characteristics, such as CPUs, RAM, etc.).
- Storage size (reserved to the full extent when you create a cluster).
- Network your cluster will be connected to.
- Number of hosts for the cluster and the availability zone for each host.
For more information, see Getting started.
How many database hosts can there be in one cluster?
The minimum number of hosts depends on the selected type of storage:
- If you use non-replicated SSD (
ssd-network-nonreplicated
) or local SSD (local-ssd
) storage, the minimum number of hosts is 3. - If you use network SSD (
network-ssd
) or network HDD (network-hdd
) storage, you can create a single-host cluster.
The maximum number of hosts in a cluster is only limited by the requested computing resources and the size of the storage for the cluster.
For more information, see Quotas and limits.
How can I access a running DB host?
You can connect to Managed Service for MySQL® databases using standard DBMS methods.
Learn more about connecting to clusters.
How many clusters can I create within a single cloud?
For more information on MDB technical and organizational limitations, see Quotas and limits.
How are DB clusters maintained?
In Managed Service for MySQL®, maintenance implies:
- Automatic installation of DBMS updates and revisions for DB hosts (including disabled clusters).
- Changes to the host class and storage size.
- Other Managed Service for MySQL® maintenance activities.
For more information, see Maintenance.
Which MySQL® version does Managed Service for MySQL® use?
Managed Service for MySQL® supports MySQL® 5.7 and MySQL® 8.
What happens when a new DBMS version is released?
The database software is updated when new minor versions are released. Owners of the affected DB clusters are notified of expected work times and DB availability in advance.
What happens when a DBMS version becomes deprecated?
One month after the database version becomes deprecated, Managed Service for MySQL® automatically sends email notifications to the owners of DB clusters created with this version.
New hosts can no longer be created using deprecated DBMS versions. Database clusters are automatically upgraded to the next supported version seven days after notification for minor versions and one month after notification for major versions. Deprecated major versions are going to be upgraded even if you have disabled automatic updates.
How do you calculate usage cost for a database host?
In Managed Service for MySQL®, the usage cost is calculated based on the following parameters:
- Selected host class.
- Size of the storage reserved for the database host.
- Size of the database cluster backups. Backup size equal to the storage size is free of charge. Backup storage that exceeds this size is charged at special rates.
- Number of hours of database host operation. Partial hours are rounded to an integer value. You can find the cost per hour data for each host class in the Pricing policy section.
How can I change the computing resources and storage size for a database cluster?
You can change computing resources and storage size in the management console. All you need to do is choose a different host class for the required cluster.
The cluster characteristics change within 30 minutes. During this period, other maintenance activities may also be enabled for the cluster, such as installing updates.
Is database host backup enabled by default?
Yes, backup is enabled by default. For MySQL®, a full backup takes place once a day and saves all DB cluster transaction logs. This allows you to restore the cluster state to any point in time during the backup storage period, except for the last 30 seconds.
By default, backups are stored for seven days.
When are backups performed? Is a database cluster available during backup?
The backup window is an interval during which a full daily backup of the DB cluster is performed. The backup window is from 01:00 to 05:00 (UTC+3).
Clusters remain fully accessible during the backup window.
Can I change the retention period of automatic backups?
You can set the retention period for automatic backups when creating or modifying a cluster.
What metrics and processes can be tracked using monitoring?
For all DBMS types, you can track:
- CPU, memory, network, or disk usage, in absolute terms.
- Memory, network, or disk usage as a percentage of the set limits for the corresponding cluster host class.
- Amount of data in the DB cluster and the remaining free space in the data storage.
For DB hosts, you can track metrics specific to the corresponding type of DBMS. For example, for MySQL®, you can track:
- Average query execution time.
- Number of queries per second.
- Number of errors in logs, etc.
Monitoring can be performed with a minimum granularity of 5 seconds.
on Personal Data
?
Does the service meet the requirements under Russian Federation Federal Law No. 152-FZ Yes, it does. You can read the full security audit conclusion
Can I get logs of my operations with services?
Yes, you can request log records about your resources from Yandex Cloud services. For more information, see Data requests.
Questions about MySQL®
What MySQL® versions does Managed Service for MySQL® support?
Managed Service for MySQL® supports MySQL® 5.7 and MySQL® 8.
Are DB cluster backups enabled by default?
Yes, backup is enabled by default. For MySQL® clusters, a full backup takes place once a day and saves all DB cluster transaction logs. This allows you to restore the cluster state to any point in time during the backup storage period, except for the last 30 seconds.
Are MySQL® database cluster connections encrypted?
Connections between a database cluster and an application are always encrypted using SSL. You cannot disable cluster connection encryption.
What is a read-only replica in MySQL®?
A read-only replica is a host in a MySQL® DB cluster that can only be read. Its data is synced with the master host (if the cluster has more than one host). You can use a read-only replica to reduce the load on the DB master host with a large number of read requests.
What limitations are imposed on MySQL® database clusters?
For more information on Managed Service for MySQL® limits, see Quotas and limits. To learn about the properties of the clusters you can create using Managed Service for MySQL®, see Host classes.
Why is the cluster slow even though the computing resources are not used fully?
Perhaps, the maximum storage IOPS and bandwidth values are insufficient for processing the current number of requests. In this case, throttling is triggered and the performance of the entire cluster degrades.
The maximum IOPS and bandwidth values increase by a fixed value when the storage size increases by a certain step. The step and increment values depend on the disk type:
Disk type | Step, GB | Max IOPS increase (read/write) | Max bandwidth increase (read/write), MB/s |
---|---|---|---|
network-hdd |
256 | 300/300 | 30/30 |
network-ssd |
32 | 1,000/1,000 | 15/15 |
network-ssd-nonreplicated |
93 | 28,000/5,600 | 110/82 |
To increase the maximum IOPS and bandwidth values and make throttling less likely, increase the storage size when you update your cluster.
If you are using the network-hdd
storage type, consider switching to network-ssd
or network-ssd-nonreplicated
by restoring the cluster from a backup.
Connection
How do I connect to a cluster?
View the connection examples in the documentation or on the cluster page in the management console
MySQL® hosts with public access only support connections with an SSL certificate.
There are also Special FQDNs pointing to the current master and the cluster's least lagging replica.
Why cannot I connect from the internet?
Check whether there is public access to the host. To do this, in the management console
- Go to the folder page and select Managed Service for MySQL.
- Click the name of the cluster you need and select the Hosts tab.
- Look at the value in the specific host's Public access column.
MySQL® hosts with public access only support connections with an SSL certificate.
Additional information:
- If public access is only configured for certain hosts in your cluster, automatic master change may make the master unavailable over the internet.
- If you are using Special FQDNs, check the host list to see whether the current master or replica have public access.
- If you are using Security groups, check their settings.
Why cannot I connect from Yandex Cloud?
Please check that you are connecting from a Yandex Cloud VM located on the same virtual network as the MySQL® cluster.
To do this, in the management console:
- Go to the folder page, select Managed Service for MySQL, and click the name of the specific cluster.
- Check the value of the Cloud network parameter and click the network name link to see its subnets.
- Make sure the virtual machine is located on one of the network's subnets.
Additional information:
- If you are connecting to a host with public access, a connection can only be established with an SSL certificate.
- If you are using Special FQDNs, check the host list to see whether the current master or replica have public access.
- If you are using Security groups, check their settings.
Why cannot I connect to a multi-host cluster?
If public access is only configured for certain hosts in your cluster, automatic master change may make the master unavailable over the internet.
Check whether there is public access to the host. To do this, in the management console
- Go to the folder page and select Managed Service for MySQL.
- Click the name of the cluster you need and select the Hosts tab.
- Look at the value in the specific host's Public access column.
Additional information:
-
If you are using Special FQDNs, check the host list to see whether the current master or replica have public access.
-
If you cannot connect to the host you added, check that the cluster security group is configured correctly for the host's subnet.
Can I connect to cluster hosts via SSH or get superuser permissions on hosts?
You cannot connect to hosts via SSH, nor can you get superuser permissions. This is done for the sake of security and user cluster fault tolerance because direct changes inside a host can render it completely inoperable.
Why would the connection limit be exceeded?
The maximum number of concurrent connections to a Managed Service for MySQL® cluster host depends on the max_connections
parameter and by default equals <MB_of_RAM_per_host> ÷ 32
but not less than 100.
For example, for a s1.micro (2 vCPU, 8 GB) class host, the default max_connections
parameter value is: 8,192 ÷ 32 = 256.
You can edit the Max connections value in the cluster settings.
Cluster read/write issues
Why are cluster writes failing?
- If database storage is 95% full, the cluster will switch to read-only mode. Check the amount of free space in your storage and increase the storage size as required. To check the amount of free space:
- Go to the folder page and select Managed Service for MySQL.
- Click the cluster name and go to the Monitoring tab.
- Check the Disk usage chart.
- Make sure that the host being written to is the master rather than a replica.
What causes a replica to lag?
- Check that the
slave_rows_search_algorithms
parameter is set toINDEX_SCAN,HASH_SCAN
. - For large tables, we recommend using the
pt-online-schema-change
utility from the Percona Toolkit rather thanALTER TABLE
statements to avoid locking. - If the lag persists, enable parallel replication. To do this, configure the following parameters:
slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=8
- Run the
SHOW SLAVE STATUS;
command on the replica. If the value ofExecuted_Gtid_Set
remains unchanged for a long time, make sure all the tables have indexes. - If data is continuously written to the DB, and the host has 8GB RAM or more, we recommend increasing
innodb_log_file_size
up to 1 or 2 GB (parameter updates apply on server restart).
Why is the cluster size much bigger than the amount of data stored?
This happens because of the way MySQL® stores data and not because of Managed Service for MySQL® in Yandex Cloud. Factors affecting storage space usage:
- Fragmentation.
- Index fill factor.
- Rollback segment storage.
- Type packaging.
To find out the actual table size within a database, access the INNODB_SYS_TABLESPACES
system table. For more information, see Finding MySQL Table Size on Disk
What should I do if I encounter any other application error?
- Check whether there is space available on the disk hosting your cluster:
- Go to the folder page and select Managed Service for MySQL.
- Click the cluster name and go to the Monitoring tab.
- Check the Disk usage chart.
- Check host monitoring charts:
- 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.
- Find the problem resource: its chart will be approaching or will have crossed the boundary.
- Select the other hosts from the drop-down list and check them as well.
- Check the virtual machine or the server running the application connecting to your database for available resources.
Performance issues
How do I figure out the cause of performance degradation at peak loads?
Review the slow query log:
- In the MySQL® cluster settings, set Long query time to a value greater than zero.
- In the management console
, select the Logs tab on the cluster page. - In the top-left corner, select
MYSQL_SLOW_QUERY
from the drop-down list.
How do I figure out the cause of general performance degradation?
Check host monitoring charts:
- 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:
- We recommend upgrading the host class:
- If the
Steal
value in the CPU usage chart is stable high. - If the
Free
value in the Memory usage chart is stable low.
- If the
- If
iowait
on the CPU usage chart is high, the disk storage IOPS limits may be exceeded. We recommend increasing the value to the next allocation unit threshold, at least, or using faster disks. For more information about disk limits and performance, see the Yandex Compute Cloud documentation.
- We recommend upgrading the host class:
What causes a replica to lag?
- Check that the
slave_rows_search_algorithms
parameter is set toINDEX_SCAN,HASH_SCAN
. - For large tables, we recommend using the
pt-online-schema-change
utility from the Percona Toolkit rather thanALTER TABLE
statements to avoid locking. - If the lag persists, enable parallel replication. To do this, configure the following parameters:
slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=8
- Run the
SHOW SLAVE STATUS;
command on the replica. If the value ofExecuted_Gtid_Set
remains unchanged for a long time, make sure all the tables have indexes. - If data is continuously written to the DB, and the host has 8GB RAM or more, we recommend increasing
innodb_log_file_size
up to 1 or 2 GB (parameter updates apply on server restart).
How do I figure out why resources take long to load?
Check host monitoring charts:
- 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.
- Find the problem resource: its chart will be approaching or will have crossed the boundary.
- Select the other hosts from the drop-down list and check them as well.
If the charts do not show overload in the cluster's resources, refer to the recommendations under Locking mechanisms and Query optimization.
How do I figure out why the CPU resource is utilized?
You can retrieve information on the use of the CPU resource with the help of system views. To access them, you need the PROCESS
cluster-level administrative privilege.
-
Grant a user the
PROCESS
privilege by running the CLI command below:yc managed-mysql user update \ --global-permissions PROCESS <username> \ --cluster-id <cluster_ID>
-
Retrieve a list of longest-running database queries by executing the following query:
SELECT * FROM sys.statement_analysis LIMIT 10;
Note the queries with high rows_examined
, rows_sorted
, or the full_scan
flag since it is highly likely that these are taking up CPU resources. For more information, see the MySQL® documentation
How do I figure out why the IO resource is utilized?
Approximate IO usage by MySQL® threads is available from system views. To access them, you need the PROCESS
cluster-level administrative privilege.
-
Grant a user the
PROCESS
privilege by running the CLI command below:yc managed-mysql user update \ --global-permissions PROCESS <username> \ --cluster-id <cluster_ID>
-
Retrieve a list of threads using the query below:
SELECT t.name AS thread_name, t.processlist_user AS user, t.processlist_info AS query, t.processlist_time AS time, io.bytes AS bytes FROM performance_schema.threads t JOIN ( SELECT thread_id, sum(number_of_bytes) AS bytes FROM performance_schema.events_waits_history_long WHERE object_type='FILE' GROUP BY thread_id) io ON t.thread_id = io.thread_id ORDER BY io.bytes DESC;
The threads supporting the buffer pool and replication are generally higher in the table. This is normal.
How do I figure out why the network resource is utilized?
High network load may result: from a SELECT
that returns many rows, an INSERT
of large amounts of data, or an UPDATE
that modifies many records. In the event of a write operation, updates will copy over to the replicated hosts, which will create additional traffic.
Approximate network usage by MySQL® threads is available from system views. To access them, you need the PROCESS
cluster-level administrative privilege.
-
Grant a user the
PROCESS
privilege by running the CLI command below:yc managed-mysql user update \ --global-permissions PROCESS <username> \ --cluster-id <cluster_ID>
-
Retrieve a list of threads using the query below:
SELECT t.name AS thread_name, t.processlist_user AS user, t.processlist_info AS query, t.processlist_time AS time, net.bytes/t.processlist_time AS avg_bytes, net.bytes AS total_bytes FROM performance_schema.threads t JOIN ( SELECT thread_id, Sum(variable_value) bytes FROM performance_schema.status_by_thread WHERE variable_name IN ('Bytes_sent', 'Bytes_received') GROUP BY thread_id ) net ON t.thread_id = net.thread_id WHERE t.processlist_time IS NOT NULL ORDER BY net.bytes DESC;
This query returns statistics from the thread launch, so long-lived connections (such as those used for replication) will be closer to the top.
How do I figure out why locks are put in place?
If there is no unusual load on the cluster's resources, and queries still take too long to run, use system views to retrieve information on lock waits. To access them, you need the PROCESS
cluster-level administrative privilege.
-
Grant a user the
PROCESS
privilege by running the CLI command below:yc managed-mysql user update \ --global-permissions PROCESS <username> \ --cluster-id <cluster_ID>
-
To view table-level locks, run the following query:
SELECT * FROM sys.schema_table_lock_waits
-
To view row-level locks, run the following query:
SELECT * FROM sys.innodb_lock_waits
For more information, see the MySQL® documentation
How do I optimize problem queries?
See the official MySQL® documentation:
Updating a cluster
Can I manage a cluster using SQL commands?
There are some restrictions for cluster management using SQL commands. For more information, see SQL command limits.
How do I reduce the size of a disk?
It is not possible to reduce cluster storage size.
You can create a new cluster with less storage and migrate the data to it.
How do I increase the size of a disk?
Storage size cannot be increased for non-replicated SSD storage: follow the instructions at Updating clusters.
To increase the size of non-replicated SSD storage, restore the cluster from a backup and set the required size.
You can check the disk type in the management console
How do I change the disk type?
To change the disk type, restore your cluster from a backup.
Why cannot I add or delete a host?
The number of hosts in a cluster is limited by quotas. Before adding a host, check the resources currently in use: open the Quotas
You can delete a host as long as it is not the only one in a cluster. To replace the only host, first create a new host and then delete the old one.
Clusters with local disks have a fault-tolerant 3-host configuration. Fewer hosts are not permitted.
How do I perform a manual failover?
In a failover cluster with multiple hosts, you can switch the master role from the current master host to the replica host. After this operation, the current master host becomes the replica host of the new master.
- Go to the cluster page and click the Hosts tab.
- Click Switch master.
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 switch the master:
-
View a description of the CLI switch master command:
yc managed-mysql cluster start-failover --help
-
Run the
yc managed-mysql cluster start-failover
command.
How do I restart a host?
You cannot restart a separate cluster host. To restart hosts, stop and restart the cluster.
Monitoring and logs
How do I view cluster and host charts?
In the management console
To view charts in Yandex Monitoringservice
parameter to Managed Service for MySQL.
How do I view logs?
- Go to the cluster page and click the Logs tab.
- At the top of the page, select an available log:
MYSQL_ERROR
: Primary MySQL® log containing error messages. Always enabled.MYSQL_SLOW_QUERY
: Information on slow queries. Enabled iflong_query_time
is greater than0
.MYSQL_AUDIT
: Information on database connections.MYSQL_GENERAL
: Full query list. We recommend enabling it only for cluster debugging purposes in the development environment.
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.
-
View a description of the CLI command to view the logs:
yc managed-mysql cluster list-logs --help
-
Run the
yc managed-mysql cluster cluster list-logs
command.
What is the retention period for logs?
Cluster logs are stored for 30 days.
How do I view current queries?
To view the current user's queries, run:
SHOW PROCESSLIST;
For a detailed description of the output, see the MySQL® documentation
What other diagnostic information is available?
A user with the PROCESS
cluster-level privilege can run the following queries:
SHOW FULL PROCESSLIST
SHOW ENGINE INNODB STATUS
SELECT
from theperformance_schema
and thesys
system schemas.
To grant a user the PROCESS
privilege, run this CLI command:
yc managed-mysql user update \
--global-permissions PROCESS <username> \
--cluster-id <cluster_ID>
How do I set up alerts?
Use Yandex Monitoring
When selecting a metric, set the service
parameter to Managed Service for MySQL.
How do I set up an alert that triggers as soon as a certain percentage of disk space has been used up?
Create an alert with the disk.used_bytes
metric in Yandex Monitoring. This metric shows the disk space usage in the Managed Service for MySQL® cluster.
For disk.used_bytes
, use notification thresholds. The recommended values are as follows:
Alarm
: 90% of the disk spaceWarning
: 80% of the disk space
Thresholds are set in bytes only. For example, the recommended values for a 100 GB disk are as follows:
Alarm
:96636764160
bytes (90%)Warning
:85899345920
bytes (80%)
Migration/transfer
How do I transfer data to a cluster?
Follow the steps described in Migrating data from a third-party MySQL® cluster.
How do I move a cluster to a different folder or cloud?
To move a cluster:
- To a different folder: Restore the cluster from a backup. Specify the desired folder when configuring the new cluster.
- To a different cloud: Follow the instructions in Migrating data from a third-party MySQL® cluster.
Configuring MySQL®
How do I set a time zone?
A time zone is specified in terms of a UTC offset. Moscow's, for instance, is '+03:00'
. Time zone names are not supported.
For more information, see the MySQL® documentation
How do I set the SQL mode (sql_mode)?
Follow the steps described in Managing databases.
What value should I set for innodb_buffer_pool_size?
Recommended parameter range:
- Minimum 25% of host RAM.
- Maximum 75% of host RAM provided that at least 1 to 1.6 GB are free for running queries, monitoring, and system processes.
How do I disable InnoDB strict mode?
By default, InnoDB strict mode is enabled. If you disable this mode using MySQL® tools, the following error will occur:
Mysql query error: (1227) Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation (400)
To disable strict mode, change the innodb_strict_mode
parameter value via the Yandex Cloud interfaces. You should also make sure that your configuration contains no command that disables strict mode, such as $connection–>queryExecute("SET innodb_strict_mode=0");
.
How do I change character sets and collation rules (CHARACTER SET, COLLATE)?
Follow the steps described in Managing databases.
How do I configure other parameters?
Editable MySQL® settings are listed in the documentation. You can use one of the available interfaces (management console, CLI, API, or Terraform) to modify these. Follow the instructions in Updating a cluster.