Performance Deterioration Caused by Dirty Page Flushing Efficiency During TPC-C High-Concurrency Long-Term Stable Running

Symptom

TPC-C performance deteriorates due to dirty page flushing efficiency during high-concurrency long-term stable running. The details are as follows: The initial performance is high. As the running time increases, the value of tmpTotal in the database decreases, the CPU usage of the WalWriter thread is 100%, and other CPUs are almost not loaded. In the WDR report, the waiting time for dirty page flushing accounts for the highest proportion.

Cause

Generally, you can analyze the specific cause by checking the process status and operating system resource usage (such as CPU and I/O), or further analyze the root cause based on the WDR. In this scenario, dirty page refreshing is inefficient.

Solution

1. Reduce the concurrency or increase the value of shared_buffers.
2. Adjust dirty page parameters. In scenarios where doublewrite is enabled, you can adjust parameters such as page_writer_sleep (downward adjustment) and max_io_capacity (upward adjustment) to improve dirty page elimination efficiency.
3. Replace high-performance disks (such as NVMe disks).

The resources occupied by the database must meet the service requirements. In a high-concurrency test, you need to add resources to ensure that database services are available.