How to use the command pgbench (with examples)

The pgbench command is used to run benchmark tests on PostgreSQL. It allows users to simulate database workloads and measure the performance of a PostgreSQL database.

Use case 1: Initialize a database with a scale factor of 50 times the default size

Code:

pgbench --initialize --scale=50 database_name

Motivation for using the example: Initializing a database with a scale factor is useful when you want to simulate a large workload on the PostgreSQL database. By scaling up the database, you can test the performance and responsiveness of the system under heavy load conditions.

Explanation of every argument given in the command:

• --initialize: This flag tells pgbench to initialize the database. It creates the necessary data for running the benchmark.
• --scale=50: This flag sets the scale factor for the benchmark. In this example, the scale factor is set to 50 times the default size.
• database_name: This is the name of the PostgreSQL database that will be initialized.

Example output:

creating tables...
100000 of 100000 tuples done (elapsed 0.405 s, remaining 0.000 s)
vacuuming...
finding optimal layout for the "pgbench_branches" table...
100000 of 100000 tuples done (elapsed 0.799 s, remaining 0.000 s)
finding optimal layout for the "pgbench_tellers" table...
100000 of 100000 tuples done (elapsed 1.208 s, remaining 0.000 s)
finding optimal layout for the "pgbench_accounts" table...
100000 of 100000 tuples done (elapsed 1.839 s, remaining 0.000 s)
creating foreign keys for table "pgbench_accounts"...
100000 of 100000 tuples done (elapsed 2.205 s, remaining 0.000 s)
creating triggers...
100000 of 100000 tuples done (elapsed 2.620 s, remaining 0.000 s)
done.

Use case 2: Benchmark a database with 10 clients, 2 worker threads, and 10,000 transactions per client

Code:

pgbench --client=10 --jobs=2 --transactions=10000 database_name

Motivation for using the example: Benchmarking a PostgreSQL database with multiple clients and worker threads allows you to test the performance and concurrency of the database. By specifying the number of clients, worker threads, and transactions, you can simulate a realistic workload and evaluate the database’s ability to handle concurrent requests.

Explanation of every argument given in the command:

• --client=10: This flag sets the number of simulated clients that will be used to execute transactions concurrently.
• --jobs=2: This flag sets the number of worker threads to be used in the benchmark.
• --transactions=10000: This flag sets the number of transactions each client will execute.
• database_name: This is the name of the PostgreSQL database that will be benchmarked.

Example output:

starting vacuum...end.
transaction type: pgbench
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = 2091.763443 (including connections establishing)
tps = 2092.925717 (excluding connections establishing)

Conclusion:

The pgbench command is a powerful tool for benchmarking PostgreSQL databases. It allows users to initialize databases with custom scale factors and simulate realistic workloads to measure the performance of the database. By understanding the different options and parameters available, users can optimize the database and improve its responsiveness under heavy load conditions.