Contact UsGet started
© 2024 Iron Hive LLC Belgrade

FAQ about Managed Service for MySQL®®

Written by
Updated at October 14, 2024

General questions

Questions about MySQL®

Connection

Cluster read/write issues

Performance issues

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 creating 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 must define its characteristics:

  • Host class (performance characteristics, such as CPUs, memory, etc.).
  • Storage size (reserved in full when you create the cluster).
  • Network your cluster will be connected to.
  • Number of hosts for the cluster and the availability zone for each host.

For a detailed guide, see Getting started with Managed Service for MySQL®®.

How many DB hosts can a cluster contain?

The minimum number of hosts depends on the selected type of storage:

  • If you use non-replicated SSD (ssd-network-nonreplicated) or local SSD storage (local-ssd), the minimum number of hosts is 3.
  • If using network SSD (network-ssd) or network HDD (network-hdd) storage, you can create single-host clusters.

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?

You can find MDB technical and organizational limitations in Quotas and limits in Managed Service for MySQL®.

How are DB clusters maintained?

Maintenance in Managed Service for MySQL® 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 space in the amount of the reserved storage 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 of operation for each host class in Pricing policy.

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 DB 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 DB 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.

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.

Does the service meet the requirements under Russian Federation Federal Law No. 152-FZ on Personal Data?

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 (click Connect in the top panel).

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:

  1. Go to the folder page and select Managed Service for MySQL.
  2. Click the name of the cluster you need and select the Hosts tab.
  3. 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:

  1. Go to the folder page, select Managed Service for MySQL, and click the name of the specific cluster.
  2. Check the value of the Cloud network parameter and click the network name link to see its subnets.
  3. 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:

  1. Go to the folder page and select Managed Service for MySQL.
  2. Click the name of the cluster you need and select the Hosts tab.
  3. 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?

  1. 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:
    1. Go to the folder page and select Managed Service for MySQL.
    2. Click the cluster name and go to the Monitoring tab.
    3. Check the Disk usage chart.
  2. Make sure that the host being written to is the master rather than a replica.

What causes a replica to lag?

  1. Check that the slave_rows_search_algorithms parameter is set to INDEX_SCAN,HASH_SCAN.
  2. For large tables, we recommend using the pt-online-schema-change utility from the Percona Toolkit rather than ALTER TABLE statements to avoid locking.
  3. If the lag persists, enable parallel replication. To do this, configure the following parameters: 
    slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
  4. Run the SHOW SLAVE STATUS; command on the replica. If the value of Executed_Gtid_Set remains unchanged for a long time, make sure all the tables have indexes.
  5. 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?

  1. Check whether there is space available on the disk hosting your cluster:
    1. Go to the folder page and select Managed Service for MySQL.
    2. Click the cluster name and go to the Monitoring tab.
    3. Check the Disk usage chart.
  2. Check host monitoring charts:
    1. Go to the folder page and select Managed Service for MySQL.
    2. Click the cluster name and select the Hosts tab.
    3. Go to the Monitoring page.
    4. Find the problem resource: its chart will be approaching or will have crossed the boundary.
    5. Select the other hosts from the drop-down list and check them as well.
  3. 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:

  1. In the MySQL® cluster settings, set Long query time to a value greater than zero.
  2. In the management console, select the Logs tab on the cluster page.
  3. 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:

  1. Go to the folder page and select Managed Service for MySQL.
  2. Click the cluster name and select the Hosts tab.
  3. 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 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.

What causes a replica to lag?

  1. Check that the slave_rows_search_algorithms parameter is set to INDEX_SCAN,HASH_SCAN.
  2. For large tables, we recommend using the pt-online-schema-change utility from the Percona Toolkit rather than ALTER TABLE statements to avoid locking.
  3. If the lag persists, enable parallel replication. To do this, configure the following parameters: 
    slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
  4. Run the SHOW SLAVE STATUS; command on the replica. If the value of Executed_Gtid_Set remains unchanged for a long time, make sure all the tables have indexes.
  5. 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:

  1. Go to the folder page and select Managed Service for MySQL.
  2. Click the cluster name and select the Hosts tab.
  3. Go to the Monitoring page.
  4. Find the problem resource: its chart will be approaching or will have crossed the boundary.
  5. 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.

  1. 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>

  2. 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.

  1. 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>

  2. 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.

  1. 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>

  2. 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.

  1. 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>

  2. To view table-level locks, run the following query:

    SELECT * FROM sys.schema_table_lock_waits

  3. 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. To do this, find the Storage parameter under Resources on the cluster page.

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 page and find the Managed Databases section.

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.

  1. Go to the cluster page and click the Hosts tab.
  2. 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:

  1. View a description of the CLI switch master command:

    yc managed-mysql cluster start-failover --help

  2. 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, go to the cluster page. To view cluster charts, go to Monitoring. To find individual host charts, go to Hosts → Monitoring.

To view charts in Yandex Monitoring, on the home page, select the Managed Service for MySQL service dashboard. To view individual metrics, go to Metric Explorer and set the service parameter to Managed Service for MySQL.

How do I view logs?

  1. Go to the cluster page and click the Logs tab.
  2. 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 if long_query_time is greater than 0.
    • 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.

  1. View a description of the CLI command to view the logs:

    yc managed-mysql cluster list-logs --help

  2. 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 the performance_schema and the sys 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. For the setup guide, see Creating alerts.

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 space
  • Warning: 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:

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.

Previous
Configuring MySQL