postgresSQL資料庫主備流複制性能測試

資料庫主備搭建步驟

修改postgresql.conf配置

# postgresql.conf
max_connections = 500
shared_buffers = 2GB
temp_buffers = 64MB
work_mem = 64MB
max_stack_depth = 8MB
dynamic_shared_memory_type = posix
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 50MB
log_min_duration_statement = 0
log_connections = on
log_disconnections = on
log_line_prefix = '<%m> '
log_timezone = 'PRC'
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
port = 5432
fsync = off
max_wal_size = 4GB
checkpoint_timeout = 20min   
synchronous_commit = off
listen_addresses = '*'
wal_level = replica
max_wal_senders = 5  # 設定了可以最多有幾個流複制的連結
wal_keep_segments = 1024 #保留1024個WAL檔案，每個wal檔案16M，總共大小16M*1024=16G
wal_log_hints = on
hot_standby = on

修改pg_hba.conf配置

# psql_hba.con
# TYPE    DATABASE    USER    ADDRESS    METHOD
local        all       all                 trust
host         replication postgres 0.0.0.0/0 trust
host         all       all    127.0.0.1/32 trust
host         replication postgres ::1/128 trust
host         all       all    0.0.0.0/0      trust
host         replication postgres all md5
host         all       all    all          md5

重新開機主資料節點服務

su - postgres -c "/usr/local/appsys/package/pgsql/bin/pg_ctl -D /usr/local/appdata/data/datazddi/ restart -l /home/postgres/pg.log"

開始基礎備份

# 删除/usr/local/appdata/data/datazddi檔案夾下面資料
rm -fr /usr/local/appdata/data/datazddi/*
sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi

備份完成後，啟動服務

su - postgres -c "/usr/local/appsys/package/pgsql/bin/pg_ctl -D /usr/local/appdata/data/datazddi/ start -l /home/postgres/pg.log"

啟動完成後，再主資料節點上檢視主備流複制狀态

cloudipam=# select * from pg_stat_replication ;
 pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_location | write_location | flush_location | repla
y_location | sync_priority | sync_state 
------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+------
-----------+---------------+------------
 1053 |       10 | postgres | walreceiver      | 10.2.0.238  |                 |       51830 | 2021-03-30 17:42:14.499251+08 |              | streaming | 0/4004888     | 0/4004888      | 0/4004888      | 0/400
4888       |             0 | async

監控指令

#cpu使用率：
vmstat | sed -n '3p'| awk {'print $13, $14,$15,$16'}
#系統負載：
`uptime |  awk {'print $8'} | sed 's/,//g'`
#記憶體使用率：
head -5 /proc/meminfo | awk 'NR==1{MemTotal=$2}NR==2{MemFree=$2}NR==4{Buffers=$2}NR==5{Cached=$2;print (MemTotal-MemFree-Buffers-Cached)/MemTotal}'
#網卡出入口帶寬：
ifstat -i eth0 -n 1 1 | awk 'NR>2 {print $1,$2}'
#磁盤IO占有率：
iostat -d -x -k 1 1 | sed -n '4p'| awk '{print $14}'
# 檢視主備資料庫差異大小(在主資料節點上執行)：
[[email protected] ~]# psql -d cloudipam -U postgres -c 'select client_addr,pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/1024/1024 as MB  from pg_stat_replication;' | awk 'NR==3{ip1=$1;mb1=$3}NR==4{ip2=$1;mb2=$3}NR==5{ip3=$1;mb3=$3}NR==6{ip4=$1;mb4=$3}NR==7{ip5=$1;mb5=$3;print ip1,mb1,ip2,mb2,ip3,mb3,ip4,mb4,ip5,mb5}'
10.2.0.238 0.00000000000000000000 10.2.2.16 0.00000000000000000000 10.2.2.15 0.00000000000000000000 10.2.2.14 0.00000000000000000000 10.2.2.17 0.00000000000000000000
# 檢視主備資料庫時延
SELECT
        CASE
                WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
                ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
        END
AS replication_lag;"

#檢視LSN
sql="
SELECT 
	CASE
		WHEN pg_is_in_recovery()
		THEN 
			coalesce(pg_last_xlog_receive_location(), pg_last_xlog_replay_location())
		ELSE
			pg_current_xlog_flush_location()
	END;"
#AS replication_lag;"
echo -e "`psql -c "$sql" -d cloudipam -U postgres`"

建立主備流複制時間測試

測試拓撲

postgresSQL資料庫主備流複制性能測試

測試步驟

在資料上執行監控腳本

echo "cs us sy id load mem_use_rate in out %util"
while true
do
	echo -e "`vmstat | sed -n '3p'| awk {'print $13, $14,$15,$16'}` \c";echo -e "`uptime |  awk {'print $8'} | sed 's/,//g'` \c";echo -e "`head -5 /proc/meminfo | awk 'NR==1{MemTotal=$2}NR==2{MemFree=$2}NR==4{Buffers=$2}NR==5{Cached=$2;print (MemTotal-MemFree-Buffers-Cached)/MemTotal}'` \c"; echo -e "`ifstat -i eth0 -n 1 1 | awk 'NR>2 {print $1,$2}'` \c";echo  -e "`iostat -d -x -k 1 1 | sed -n '4p'| awk '{print $12}'` "
	sleep 1
done

删除備資料節點/usr/local/appdata/data/datazddi目錄下面的檔案，在備機上執行

rm -rf /usr/local/appdata/data/datazddi/*
time sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi/

配置主備資料庫網絡時延

tc qdisc add dev eth0 root netem delay 100ms

網絡帶寬測試

使用iperf3工具對主備流複制的網絡進行帶寬測試

時延	帶寬	最大傳輸速率
98.0 Mbits/sec	11.3 MBytes
30ms	16.4 Mbits/sec	1.96 MBytes
60ms	8.22 Mbits/sec	1.01 MBytes

正常區域網路：
PING 10.2.0.238 (10.2.0.238) 56(84) bytes of data.
64 bytes from 10.2.0.238: icmp_seq=1 ttl=64 time=2.02 ms
64 bytes from 10.2.0.238: icmp_seq=2 ttl=64 time=2.03 ms
[[email protected] opt]# iperf3 -c 10.2.0.238 -t 60
Connecting to host 10.2.0.238, port 5201
[  4] local 10.2.0.246 port 37072 connected to 10.2.0.238 port 5201
[ ID] Interval           Transfer     Bandwidth       Retr  Cwnd
[  4]   0.00-1.00   sec  11.7 MBytes  98.0 Mbits/sec    0   65.6 KBytes       
[  4]   1.00-2.00   sec  11.3 MBytes  94.8 Mbits/sec    0   65.6 KBytes       
[  4]   2.00-3.00   sec  11.3 MBytes  94.8 Mbits/sec    0   65.6 KBytes       
[  4]   3.00-4.00   sec  11.3 MBytes  94.8 Mbits/sec    0   65.6 KBytes       
[  4]   4.00-5.00   sec  11.3 MBytes  95.1 Mbits/sec    0   65.6 KBytes       
[  4]   5.00-6.00   sec  11.3 MBytes  94.8 Mbits/sec    0   65.6 KBytes       
[  4]   6.00-7.00   sec  11.3 MBytes  94.8 Mbits/sec    0   65.6 KBytes       
[  4]   7.00-8.00   sec  11.3 MBytes  94.8 Mbits/sec    0   65.6 KBytes

主備網卡配置時延30ms
[[email protected] opt]# ping 10.2.0.238
PING 10.2.0.238 (10.2.0.238) 56(84) bytes of data.
64 bytes from 10.2.0.238: icmp_seq=1 ttl=64 time=32.0 ms
64 bytes from 10.2.0.238: icmp_seq=2 ttl=64 time=32.0 ms
64 bytes from 10.2.0.238: icmp_seq=3 ttl=64 time=32.0 ms
64 bytes from 10.2.0.238: icmp_seq=4 ttl=64 time=32.0 ms
Connecting to host 10.2.0.238, port 5201
[  4] local 10.2.0.246 port 38952 connected to 10.2.0.238 port 5201
[ ID] Interval           Transfer     Bandwidth       Retr  Cwnd
[  4]   0.00-1.00   sec  2.57 MBytes  21.5 Mbits/sec    0    134 KBytes       
[  4]   1.00-2.00   sec  1.93 MBytes  16.2 Mbits/sec    0    134 KBytes       
[  4]   2.00-3.00   sec  1.96 MBytes  16.4 Mbits/sec    0    134 KBytes       
[  4]   3.00-4.00   sec  1.90 MBytes  15.9 Mbits/sec    0    134 KBytes       
[  4]   4.00-5.00   sec  1.96 MBytes  16.4 Mbits/sec    0    134 KBytes       
[  4]   5.00-6.00   sec  1.93 MBytes  16.2 Mbits/sec    0    134 KBytes       
[  4]   6.00-7.00   sec  1.96 MBytes  16.4 Mbits/sec    0    134 KBytes       
[  4]   7.00-8.00   sec  1.93 MBytes  16.2 Mbits/sec    0    134 KBytes       
[  4]   8.00-9.00   sec  1.96 MBytes  16.4 Mbits/sec    0    134 KBytes       
[  4]   9.00-10.00  sec  1.93 MBytes  16.2 Mbits/sec    0    134 KBytes       
[  4]  10.00-11.00  sec  1.96 MBytes  16.4 Mbits/sec    0    134 KBytes       
[  4]  11.00-12.00  sec  1.93 MBytes  16.2 Mbits/sec    0    134 KBytes       
[  4]  12.00-13.00  sec  1.96 MBytes  16.4 Mbits/sec    0    134 KBytes       
[  4]  13.00-14.00  sec  1.96 MBytes  16.4 Mbits/sec    0    134 KBytes

主備網卡配置時延60ms
PING 10.2.0.238 (10.2.0.238) 56(84) bytes of data.
64 bytes from 10.2.0.238: icmp_seq=1 ttl=64 time=62.0 ms
64 bytes from 10.2.0.238: icmp_seq=2 ttl=64 time=62.0 ms
64 bytes from 10.2.0.238: icmp_seq=3 ttl=64 time=62.0 ms
Connecting to host 10.2.0.238, port 5201
[  4] local 10.2.0.246 port 51892 connected to 10.2.0.238 port 5201
[ ID] Interval           Transfer     Bandwidth       Retr  Cwnd
[  4]   0.00-1.00   sec  1.60 MBytes  13.4 Mbits/sec    0    135 KBytes       
[  4]   1.00-2.00   sec  1004 KBytes  8.22 Mbits/sec    0    135 KBytes       
[  4]   2.00-3.00   sec  1004 KBytes  8.22 Mbits/sec    0    135 KBytes       
[  4]   3.00-4.00   sec  1.01 MBytes  8.48 Mbits/sec    0    135 KBytes       
[  4]   4.00-5.00   sec  1.01 MBytes  8.48 Mbits/sec    0    135 KBytes       
[  4]   5.00-6.00   sec  1004 KBytes  8.22 Mbits/sec    0    135 KBytes       
[  4]   6.00-7.00   sec  1004 KBytes  8.22 Mbits/sec    0    135 KBytes       
[  4]   7.00-8.00   sec  1.01 MBytes  8.48 Mbits/sec    0    135 KBytes       
[  4]   8.00-9.00   sec  1.01 MBytes  8.48 Mbits/sec    0    135 KBytes       
[  4]   9.00-10.00  sec  1004 KBytes  8.22 Mbits/sec    0    135 KBytes       
[  4]  10.00-11.00  sec  1004 KBytes  8.22 Mbits/sec    0    135 KBytes       
[  4]  11.00-12.00  sec  1.01 MBytes  8.48 Mbits/sec    0    135 KBytes       
[  4]  12.00-13.00  sec  1.01 MBytes  8.48 Mbits/sec    0    135 KBytes       
[  4]  13.00-14.00  sec  1004 KBytes  8.22 Mbits/sec    0    135 KBytes

測試結果

1000萬資料情況下統計

1、主備同步完成時間

時延	時間	占用帶寬
1ms	8m18.970s	12MB/s
30ms	26m40.911s	2MB/s
60ms	48m49.004s	1MB/s

2、執行基礎備份占用帶寬

postgresSQL資料庫主備流複制性能測試

從圖中可以看出，網卡出口速率在12000KB/s。目前測試環境是百兆的網口，理論線速在100Mbps/8 = 12.5MB/s，基本接近理論速率，占用帶寬很大。

主備同步完成時間分析，目前1000萬的資料量情況下，datazddi同步資料的大小在2.4G，主備同步完成時間在：

備注：pg_basebackup不會同步pg_xlog目錄

waiting for checkpoint的時間在4-6分鐘，不同時延下，傳輸時間：
2.5*1024*1024KB/11000KB=230秒 大概時間4分鐘  
2.5*1024*1024KB/2000KB=1310秒 大該時間22分鐘
2.5*1024*1024KB/1000KB=2620秒 大該時間44分鐘

postgresSQL資料庫主備流複制性能測試

同步完成後檔案大小在2.5G：

postgresSQL資料庫主備流複制性能測試

500萬資料情況下統計

主備同步完成時間

時延	時間	占用帶寬
1ms	1m50.774s	12MB/s
30ms	14m17.377s	2MB/s
60ms	21m3.268s	1MB/s

# 資料量
cloudipam=# select count(*) from addressv4info ;
  count  
---------
 5000000
(1 row)

#1ms時延資料
[[email protected] datazddi]# time sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi/
1278998/1278998 kB (100%), 1/1 tablespace

real	1m50.774s
user	0m0.928s
sys	0m5.364s

# 30ms時延
[[email protected] datazddi]# time sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi/
1672239/1672239 kB (100%), 1/1 tablespace

real	14m17.377s
user	0m2.768s
sys	0m13.705s

# 60ms時延
[[email protected] datazddi]# time sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi/
1279134/1279134 kB (100%), 1/1 tablespace

real	21m3.268s
user	0m2.213s
sys	0m10.901s

同步完成後檔案大小在1.6G：

postgresSQL資料庫主備流複制性能測試

100萬資料情況下統計

主備同步完成時間

時延	時間	占用帶寬
1ms	0m29.969s	12MB/s
30ms	2m57.029s	2MB/s
60ms	5m40.394s	1MB/s

# 資料量
cloudipam=# select count(*) from addressv4info ;
  count  
---------
 1000000
(1 row)

#1ms時延資料
[[email protected] datazddi]# time sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi/
344138/344138 kB (100%), 1/1 tablespace

real	0m29.969s
user	0m0.274s
sys	0m1.536s

# 30ms時延
[[email protected] datazddi]# time sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi/
344139/344139 kB (100%), 1/1 tablespace

real	2m57.029s
user	0m0.543s
sys	0m2.976s

# 60ms時延
[[email protected] datazddi]# time sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi/
344139/344139 kB (100%), 1/1 tablespace

real	5m40.394s
user	0m0.632s
sys	0m3.012s

同步完成後檔案大小在336M

資料庫為空情況下統計

主備同步完成時間

時延	時間	占用帶寬
1ms	0m11.617s	12MB/s
30ms	0m56.588s	2MB/s
60ms	1m49.017s	1MB/s

# 資料量
cloudipam=# select count(*) from addressv4info ;
 count 
-------
     0
(1 row)

#1ms時延資料
[[email protected] datazddi]# time sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi/
109726/109726 kB (100%), 1/1 tablespace

real	0m11.617s
user	0m0.095s
sys	0m0.530s

# 30ms時延
[[email protected] datazddi]# time sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi/
109727/109727 kB (100%), 1/1 tablespace

real	0m56.588s
user	0m0.185s
sys	0m0.990s

# 60ms時延
[[email protected] datazddi]# time sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi/
109727/109727 kB (100%), 1/1 tablespace

real	1m49.017s
user	0m0.236s
sys	0m1.004s

同步完成後檔案大小在107M

資料庫主備時延測試

測試拓撲

postgresSQL資料庫主備流複制性能測試

環境搭建

分别在備資料節點執行：

# 删除資料
rm -rf /usr/local/appdata/data/datazddi/*
# 開始基礎備份
time sudo -u postgres /usr/local/appsys/package/pgsql/bin/pg_basebackup -h 10.2.0.246 -p 5432 -U postgres -F p -x -P -R -D /usr/local/appdata/data/datazddi/
# 啟動服務
su - postgres -c "/usr/local/appsys/package/pgsql/bin/pg_ctl -D /usr/local/appdata/data/datazddi/ start -l /home/postgres/pg.log"

主備資料關系建立完成後，在主資料節點上檢視主備流複制關系：

cloudipam=# select application_name, client_addr,  backend_start, state  from pg_stat_replication ;
 application_name | client_addr |         backend_start         |   state   
------------------+-------------+-------------------------------+-----------
 walreceiver      | 10.2.0.238  | 2021-04-02 09:28:15.471481+08 | streaming
 walreceiver      | 10.2.2.16   | 2021-04-02 10:59:45.880786+08 | streaming
 walreceiver      | 10.2.2.15   | 2021-04-02 10:59:45.911572+08 | streaming
 walreceiver      | 10.2.2.14   | 2021-04-02 11:00:18.424841+08 | streaming
 walreceiver      | 10.2.2.17   | 2021-04-02 11:06:06.860291+08 | streaming
(5 rows)

測試步驟

在主資料節點（10.2.0.246）上執行如下sql腳本：

-- test.sql
create or replace function insert_data() returns void as $$
INSERT INTO addressv4info AS t (ip_addr, type, u_type, u_ip_resource_id, u_lease_state, u_hardware_ethernet, u_available, o_last_online_time, o_available, calc_flag) SELECT TRUNC(random()*5000000000) AS ip_addr, 'manual' AS type, 'manual' AS u_type, '130.0.0.43-130.0.0.43' AS u_ip_resource_id, 'free' AS u_lease_state, '00:00:00:00:00:01' AS u_hardware_ethernet, 1 AS u_available,  '2021-04-01 10:53:49' AS o_last_online_time,  1 AS o_available,  0 AS calc_flag  ON CONFLICT(ip_addr) DO UPDATE SET (u_type,u_hardware_ethernet,u_ip_resource_id, u_lease_state, u_available, o_last_online_time, o_available, calc_flag)= ('manual', '00:00:00:00:00:01', '130.0.0.43-130.0.0.43', 'free', 1, '2021-04-01 10:53:49', 1, 0);
UPDATE addressv4info SET u_hardware_ethernet = '00:00:00:00:00:02' where ip_addr=3221225473;
INSERT into macinfo ("MAC", login_time, expire_time) VALUES (substring(md5(random()::text),1,12), now(), now()) ON CONFLICT("MAC") DO NOTHING;;
$$ language sql strict;

psql -d cloudipam -U postgres -f test.sql

在壓力機(10.2.0.153)上建立如下sql檔案：

--test.sql
select insert_data()

在壓力機上執行：

pgbench -M prepared -n -r -d cloudipam -f ./test.sql -h 10.2.0.246 -p 5432 -U postgres  -c 1 -j 1 -T 10

主資料節點（10.2.0.246）上的監控腳本

# test.sh
while true
do
	echo -e "`vmstat | sed -n '3p'| awk {'print $13, $14,$15,$16'}` \c";echo -e "`uptime |  awk {'print $10'} | sed 's/,//g'` \c";echo -e "`head -5 /proc/meminfo | awk 'NR==1{MemTotal=$2}NR==2{MemFree=$2}NR==4{Buffers=$2}NR==5{Cached=$2;print (MemTotal-MemFree-Buffers-Cached)/MemTotal}'` \c"; echo -e "`ifstat -i eth0 -n 1 1 | awk 'NR>2 {print $1,$2}'` \c";echo  -e "`iostat -d -x -k 1 1 | sed -n '4p'| awk '{print $14}'` \c";echo -e "`psql -d cloudipam -U postgres -c 'select client_addr,pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/1024/1024 as MB  from pg_stat_replication;' | awk 'NR==3{ip1=$1;mb1=$3}NR==4{ip2=$1;mb2=$3}NR==5{ip3=$1;mb3=$3}NR==6{ip4=$1;mb4=$3}NR==7{ip5=$1;mb5=$3;print ip1,mb1,ip2,mb2,ip3,mb3,ip4,mb4,ip5,mb5}'`"
	sleep 3
done

備資料節點監控腳本

#get_time_relay.sh
i=0
sql="
SELECT
        CASE
                WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
                ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
        END
AS replication_lag;"

while true
do
        echo -e "`psql -t -A -c "$sql" -d cloudipam -U postgres`" | \c";echo -e "`ifstat -i eth0 -n 1 1 | awk 'NR>2 {print $1,$2}'`"
	sleep 3
done

測試結果

通過pgbench打壓統計結果

transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
duration: 300 s
number of transactions actually processed: 481246
latency average = 0.623 ms
tps = 1604.152649 (including connections establishing)
tps = 1604.161146 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.614  select insert_data()

1ms時延下，不同數量統計統計資料

主備資料節點各個網卡的速率統計

postgresSQL資料庫主備流複制性能測試

postgresSQL資料庫主備流複制性能測試
主備資料節點資料庫時延

postgresSQL資料庫主備流複制性能測試

postgresSQL資料庫主備流複制性能測試

postgresSQL資料庫主備流複制性能測試

postgresSQL資料庫主備流複制性能測試
主備資料節點資料庫差異

postgresSQL資料庫主備流複制性能測試

postgresSQL資料庫主備流複制性能測試

postgresSQL資料庫主備流複制性能測試

postgresSQL資料庫主備流複制性能測試

資料庫主備切換

主備資料庫切換流程

postgresSQL資料庫主備流複制性能測試

備機提升為主機

#将備機提升為主機
su - postgres -c "/usr/local/appsys/package/pgsql/bin/pg_ctl -D /usr/local/appdata/data/datazddi/ promote"

主備切換後，其他節點同步更新主資料節點腳本

if [ $# -ne 2 ]
    then
        echo "請輸入主資料節點和自身節點的IP位址 ip1 ip2"
        exit 1
fi


modify_recovery(){
    if [ -e /root/recovery.done ]
    then
    mv /usr/local/appdata/data/datazddi/recovery.done /usr/local/appdata/data/datazddi/recovery.conf
    fi
    echo "standby_mode = 'on'" > /usr/local/appdata/data/datazddi/recovery.conf
    echo "primary_conninfo = 'user=postgres [email protected] host="$1 "port=5432 sslmode=disable sslcompression=1'" >> /usr/local/appdata/data/datazddi/recovery.conf
    echo "recovery_target_timeline  = 'latest'" >> /usr/local/appdata/data/datazddi/recovery.conf
    chown -R postgres:postgres /usr/local/appdata/data/datazddi/recovery.conf
    # 啟動資料庫
    su - postgres -c "/usr/local/appsys/package/pgsql/bin/pg_ctl -D /usr/local/appdata/data/datazddi/ start -l /home/postgres/pg.log"
}


#在執行pg_rewind之前先checkpoint,将某個時間點之前的髒資料全部重新整理到磁盤
/usr/local/appsys/package/pgsql/bin/psql -U postgres -c "checkpoint;"

#停止postgres服務
su - postgres -c "/usr/local/appsys/package/pgsql/bin/pg_ctl -D /usr/local/appdata/data/datazddi/ -l /home/postgres/pg_lease.log stop -w >/dev/null 2>&1"
echo "資料庫服務已停止，請先将備機提升為主機，執行如下指令："
echo "su - postgres -c '/usr/local/appsys/package/pgsql/bin/pg_ctl -D /usr/local/appdata/data/datazddi/ promote'"
sleep 30
echo "檢查資料庫服務是否存在?"
#檢查資料庫服務是否存在
su - postgres -c "/usr/local/appsys/package/pgsql/bin/pg_ctl -D /usr/local/appdata/data/datazddi/ -l /home/postgres/pg.log status" 2>&1|grep "no server running"

if [ $? -ne 0 ]
    then
    echo "資料庫狀态檢測失敗"
    exit 1
fi

echo "修改recovery配置檔案"

modify_recovery $1

count=10
while [[ $count -ge 0 ]]
    do
        psql -d cloudipam -U postgres -h $1 -t -c "select state from pg_stat_replication where client_addr='$2';"|grep streaming
        if [[ $? -eq 0 ]]
            then
                echo "主備資料庫切換成功，流複制建立完成"
                exit 1
        else
            sleep 5
            count=`expr $count - 1`
        fi

    done

#停止postgres服務
su - postgres -c "/usr/local/appsys/package/pgsql/bin/pg_ctl -D /usr/local/appdata/data/datazddi/ -l /home/postgres/pg_lease.log stop -w >/dev/null 2>&1"

echo "pg_rewind将主機降為備機，同步主資料節點時間線"
#pg_rewind将主機降為備機，同步主資料節點時間線
su - postgres -c "/usr/local/appsys/package/pgsql/bin/pg_rewind --target-pgdata /usr/local/appdata/data/datazddi/ --source-server='host=$1 port=5432  user=postgres dbname=cloudipam' -P --debug"

echo "修改recovery配置檔案"
modify_recovery $1

存在問題
- pg_rewind執行得時間可能會比較長

測試拓撲

postgresSQL資料庫主備流複制性能測試

模拟主備資料切換

模拟主資料節點10.2.0.246和10.2.0.238節點的主備資料切換

待完成.。。。。

postgresSQL資料庫主備流複制性能測試

資料庫主備搭建步驟

監控指令

建立主備流複制時間測試

測試拓撲

測試步驟

網絡帶寬測試

測試結果

1000萬資料情況下統計

500萬資料情況下統計

100萬資料情況下統計

資料庫為空情況下統計

資料庫主備時延測試

測試拓撲

環境搭建

測試步驟

測試結果

資料庫主備切換

主備資料庫切換流程

測試拓撲

模拟主備資料切換

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述