In this recipe, we will be discussing how to perform various tests using the pgbench tool.
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.
Let us discuss about read-only and write-only in brief:
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.
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.