pgbench is tools that used to stress test database base on deferent scenarios you can define by simulating database traffic .
pgbench can help use also now our database setup limitations
and also get clear understand how our configuration will be able to tackle live use .
i personly use pgbench for two propose
1- in my home lab to study performance tuning
2- for customer we will do stress test on this setup of HA or DR to determine its readiness before we handover the setup .
installing pgbench
pgbench come bundled with postgresql-contrib which one of core component of postgresql installation
if you have postgresql already installed , then you most properly have postgresql-contrib installed
to determine whether pgbench is installed use the whichcommand
which pgbench
if pgbench was not installed then you can simply run below command to get it up and running
sudo apt install postgresql-contrib -y
using pgbench
for testing propose i like to create separate database for stress test ,
login to psql and create database
create database testdb'
now we can start bench mark we will use the below command
pgbench -i -s 50 [database-name]
pgbench -i -s 50 testdb
-i This option stands for “initialize.” It tells pgbench to set up the benchmarking tables and data in the database.-s his is the scale factor. The scale factor controls the size of the data set that pgbench creates. A scale factor of 50 means that pgbench will create tables and data that are 50 times larger than the default size
this means that database will be create 50 times size the default size which is 16MB
the number of record per table that will be genrated during bench will 5,000,000 records.
\list+
\dt+
threading and transaction stress test
now we will more real world test
with the -j option we can define number of thread that we want for our stress test
and with option -t we can specify number of transaction-c we define number of client per transaction
this stress test is very useful to test setup such as pgpool
or patroni with load balance installed such haproxy
this command we will simulate a total of 100,000 transactions from 10 clients.
pgbench -c 10 -j 2 -t 10000 testdb
we can see from result that test seceded .
reference : https://medium.com/@c.ucanefe/pgbench-load-test-166bdfb5c75a