When you use JOIN operations on multiple tables with ApsaraDB RDS for MySQL or PolarDB for MySQL data sources, Data Transmission Service (DTS) syncs both the primary table and secondary tables to OpenSearch. Secondary table updates can cascade into many primary table writes, which may push throughput past the limits required for real-time synchronization.
How write amplification works
When you JOIN a primary table and a secondary table, OpenSearch produces a wide table that combines records from both.
Primary table updates are one-to-one: each primary key ID maps to exactly one wide table record, so updating one primary table record updates one wide table record.
Secondary table updates can be one-to-many. When the ratio of primary table records to secondary table records is N:1, one primary key ID in the secondary table may correspond to N records in the primary table. Updating one secondary table record can therefore trigger N wide table updates.
The following figure shows how a primary table and a secondary table are joined:
The following figure shows the N:1 relationship and its cascading update effect:
Throughput limits
| Limit | Threshold | Effect when exceeded |
|---|---|---|
| Combined updates on primary and secondary tables | ≤ 1,500 transactions per second (TPS) | Real-time sync is not guaranteed for either table |
| Secondary table updates that trigger primary table updates | ≤ 1,000 TPS | Data synchronization from the primary table and secondary tables may be delayed |
| Primary-to-secondary record ratio (N:1) | N ≤ 10 | Higher N amplifies write fan-out and increases the risk of exceeding the TPS limits above |
Reduce synchronization latency
If you are experiencing synchronization delays, submit a ticket to adjust the TPS limit for secondary table updates. The two options trade off primary table latency against secondary table latency:
Option 1: Raise the secondary table TPS limit
Submit a ticket to increase the maximum TPS for secondary table updates. Secondary tables sync faster, but the increased write fan-out may delay primary table updates. Choose this option when primary table latency is acceptable.
Option 2: Cap the secondary table TPS
Submit a ticket to set a lower maximum TPS for secondary table updates. This protects primary table update latency by slowing secondary table sync. Choose this option when primary table freshness is critical.