Product Documentation

Appearance

All platforms

Replication Performance Benchmark ​

This document provides detailed replication benchmarks obtained using Yellowbrick version 7.2.0-69679. All measurements were performed with two 6-blade Andromeda second generation appliances connected by a 10 Gbps network link. A peak replication rate of 0.981 GB/s was recorded from the primary and secondary for a single replica, which represents 86% of the available network capacity.

Benchmark Setup ​

Environment ​

ParameterValue
Source ApplianceAndromeda (6 blades, v7.2.0-69679)
Destination ApplianceAndromeda (6 blades, v7.2.0-69679)
Network Bandwidth9.42 Gbps (iperf 30 s, 8 streams)
Memory per Compute Node512 GB
WLM ProfileDefault
Database EncodingLATIN9
Total Rows24,576,024,576
Compressed size630.8 GB
Uncompressed size2.2 TB
Compression ratio3.5×
Average row size27.56 bytes

Table Definition ​

sql
CREATE TABLE public.addition_1 (
  worker_id   UUID     NOT NULL,
  worker_lid  SMALLINT NOT NULL,
  node_id     INTEGER  NOT NULL,
  thread_id   SMALLINT NOT NULL,
  range       BIGINT   NOT NULL,
  range_min   BIGINT   NOT NULL,
  range_max   BIGINT   NOT NULL,
  row_number  BIGINT   NOT NULL,
  random_seed BIGINT   NOT NULL,
  random      BIGINT   NOT NULL
)
DISTRIBUTE RANDOM;

Query to Populate Table ​

The following query was used to populate the test table.

sql
CREATE TABLE public.addition_1 AS SELECT * FROM sys.rowgenerator;

INSERT INTO public.addition_1 SELECT * from public.addition_1;
-- Repeat until the table reaches a size that saturates replication bandwidth,
-- minimizing overhead from startup or I/O latency.
-- In our tests we used ~ 630GB but we could reach network saturation at earlier sizes
-- such as 100GB.

Baseline: Replication Without Additional Workload ​

Each replica was created using the following command:

sql
ALTER DATABASE "<database_name>" ADD REPLICA "<replica_name>"
TO "<remote_server>"
WITH (
  frequency 99999,
  alias '<replica_db_name>'
);

Once created, the replica was started with:

sql
ALTER DATABASE "<database_name>" ALTER REPLICA "<replica_name>" START;

This resulted in the following throughput and timings on the benchmarked system:

MetricValue
Bytes sent881,083,886,988
Elapsed time14 minutes 35.96 seconds (875,959 ms)
Bandwidth8,046.80 MiB/s (1,005 MB/s)

The single replication workload used 86% of the measured network bandwidth:

MetricValue
Measured iperf bandwidth9.42 Gbps
Replication throughput8.05 Gbps
Utilization of capacity85.5%

Scalability: Two Concurrent Replication Workloads ​

Further tests were run with 2 and 4 replicas operating in parallel. The replication process was forced to complete one full cycle in each case.

Replica CountBytes SentElapsed TimeBandwidth (MiB/s)Bandwidth (MB/s)Replica Name
1881,107,588,91314 min 35.96 sec8,046.801,005repl_test_bandwidth_a
2881,112,026,79926 min 6.15 sec4,502.62562repl_test_bandwidth_b
2881,107,588,91326 min 7.52 sec4,496.83562repl_test_bandwidth_c
4880,736,450,36455 min 24.51 sec2,119.38264repl_test_bandwidth_d
4880,642,224,29255 min 25.92 sec2,118.25264repl_test_bandwidth_e
4881,295,115,69055 min 26.06 sec2,119.73264repl_test_bandwidth_f
4881,774,587,79555 min 27.19 sec2,120.17265repl_test_bandwidth_g

Parallel replication tasks were started with the following commands, in the case of 4 replicas:

sql
-- The following 4 operations are asynchronous
alter database "repl_parallel_1" alter replica "repl_test_bandwidth_d" start;
alter database "repl_parallel_2" alter replica "repl_test_bandwidth_e" start;
alter database "repl_parallel_3" alter replica "repl_test_bandwidth_f" start;
alter database "repl_parallel_4" alter replica "repl_test_bandwidth_g" start;

Observations ​

  • Single Replica: Achieved 1,005 MB/s (≈8.05 Gbps), which is 86% of the measured 9.42 Gbps network bandwidth. This indicates good single-stream efficiency and a likely underutilization of the CPUs.
  • Two Replicas: Each replica achieved 562 MB/s, summing to 1,124 MB/s (≈ 9.0 Gbps). This is within 4.5% of total capacity, indicating full network utilization, with minor inefficiencies from concurrency overhead, and full CPU utilization.
  • Four Replicas: Each replica achieved 264–265 MB/s, totaling 1,056 MB/s (≈ 8.45 Gbps). This shows the network remained saturated, but additional parallelism did not increase total throughput, suggesting a network bottleneck — not a CPU or disk I/O bottleneck.

Delete & Update Operation Performance ​

Delete and update operations are more expensive than a simple append operation. This is because additional metadata must be sent from the source system to the destination. This can range between 4 bytes (with compression) to ≈130 bytes per deleted / updated row (rowupdated, rowunique, rowtxid).

The performance of the delete operation is highly influenced by how much memory a query executed on the destination system can claim from the applied WLM profile. We recommend allowing at last 25 GB of memory for these types of queries.

Those queries are generally of the form:

sql
 DELETE WITH NO TABLEDELETE FROM public.test_delete           
 WHERE                                                        
     rowtxid != 73370                                         
  AND rowunique % 4 = 1                                       
         AND rowunique IN(                                    
             SELECT rowunique                                 
             FROM public.test_delete                          
             WHERE rowtxid = 73370 AND rowunique % 4 = 1      
                 AND rowupdated = 't'                         
             UNION ALL SELECT rowunique                       
             FROM public.yb_deletes_17962                     
             WHERE                                            
                 rowtxid = 73370 AND rowunique % 4 = 1        
 )

The compute cluster that the data is being replicated to will partition the query execution into N buckets depending on the available memory set by the WLM profile (in the example above, the query was executed in a batch of 4 — rowunique % 4).

For example, the following replication cycle included the deletion of all 24,576,024,576 records, and resulted in the following benchmarks:

MetricValue
sent_bytes101,214,543,909
elapsed_ms189,063
elapsed_time3m 9.06s
bandwidth_mib/s4,282.79
bandwidth_MB/s535

In the above table, sent_bytes represents the size of the metadata sent from the source to the destination during replication.

The time spent performing the delete operations can be determined by adding up the delete queries:

sql
yellowbrick=# select sum(total_ms) from sys.log_query where query_text ilike '%yb_deletes_17962%' and type = 'delete';                                                                                                                                                                       
-----------
 90054.536
(1 row)

It follows that the rate of deletion on the replica was ≈ 272 million rows per second:

MetricValue
Rows Deleted24,576,024,576
Elapsed time1 minute 30.05 seconds (90,055 ms)
Rows Deleted/s272 million

Fixed Overhead Per Table ​

There is a fixed cost for replicating a single table regardless of the replication operations performed. To demonstrate this, create a database with 1,000 tables each containing a single row, and then start a replication process:

sql

-- Connected to the DB to be replicated. The simple SQL block simulated 1000 tables 
-- that need to be replicated as they contain data.

DO $$
DECLARE
  i INT;
BEGIN
  FOR i IN 1..1000 LOOP
    EXECUTE format('CREATE TABLE public.t%s (x INTEGER);', i);
    EXECUTE format('INSERT INTO public.t%s VALUES (1);', i);
  END LOOP;
END;
$$;

alter database "repl_overhead_per_table" add replica "repl_overhead_per_table_repl" to "remote_server" with (frequency 60, alias repl_overhead_per_table_copy);
alter database "repl_overhead_per_table" alter replica "repl_overhead_per_table_repl" start;

The results at the end of the single replication cycle were:

MetricValue
Total bytes sent243,000
Elapsed time4 minutes 26.39 seconds
Average overhead/table≈266 ms

The implication of this ≈266 ms per table replicated overhead is that high table counts with frequent small updates may degrade overall replication performance. This overhead imposes a fundamental limit on how many tables can be replicated over a given period of time:

Time Budget (seconds)Maximum Number of Replicated Tables
518
60225
300 (5 minutes)1,126
1,800 (30 minutes)6,757

The recommendation here is to group operations when possible. Use temporary tables that are automatically excluded from replication for staging operations. Use replication’s exclude schema option to exclude entire schemas that are constantly modified that shouldn’t be replicated.

Formula to Determine Table Count and Replicated Data Limits ​

Given the fixed per-table replication overhead and the throughput rates measured, use the following constraint to help plan your replication setup:

Constraint: (maxTables × 0.266) + (maxData / 0.981) ≤ 3600

Where:

  • maxTables — number of tables replicated
  • maxData — size of data replicated (in GB)
  • 0.266 s — fixed cost per table
  • 0.981 GB/s — effective data replication rate

This formula leads to the following example constraints:

Time Budget (seconds)Max TablesMax Data (GB)
300 (5 min)1297.3
3001,00035.3
300500164.5
600 (10 min)2,00070.7
1,800 (30 min)6,000885.5
3,600 (1 hour)5,8002,000