Re: Postgresql TPS Bottleneck

On Wed, Apr 20, 2022 at 5:13 AM <wakandavision(at)outlook(dot)com> 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