Discover how to resolve MySQL write stalls and reduce active threads by increasing the redo log size.

In MySQL, encountering write stalls and a surge in active threads can be a perplexing situation. At Mydbops, we recently faced this challenge and successfully resolved it within our managed service. In this blog post, we aim to shed light on the issue and share the solution that helped us overcome it. By increasing the redo log size, we effectively tackled the problem and restored optimal performance. If you've ever wondered about the implications of excessive active threads and stalled writes in MySQL, this blog will provide valuable insights and actionable steps to address the issue.

Workaround Details

Understanding the Problem

MySQL write stalls: Spike in active threads and stalled DB writes observed:

Recently, we noticed a sudden increase in the number of active threads running in the MySQL database. This surge in active threads coincided with a noticeable slowdown in write operations. It was evident that the DB writes were getting stalled, impacting the overall performance of the system. This situation raised concerns and prompted us to investigate further.

Analysis of processlist output

To gain deeper insights into the issue, we examined the processlist output in MySQL. It revealed a significant number of threads in the commit state. Typically, when threads are in the commit state, it indicates that the transactions are being finalized and data is being written to the disk. However, in our case, this was causing a bottleneck, leading to the accumulation of active threads and subsequent write stalls.

This observation sparked the need to explore the underlying cause of the problem and find an effective solution.

Identifying the Cause

Sync flushing and its impact on writes

Our engineers at Mydbops conducted an in-depth analysis and discovered that the issue stemmed from sync flushing. During sync flushing, all writes are blocked, and InnoDB performs page flushing up to a certain point. This point is determined by the log_lsn_checkpoint_age, which measures the age of the checkpoint by subtracting the "Log sequence number" from the "Last checkpoint at" value. As a result, numerous active threads were observed in the commit state, leading to write stalls and degraded performance.

Explanation of InnoDB checkpoint age

To comprehend the significance of the issue, it's essential to understand the concept of InnoDB checkpoint age. The checkpoint age represents the difference between the current log sequence number and the last checkpoint recorded. When the log_lsn_checkpoint_age reaches a certain threshold, it triggers the sync flushing mechanism, causing the system to block writes and focus on page flushing instead.

Solution Implemented by Mydbops

Increasing Redo Log Size

Our analysis revealed that the small redo log size was causing write stalls and excessive active threads. To address this, we increased the redo log size from 1G to 8G.

Importance of redo log size: The redo log ensures data durability and recovery in MySQL. Insufficient redo log size restricts efficient data flushing, leading to write stalls and degraded performance.

Improved approach: MySQL 8.0.30 introduced innodb_redo_log_capacity, replacing innodb_log_files_in_group and innodb_log_file_size. This update enables dynamic resizing of the redo log for greater flexibility.

Note: Starting from MySQL 8.0.30, innodb_redo_log_capacity replaces the previous variables, innodb_log_files_in_group and innodb_log_file_size, for resizing the redo log. To learn more about dynamically changing the redo log file size, we invite you to read our blog post on "Dynamic InnoDB Redo Log Resize in MySQL 8.0.30."

Dynamic Redo Log Size Adjustment

We began dynamically increasing the redo log file size from 2 GB to 8 GB and verified if sync flushing was still occurring. Once we made the adjustment to an 8 GB redo log size, everything returned to normal as discussed earlier in this blog post.

The active threads in the database are now running smoothly without any previous instances of stalling, even considering the typically high concurrency of writes on this server. The increased redo log size has effectively resolved the write stalls and restored optimal performance.

Significance of adjustment: Correctly sizing the redo log file prevents write bottlenecks and optimizes performance by accommodating higher write volumes.

Calculating checkpoint age: Checkpoint age, determined by subtracting the "Log sequence number" from the "Last checkpoint at" value, helps evaluate flushing behavior and redo log suitability.

Results and Positive Impact

Increasing the redo log size yielded significant improvements in database operations:

• Active thread management: The larger redo log size reduced active threads in the commit state, eliminating write stalls and enhancing transaction processing concurrency.
• Restored normal DB operations: With unimpeded write operations, the database returned to normal functioning, ensuring consistent and efficient data writes.

Implementing this solution successfully mitigated write stalls and boosted the performance of our managed MySQL service.

If you're facing similar challenges with write stalls or performance degradation in MySQL, we encourage you to consider resizing the redo log dynamically. Take advantage of the innodb_redo_log_capacity variable introduced in MySQL 8.0.30 to optimize the redo log size according to your workload requirements.

We invite you to explore our other insightful blog posts covering a wide range of topics related to MySQL and database management. From performance tuning to high availability solutions, our blog offers valuable resources to help you optimize your database environment and stay informed about the latest developments in MySQL, MongoDB, PostgreSQL and more.

At Mydbops, we are committed to sharing our expertise and helping you overcome challenges in managing and optimizing your MySQL environment. Explore our blog and empower yourself with knowledge to make informed decisions for your database infrastructure.