On Wed, Apr 20, 2022 at 5:13 AM <wakandavision@xxxxxxxxxxx> wrote:
The next thing I did was starting two independent Postgres instances on
the same server and run independent client applications against each of
them. This resulted in our application getting almost double of the TPS
compared to running a single instance (from 13k to 23k) - Each Postgres
instance had about 45k TPS which did not increase (?).
How could that be? Isn't there a one to one correspondence between app progress and PostgreSQL transactions? How could one almost double while the other did not increase? Anyway, 2x45 does seem like an increase (smallish) over 65.
Your bottleneck for pgbench may be IPC/context switches. I noticed that -S did about 7 times more than the default, and it only makes one round trip to the database while the default makes 7.
You could package up the different queries made by the default transaction into one function call, in order to do the same thing but with fewer round trips to the database. This would be an easy way to see if my theory is true. If it is, I don't know what that would mean for your app though, as we know nothing about its structure.
I have a patch handy (attached) which implements this feature as the builtin transaction "-b tpcb-func". If you don't want to recompile pgbench, you could dissect the patch to reimplement the same thing as a -f style transaction instead.
Note that packaging it up this way does violate the spirit of the benchmark, as clearly someone is supposed to look at the results of the first select before deciding to proceed with the rest of the transaction. But you don't seem very interested in the spirit of the tpc-b benchmark, just in using it as a tool to track down a bottleneck.
Cheers,
Jeff
Attachment:
pgbench_function_v13.patch
Description: Binary data
