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?

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

• How do I figure out why the CPU resource is utilized?

• How do I figure out why the IO resource is utilized?

• How do I figure out why the network resource is utilized?

• How do I figure out why locks are put in place?

• How do I optimize problem queries?

How do I figure out the cause of performance degradation at peak loads?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?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?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?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?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?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?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?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?How do I optimize problem queries?

See the official MySQL® documentation:

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