Managed Service for MySQL® performance analysis and tuning

The following issues normally impact Managed Service for MySQL® cluster performance:

• High CPU load, frequent disk reads and writes, and network requests.
• Inefficient query execution in MySQL®.
• Locks.

Monitoring tools for MySQL® to troubleshoot performance issues in Managed Service for MySQL® clusters and special MySQL® queries will help detect these problems.

Getting startedGetting started

1. Select databases to troubleshoot.
2. Enable statistics collection.
3. Create a MySQL® user with the PROCESS privilege for these databases. Troubleshooting queries must run under this user's account.

Diagnosing resource shortagesDiagnosing resource shortages

Resource shortage is a likely cause of cluster performance degradation. Resource shortages become evident from cluster monitoring charts (CPU, disk I/O operations, network connections). If a continuously increasing resource utilization plot has leveled out, resource usage has reached its limit or exceeded the guaranteed service level.

Special queries can help determine the reasons for elevated resource utilization:

• To estimate Disk IO utilization by different MySQL® threads, execute the 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 
  

  The query returns a list of MySQL® file threads, ranked in descending order by the amount of memory used. Threads that serve replication and the InnoDB buffer for caching tables and indexes are usually at the top of the list.

• To estimate network resource utilization by different MySQL® threads, execute the 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 from thread launch, so long-lived connections (such as replication) will be returned in the top rows.

  Note the read and write operations that handle a large number of rows. These can also cause increased network load. For writes, changes in WAL will be copied to replicas, which will further increase network load.

• It is not possible to track CPU utilization with individual queries against MySQL®, but inefficient queries (see below) can be identified.

Diagnosing inefficient query executionDiagnosing inefficient query execution

To identify problematic queries against MySQL®, execute the query:

SELECT *
FROM   sys.statement_analysis
LIMIT  10

The query returns the 10 longest running queries in the entire server history.

You should pay attention to queries with high ROWS_EXAMINED, ROWS_SORTED values, or the FULL_SCAN flag.

To learn more about the information in the output, see the MySQL® documentation.

Detecting locksDetecting locks

Cluster performance may degrade because of locks obtained when there are multiple simultaneous attempts to access the same DB resource (table, row).

To troubleshoot, check lock queues in queries:

• Table-level lock queue:

  SELECT * FROM sys.schema_table_lock_waits 
  

• Individual row-level lock queue:

  SELECT * FROM sys.innodb_lock_waits
  

TroubleshootingTroubleshooting

You can try and optimize the problematic queries you have diagnosed. There are several ways to approach such optimization:

• Analyze the query plan using the EXPLAIN command and use the query optimization techniques from the MySQL® documentation.

• Optimize InnoDB tables to reduce disk workload.

If you can't optimize the identified problem queries or manage without them, you can raise the host class.