几天前测试过一个PostgreSQL 的fail-back补丁, 发现fail-back未成功,
这个bug实际上是另一个补丁引起的.
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=91c3613d3748d881706c3e60d8221ea92833ac1a
Fix assertion failure by an immediate shutdown.
In PM_WAIT_DEAD_END state, checkpointer process must be dead already.
But an immediate shutdown could make postmaster's state machine
transition to PM_WAIT_DEAD_END state even if checkpointer process is
still running, and which caused assertion failure. This bug was introduced
in commit 457d6cf049c57cabe9b46ea13f26138040a214ec.
This patch ensures that postmaster's state machine doesn't transition to
PM_WAIT_DEAD_END state in an immediate shutdown while checkpointer
process is running.
因为切换时用到了stop -m immediate.
fail-back的这个补丁实际上还是有效的. 因为它解决了以下问题 :
使用这个src snapshot.
本文将分几个常见重新测试 :
>You mean to make the master wait the data page write until WAL has been not only
>flushed to disk but also replicated to the standby?
Yes. Master should not write the data page before corresponding WAL
records have been replicated to the standby. The WAL records have been
flushed to disk on both master and standby.使用这个src snapshot.
[root@db-172-16-3-39 ]# tar -zxvf postgresql-91c3613.tar.gz
[root@db-172-16-3-39 ]# cd postgresql-91c3613
[root@db-172-16-3-39 postgresql-91c3613]# patch -p1 < ../postgresql-e5592c6/failback_safe_standby_v4.patch
patching file src/backend/access/transam/clog.c
patching file src/backend/access/transam/slru.c
patching file src/backend/access/transam/twophase.c
patching file src/backend/access/transam/xact.c
patching file src/backend/access/transam/xlog.c
Hunk #1 succeeded at 1209 (offset -15 lines).
Hunk #2 succeeded at 2991 (offset -5 lines).
Hunk #3 succeeded at 3119 (offset -15 lines).
Hunk #4 succeeded at 8228 (offset -4 lines).
Hunk #5 succeeded at 8379 (offset -15 lines).
patching file src/backend/catalog/storage.c
patching file src/backend/replication/syncrep.c
patching file src/backend/storage/buffer/bufmgr.c
patching file src/backend/utils/cache/relmapper.c
Hunk #1 succeeded at 774 (offset 53 lines).
patching file src/backend/utils/misc/guc.c
Hunk #2 succeeded at 3302 (offset 13 lines).
patching file src/backend/utils/misc/postgresql.conf.sample
patching file src/backend/utils/time/tqual.c
Hunk #1 succeeded at 60 (offset -2 lines).
Hunk #2 succeeded at 118 (offset -1 lines).
patching file src/include/access/xlog.h
patching file src/include/replication/syncrep.h
[root@db-172-16-3-39 postgresql-91c3613]# ./configure --prefix=/home/pg94/pgsql9.4devel --with-pgport=2999 --with-perl --with-tcl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 && gmake && gmake install
[root@db-172-16-3-39 postgresql-91c3613]# cd contrib/
[root@db-172-16-3-39 postgresql-91c3613]# gmake && gmake install
pg94@db-172-16-3-39-> initdb -D $PGDATA -E UTF8 --locale=C -W -U postgres -k本文将分几个常见重新测试 :
一.
二.
三.
四.
五.
[小结]
[参考]
synchronous_commit = remote_write
synchronous_transfer = all
fail-back成功.
使用这种配置, 在master节点stop -m immediate后, standby promote, 最后成功的完成了master节点的fail-back.
pg94@db-172-16-3-33-> pgbench -M prepared -f ./test.sql -r -n -h $PGDATA -p 2999 -U postgres -c 16 -j 4 -T 10 --progress=1 digoal
progress: 1.0 s, 4996.2 tps, 3.202 ms lat
progress: 2.0 s, 13594.8 tps, 1.177 ms lat
progress: 3.0 s, 8177.5 tps, 1.957 ms lat
progress: 4.0 s, 16563.1 tps, 0.966 ms lat
progress: 5.0 s, 9317.0 tps, 1.717 ms lat
progress: 6.0 s, 2228.6 tps, 7.179 ms lat
progress: 7.0 s, 2128.4 tps, 7.517 ms lat
progress: 8.0 s, 14069.0 tps, 1.137 ms lat
progress: 9.0 s, 13270.4 tps, 1.206 ms lat
progress: 10.0 s, 2715.9 tps, 5.891 ms lat
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 10 s
number of transactions actually processed: 87190
tps = 8708.791555 (including connections establishing)
tps = 8728.552746 (excluding connections establishing)二.
synchronous_commit = off
synchronous_transfer = all
使用这种配置, fail-back失败.
原因在于synchronous_commit = off, xlog flush前不需要等待远程节点接收到xlog信息的反馈.
pg94@db-172-16-3-39-> pgbench -M prepared -f ./test.sql -r -n -h $PGDATA -p 2999 -U postgres -c 16 -j 4 -T 10 --progress=1 digoal
progress: 1.0 s, 30353.8 tps, 0.527 ms lat
progress: 2.0 s, 40421.5 tps, 0.396 ms lat
progress: 3.0 s, 43310.1 tps, 0.369 ms lat
progress: 4.0 s, 43606.8 tps, 0.367 ms lat
progress: 5.0 s, 44466.9 tps, 0.360 ms lat
progress: 6.0 s, 44329.6 tps, 0.361 ms lat
progress: 7.0 s, 45618.9 tps, 0.351 ms lat
progress: 8.0 s, 44800.0 tps, 0.357 ms lat
progress: 9.0 s, 44561.3 tps, 0.359 ms lat
progress: 10.0 s, 45660.3 tps, 0.350 ms lat
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 10 s
number of transactions actually processed: 427175
tps = 42693.877478 (including connections establishing)
tps = 42769.866355 (excluding connections establishing)
statement latencies in milliseconds:
0.371739 select func();三.
synchronous_commit = remote_write
synchronous_transfer = data_flush
使用这种配置, fail-back失败.
原因在于synchronous_transfer = data_flush, 因为提交状态不需要等待远程节点的反馈.
pg94@db-172-16-3-39-> pgbench -M prepared -f ./test.sql -r -n -h $PGDATA -p 2999 -U postgres -c 16 -j 4 -T 10 --progress=1 digoal
progress: 1.0 s, 11883.3 tps, 1.346 ms lat
progress: 2.0 s, 14745.8 tps, 1.085 ms lat
progress: 3.0 s, 18044.3 tps, 0.887 ms lat
progress: 4.0 s, 17891.5 tps, 0.894 ms lat
progress: 5.0 s, 19712.9 tps, 0.812 ms lat
progress: 6.0 s, 21122.7 tps, 0.757 ms lat
progress: 7.0 s, 20677.3 tps, 0.774 ms lat
progress: 8.0 s, 20585.9 tps, 0.777 ms lat
progress: 9.0 s, 22592.0 tps, 0.708 ms lat
progress: 10.0 s, 22884.6 tps, 0.699 ms lat
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 10 s
number of transactions actually processed: 190163
tps = 19003.894258 (including connections establishing)
tps = 19036.122314 (excluding connections establishing)
statement latencies in milliseconds:
0.837460 select func();四.
synchronous_commit = remote_write
synchronous_standby_names = '*'
synchronous_transfer = data_flush
synchronous_standby_names节点fail-back成功.
使用这种配置, 在master节点stop -m immediate后, standby promote, 最后成功的完成了master节点的fail-back.
因为synchronous_standby_names节点commit信息的xlog信息需要等待standby节点的反馈, 同时又配置了synchronous_transfer = data_flush. 所以可以fail-back.
pg94@db-172-16-3-33-> pgbench -M prepared -f ./test.sql -r -n -h $PGDATA -p 2999 -U postgres -c 16 -j 4 -T 10 --progress=1 digoal
progress: 1.1 s, 4081.8 tps, 3.920 ms lat
progress: 2.0 s, 6884.0 tps, 2.324 ms lat
progress: 3.0 s, 6905.0 tps, 2.317 ms lat
progress: 4.0 s, 8633.6 tps, 1.853 ms lat
progress: 5.0 s, 9102.9 tps, 1.758 ms lat
progress: 6.0 s, 11641.0 tps, 1.374 ms lat
progress: 7.0 s, 9036.4 tps, 1.771 ms lat
progress: 8.0 s, 11832.6 tps, 1.352 ms lat
progress: 9.0 s, 8994.9 tps, 1.779 ms lat
progress: 10.0 s, 12888.9 tps, 1.241 ms lat
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 10 s
number of transactions actually processed: 89877
tps = 8981.651756 (including connections establishing)
tps = 9001.796363 (excluding connections establishing)
statement latencies in milliseconds:
1.774194 select func();五.
synchronous_commit = remote_write
synchronous_standby_names = '*'
synchronous_transfer = commit
synchronous_standby_names节点fail-back成功.
使用这种配置, 在master节点stop -m immediate后, standby promote, 最后成功的完成了master节点的fail-back.
某些情况可能失败. 就是补丁中提到的
>You mean to make the master wait the data page write until WAL has been not only
>flushed to disk but also replicated to the standby?
pg94@db-172-16-3-39-> pgbench -M prepared -f ./test.sql -r -n -h $PGDATA -p 2999 -U postgres -c 16 -j 4 -T 10 --progress=1 digoal
progress: 1.0 s, 8192.0 tps, 1.953 ms lat
progress: 2.0 s, 11442.8 tps, 1.398 ms lat
progress: 3.0 s, 8907.9 tps, 1.796 ms lat
progress: 4.0 s, 11571.7 tps, 1.383 ms lat
progress: 5.2 s, 9421.1 tps, 1.698 ms lat
progress: 6.0 s, 10646.7 tps, 1.503 ms lat
progress: 7.0 s, 12180.4 tps, 1.314 ms lat
progress: 8.0 s, 10138.0 tps, 1.578 ms lat
progress: 9.1 s, 8649.3 tps, 1.850 ms lat
progress: 10.0 s, 13784.1 tps, 1.161 ms lat
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 10 s
number of transactions actually processed: 104424
tps = 10436.464782 (including connections establishing)
tps = 10453.188998 (excluding connections establishing)
statement latencies in milliseconds:
1.527362 select func();[小结]
1. 为了能够实现fail-back, 主节点的配置至少包含如下级别的配置.
synchronous_commit = remote_write | on
synchronous_transfer = all
但是性能影响就比较大, 因为xlog flush无法使用异步模式.
或者开启同步复制模式.
