Amazon Cloud Agent: Diagnosis and Actual Investigation of Severe Database Performance Fluctuation during AWS RDS Automatic Backup Window
Many operation and maintenance and DBA engineers have encountered such a troublesome scenario: at a fixed time in the morning every day, the system alarm group starts to "bomb indiscriminately"-the CPU usage of the database increases sharply, the number of slow queries increases sharply, the API calls on the application side timeout a large number of times, and even the database connection pool is full.
Looking at the RDS monitoring panel of AWS Console, it is found that the time period when the problem occurs exactly coincides with the RDS automatic backup window (Backup Window).
Why does automatic backup become a "performance killer" of business systems during operation? This article will take you to understand and solve this problem from four dimensions: the underlying physical mechanism of AWS RDS, storage architecture bottlenecks, diagnosis and troubleshooting paths, and architecture governance solutions.
1. tracing back to the source: what happened at the bottom of the backup window?
To thoroughly diagnose this issue, you first need to understand the underlying workings of AWS RDS automated backups. RDS snapshot backup is not a simple database
mysqldump
or logical export, but based on the underlying
Block-level snapshots of Amazon EBS(Elastic Block Store) volumes (Snapshot)
.
When the automatic backup window starts, the AWS bottom layer triggers the snapshot mechanism. There are three core reasons why this process affects database performance:
1. I/O latency due to Copy-on-Write (COW)
EBS uses an incremental snapshot mechanism when creating snapshots. Although the first snapshot is full and subsequent snapshots are incremental, the storage system needs to mark the status of the data block with metadata at the moment when the snapshot is triggered.
During the snapshot creation process, if the application initiates a write operation, the storage layer needs to perform "copy-on-write" or redirect write logic. This leads to write amplification, which directly increases the read and write latency (Read/Write Latency) and disk queue depth (Disk Queue Depth) of the disk.
2. Differences between Single-AZ (single-zone) and Multi-AZ (multi-zone) mechanisms
Single-AZ architecture: An RDS instance has only one master node. The snapshot must be performed directly on the EBS volume of the master node. In the initial stage of snapshot creation, the EBS volume will have an instantaneous I/O hang (I/O Suspension), which can take from a few seconds to tens of seconds. For highly concurrent write services, these few seconds of I/O suspension are sufficient to cause upstream requests to pile up and the connection pool to burst.
Multi-AZ architecture: AWS will drop automatic backups to standby nodes (Standby Instance)
Okay. In theory, the read and write I/O of the master node is not directly affected by the snapshot. However, if the I/O performance of the standby node is degraded due to backup and cannot catch up with the replication log of the primary node, the primary node may be subject to synchronous replication (such as semi-synchronous mechanism or data log flushing blocking), resulting in performance jitter.
3. Storage IOPS and Burst Balance are exhausted
If the RDS is using an older generation
GP2 (General Purpose SSD)
Storage, whose IOPS performance depends on the Burst Balance ".
During the backup window, snapshot read data and business read and write, it is easy to quickly pull up the GP2 IOPS. Once the burst points are exhausted, the IOPS of EBS will instantly drop to the baseline level (for example, the baseline of small-capacity GP2 storage is only 100 IOPS), directly causing the database to jam. Even if
GP3
Storage, if the IOPS or throughput provisioned by the business is insufficient, it will also hit the performance wall during backup.
2. four-step diagnostics: how to pinpoint the root cause of bottlenecks?
When the database performance fluctuates sharply within the backup window, do not blindly expand. It is recommended to locate the real cause according to the following "four-step diagnosis method:
[Step 1: CloudWatch Timeline Alignment]-> [Step 2: Performance Insights Wait for Events]
│
[Step 4: Check Business Scheduled Tasks/Large Transactions] <── [Step 3: Check Storage Type and IOPS Bottleneck]
Step 1: Timeline alignment (CloudWatch monitoring cross-comparison)
Go to the CloudWatch monitoring panel, zoom the time range to 2 hours before and after the exception occurs, and observe the following core indicators:
WriteLatency and ReadLatency: Observe whether the disk read/write latency has a steep peak when the backup window is opened (normally, it should be less than 10ms, if it spikes to tens or even hundreds of milliseconds, the storage layer bottleneck is obvious).
ReadIOPS / WriteIOPS and DiskQueueDepth: Check whether IOPS has reached the upper limit of the current storage volume, and observe whether the disk queue depth is far beyond the normal value (it is generally recommended that the queue depth be maintained at about preset IOPS/500, which indicates serious backlog of I/O).
EBSSurplusBalance / BurstBalance: If using GP2 storage, check if the Burst Balance index drops to 0%.
Step 2: With Perfo
rmance Insights (performance drill-down)
The Performance Insights of opening RDS can help us see what SQL is slowing down the database. Key Focus
AAS(Average Active Sessions, Average Active Sessions)
And wait events:
If there is a large number of IO/file/innodb/innodb_data_file or IO:DataFileRead / IO:DataFileWrite waiting, the main bottleneck is concentrated on disk physical I/O.
If there are a large number of wait/synch/sxlock/innodb/btr_search_latch or memory lock waiting, it means that the data page cannot be flushed in time due to I/O blocking, which leads to lock contention within the database.
Step 3: Check the instance architecture and storage type
Confirm the attribute configuration of the current RDS instance:
Single-AZ or Multi-AZ?
Is the storage type GP2, GP3, or Provisioned IOPS (io1/io2)?
Is the database engine enabled for large-scale Undo Log cleaning or Dirty Pages (dirty pages) high-scale disk brushing?
Step 4: Troubleshoot conflicts between application-side and background timing tasks
Many teams are used to running long transactions, data archiving, ETL report generation and other scheduled tasks at night. If these business timing tasks happen to coincide with the RDS automatic backup window of AWS, the superposition effect of "write amplification and snapshot reading" will be formed, which will directly pull up the disk I/O.
3. Thorough Governance and Architecture Optimization Program
After locating the root cause of the problem, we can conduct targeted governance from four aspects: "architecture decoupling", "storage upgrade", "configuration adjustment" and "operation and maintenance guarantee.
1. Architecture upgrade: single zone to multiple zones (Single-AZ upgrade to Multi-AZ)
If the production environment is still using RDS Single-AZ, we strongly recommend that you upgrade it to Multi-AZ deployment.
Effect: After upgrading, AWS will automatically transfer daily automatic backup tasks to the Standby standby node for execution, completely cutting off the direct impact of snapshot I/O suspension on the production business of the primary node.
2. Storage transformation: Seamless migration from GP2 to GP3, or provisioning Provisioned IOPS
Say goodbye to GP2:GP2 relies on burst points (Burst Balance), and its performance is extremely unstable. GP3 provides independent IOPS and throughput provisioning with 3,000 IOPS and 125 MB/s on base configuration
throughput.
Use io1/io2 in high-load scenarios: For core databases with extremely high concurrency and low latency requirements, it is recommended to directly use Provisioned IOPS(io1/io2) storage and set sufficient IOPS preset values according to business computing power requirements.
3. Rearrange backup window and timing task peak shift
Respecify Backup Window: In RDS settings, adjust the automatic backup window to the time period with the lowest business traffic throughout the day (for example, 03:00-04:00).
Staggering of scheduled tasks: Staggering scheduled tasks such as batch processing, data archiving, and index reconstruction in the system from the automatic backup window by at least 1-2 hours to avoid traffic overlap.
4. Operation and maintenance support: cloud resource expansion and budget management
Whether upgrading Single-AZ to Multi-AZ, upgrading GP2 to GP3/io2, or increasing instance specifications and IOPS provisioning will bring about certain changes in cloud infrastructure costs.
When making these architectural adjustments and resource changes, it is important to ensure that the AWS account is healthy and has sufficient quota. For enterprise users, regularly check the financial status of the account and complete it in a timely manner
AWS Account Top-Up
It is a key operation and maintenance support action. If the service is limited or the change is interrupted due to account arrears during the change peak period or automatic expansion phase, more serious production accidents may be caused. Therefore, incorporating financial and budget management into the daily Standard Operating Procedure (SOP) of operations is an important part of ensuring high database availability.
4. Summary and Best Practice Checklist
performance fluctuations caused by AWS RDS automated backups, which are essentially
Physical storage I/O resources bottleneck under snapshot pressure
The embodiment. Through reasonable architecture design and parameter tuning, it is possible to achieve "backup without sensitization".
In daily operations, we recommend that you refer to the following best practice checklist (Checklist):
Check dimension
Best Practice Requirements
Description
Deployment architecture
The production database must be Multi-AZ
Depressurize backup I/O to Standby standby node
Storage type
Deprecate GP2 and upgrade to GP3 or io1/io2
Provides predictable IOPS and throughput to avoid zero burst points
Window Management
Backup Window Avoid Business Peak
Ensure there are no heavy ETL or bulk Delete/Update tasks within the backup window
Monitoring and Alerting
Configure CloudWatch WriteLatency and DiskQueueDepth alarms
Early detection of signs of storage performance degradation
Financial operation and maintenance
Maintain
AWS account recharge and sufficient funds
Ensure smooth implementation of elastic capacity, storage changes and Multi-AZ upgrades
As long as you understand the operation mechanism of the underlying EBS Snapshot, combined with clear diagnosis and troubleshooting steps and reasonable architecture transformation, you can easily overcome the problem of severe performance fluctuations during RDS automatic backup and escort the smooth operation of online services.
