[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for MySQL®](../index.md) > FAQ > Performance issues # Performance issues * [How do I find out what is causing performance degradation at peak loads?](#degradation-at-peak) * [How do I find out what is causing overall performance degradation?](#general-degradation) * [How do I find out why resources take long to load?](#long-load) * [How do I find out what is causing high CPU usage?](#high-cpu) * [How do I find out what is causing high I/O usage?](#high-io) * [How do I find out what is causing high network usage?](#high-network) * [How do I find out the causes of locks?](#locks) * [How do I optimize queries with performance issues?](#query-optimization) #### How do I find out what is causing performance degradation at peak loads? {#degradation-at-peak} Review the slow query log: 1. In the [MySQL® cluster settings](../operations/update.md#change-mysql-config), set **Long query time** to a value greater than zero. 1. In the [management console](https://console.yandex.cloud), select the **Logs** tab on the cluster page. 1. In the top-left corner, select `MYSQL_SLOW_QUERY` from the drop-down list. #### How do I find out what is causing overall performance degradation? {#general-degradation} Check host monitoring charts: 1. Navigate to the folder dashboard and select **Managed Service for MySQL**. 1. Click the name of your cluster and select the **Hosts** tab. 1. Go to the **Monitoring** page: * We recommend upgrading your host class: * If the `Steal` value in the **CPU usage** chart remains consistently high. * If the `Free` value in the **Memory usage** chart remains consistently low. * High `iowait` values in the **CPU usage** chart may signal that the disk storage is hitting its IOPS limits. We recommend increasing the value to at least the next [allocation unit](../../compute/concepts/limits.md#compute-limits-disks) threshold or using higher-speed disks. For more on disk limits and performance, see [this Yandex Compute Cloud guide](../../compute/concepts/disk.md). #### What causes a replica to lag? {#replica-lagging} 1. Check that `slave_rows_search_algorithms` is set to `INDEX_SCAN,HASH_SCAN`. 1. For large tables, we recommend using `pt-online-schema-change` from the Percona Toolkit rather than `ALTER TABLE` statements to avoid locking. 1. If the lag persists, enable parallel replication. To do this, configure the following settings: ```text slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=8 ``` 1. Run the `SHOW SLAVE STATUS;` command on the replica. If the `Executed_Gtid_Set` value remains unchanged for a long time, make sure all the tables have indexes. 1. For continuous database writes on a host with 8 GB or more of RAM, we recommend increasing `innodb_log_file_size` up to 1 or 2 GB (changing this setting requires a server restart). #### How do I find out why resources take long to load? {#long-load} Check host monitoring charts: 1. Navigate to the folder dashboard and select **Managed Service for MySQL**. 1. Click the name of your cluster and select the **Hosts** tab. 1. Go to the **Monitoring** page. 1. Find the resource in question: its chart will be approaching or crossing the limit. 1. Select the other hosts from the drop-down list and check them as well. If the charts do not show overloading of the cluster resources, follow the recommendations under [Causes of locks](#locks) and [Query optimization](#query-optimization). #### How do I find out what is causing high CPU usage? {#high-cpu} To get data on CPU usage, use system views. To access these views, you need the `PROCESS` [administrative privilege](../concepts/settings-list.md#setting-administrative-privileges) for the cluster. 1. Grant the `PROCESS` privilege to the user by running this [CLI](../../cli/index.md) command: ```bash yc managed-mysql user update \ --global-permissions PROCESS \ --cluster-id ``` 1. Use the following query to get the list of longest-running database queries: ```sql SELECT * FROM sys.statement_analysis LIMIT 10; ``` Pay attention to queries with high values for `rows_examined` and `rows_sorted`, or those with the `full_scan` flag, as they are likely to use the most CPU. For more information, see [this MySQL® guide](https://dev.mysql.com/doc/mysql-em-plugin/en/myoem-metric-sysschema-statementanalysis-category.html). #### How do I find out what is causing high I/O usage? {#high-io} To get approximate I/O usage by MySQL® threads, use system views. To access these views, you need the `PROCESS` [administrative privilege](../concepts/settings-list.md#setting-administrative-privileges) for the cluster. 1. Grant the `PROCESS` privilege to the user by running this [CLI](../../cli/index.md) command: ```bash yc managed-mysql user update \ --global-permissions PROCESS \ --cluster-id ``` 1. Get the list of threads using the following query: ```sql 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; ``` Typically, the threads at the top of the table are those handling the buffer pool and replication, which is normal. #### How do I find out what is causing high network usage? {#high-network} High network load may result from a `SELECT` returning a large number of rows, an `INSERT` of large amounts of data, or an `UPDATE` affecting many rows. Writes will replicate changes to replica hosts, generating extra traffic. To get approximate network usage by MySQL® threads, use system views. To access these views, you need the `PROCESS` [administrative privilege](../concepts/settings-list.md#setting-administrative-privileges) for the cluster. 1. Grant the `PROCESS` privilege to the user by running this [CLI](../../cli/index.md) command: ```bash yc managed-mysql user update \ --global-permissions PROCESS \ --cluster-id ``` 1. Get the list of threads using the following query: ```sql 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 since the threads were started, so long-lived connections, such as those used for replication, will be closer to the top. #### How do I find out the causes of locks? {#locks} If the cluster resources are not overloaded yet queries run slowly, use system views to retrieve information on lock waits. To access these views, you need the `PROCESS` [administrative privilege](../concepts/settings-list.md#setting-administrative-privileges) for the cluster. 1. Grant the `PROCESS` privilege to the user by running this [CLI](../../cli/index.md) command: ```bash yc managed-mysql user update \ --global-permissions PROCESS \ --cluster-id ``` 1. To view table-level locks, run the following query: ```sql SELECT * FROM sys.schema_table_lock_waits ``` 1. To view row-level locks, run the following query: ```sql SELECT * FROM sys.innodb_lock_waits ``` For more information, see [this MySQL® guide](https://dev.mysql.com/doc/refman/8.0/en/sys-schema-table-lock-waits.html). #### How do I optimize queries with performance issues? {#query-optimization} See the official MySQL® documentation: * [Optimizing queries with `EXPLAIN`](https://dev.mysql.com/doc/refman/5.7/en/using-explain.html). * [Optimizing SQL statements](https://dev.mysql.com/doc/refman/5.7/en/statement-optimization.html). * [Optimizing tables](https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb.html).