Ypu wouldn't get an increasing running two instances on the same server. Distributed database severs is a complex application and tuning it will depend on storage and CPU capacity. It could be as simple as a bus. Are you running this locally or on the cloud? Are you running this on a distributed file system or across a network? There are a dozen different reasons why a database would not be using 100% of capacity from indexing to disk or bus bound or network bound.
Thanks,
Ben
On Wed, Apr 20, 2022, 1:27 PM <wakandavision@xxxxxxxxxxx> wrote:
Clearly, I have only supplied half of the information there. I'm really sorry about that. The TPS measurement of the application does in no way correspond to the TPS of Postgres.They are measured completely different but it's the measure we actually are interested in - as we want to assess the scalability of the application.
What I wanted to show is that the server we are hosting Postgres on is not bottlenecked (in an obvious way), as running two instances in parallel on the same server gives us almost doublethe performance in our application and double the resource usage on the DB server. But what actually is strange(?), is that the TPS of Postgres does not change much, i.e. it's just 'distributed' to the two instances.
It would seem like our application could not handle more throughput, but I did the same with three instances, where we stayed again with 'only' double the performance and the TPS of Postgres distributed to three instances(each client application running on an independent node).
I'm really getting frustrated here as I (and no one I asked yet) has an explanation for this behavior.
From: Jeff Janes <jeff.janes@xxxxxxxxx>
Sent: Wednesday, April 20, 2022 5:49 PM
To: wakandavision@xxxxxxxxxxx <wakandavision@xxxxxxxxxxx>
Cc: Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Postgresql TPS BottleneckOn 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
