Thread: Not able to increase TPS in Postgres BDR set up using pgbench utility
Hi ,
Not able to increase TPS in Postgres BDR set up using pgbench utility:
I am getting constant TPS as 500 while using pgbench with different TPS as input .How to increase the TPS in Postgres BDR set up, please suggest .
bdr_version used : 1.0.3-2017-11-21-15283baPostgres version : 9.4Scenario 1:
Shared_Buffer = 1024 MB and throttling TPS as 2000
command used:
pgbench -h <hostname> -p 5432 -U postgres -d <dbname> -c 50 -j 50 -r -R 2000
O/P :
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 50
duration: 2400 s
number of transactions actually processed: 1200964
latency average: 894071.370 ms
latency stddev: -nan ms
rate limit schedule lag: avg 893971.489 (max 1819008.190) ms
tps = 500.382936 (including connections establishing)
tps = 500.385107 (excluding connections establishing)
statement latencies in milliseconds: 0.109225 \set nbranches 1 * :scale 0.100045 \set ntellers 10 * :scale 0.085917 \set naccounts 100000 * :scale 0.064758 \setrandom aid 1 :naccounts 0.057230 \setrandom bid 1 :nbranches 0.052089 \setrandom tid 1 :ntellers 0.049827 \setrandom delta -5000 5000 0.471368 BEGIN; 0.678317 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.599331 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 80.328257 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 15.537372 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.698180 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.995450 END;
Thu Aug 16 09:52:30 UTC 2018
Scenario 2:
Shared_Buffer = 1024 MB and throttling TPS as 6000
Command Used:
pgbench -h <hostname> -p 5432 -U postgres -d <dbname> -c 50 -j 50 -r -R 6000
O/P:
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 50
duration: 2400 s
number of transactions actually processed: 1184746
latency average: 1096729.554 ms
latency stddev: -nan ms
rate limit schedule lag: avg 1096628.305 (max 2208483.098) ms
tps = 493.625936 (including connections establishing)
tps = 493.629106 (excluding connections establishing)
statement latencies in milliseconds: 0.108491 \set nbranches 1 * :scale 0.098740 \set ntellers 10 * :scale 0.084497 \set naccounts 100000 * :scale 0.064168 \setrandom aid 1 :naccounts 0.056658 \setrandom bid 1 :nbranches 0.051678 \setrandom tid 1 :ntellers 0.049427 \setrandom delta -5000 5000 0.480755 BEGIN; 0.696514 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.607114 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 81.404448 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 15.775295 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.708403 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 1.009272 END;
Thu Aug 16 11:33:17 UTC 2018Re: Not able to increase TPS in Postgres BDR set up using pgbenchutility
From
soumitra bhandary
Date:
Hi ,
Anyone have any idea about the below observation . I am awaiting response .
Thanks ,
Soumitra
Soumitra
From: soumitra bhandary <soumitra.bhandary@hotmail.com>
Sent: Thursday, August 23, 2018 11:13 AM
To: pgsql-admin@lists.postgresql.org
Subject: Not able to increase TPS in Postgres BDR set up using pgbench utility
Sent: Thursday, August 23, 2018 11:13 AM
To: pgsql-admin@lists.postgresql.org
Subject: Not able to increase TPS in Postgres BDR set up using pgbench utility
Hi ,
Not able to increase TPS in Postgres BDR set up using pgbench utility:
I am getting constant TPS as 500 while using pgbench with different TPS as input .How to increase the TPS in Postgres BDR set up, please suggest .
bdr_version used : 1.0.3-2017-11-21-15283baPostgres version : 9.4Scenario 1:
Shared_Buffer = 1024 MB and throttling TPS as 2000
command used:
pgbench -h <hostname> -p 5432 -U postgres -d <dbname> -c 50 -j 50 -r -R 2000
O/P :
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 50
duration: 2400 s
number of transactions actually processed: 1200964
latency average: 894071.370 ms
latency stddev: -nan ms
rate limit schedule lag: avg 893971.489 (max 1819008.190) ms
tps = 500.382936 (including connections establishing)
tps = 500.385107 (excluding connections establishing)
statement latencies in milliseconds: 0.109225 \set nbranches 1 * :scale 0.100045 \set ntellers 10 * :scale 0.085917 \set naccounts 100000 * :scale 0.064758 \setrandom aid 1 :naccounts 0.057230 \setrandom bid 1 :nbranches 0.052089 \setrandom tid 1 :ntellers 0.049827 \setrandom delta -5000 5000 0.471368 BEGIN; 0.678317 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.599331 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 80.328257 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 15.537372 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.698180 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 0.995450 END;
Thu Aug 16 09:52:30 UTC 2018
Scenario 2:
Shared_Buffer = 1024 MB and throttling TPS as 6000
Command Used:
pgbench -h <hostname> -p 5432 -U postgres -d <dbname> -c 50 -j 50 -r -R 6000
O/P:
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 50
duration: 2400 s
number of transactions actually processed: 1184746
latency average: 1096729.554 ms
latency stddev: -nan ms
rate limit schedule lag: avg 1096628.305 (max 2208483.098) ms
tps = 493.625936 (including connections establishing)
tps = 493.629106 (excluding connections establishing)
statement latencies in milliseconds: 0.108491 \set nbranches 1 * :scale 0.098740 \set ntellers 10 * :scale 0.084497 \set naccounts 100000 * :scale 0.064168 \setrandom aid 1 :naccounts 0.056658 \setrandom bid 1 :nbranches 0.051678 \setrandom tid 1 :ntellers 0.049427 \setrandom delta -5000 5000 0.480755 BEGIN; 0.696514 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.607114 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 81.404448 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; 15.775295 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; 0.708403 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); 1.009272 END;
Thu Aug 16 11:33:17 UTC 2018