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 <wakandavis...@outlook.com> 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 double > the 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.ja...@gmail.com> > *Sent:* Wednesday, April 20, 2022 5:49 PM > *To:* wakandavis...@outlook.com <wakandavis...@outlook.com> > *Cc:* Tomas Vondra <tomas.von...@enterprisedb.com>; > pgsql-performance@lists.postgresql.org < > pgsql-performance@lists.postgresql.org> > *Subject:* Re: Postgresql TPS Bottleneck > > On Wed, Apr 20, 2022 at 5:13 AM <wakandavis...@outlook.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 >
