Running read/write pgbench test cases

In this recipe, we will be discussing how to perform various tests using the pgbench tool.

Getting ready

Using pgbench options, we can benchmark the database for read/write operations. Using these measurements, we can estimate the disk read-write speed by including the system buffers. To perform a read-write-only test, then either we can go with pgbench arguments, or create a custom SQL script with the required SELECT, INSERT, UPDATE, or DELETE statements, then execute them with the required number of concurrent connections.

How to do it...

Let us discuss about read-only and write-only in brief:

Read-only

To perform read-only benchmarking with pgbench predefined tables, we need to use the -S option. Otherwise, as we discussed earlier, we need to prepare a SQL file with the required SELECT statements.

Write-only

To perform write-only benchmarking with pgbench predefined tables, we need to use the -N or -b simple-update options. Otherwise, as we discussed earlier, we have to prepare a SQL file with the required UPDATE, DELETE, and INSERT statements.

How it works...

While running read-only test cases, it's good practice to measure the database cache hit ratio, which defines the reduction in I/O usage. You can get the database hit ratio using the following SQL command:

postgres=# SELECT TRUNC(((blks_hit)/(blks_read+blks_hit)::numeric)*100, 2) hit_ratio FROM pg_stat_database WHERE datname = 'postgres';
hit_ratio 
-----------
99.69
(1 row)

Also, if we enable track_io_timing in postgresql.conf, it will provide some information about disk blocks read/write operations by each backend process. We can get these disk I/O timing values from the pg_stat_database catalog view.

Note

Refer to the following URL, where pgbench supports various test suites, such as disk, CPU, memory, and so on: https://wiki.postgresql.org/wiki/Pgbenchtesting.