pgbench paramaters for concurrency control
pgbench offers two parameters for controlling the concurrency in the benchmark. Namely:
- -j for ‘jobs’. The number of pgbench threads to run.
-j, --jobs=NUM number of threads (default: 1)
- -c for ‘clients’. The number of “postgres” processes to run.
-c, --client=NUM number of concurrent database clients (default: 1)
Here are the TPS delivered for a simple combination of -j(1,10) and -c(1,10) using a very small (cached) database.
The machine is a GCP instance (e2-standard-8 (8 vCPUs, 32 GB memory). The database size is tiny (Scale Factor 100).
pgbench read-only test.
Firstly I ran pgbench with the -S flag “Select Only” to avoid having the disk be a bottleneck. In this experiment we are mainly interested in the concurrency options.
The result shows that the number of “clients” (postgres client processes) is the clear dominant factor. With the tiny DB and 8 cores a single pgbench thread (-j=1) is almost able to saturate the 8 cores. With j=1 and c=10 there was about 20% idle across all the cores.
With 10 pgbench threads and 10 postgres client processes (-j=10 -c=10) all 8 cores were 100% saturated
pgbench read/write test.
For completeness I re-ran the experiment without the “-S” option. The GCP instance had a single disk and was easily overwhelmed by the amount of IO generated by 8 cores at full blast. At any rate the number of postgres client processes (-c=10) is the clear dominant factor – albeit at a much lower TPS rate (due to the fact that so much time is spent waiting on disk).
What’s really interesting here is that most of the cores are showing “idle” rather than IO wait. I believe that the postgres threads must be waiting on a single writer thread to finish disk IO before they can continue (via lock. or cv_wait. So, in reality all the CPU’s/Threads are blocked on IO, but not directly so the kernel does not know to show that the CPU’s could be doing more work if the IO were faster.