test2_files_perf_15-clients.tar

application/x-tar
Filename: test2_files_perf_15-clients.tar
Type: application/x-tar
Part: 0
Message: Re: Conflict detection for update_deleted in logical replication
test2_measure.sh0000664000175400017540000000147015033407317013201 0ustar  nishanisha#!/bin/bash

##################
### Definition ###
##################

## prefix
##PUB_PREFIX="/home/nisha/project/pg2/postgres/inst/bin"

## Used source
SOURCE=head

## Number of runs
NUMRUN=3

## Measurement duration
DURATION=300

## Number of clients during a run
NUMCLIENTS=15

###########################
### measure performance ###
###########################

for i in `seq ${NUMRUN}`
do
    # Prepare clean enviroment for each measurements
    sh test2_setup.sh $SOURCE

    echo "=================="
    echo "${SOURCE}_${i}.dat"
    echo "=================="

    ./pgbench -p 5433 -U postgres postgres -c $NUMCLIENTS -j $NUMCLIENTS -T $DURATION -f pgbench_script.sql -n > pub_${SOURCE}_${i}.dat &
    ./pgbench -p 5434 -U postgres postgres -c $NUMCLIENTS -j $NUMCLIENTS -T $DURATION > sub_${SOURCE}_${i}.dat
done
test2_setup.sh0000664000175400017540000000502015033400336012665 0ustar  nishanisha#!/bin/bash

port_pub=5433
port_sub=5434

##################
### Definition ###
##################

## prefix
##PUB_PREFIX="/home/nisha/project/pg2/postgres/inst/bin"

## scale factor
SCALE=60

## pgbench init command
#INIT_COMMAND="./pgbench -i -U postgres postgres -s $SCALE"

SOURCE=$1

################
### clean up ###
################

./pg_ctl stop -D data_pub -w
./pg_ctl stop -D data_sub -w
rm -rf data* *log

#######################
### setup publisher ###
#######################

./initdb -D data_pub -U postgres
cat << EOF >> data_pub/postgresql.conf
port=$port_pub
autovacuum = false
shared_buffers = '30GB'
max_wal_size = 20GB
min_wal_size = 10GB
wal_level = logical
max_replication_slots = 500
max_logical_replication_workers = 100
max_active_replication_origins = 500
max_wal_senders=500
max_worker_processes=1000 
max_replication_slots=500 
EOF

./pg_ctl -D data_pub start -w -l pub.log


## create 15 tables
./psql -U postgres -p  $port_pub -f pub_tables.sql

## intialize data in 15 tables
./psql -U postgres -p $port_pub -f pub_tables_insert.sql

## create 15 publications
./psql -U postgres -p $port_pub -f publications.sql

#######################
### setup sublisher ###
#######################

./initdb -D data_sub -U postgres

cat << EOF >> data_sub/postgresql.conf
port=$port_sub
autovacuum = false
shared_buffers = '30GB'
max_wal_size = 20GB
min_wal_size = 10GB
track_commit_timestamp = on
# log_min_messages = DEBUG1
max_replication_slots = 500
max_logical_replication_workers = 100
max_active_replication_origins = 500
max_wal_senders=500
max_worker_processes=1000 
max_replication_slots=500 
EOF

./pg_ctl -D data_sub start -w -l sub.log


## sub's own pgbench tables
./pgbench -i -p $port_sub -U postgres postgres -s $SCALE

## create the tables of publisher
./psql -U postgres -p $port_sub -f pub_tables.sql


## create 15 subscriptions
if [ $SOURCE = "head" ]
then
     ./psql -U postgres -p $port_sub -f subscriptions_head.sql
else
     ./psql -U postgres -p $port_sub -f subscriptions.sql
fi

# Wait until all the table sync is done
REMAIN="f"

while [ "$REMAIN" = "f" ]
do
    # Sleep a bit to avoid running the query too much
    sleep 1s

    # Check pg_subscription_rel catalog. This query is ported from wait_for_subscription_sync()
    # defined in Cluster.pm.
    REMAIN=`./psql -qtA -U postgres -p $port_sub -c "SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's');"`

    # Print the result for the debugging purpose
    echo $REMAIN
done

./psql -U postgres -p $port_pub -c "VACUUM ANALYZE;"

sleep 5s
pgbench_script.sql0000664000175400017540000000137415033207574013604 0ustar  nishanisha-- Set variables                                                                                    
\set scale 60
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)


-- Only run for clients 0 to 14
\if :client_id < 15
BEGIN;
UPDATE pgbench_accounts_:client_id SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts_:client_id WHERE aid = :aid;
UPDATE pgbench_tellers_:client_id SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches_:client_id SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history_:client_id (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
\endifpublications.sql0000664000175400017540000000041715033207574013303 0ustar  nishanishaDO $$
DECLARE
    i int;
BEGIN
    FOR i IN 0..14 LOOP
        EXECUTE format(
            'CREATE PUBLICATION pub_%s FOR TABLE pgbench_accounts_%s, pgbench_tellers_%s, pgbench_branches_%s, pgbench_history_%s;',
            i, i, i, i, i
        );
    END LOOP;
END;
$$;pub_tables.sql0000664000175400017540000000304315033216373012722 0ustar  nishanisha-- Create pgbench_history_X tables
DO $$
DECLARE
    i int;
BEGIN
    FOR i IN 0..14 LOOP
        EXECUTE format('CREATE TABLE pgbench_history_%s (
                            tid int,
                            bid int,
                            aid bigint,
                            delta int,
                            mtime timestamp,
                            filler char(22)
                        );', i);
    END LOOP;
END$$;


-- Create pgbench_tellers_X tables
DO $$
DECLARE
    i int;
BEGIN
    FOR i IN 0..14 LOOP
        EXECUTE format('CREATE TABLE pgbench_tellers_%s (
                            tid int not null primary key,
                            bid int,
                            tbalance int,
                            filler char(84)
                        );', i);
    END LOOP;
END$$;


-- Create pgbench_accounts_X tables
DO $$
DECLARE
    i int;
BEGIN
    FOR i IN 0..14 LOOP
        EXECUTE format('CREATE TABLE pgbench_accounts_%s (
                            aid bigint not null primary key,
                            bid int,
                            abalance int,
                            filler char(84)
                        );', i);
    END LOOP;
END$$;


-- Create pgbench_branches_X tables
DO $$
DECLARE
    i int;
BEGIN
    FOR i IN 0..14 LOOP
        EXECUTE format('CREATE TABLE pgbench_branches_%s (
                            bid int not null primary key,
                            bbalance int,
                            filler char(88)
                        );', i);
    END LOOP;
END$$;


pub_tables_insert.sql0000664000175400017540000000210715033425624014307 0ustar  nishanishaDO $$
DECLARE
    i int;
    nbranches int := 1;
    ntellers int := 10;
    naccounts int := 100000;
    scale int := 60;

    total_branches int := nbranches * scale;
    total_tellers int := ntellers * scale;
    total_accounts int := naccounts * scale;
    sql text;
BEGIN
    FOR i IN 0..14 LOOP
        -- Insert into pgbench_branches_X
        sql := format('INSERT INTO pgbench_branches_%s (bid, bbalance) SELECT bid, 0 FROM generate_series(1, %s) AS bid;',
                      i, total_branches);
        EXECUTE sql;

        -- Insert into pgbench_tellers_X
        sql := format('INSERT INTO pgbench_tellers_%s (tid, bid, tbalance) SELECT tid, ((tid - 1) / %s) + 1, 0 FROM generate_series(1, %s) AS tid;',
                      i, ntellers, total_tellers);
        EXECUTE sql;

        -- Insert into pgbench_accounts_X
        sql := format('INSERT INTO pgbench_accounts_%s (aid, bid, abalance, filler) SELECT aid, ((aid - 1) / %s) + 1, 0, '''' FROM generate_series(1, %s) AS aid;',
                      i, naccounts, total_accounts);
        EXECUTE sql;
    END LOOP;
END$$;

subscriptions.sql0000664000175400017540000000333315033374067013520 0ustar  nishanishaCREATE SUBSCRIPTION sub_0  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_0  WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_1  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_1  WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_2  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_2  WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_3  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_3  WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_4  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_4  WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_5  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_5  WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_6  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_6  WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_7  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_7  WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_8  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_8  WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_9  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_9  WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_10 CONNECTION 'port=5433 user=postgres' PUBLICATION pub_10 WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_11 CONNECTION 'port=5433 user=postgres' PUBLICATION pub_11 WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_12 CONNECTION 'port=5433 user=postgres' PUBLICATION pub_12 WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_13 CONNECTION 'port=5433 user=postgres' PUBLICATION pub_13 WITH (retain_conflict_info = on);
CREATE SUBSCRIPTION sub_14 CONNECTION 'port=5433 user=postgres' PUBLICATION pub_14 WITH (retain_conflict_info = on);
subscriptions_head.sql0000664000175400017540000000234215033374056014476 0ustar  nishanishaCREATE SUBSCRIPTION sub_0  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_0;
CREATE SUBSCRIPTION sub_1  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_1;
CREATE SUBSCRIPTION sub_2  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_2;
CREATE SUBSCRIPTION sub_3  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_3;
CREATE SUBSCRIPTION sub_4  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_4;
CREATE SUBSCRIPTION sub_5  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_5;
CREATE SUBSCRIPTION sub_6  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_6;
CREATE SUBSCRIPTION sub_7  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_7;
CREATE SUBSCRIPTION sub_8  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_8;
CREATE SUBSCRIPTION sub_9  CONNECTION 'port=5433 user=postgres' PUBLICATION pub_9;
CREATE SUBSCRIPTION sub_10 CONNECTION 'port=5433 user=postgres' PUBLICATION pub_10;
CREATE SUBSCRIPTION sub_11 CONNECTION 'port=5433 user=postgres' PUBLICATION pub_11;
CREATE SUBSCRIPTION sub_12 CONNECTION 'port=5433 user=postgres' PUBLICATION pub_12;
CREATE SUBSCRIPTION sub_13 CONNECTION 'port=5433 user=postgres' PUBLICATION pub_13;
CREATE SUBSCRIPTION sub_14 CONNECTION 'port=5433 user=postgres' PUBLICATION pub_14;