Detailed explanation for higher than expected Disk I/O on MySQL Hot Tables

MySQL Hot Tables are tables that experience a high volume of traffic and activity, and can quickly become a bottleneck for database performance. When you see higher than expected disk I/O on MySQL Hot Tables, it is an indication that the database is doing a lot of read and write operations, which can lead to performance issues.

Here are some possible causes of higher than expected disk I/O on MySQL Hot Tables:

  1. Lack of indexing: If the Hot Table does not have appropriate indexes, the database may be performing full table scans to retrieve data, resulting in high disk I/O. Proper indexing can help reduce the number of disk reads required to retrieve data.
  2. Large row size: If the Hot Table has a large number of columns or large column sizes, it can increase the amount of disk I/O required to read or write a single row. Consider reducing the number of columns or using more efficient data types to reduce the row size.
  3. Slow disk or storage system: Slow disk or storage systems can cause high disk I/O, especially if the Hot Table experiences a high volume of traffic. Consider upgrading to faster storage solutions, such as solid-state drives (SSDs).
  4. Inefficient queries: Inefficient queries, such as those that use table joins or subqueries, can cause high disk I/O by requiring the database to access multiple tables or perform complex calculations. Consider optimizing queries by using appropriate indexing, minimizing the use of subqueries, and breaking complex queries into smaller, simpler ones.
  5. Large transaction logs: If the Hot Table experiences a high volume of writes, it can quickly fill up the transaction logs, resulting in high disk I/O as the database writes data to disk. Consider increasing the size of the transaction logs or using a larger disk or storage system.

To address high disk I/O on MySQL Hot Tables, it is important to identify the underlying cause and address it appropriately. This may involve optimizing queries, adding appropriate indexes, using more efficient data types, upgrading to faster storage solutions, or other strategies. By identifying and addressing the root cause of high disk I/O, you can help ensure that your Hot Tables perform well and do not become a bottleneck for database performance.

Here’s an SQL script to monitor disk I/O happening to MySQL using Performance Schema:

This script queries the performance_schema.events_waits_summary_global_by_event_name table to retrieve statistics on I/O wait events. The event_name LIKE ‘wait/io/%’ condition filters the results to include only I/O wait events. The query returns the following columns:

  • event_name: The name of the I/O wait event.
  • total_events: The total number of times the event occurred.
  • total_wait_time_ms: The total time spent waiting for the event, in milliseconds.
  • avg_wait_time_ms: The average time spent waiting for the event, in milliseconds.

This information can help you identify which I/O wait events are consuming the most resources and potentially causing performance issues. You can use this information to optimize your database configuration and queries to reduce disk I/O and improve performance.

About Shiv Iyer 422 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.