Source dataset: 1000 rows, bytea LOB 1–20 MB each, 10 GB logical (uncompressed, STORAGE EXTERNAL). Source/target: Aurora PostgreSQL 17.7, db.r8g.4xlarge. DMS: dms.r5.4xlarge. All in ap-southeast-2a (same AZ). Each test uses a fresh copy-on-write clone as source (cold cache).
| # | Method | Key settings | Full-load start | Full-load finish | Duration (s) | Throughput | Rows | Integrity |
|---|---|---|---|---|---|---|---|---|
| 1 | DMS Full LOB | FullLobMode, LobChunkSize=64KB, single-thread, CommitRate=10000 | 18:44:36.233 | 18:52:51.029 | 494.8 (8m15s) | ~20.7 MB/s | 1000 | 10 GB, no truncation ✔ |
| 2 | DMS Full LOB (tuned) | FullLobMode, LobChunkSize=128KB, 8-way parallel-load ranges, CommitRate=50000 | 19:12:06.722 | 19:14:06.245 | 119.5 (2m00s) | ~85.8 MB/s | 1000 | 10 GB, no truncation ✔ |
| 3 | DMS Limited LOB | LimitedSizeLobMode, LobMaxSize=22 MB, single-thread, CommitRate=10000 | 19:26:45.477 | 19:41:02.111 | 856.6 (14m17s) | ~12.0 MB/s | 1000 | 10 GB, min 1 MB/max 20 MB, no truncation ✔ |
| 4 | Logical replication (native) | PUBLICATION/SUBSCRIPTION, copy_data, single sync worker, COPY protocol | 11:53:22Z | ~11:56:02Z | ~160 (2m40s)¹ | ~64 MB/s | 1000 | 10 GB, min 1 MB/max 20 MB, no truncation ✔ |
| 5 | DMS Limited LOB (tuned) | LimitedSizeLobMode, LobMaxSize=22 MB, 8-way parallel-load ranges, CommitRate=50000 | 20:10:16.346 | 20:15:50.537 | 334.2 (5m34s) | ~30.7 MB/s | 1000 | 10 GB, min 1 MB/max 20 MB, no truncation ✔ |
| 6 | DMS Full LOB (buffer-tuned, NO parallel) | FullLobMode, LobChunkSize=128KB, StreamBufferSizeInMB=64, StreamBufferCount=8, LoadMaxFileSize=100MB, CommitRate=50000, single-thread | 21:38:48.133 | 21:47:06.539 | 498.4 (8m18s) | ~20.5 MB/s | 1000 | 10 GB, no truncation ✔ |
| 7 | DMS Full LOB (buffer-tuned + InlineLobMaxSize=2MB, NO parallel) | as #6 + InlineLobMaxSize=2048KB | 21:59:57.046 | 22:09:05.766 | 548.7 (9m09s) | ~18.6 MB/s | 1000 | 10 GB, min 1 MB/max 20 MB, no truncation ✔ |
¹ Logical replication copy time measured by polling pg_subscription_rel every ~16 s; completion fell in the 152–168 s window (reported midpoint ~160 s).
Scenarios 6 & 7 conclusion (tuning WITHOUT parallel-load)
Compared to the single-threaded baseline (#1, 494.8 s), tuning LobChunkSize (64→128 KB), StreamBufferSizeInMB (8→64), StreamBufferCount (3→8), LoadMaxFileSize (→100 MB, DMS max) and CommitRate (10k→50k) produced no meaningful improvement (498.4 s ≈ baseline). Adding InlineLobMaxSize=2 MB (#7) also did not help (548.7 s, within run-to-run noise) — only ~10 % of rows are ≤2 MB while ~90 % of the 10 GB volume is in larger LOBs that still use the chunked full-LOB path. Conclusion: for large multi-MB LOBs, single-thread throughput is capped (~20 MB/s) regardless of buffer/chunk/CSV/inline tuning; parallel-load is the only lever that materially changes full-load time (#2 vs #1/#6 = 4.1× faster).
Round 3 — large LobChunkSize (2 MB) and large InlineLobMaxSize (all Full LOB mode)
Same dataset/method (fresh cold clone per scenario, ap-southeast-2a, CommitRate 50000, LoadMaxFileSize 100 MB, StreamBufferCount 8).
| # | Method | Key settings | Duration (s) | Throughput | Rows | Integrity |
|---|---|---|---|---|---|---|
| S8 | Full LOB, 2 MB chunk, no parallel | LobChunkSize=2048KB, StreamBufferSizeInMB=64 | 483.7 (8m04s) | ~21.1 MB/s | 1000 | 10 GB ✔ |
| S9 | Full LOB, 2 MB chunk, 8-way parallel | LobChunkSize=2048KB, StreamBufferSizeInMB=128, parallel-load ranges | 93.0 (1m33s) | ~110 MB/s | 1000 | 10 GB ✔ |
| S10 | Full LOB, InlineLobMaxSize=10 MB, no parallel | LobChunkSize=128KB, StreamBufferSizeInMB=256 | 497.5 (8m18s) | ~20.5 MB/s | 1000 | 10 GB, min 1/max 20 MB ✔ |
| S11 | Full LOB, InlineLobMaxSize=20 MB (inlines all rows), no parallel | LobChunkSize=128KB, StreamBufferSizeInMB=512 | 618.4 (10m18s) | ~16.5 MB/s | 1000 | 10 GB, min 1/max 20 MB ✔ |
Round 3 conclusions
- Large chunk size only helps WITH parallelism. 2 MB chunk single-threaded (S8, 483.7 s) ≈ baseline/128 KB (494.8 s). But 2 MB chunk + 8-way parallel (S9, 93.0 s) beat the 128 KB + parallel run (S2, 119.5 s) by ~22% and is the fastest result across all rounds. Fewer, larger per-LOB transfers reduce overhead once multiple workers saturate the pipe.
- InlineLobMaxSize did not help single-threaded for this size distribution. Inline 10 MB (S10) ≈ baseline; inline 20 MB / all-inline (S11) was slower (618 s) — pushing full multi-MB LOBs through the inline CSV stream (with large stream buffers) adds overhead that only pays off if the data skews small or is parallelized.
- Reinforces the overall theme: parallel-load is the primary lever; chunk size is a useful secondary optimization on top of parallelism. Best config found: Full LOB + parallel-load ranges + 2 MB LobChunkSize (S9, 93 s, ~110 MB/s).
No comments:
Post a Comment