PostgreSQL database streaming replication stream replication main standby delay test

What is the latency between the PostgreSQL database stream replication master and standby and should be evaluated for both HA and load balancing. A simple HA architecture, for example, is how much time we allow for data loss in the event of a failure of the main library. No nonsense, go directly into this experiment test.

Test environment:

Main Library: Memory: 32g,cpu:8 Core, ip:192.168.122.101

Standby: Memory: 32g,cpu:8 Core, ip:192.168.122.102

Database configuration: Default

Test Preparation:

Install the PostgreSQL database on both servers, the installation process is not clear can refer to the article "PostgreSQL Database compilation and Installation", Website: http://www.sijitao.net/1535.html.

Build asynchronous stream replication environment between databases, configuration process reference article "PostgreSQL Streaming replication Stream replication Environment Building", Website: http://www.sijitao.net/1764.html.

Important: Be sure to synchronize the time between the main library and the standby two servers before testing, otherwise the delay time will not be prepared.

Test steps:

Create test database and test table, here I use the German test model, simulation user login operation.

1. Create a test table

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. Initialize test data

insert into user_info (userid, engname, cnname, occupation, birthday, signname, email, qq, crt_time, mod_time)
select generate_series (1,2000000),
‘Zhangnq’,
‘Zhang Langworm’,
‘DBA’,
‘1970-01-01’
, E’I’m Zhang Langworm. ‘,
‘[Email protected]’,
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 Business functions

-Functions that simulate user login
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;
-Functions that simulate user exit
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. Create a test script

\setrandom userid 1 2000000
SELECT f_user_login(:userid);

5. Create a stream replication time delay test script

Create a script for the time delay test in the standby database, where we monitor the load of the standby, the network traffic, and the synchronization delay time, which I tested 100 times.

#!/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. Start testing

Use Pgbench to press the database in the main library.

Pgbench-m prepared-n-r-f/test.sql-h 127.0.0.1-p 5432-u postgres-c 64-j 32-t

Run the Stream replication delay test script on the standby and record the values after the test.

After modifying the number of connections and the number of threads in the Pgbench, test several times to get a result similar to the following.

[email protected]:~$ ./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
......
......

Test results

The maximum time of synchronization delay is approximately 8 seconds, and the number of delays increases when the number of concurrent connections increases.

The bandwidth usage is proportional to the number of connection concurrency.

System load does not change much when the number of concurrent database connections increases, system resource utilization is not high.

The next step is to optimize or add the deferred data to the Nagios monitor.

Original link: http://www.sijitao.net/1860.html

Reference URL:

Http://blog.163.com/[email protected]/blog/static/163877040201221382150858/

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

PostgreSQL database streaming replication stream replication main standby delay test