Re: Deterioration in performance when query executed in multi threads

> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-
> owner(at)postgresql(dot)org] On Behalf Of Anne Rosset
> Sent: Wednesday, May 01, 2013 1:10 PM
> To: ktm(at)rice(dot)edu
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Deterioration in performance when query executed
> in multi threads
>
> Thanks Ken. I am going to test with different pool sizes and see if I
> see any improvements.
> Are there other configuration options I should look like? I was
> thinking of playing with shared_buffer.
>
> Thanks,
> Anne
>
> -----Original Message-----
> From: ktm(at)rice(dot)edu [mailto:ktm(at)rice(dot)edu]
> Sent: Wednesday, May 01, 2013 9:27 AM
> To: Anne Rosset
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Deterioration in performance when query executed
> in multi threads
>
> On Wed, May 01, 2013 at 04:07:55PM +0000, Anne Rosset wrote:
> > Hi Ken,
> > Thanks for your answer. My test is actually running with jboss 7/jdbc
> and the connection pool is defined with min-pool-size =10 and max-
> pool-size=400.
> >
> > Why would you think it is an issue with the connection pool?
> >
> > Thanks,
> > Anne
> >
>
> Hi Anne,
>
> You want to be able to run as many jobs productively at once as your
> hardware is capable of supporting. Usually something starting a 2 x
> number of CPUs is best.
> If you make several runs increasing the size of the pool each time, you
> will see a maximum throughput somewhere near there and then the
> performance will decrease as you add more and more connections. You can
> then use that sweet spot.
> Your test harness should make that pretty easy to find.
>
> Regards,
> Ken
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-
> performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Anne,

Before expecting advice on specific changes to Postgres configuration parameters,
You should provide this list with your hardware configuration, Postgres version, your current Postgres configuration parameters (at least those that changed from defaults).
And, if you do the testing using specific query, would be nice if you provide the results of:

Explain analyze <your_select>;

along with the definition of database objects (tables, indexes) involved in this select.

Also, you mention client-side connection pooler. In my experience, server-side poolers, such as PgBouncer mentioned earlier, are much more effective.

Regards,
Igor Neyman