Google Cloud Account Recharge: GCP Cloud SQL Master-Slavery Synchronization Interruption and Read Replica Super High Latency Troubleshooting Guide

At 3 o'clock in the middle of the night last week, the PagerDuty alarm bell broke the silence: the Read Replica of a core business on the line was delayed by 3600 seconds (1 hour), the read-only node query frequently reported errors, and the master-slave synchronization was about to be completely disconnected.

For teams that rely on Google Cloud SQL (whether MySQL or PostgreSQL) to build a read-write separation architecture, the sudden increase in Read Replica delay or synchronization interruption (Replication Lag / Broken) is definitely one of the most troublesome problems.

As SRE who has stepped on the pit on GCP for many years, I will take you back to the scene through this article and sort out a set

Real and Landing Cloud SQL Replication Delay Troubleshooting and Optimizing the Whole Process

.

1. phenomenon: how did the failure happen?

In general, there are two typical manifestations of replication latency explosion:

Boiled Frog: On the Cloud SQL monitoring panel of Google Cloud Console, the replica_lag indicator continues to rise at a 45-degree angle.

Cliff and avalanche type: The master database performs a time-consuming batch update, and the slave database suddenly stops synchronization, or the WAL/Binlog of the read-only node cannot catch up with the master database, which directly causes the replication chain to be interrupted.

To troubleshoot this problem, we first need to understand the underlying replication logic of GCP.

A Brief Analysis of 2. Core Principles: Synchronization Mechanism of Cloud SQL

Cloud SQL for MySQL: GTID-based row-level replication (Row-Based Replication). The main library records the writes to Binlog, the IO Thread of the slave library is responsible for pulling Binlog, and SQL Thread (or Parallel Workers) is responsible for local replay.

Cloud SQL for PostgreSQL: Streaming-based Replication (streaming replication). The master library writes the WAL, which is received from the WAL Receiver of the library and is Startup by the WAL Apply/Replay process.

The delay occurs when the write concurrency of the master library is extremely high, or the slave library cannot apply these changes in time.

3. four-step troubleshooting method: find the culprit causing the synchronization delay

Faced with a delay of several thousand seconds, don't blindly restart the slave library. Restarting a read-only node will cause the local buffer to be lost, and replay may worsen the delay. Follow these steps to troubleshoot step by step:

1. Check the resource bottleneck: Is the master and slave specifications equivalent?

This is the easiest pit for 80% of newbies to step on. In order to save money, many people like to match the main library

16vCPU / 64G, while the slave library only gives 2vCPU/8G.

The problem is that the main library can easily handle massive writes with high concurrent CPU, but the slave library is unable to replay Binlog/WAL with single thread (or limited multi-thread) under the condition of limited resources, and the CPU often hits 100 directly.

How to troubleshoot: View the CPU usage and Disk I/O Utilization of the slave library in Cloud Monitoring.

2. Find large transactions on the main library and "no primary key table"

Large Transactions: Someone ran a DELETE FROM orders WHERE status = 0 on the main library and deleted 5 million pieces of data. In MySQL, this transaction will not be sent to the slave library in whole blocks until the master library commits. During the replay of the large transaction from the library, all subsequent synchronization will be blocked.

No primary key table (Missing Primary Keys): In Row-Based replication mode, if a large table does not have a primary key, the main library only needs to scan the full table once to update a record, but every row change needs to be scanned once when replaying from the library! When 10000 rows are updated, the slave library needs to perform 10000 full table scans, and the delay soars directly to the sky.

3. Is there a long query lock conflict from the library?

The read-only node is not only synchronizing data, it is also responding to the business's Read request.

In PostgreSQL, if the slave library is running an analysis SQL that takes 10 minutes, and the WAL passed from the master library just modifies the table that the SQL is reading, a conflict will occur. Depending on the max_standby_streaming_delay parameter configuration of the PostgreSQL, the slave will wait for the query to complete, thereby suspending the application of the WAL.

In MySQL, large queries on the slave library may hold table locks or implicit locks, blocking writes to SQL Threads.

4. Check network and cross-region latency

If your Read Replica is deployed off-site (e. g. the main library is in Tokyo

asia-northeast1

from the library in Singapore

asia-southeast1

), network jitter and physical latency across geographies will directly lengthen

network_lag

.

4. Rapid Hemostasis and Radical Treatment

For the root causes identified above, we can quickly deal with them by the following means:

1. Emergency hemostasis: dynamic upgrade and query killing process

One-click temporary upgrade of slave library: read Replica vCPU/directly in GCP Console without modifying the master library/

Memory is promoted to be consistent with the main library (or even slightly higher than the main library), providing it with enough CPU and I/O resources to catch up.

Kill Slow Query on Slow Query from Library: If it is found that there is a complex analysis SQL that occupies resources from the library, Kill it decisively.

Ensure the stability of the production account: When performing these emergency expansion and high-specification instance tuning in the cloud, make sure that the cloud resources and deduction status are normal. Due to the cumbersome financial approval process, many enterprises often fail to operate due to insufficient credit card limit or account arrears when faced with sudden traffic and need to quickly expand resources. In order to avoid this embarrassment, many operation and maintenance and finance teams will choose to recharge Google cloud accounts through professional cloud service providers, and flexibly supplement the quota by means of public payment, prepayment or issuing domestic invoices, so as to ensure that the configuration can be raised at any time at the critical moment of failure response.

2. MySQL Special Optimization: Enable Parallel Replication (Parallel Replication)

Cloud SQL MySQL may not maximize parallel replay performance by default. You can enable parallel replication by modifying the flag:

Set the replica_parallel_workers (or slave_parallel_workers) to a value consistent with the number of slave vCPUs.

With the setting of replica_parallel_type = LOGICAL_CLOCK, the efficiency of applying Binlog from the library is greatly improved.

3. PostgreSQL-specific optimization: weighing query and synchronization

If your PG Read Replica delay is extremely high due to query conflicts, you can try to adjust the following parameters in the Database Flags of the slave library:

If the max_standby_streaming_delay is set to 30s, the system will preferentially cancel read-only queries (report canceling statement due to conflict with recovery) when a synchronization conflict occurs to ensure real-time synchronization.

4. Business layer changes: avoid large single transactions

Split a large UPDATE or DELETE operation into small batches (Batch Processing) and submit them in batches.

Strictly standardize database table building specifications: all tables must contain primary keys.

5. Summary

GCP Cloud SQL's Read Replica Latency Troubleshooting is essentially a

Resources, Concurrency, and Locks

of the game. When delayed alarms are encountered, follow the"

Check the specification-> confirm the large transaction/primary key-> exclude the conflict from the library lock-> adjust the database flag/temporary upgrade.

This set of combination, the vast majority of synchronization problems can be solved.

Retainer

The scientific nature of the structure, the abundance of operation and maintenance resources, and the rigor of database specifications are the only way to make the business worry-free.