PostgreSQL数据库Streaming Replication流复制主备延迟测试
发布时间：2024-04-15 点击：126
postgresql数据库流复制主库和备库之间的延迟时间是多少，无论对ha还是负载均衡来说都应该做个评估。比如单纯的ha架构，当主库发生故障时，我们允许多少时间内的数据丢失。不废话，直接进入本次实验测试。
测试环境：主库：内存：32g，cpu：8核，ip：192.168.122.101
备库：内存：32g，cpu：8核，ip：192.168.122.102
数据库配置：默认
测试准备：在两台服务器上安装好postgresql数据库，安装过程不清楚的可以参考文章《postgresql数据库编译安装》，网址：http://www.szl724.com/?p=1535.html 。
搭建数据库之间的异步流复制环境，配置过程参考文章《postgresql streaming replication流复制环境搭建》，网址：http://www.szl724.com/?p=1764.html 。
重要：测试之前一定要同步下主库和备库两台服务器的时间，不然会出现延迟时间不准备的情况。
测试步骤：创建测试数据库和测试表，这里我用的德哥的测试模型，模拟用户登陆操作。
1、创建测试表create table user_info(userid int,engname text,cnname text,occupation text,birthday date,signname text,email text,qq numeric,crt_time timestamp without time zone,mod_time timestamp without time zone);create table user_session(userid int,logintime timestamp(0) without time zone,login_count bigint default 0,logouttime timestamp(0) without time zone,online_interval interval default interval \'0\');create table user_login_rec(userid int,login_time timestamp without time zone,ip inet);create table user_logout_rec(userid int,logout_time timestamp without time zone,ip inet);2、初始化测试数据insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)select generate_series(1,2000000),\'zhangnq\',\'章郎虫\',\'dba\',\'1970-01-01\',e\'我就是章郎虫。\',\'248687950@qq.com\',248687950,clock_timestamp(),null;insert into user_session (userid) select generate_series(1,2000000);alter table user_info add constraint pk_user_info primary key (userid);alter table user_session add constraint pk_user_session primary key (userid);3、创建业务函数-- 模拟用户登录的函数create or replace function f_user_login (i_userid int,out o_userid int,out o_engname text,out o_cnname text,out o_occupation text,out o_birthday date,out o_signname text,out o_email text,out o_qq numeric)as $body$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qqfrom user_info where userid=i_userid;insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());update user_session set logintime=now(),login_count=login_count 1 where userid=i_userid;return;end;$body$language plpgsql;-- 模拟用户退出的函数create or replace function f_user_logout(i_userid int,out o_result int)as $body$declarebegininsert into user_logout_rec (userid,logout_time,ip) values (i_userid,now(),inet_client_addr());update user_session set logouttime=now(),online_interval=online_interval (now()-logintime) where userid=i_userid;o_result := 0;return;exception when others theno_result := 1;return;end;$body$language plpgsql;4、创建测试脚本\\setrandom userid 1 2000000select f_user_login(:userid);5、创建流复制时间延迟测试脚本在备数据库中创建时间延迟测试的脚本，这里一起监测了备库的负载，网络流量和同步延迟时间，这里我测试了100次。
#!/bin/bashexport path=/opt/postgresql/93/bin:$pathexport pgdata=/data/pgsqlexport pghome=/opt/postgresql/93export pgport=5432i=0sql=\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())? endas replication_lag;\while [ $i -lt 100 ]do? echo -e \`/usr/bin/top -b -n 1 |sed -n \'1p\' |awk \'{print $(nf-2)}\'` | \\c\;echo -e `psql -t -a -c \$sql\ -d zhangnq`\ | \\c\;/usr/bin/ifstat -i eth0 -n 1 1 | awk \'nr>2 {print $1 \ kb/s\}\'? let i=$i 1done6、开始测试在主库使用pgbench对数据库施压。
pgbench -m prepared -n -r -f ./test.sql -h 127.0.0.1 -p 5432 -u postgres -c 64 -j 32 -t 300 zhangnq同时在备库上运行流复制延迟测试脚本，记录测试后的数值。
修改pgbench的连接数和线程数后测试多次，得到类似如下的结果。
postgres@ubuntu:~$ ./pglag_time.sh 0.24 | 28.444522 | 3833.48 kb/s0.24 | 28.442567 | 4260.23 kb/s0.24 | 28.442438 | 4676.84 kb/s0.3 | 0 | 5151.29 kb/s0.3 | 28.442349 | 5439.33 kb/s............
测试结果
同步延迟最大时间基本都是在8秒左右，连接并发数增大时延迟次数增加。
带宽使用使用量和连接并发数成正比关系。
系统负载在数据库连接并发数增加时没怎么变化，系统资源使用率不高。
接下来就可以优化或者可以把延迟数据添加进nagios监控了。
参考网址：
http://blog.163.com/digoal@126/blog/static/163877040201221382150858/
monitoring approach for streaming replication with hot standby in postgresql9.3.
山东云服务器配置方案
Hibernate中Validator验证注解有哪些
网址栏出现异常-虚拟主机/数据库问题
华为云服务器怎么解析域名
提供数据库-其他问题
“我的地盘我作主”百度市场重新洗牌
云服务器挂店铺怎么上产品
到期域名如何抢注
测试环境：主库：内存：32g，cpu：8核，ip：192.168.122.101
备库：内存：32g，cpu：8核，ip：192.168.122.102
数据库配置：默认
测试准备：在两台服务器上安装好postgresql数据库，安装过程不清楚的可以参考文章《postgresql数据库编译安装》，网址：http://www.szl724.com/?p=1535.html 。
搭建数据库之间的异步流复制环境，配置过程参考文章《postgresql streaming replication流复制环境搭建》，网址：http://www.szl724.com/?p=1764.html 。
重要：测试之前一定要同步下主库和备库两台服务器的时间，不然会出现延迟时间不准备的情况。
测试步骤：创建测试数据库和测试表，这里我用的德哥的测试模型，模拟用户登陆操作。
1、创建测试表create table user_info(userid int,engname text,cnname text,occupation text,birthday date,signname text,email text,qq numeric,crt_time timestamp without time zone,mod_time timestamp without time zone);create table user_session(userid int,logintime timestamp(0) without time zone,login_count bigint default 0,logouttime timestamp(0) without time zone,online_interval interval default interval \'0\');create table user_login_rec(userid int,login_time timestamp without time zone,ip inet);create table user_logout_rec(userid int,logout_time timestamp without time zone,ip inet);2、初始化测试数据insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)select generate_series(1,2000000),\'zhangnq\',\'章郎虫\',\'dba\',\'1970-01-01\',e\'我就是章郎虫。\',\'248687950@qq.com\',248687950,clock_timestamp(),null;insert into user_session (userid) select generate_series(1,2000000);alter table user_info add constraint pk_user_info primary key (userid);alter table user_session add constraint pk_user_session primary key (userid);3、创建业务函数-- 模拟用户登录的函数create or replace function f_user_login (i_userid int,out o_userid int,out o_engname text,out o_cnname text,out o_occupation text,out o_birthday date,out o_signname text,out o_email text,out o_qq numeric)as $body$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qqfrom user_info where userid=i_userid;insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());update user_session set logintime=now(),login_count=login_count 1 where userid=i_userid;return;end;$body$language plpgsql;-- 模拟用户退出的函数create or replace function f_user_logout(i_userid int,out o_result int)as $body$declarebegininsert into user_logout_rec (userid,logout_time,ip) values (i_userid,now(),inet_client_addr());update user_session set logouttime=now(),online_interval=online_interval (now()-logintime) where userid=i_userid;o_result := 0;return;exception when others theno_result := 1;return;end;$body$language plpgsql;4、创建测试脚本\\setrandom userid 1 2000000select f_user_login(:userid);5、创建流复制时间延迟测试脚本在备数据库中创建时间延迟测试的脚本，这里一起监测了备库的负载，网络流量和同步延迟时间，这里我测试了100次。
#!/bin/bashexport path=/opt/postgresql/93/bin:$pathexport pgdata=/data/pgsqlexport pghome=/opt/postgresql/93export pgport=5432i=0sql=\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())? endas replication_lag;\while [ $i -lt 100 ]do? echo -e \`/usr/bin/top -b -n 1 |sed -n \'1p\' |awk \'{print $(nf-2)}\'` | \\c\;echo -e `psql -t -a -c \$sql\ -d zhangnq`\ | \\c\;/usr/bin/ifstat -i eth0 -n 1 1 | awk \'nr>2 {print $1 \ kb/s\}\'? let i=$i 1done6、开始测试在主库使用pgbench对数据库施压。
pgbench -m prepared -n -r -f ./test.sql -h 127.0.0.1 -p 5432 -u postgres -c 64 -j 32 -t 300 zhangnq同时在备库上运行流复制延迟测试脚本，记录测试后的数值。
修改pgbench的连接数和线程数后测试多次，得到类似如下的结果。
postgres@ubuntu:~$ ./pglag_time.sh 0.24 | 28.444522 | 3833.48 kb/s0.24 | 28.442567 | 4260.23 kb/s0.24 | 28.442438 | 4676.84 kb/s0.3 | 0 | 5151.29 kb/s0.3 | 28.442349 | 5439.33 kb/s............
测试结果
同步延迟最大时间基本都是在8秒左右，连接并发数增大时延迟次数增加。
带宽使用使用量和连接并发数成正比关系。
系统负载在数据库连接并发数增加时没怎么变化，系统资源使用率不高。
接下来就可以优化或者可以把延迟数据添加进nagios监控了。
参考网址：
http://blog.163.com/digoal@126/blog/static/163877040201221382150858/
monitoring approach for streaming replication with hot standby in postgresql9.3.
山东云服务器配置方案
Hibernate中Validator验证注解有哪些
网址栏出现异常-虚拟主机/数据库问题
华为云服务器怎么解析域名
提供数据库-其他问题
“我的地盘我作主”百度市场重新洗牌
云服务器挂店铺怎么上产品
到期域名如何抢注
上一篇：蓝队云服务器网站根目录