18 November,2019 by Rambler
Pgbench is used for Postgresql benchmark testing. Pgbench iterates through a bunch of SQL statements . There are various configurables such as sessions (single or multiple) and clients. The pgbench approach is based on TPC-B . The TPC-B benchmark focuses on benchmarking as opposed to OLTP type testing.
The measurement is based on transactions per second. In other words - you'll define how many transactions to execute per client and this will result in a rate per second.
There is a default test framework – which involves 5 SELECT , UPDATE, INSERT commands per transaction- there are options to customise different testing approaches
Notes
Create a test database
CREATE DATABASE mydb
Initialise the pgbench db – this will create the sample tables in the db
pgbench -U postgres -p 5555 -i -s 50 mydb
-I initialize the db. Creates a bunch of default tables
-s scaling option. i.e take the default rows and x 50 or whatever scaling number you require
This step will create 4 tables - which host the test date rows and other metadata
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
Establish a baseline
pgbench -U postgres -p 5555 -c 10 -j 2 -t 10000 mydb
-c number of clients
-j 2 number of threads
-t amount of transactions
These values are 10000 transactions per client. So : 10 x 10000 = 100,000 transactions
Get Results
A sample resultset
number of clients: 10 |
number of threads: 2 |
number of transactions per client: 10000 |
number of transactions actually processed: 100000/100000 |
latency average = 7.198 ms |
tps = 1389.356369 (including connections establishing) |
tps = 1389.464745 (excluding connections establishing) |
Read more on benchmark performance testing
TPC-H generate test data , test queries and sql database benchmark ...
What is acceptable database server performance testing
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |