How to use the command 'pgbench' (with examples)

Pgbench is a versatile tool designed specifically for PostgreSQL benchmarking. It helps database administrators and developers run performance tests on PostgreSQL, enabling them to simulate workloads and measure the server’s performance under different conditions. By generating a typical workload on the database, pgbench provides insights into how efficiently the database handles concurrent connections, query executions, and transaction processing. Understanding these benchmarks can be crucial for optimizing database performance and capacity planning.

Initialize a database with a scale factor of 50 times the default size

Code:

pgbench --initialize --scale=50 database_name

Motivation:

When preparing to conduct performance tests, it’s essential to create a realistic database environment that resembles real-world usage. The --scale factor in pgbench allows you to inflate the size of the test database. For instance, setting a scale factor of 50 means that the database will be 50 times larger than the default size used in basic test setups. This larger dataset assists in effectively testing the database’s scalability and efficiency when handling substantial data volumes, which is critical in identifying performance bottlenecks before they impact a production database.

Explanation:

• --initialize: This flag tells pgbench to initialize the test database for benchmarking. It sets up the necessary tables and data schema.
• --scale=50: The scale factor increases the size of the dataset. A higher scale factor results in more extensive data tables, creating a larger dataset that can better approximate real-world scenarios. In this example, a scale factor of 50 makes the database large enough to meaningfully test performance.
• database_name: This is the placeholder for the actual name of the database to be initialized. You need to replace it with your target database’s name.

Example output:

starting vacuum...end.
setting primary keys...end.
creating tables...end.
generating data...10000 tuples (1 MB) done.
100000 of 5000000 tuples (128 MB) done.
... 
5000000 of 5000000 tuples (6553 MB) done.
vacuuming public.pgbench_accounts...end.

The output indicates the successful creation of tables and primary keys and shows the incremental population of tables with data according to the scale factor.

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:

Benchmark tests are crucial in assessing how well a database handles concurrent transactions and workload distribution. Benchmarking with multiple clients connecting to the database simulates real-world scenarios where multiple users or applications are accessing the database simultaneously. This example uses 10 clients and 2 worker threads to reflect a typical multi-user environment. Each client performs 10,000 transactions, putting significant load on the database server, which helps in understanding its performance capabilities and identifying potential bottlenecks.

Explanation:

• --client=10: This option specifies the number of concurrent clients simulating the user connections at any given time. Using 10 clients creates a moderate level of simultaneity in database requests, helping gauge the server’s response under such conditions.
• --jobs=2: The --jobs parameter determines the number of worker threads. Here, it uses 2 threads, which can exploit multi-core processors by parallelizing the transactions processing, enhancing test realism.
• --transactions=10000: This flag sets the number of transactions each client executes. With each of the 10 clients performing 10,000 transactions, it ensures a significant load is applied to the database, simulating high-demand situations that help test the server’s throughput and transaction efficiency.
• database_name: Again, this is the placeholder for the actual name of the database on which you’re running the benchmark. Replace it with your specific database name.

Example output:

transaction type: TPC-B (sort of)
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 = 920.076830 (including connections establishing)
tps = 921.392945 (excluding connections establishing)

The output showcases the benchmark test configuration and results, outlining the transactions per second (tps) both including and excluding the time taken to establish connections, which is critical in assessing the server’s handling capacity.

Conclusion:

Pgbench is an invaluable tool for PostgreSQL database administrators who aim to ensure optimal performance and efficient handling of various workloads. By using pgbench’s flexible configurations, such as adjusting scale, client connections, worker threads, and transaction counts, users can simulate different conditions and better understand their database’s capabilities and potential areas for improvement. These examples illustrate how pgbench supports comprehensive testing, indispensable for maintaining and scaling a robust database system.