RDS for MySQL Performance Tuning – Resolving High I/O Issues

High I/O Caused by High Throughput

• Symptom

  If your application frequently initiates requests to update, delete, and insert data on the tables containing many indexes or large fields, the I/O of your instance significantly increases due to the data reads and the flushes of dirty pages.

  To view read/write performance, click View Metrics in the Operation column.

  

  

• Solution

  Modify instance parameters to reduce the read/write frequency, upgrade instance specifications, or tune the settings of dirty page flushing parameters. The dirty page flushing parameters are explained as follows:

  innodb_max_dirty_pages_pct: the percentage of dirty pages allowed in the buffer pool. Default value: 75.

  innodb_io_capacity: the maximum number of I/O operations allowed by InnoDB per second for each background task. The value of this parameter affects the speed at which RDS flushes dirty pages to the disk and the speed at which RDS writes data to the buffer pool. The default value varies depending on the disk type.

  innodb_io_capacity_max: the maximum number of I/O operations allowed by InnoDB per second for each background task when the flushing activity falls behind. The value of this parameter is greater than the value of innodb_io_capacity. The default value of innodb_io_capacity_max is 40000.

High I/O Caused by Temporary Tables

• Symptom

  If the temporary directory size is too big, RDS may have created large temporary tables due to operations such as the sorting and deduplication of slow SQL statements. This increases the I/O of your instance. Data writes to temporary tables also increase the I/O of your instance.

  To view the temporary table creation, click View Metrics in the Operation column.

• Solution

  On the Slow Query Logs page of the RDS console, download and view the SQL statements that are executed slowly. Analyze the information such as execution duration of the slow SQL statements and optimize the SQL statements based on the analysis result.

  • For details about how to download slow query logs, see Viewing and Downloading Slow Query Logs.
  • For details about how to optimize slow SQL statements, see Troubleshooting Slow SQL Issues for RDS for MySQL DB Instances.

High I/O Caused by Cold Data Reads

• Symptom

  If the data that is queried or modified by using SQL statements cannot be hit in the buffer pool, RDS needs to read the data from disks. This may significantly increase the I/O of your instance.

  To view the buffer pool hit ratio, click View Metrics in the Operation column.

  

• Solution

  Redesign the cache policy based on your service scenario or upgrade instance specifications.

High I/O Caused by Binlog Writes from Large Transactions

• Symptom

  A transaction only writes log records into binlog files when it is committed. If your application runs a large transaction, the transaction may write a few dozen GB of data into binlog files, for example, if the transaction contains a DELETE statement that is used to delete a large number of rows. When these binlog files are flushed to the disk, the I/O of your instance significantly increases.

• Solution

  You are advised to split large transactions. This allows you to reduce the flushes of dirty pages to the disk.

Introduction to the InnoDB I/O System

InnoDB uses an independent I/O system to read and write data pages. If a data page that is requested by an SQL statement cannot be hit in the buffer pool, physical I/O operations are performed to read and write data to the disk.

• Operations to read data pages

  The underlying read interface is called based on synchronous I/O to read data pages.

• Operations to write data pages

  Take the flushes of dirty pages for example. Background I/O threads are called based on asynchronous I/O to asynchronously flush dirty pages to the disk.

  In addition to I/O operations on common data files, a number of other operations may also significantly increase the I/O of your instance. These operations include the operations to write redo logs, undo logs, and binlogs, the operations to sort temporary tables, and the operations to rebuild tablespaces due to DDL statements.