mysql 流复制_PostgreSQL数据库Streaming Replication流复制主备延迟测试

PostgreSQL数据库流复制主库和备库之间的延迟时间是多少，无论对HA还是负载均衡来说都应该做个评估。比如单纯的HA架构，当主库发生故障时，我们允许多少时间内的数据丢失。不废话，直接进入本次实验测试。

测试环境：

主库：内存：32G，CPU：8核，IP：192.168.122.101

备库：内存：32G，CPU：8核，IP：192.168.122.102

数据库配置：默认

测试准备：

在两台服务器上安装好PostgreSQL数据库，安装过程不清楚的可以参考文章《PostgreSQL数据库编译安装》，网址：http://www.sijitao.net/1535.html 。

搭建数据库之间的异步流复制环境，配置过程参考文章《PostgreSQL Streaming Replication流复制环境搭建》，网址：http://www.sijitao.net/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$

declare

begin

select userid,engname,cnname,occupation,birthday,signname,email,qq

into o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qq

from 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$

declare

begin

insert 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 then

o_result := 1;

return;

end;

$BODY$

language plpgsql;

4、创建测试脚本

\setrandom userid 1 2000000

SELECT f_user_login(:userid);

5、创建流复制时间延迟测试脚本

在备数据库中创建时间延迟测试的脚本，这里一起监测了备库的负载，网络流量和同步延迟时间，这里我测试了100次。

#!/bin/bash

export PATH=/opt/PostgreSQL/93/bin:$PATH

export PGDATA=/data/pgsql

export PGHOME=/opt/PostgreSQL/93

export PGPORT=5432

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 [ $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+1

done

6、开始测试

在主库使用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/s

0.24 | 28.442567 | 4260.23 KB/s

0.24 | 28.442438 | 4676.84 KB/s

0.3 | 0 | 5151.29 KB/s

0.3 | 28.442349 | 5439.33 KB/s

......

......

测试结果

同步延迟最大时间基本都是在8秒左右，连接并发数增大时延迟次数增加。

带宽使用使用量和连接并发数成正比关系。

系统负载在数据库连接并发数增加时没怎么变化，系统资源使用率不高。

接下来就可以优化或者可以把延迟数据添加进nagios监控了。

原文链接：http://www.sijitao.net/1860.html

参考网址：

http://blog.163.com/digoal@126/blog/static/163877040201221382150858/

https://vibhorkumar.wordpress.com/2014/05/21/monitoring-approach-for-streaming-replication-with-hot-standby-in-postgresql-9-3/