はじめに
PostgreSQL のストリーミングレプリケーションを東京→大阪、東京→サンパウロで構成し、
地理的距離の影響を観測してみます 
東京・大阪間は約400km、東京・サンパウロ間は約18,500km なので、距離の比率は 1:46 です 
環境
環境は GCP を使用し、以下の 4つの VMインスタンスを用意しました。
マシンタイプは e2-medium (2個のvCPU, 4GBメモリ)、OSは RockyLinux 10、PostgreSQL は 17.7 で共通です。
| name | zone | primary/standby |
|---|---|---|
| pos10 | asia-northeast1-a | primary |
| pos11 | asia-northeast2-a | standby |
| pos20 | asia-northeast1-a | primary |
| pos21 | southamerica-east1-b | standby |
実施手順
下記を 1セットとし、3回繰り返します。(いずれも primary で実施)
- pgbench初期化 (スケールファクタ = 100)
- pgbench実行 (2クライアント、180秒)
- pgbench実行中に pg_stat_replication を 1秒おきに SELECTし、結果を repl_monテーブルに INSERT
pg_stat_replication はレプリケーションに関する統計情報を表示する動的統計情報ビューです。
今回は特に write_lag, flush_lag, replay_lag に注目します。
東京→大阪
repl_monテーブルへの INSERT は別ターミナルで実施しています。
結果を先に知りたい方は右の TOC から「測定結果」にジャンプしてください。
1回目
[postgres@pos10 ~]$ pgbench -c 2 -T 180 -r postgres
pgbench (17.7)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 180 s
number of transactions actually processed: 112265
number of failed transactions: 0 (0.000%)
latency average = 3.207 ms
initial connection time = 11.025 ms
tps = 623.727339 (without initial connection time)
statement latencies in milliseconds and failures:
0.002 0 \set aid random(1, 100000 * :scale)
0.001 0 \set bid random(1, 1 * :scale)
0.001 0 \set tid random(1, 10 * :scale)
0.001 0 \set delta random(-5000, 5000)
0.085 0 BEGIN;
0.578 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.249 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.246 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.261 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.202 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.577 0 END;
2回目
[postgres@pos10 ~]$ pgbench -c 2 -T 180 -r postgres
pgbench (17.7)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 180 s
number of transactions actually processed: 134357
number of failed transactions: 0 (0.000%)
latency average = 2.679 ms
initial connection time = 8.600 ms
tps = 746.448815 (without initial connection time)
statement latencies in milliseconds and failures:
0.002 0 \set aid random(1, 100000 * :scale)
0.001 0 \set bid random(1, 1 * :scale)
0.001 0 \set tid random(1, 10 * :scale)
0.001 0 \set delta random(-5000, 5000)
0.083 0 BEGIN;
0.348 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.249 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.235 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.253 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.198 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.306 0 END;
3回目
[postgres@pos10 ~]$ pgbench -c 2 -T 180 -r postgres
pgbench (17.7)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 180 s
number of transactions actually processed: 138570
number of failed transactions: 0 (0.000%)
latency average = 2.599 ms
initial connection time = 8.090 ms
tps = 769.623038 (without initial connection time)
statement latencies in milliseconds and failures:
0.002 0 \set aid random(1, 100000 * :scale)
0.001 0 \set bid random(1, 1 * :scale)
0.001 0 \set tid random(1, 10 * :scale)
0.001 0 \set delta random(-5000, 5000)
0.083 0 BEGIN;
0.293 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.240 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.228 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.250 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.187 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.311 0 END;
[postgres@pos10 ~]$
東京→サンパウロ
1回目
[postgres@pos20 ~]$ pgbench -c 2 -T 180 -r postgres
pgbench (17.7)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 180 s
number of transactions actually processed: 106691
number of failed transactions: 0 (0.000%)
latency average = 3.374 ms
initial connection time = 7.906 ms
tps = 592.749746 (without initial connection time)
statement latencies in milliseconds and failures:
0.002 0 \set aid random(1, 100000 * :scale)
0.001 0 \set bid random(1, 1 * :scale)
0.001 0 \set tid random(1, 10 * :scale)
0.001 0 \set delta random(-5000, 5000)
0.089 0 BEGIN;
0.580 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.262 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.254 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.271 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.210 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.699 0 END;
2回目
[postgres@pos20 ~]$ pgbench -c 2 -T 180 -r postgres
pgbench (17.7)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 180 s
number of transactions actually processed: 106719
number of failed transactions: 0 (0.000%)
latency average = 3.373 ms
initial connection time = 12.637 ms
tps = 592.908199 (without initial connection time)
statement latencies in milliseconds and failures:
0.003 0 \set aid random(1, 100000 * :scale)
0.001 0 \set bid random(1, 1 * :scale)
0.001 0 \set tid random(1, 10 * :scale)
0.001 0 \set delta random(-5000, 5000)
0.094 0 BEGIN;
0.507 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.274 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.263 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.283 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.221 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.722 0 END;
3回目
[postgres@pos20 ~]$ pgbench -c 2 -T 180 -r postgres
pgbench (17.7)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 2
number of threads: 1
maximum number of tries: 1
duration: 180 s
number of transactions actually processed: 112299
number of failed transactions: 0 (0.000%)
latency average = 3.206 ms
initial connection time = 9.732 ms
tps = 623.901839 (without initial connection time)
statement latencies in milliseconds and failures:
0.003 0 \set aid random(1, 100000 * :scale)
0.001 0 \set bid random(1, 1 * :scale)
0.001 0 \set tid random(1, 10 * :scale)
0.001 0 \set delta random(-5000, 5000)
0.091 0 BEGIN;
0.465 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.269 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.258 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.276 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.215 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.622 0 END;
[postgres@pos20 ~]$
測定結果
repl_mon の write_lag, flush_lag, replay_lag をグラフにすると下記のようになりました。
東京→サンパウロで稀に高いスパイクが発生しているのが気になりますが、平均では 約20倍の差となりました。(距離の比率より差は小さい)
| target | write_lag (s) avg | flush_lag (s) avg | replay_lag (s) avg |
|---|---|---|---|
| osaka | 0.011 | 0.013 | 0.015 |
| s.paulo | 0.287 | 0.288 | 0.289 |
repl_mon は Oracle ADB に移行してテーブルハイパーリンクを生成したので、↓からご覧いただけます。(有効期間90日)
テーブルハイパーリンクについてはこちら↓
レプリケーションの目的やデータの内容に応じて、適切なリージョンを選択しましょう 