Performance issues

• How do I find out what is causing performance degradation at peak loads?

• How do I find out what is causing overall performance degradation?

• How do I find out why resources take long to load?

• How do I find out what is causing high CPU usage?

• How do I find out what is causing high I/O usage?

• How do I find out what is causing high network usage?

• How do I find out the causes of locks?

• How do I optimize queries with performance issues?

How do I find out what is causing performance degradation at peak loads?How do I find out what is causing 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 find out what is causing overall performance degradation?How do I find out what is causing overall performance degradation?

Check host monitoring charts:

1. Navigate to the folder dashboard and select Managed Service for MySQL.
2. Click the name of your cluster and open the Hosts tab.
3. 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 threshold or using higher-speed disks. For more information about disk limits and performance, see this Yandex Compute Cloud article.

What causes a replica to lag?What causes a replica to lag?

1. Check that slave_rows_search_algorithms is set to INDEX_SCAN,HASH_SCAN.
2. For large tables, we recommend using pt-online-schema-change 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 settings:

   slave_parallel_type=LOGICAL_CLOCK
   slave_parallel_workers=8
   

4. 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.
5. 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?How do I find out why resources take long to load?

Check host monitoring charts:

1. Navigate to the folder dashboard and select Managed Service for MySQL.
2. Click the name of your cluster and open the Hosts tab.
3. Go to the Monitoring page.
4. Find the resource in question: its chart will be approaching or crossing the limit.
5. 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 and Query optimization.

How do I find out what is causing high CPU usage?How do I find out what is causing high CPU usage?

To get data on CPU usage, use system views. To access these views, you need the PROCESS administrative privilege for the cluster.

1. Grant the PROCESS privilege to the user by running this CLI command:

   yc managed-mysql user update \
       --global-permissions PROCESS <username> \
       --cluster-id <cluster_ID>
   

2. Use the following query to get the list of longest-running database queries:

   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® article.

How do I find out what is causing high I/O usage?How do I find out what is causing high I/O usage?

To get approximate I/O usage by MySQL® threads, use system views. To access these views, you need the PROCESS administrative privilege for the cluster.

1. Grant the PROCESS privilege to the user by running this CLI command:

   yc managed-mysql user update \
       --global-permissions PROCESS <username> \
       --cluster-id <cluster_ID>
   

2. Get the list of threads using the following query:

   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?How do I find out what is causing high network usage?

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 for the cluster.

1. Grant the PROCESS privilege to the user by running this CLI command:

   yc managed-mysql user update \
       --global-permissions PROCESS <username> \
       --cluster-id <cluster_ID>
   

2. Get the list of threads using the following query:

   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?How do I find out the causes of 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 for the cluster.

1. Grant the PROCESS privilege to the user by running this CLI command:

   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 this MySQL® article.

How do I optimize queries with performance issues?How do I optimize queries with performance issues?

See the official MySQL® documentation:

• Optimizing queries with EXPLAIN.
• Optimizing SQL statements.
• Optimizing tables.