Managed Service for MySQL® performance analysis and tuning

The following issues usually cause reduced performance of a Managed Service for MySQL® cluster:

• High CPU, disk I/O, and network usage.
• Inefficient query execution in MySQL®.
• Locks.

MySQL® monitoring and performance diagnostic tools for a Managed Service for MySQL® cluster, along with special MySQL® queries, will help detect these issues.

Getting startedGetting started

1. Select the databases to analyze.
2. Enable statistics collection.
3. Create a MySQL® user with the PROCESS privilege for these databases. Use this MySQL® user to run all diagnostic queries.

Diagnosing resource shortagesDiagnosing resource shortages

A resource shortage is a likely cause of reduced cluster performance. Cluster monitoring charts (CPU, disk I/O, and network connections) clearly show any resource shortages. If a steadily growing resource usage chart flattens, the resource has hit its limit or run beyond the guaranteed service level.

You can use special queries to find out what causes increased resource usage:

• To estimate disk I/O usage by different MySQL® threads, run this 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 
  

  This query returns a list of MySQL® file threads ranked by the amount of memory they use, from highest to lowest. Threads that serve replication and the InnoDB buffer for caching tables and indexes usually lead the list.

• To estimate network resource usage by different MySQL® threads, run this 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.

  Pay close attention to read and write operations affecting many rows. These can also cause increased network load. For writes, WAL changes will be applied to replicas, which further increases network load.

• You cannot monitor CPU consumption for individual MySQL® queries, but you can identify queries that run inefficiently (see below).

Diagnosing inefficient query executionDiagnosing inefficient query execution

To identify MySQL® queries that run inefficiently, use this query:

SELECT *
FROM   sys.statement_analysis
LIMIT  10

It returns the 10 longest-running queries in the entire server history.

Pay attention to queries with high values for ROWS_EXAMINED, ROWS_SORTED, or those with the FULL_SCAN flag.

For more information about the output, see this MySQL® article.

Diagnosing locksDiagnosing locks

Cluster performance may degrade because of locks caused by multiple simultaneous attempts to access the same database resource, e.g., table or row.

To diagnose locking issues, check the query lock wait queues:

• Table-level lock wait queue:

  SELECT * FROM sys.schema_table_lock_waits 
  

• Row-level lock wait queue:

  SELECT * FROM sys.innodb_lock_waits
  

TroubleshootingTroubleshooting

You can try to optimize any queries with performance issues you have diagnosed. There are several ways for optimization:

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

• Optimize InnoDB tables to reduce disk usage.

If optimizing or removing such queries is not an option, consider upgrading your host class.