pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help in performance tuning.
30+ messages / 17 participants
[nested] [flat]
* Need help in performance tuning.
@ 2010-07-08 19:20 Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 30+ messages in thread
From: Harpreet singh Wadhwa @ 2010-07-08 19:20 UTC (permalink / raw)
To: pgsql-performance
Hi,
I want to fine tune my postgresql to increase number of connects it
can handle in a minutes time.
Decrease the response time per request etc.
The exact case will be to handle around 100 concurrent requests.
Can any one please help me in this.
Any hardware suggestions are also welcomed.
Regards
Harpreet
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
@ 2010-07-09 03:11 ` Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 1 reply; 30+ messages in thread
From: Craig Ringer @ 2010-07-09 03:11 UTC (permalink / raw)
To: Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>; +Cc: pgsql-performance
On 9/07/2010 3:20 AM, Harpreet singh Wadhwa wrote:
> Hi,
>
>
> I want to fine tune my postgresql to increase number of connects it
> can handle in a minutes time.
> Decrease the response time per request etc.
> The exact case will be to handle around 100 concurrent requests.
If you're not using a connection pool, start using one.
Do you really need 100 *active* working query threads at one time?
Because if you do, you're going to need a scary-big disk subsystem and a
lot of processors.
Most people actually only need a few queries executing simultaneously,
they just need lots of connections to the database open concurrently
and/or lots of queries queued up for processing. For that purpose, a
connection pool is ideal.
You will get BETTER performance from postgresql with FEWER connections
to the "real" database that're all doing active work. If you need lots
and lots of connections you should use a connection pool to save the
main database the overhead of managing that.
--
Craig Ringer
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
@ 2010-07-09 04:19 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 30+ messages in thread
From: Samuel Gendler @ 2010-07-09 04:19 UTC (permalink / raw)
To: pgsql-performance
On Thu, Jul 8, 2010 at 8:11 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> If you're not using a connection pool, start using one.
>
> Do you really need 100 *active* working query threads at one time? Because
> if you do, you're going to need a scary-big disk subsystem and a lot of
> processors.
I see this issue and subsequent advice cross this list awfully
frequently. Is there in architectural reason why postgres itself
cannot pool incoming connections in order to eliminate the requirement
for an external pool?
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
@ 2010-07-09 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 05:48 ` Re: Need help in performance tuning. Hannu Krosing <hannu@2ndquadrant.com>
2010-07-09 07:52 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 20:35 ` Re: Need help in performance tuning. Robert Haas <robertmhaas@gmail.com>
0 siblings, 4 replies; 30+ messages in thread
From: Tom Lane @ 2010-07-09 04:42 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: pgsql-performance
Samuel Gendler <sgendler@ideasculptor.com> writes:
> On Thu, Jul 8, 2010 at 8:11 PM, Craig Ringer
> <craig@postnewspapers.com.au> wrote:
>> If you're not using a connection pool, start using one.
> I see this issue and subsequent advice cross this list awfully
> frequently. Is there in architectural reason why postgres itself
> cannot pool incoming connections in order to eliminate the requirement
> for an external pool?
Perhaps not, but there's no obvious benefit either. Since there's
More Than One Way To Do It, it seems more practical to keep that as a
separate problem that can be solved by a choice of add-on packages.
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-07-09 05:48 ` Hannu Krosing <hannu@2ndquadrant.com>
2010-07-14 06:44 ` Re: Need help in performance tuning. Scott Marlowe <scott.marlowe@gmail.com>
3 siblings, 1 reply; 30+ messages in thread
From: Hannu Krosing @ 2010-07-09 05:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Samuel Gendler <sgendler@ideasculptor.com>; pgsql-performance
On Fri, 2010-07-09 at 00:42 -0400, Tom Lane wrote:
> Samuel Gendler <sgendler@ideasculptor.com> writes:
> > On Thu, Jul 8, 2010 at 8:11 PM, Craig Ringer
> > <craig@postnewspapers.com.au> wrote:
> >> If you're not using a connection pool, start using one.
>
> > I see this issue and subsequent advice cross this list awfully
> > frequently. Is there in architectural reason why postgres itself
> > cannot pool incoming connections in order to eliminate the requirement
> > for an external pool?
>
> Perhaps not, but there's no obvious benefit either. Since there's
> More Than One Way To Do It, it seems more practical to keep that as a
> separate problem that can be solved by a choice of add-on packages.
One example where you need a separate connection pool is pooling really
large number of connections, which you may want to do on another host
than the database itself is running.
For example pgbouncer had to add option to use incoming unix sockets,
because they run into the IP socket port number limit (a little above
31k, or more exactly 63k/2.
And unix sockets can be used only on local host .
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 05:48 ` Re: Need help in performance tuning. Hannu Krosing <hannu@2ndquadrant.com>
@ 2010-07-14 06:44 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-14 13:58 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 30+ messages in thread
From: Scott Marlowe @ 2010-07-14 06:44 UTC (permalink / raw)
To: Hannu Krosing <hannu@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Samuel Gendler <sgendler@ideasculptor.com>; pgsql-performance
On Thu, Jul 8, 2010 at 11:48 PM, Hannu Krosing <hannu@2ndquadrant.com> wrote:
> One example where you need a separate connection pool is pooling really
> large number of connections, which you may want to do on another host
> than the database itself is running.
Definitely. Often it's best placed on the individual webservers that
are making requests, each running its own pool.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 05:48 ` Re: Need help in performance tuning. Hannu Krosing <hannu@2ndquadrant.com>
2010-07-14 06:44 ` Re: Need help in performance tuning. Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-07-14 13:58 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-14 14:54 ` Re: Need help in performance tuning. Hannu Krosing <hannu@2ndquadrant.com>
0 siblings, 1 reply; 30+ messages in thread
From: Kevin Grittner @ 2010-07-14 13:58 UTC (permalink / raw)
To: Hannu Krosing <hannu@2ndquadrant.com>; Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Samuel Gendler <sgendler@ideasculptor.com>; pgsql-performance; Tom Lane <tgl@sss.pgh.pa.us>
Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Hannu Krosing <hannu@2ndquadrant.com> wrote:
>> One example where you need a separate connection pool is pooling
>> really large number of connections, which you may want to do on
>> another host than the database itself is running.
>
> Definitely. Often it's best placed on the individual webservers
> that are making requests, each running its own pool.
Each running its own pool? You've just made a case for an
admissions policy based on active database transactions or active
queries (or both) on the server having a benefit when used with this
pooling arrangement. This collection of pools can't know when the
CPUs have enough to keep them busy and adding more will degrade
performance.
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 05:48 ` Re: Need help in performance tuning. Hannu Krosing <hannu@2ndquadrant.com>
2010-07-14 06:44 ` Re: Need help in performance tuning. Scott Marlowe <scott.marlowe@gmail.com>
2010-07-14 13:58 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-07-14 14:54 ` Hannu Krosing <hannu@2ndquadrant.com>
0 siblings, 0 replies; 30+ messages in thread
From: Hannu Krosing @ 2010-07-14 14:54 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Samuel Gendler <sgendler@ideasculptor.com>; pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
On Wed, 2010-07-14 at 08:58 -0500, Kevin Grittner wrote:
> Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > Hannu Krosing <hannu@2ndquadrant.com> wrote:
> >> One example where you need a separate connection pool is pooling
> >> really large number of connections, which you may want to do on
> >> another host than the database itself is running.
> >
> > Definitely. Often it's best placed on the individual webservers
> > that are making requests, each running its own pool.
>
> Each running its own pool? You've just made a case for an
> admissions policy based on active database transactions or active
> queries (or both) on the server having a benefit when used with this
> pooling arrangement. This collection of pools can't know when the
> CPUs have enough to keep them busy and adding more will degrade
> performance.
I guess this setup is for OLTP load (read "lots of short transactions
with low timeout limits"), where you can just open 2-5 connections per
CPU for mostly-in-memory database, maybe a little more when disk
accesses are involved. If you have more, then they just wait a few
milliseconds, if you have less, you don't have anything else to run
anyway.
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-07-09 07:52 ` Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 08:55 ` Re: Need help in performance tuning. Harpreet singh Wadhwa <harpreet@openbravo.com>
2010-07-09 09:31 ` Re: Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 09:53 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-12 16:04 ` Re: Need help in performance tuning. Bruce Momjian <bruce@momjian.us>
3 siblings, 4 replies; 30+ messages in thread
From: Craig Ringer @ 2010-07-09 07:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Samuel Gendler <sgendler@ideasculptor.com>; pgsql-performance
On 09/07/10 12:42, Tom Lane wrote:
> Samuel Gendler <sgendler@ideasculptor.com> writes:
>> On Thu, Jul 8, 2010 at 8:11 PM, Craig Ringer
>> <craig@postnewspapers.com.au> wrote:
>>> If you're not using a connection pool, start using one.
>
>> I see this issue and subsequent advice cross this list awfully
>> frequently. Is there in architectural reason why postgres itself
>> cannot pool incoming connections in order to eliminate the requirement
>> for an external pool?
>
> Perhaps not, but there's no obvious benefit either. Since there's
> More Than One Way To Do It, it seems more practical to keep that as a
> separate problem that can be solved by a choice of add-on packages.
Admittedly I'm relatively ignorant of the details, but I increasingly
think PostgreSQL will need a different big architectural change in the
coming years, as the typical performance characteristics of machines
change:
It'll need to separate "running queries" from "running processes", or
start threading backends, so that one way or the other a single query
can benefit from the capabilities of multiple CPUs. The same separation,
or a move to async I/O, might be needed to get one query to concurrently
read multiple partitions of a table, or otherwise get maximum benefit
from high-capacity I/O subsystems when running just a few big, expensive
queries.
Otherwise I'm wondering if PostgreSQL will begin really suffering in
performance on workloads where queries are big and expensive but there
are relatively few of them running at a time.
My point? *if* I'm not full of hot air and there's some truth to my
blather above, any change like that might be accompanied by a move to
separate query execution state from connection state, so that idle
connections have a much lower resource cost.
OK, that's my hand-waving for the day done.
--
Craig Ringer
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 07:52 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
@ 2010-07-09 08:55 ` Harpreet singh Wadhwa <harpreet@openbravo.com>
2010-07-09 16:44 ` Re: Need help in performance tuning. Jorge Montero <jorge_montero@homedecorators.com>
3 siblings, 1 reply; 30+ messages in thread
From: Harpreet singh Wadhwa @ 2010-07-09 08:55 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Samuel Gendler <sgendler@ideasculptor.com>; pgsql-performance
Thanx you all for the replies.
I got a gist on where should I head towards
like I should rely a bit on postgres for performance and rest on my
tomcat and application.
And will try connection pooling on postgres part.
And if I come back for any query (related to this topic) then this
time it will be more precise (with real time data of my testing). ;-)
Regards
haps
On Fri, Jul 9, 2010 at 1:22 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 09/07/10 12:42, Tom Lane wrote:
>> Samuel Gendler <sgendler@ideasculptor.com> writes:
>>> On Thu, Jul 8, 2010 at 8:11 PM, Craig Ringer
>>> <craig@postnewspapers.com.au> wrote:
>>>> If you're not using a connection pool, start using one.
>>
>>> I see this issue and subsequent advice cross this list awfully
>>> frequently. Is there in architectural reason why postgres itself
>>> cannot pool incoming connections in order to eliminate the requirement
>>> for an external pool?
>>
>> Perhaps not, but there's no obvious benefit either. Since there's
>> More Than One Way To Do It, it seems more practical to keep that as a
>> separate problem that can be solved by a choice of add-on packages.
>
> Admittedly I'm relatively ignorant of the details, but I increasingly
> think PostgreSQL will need a different big architectural change in the
> coming years, as the typical performance characteristics of machines
> change:
>
> It'll need to separate "running queries" from "running processes", or
> start threading backends, so that one way or the other a single query
> can benefit from the capabilities of multiple CPUs. The same separation,
> or a move to async I/O, might be needed to get one query to concurrently
> read multiple partitions of a table, or otherwise get maximum benefit
> from high-capacity I/O subsystems when running just a few big, expensive
> queries.
>
> Otherwise I'm wondering if PostgreSQL will begin really suffering in
> performance on workloads where queries are big and expensive but there
> are relatively few of them running at a time.
>
> My point? *if* I'm not full of hot air and there's some truth to my
> blather above, any change like that might be accompanied by a move to
> separate query execution state from connection state, so that idle
> connections have a much lower resource cost.
>
> OK, that's my hand-waving for the day done.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 07:52 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 08:55 ` Re: Need help in performance tuning. Harpreet singh Wadhwa <harpreet@openbravo.com>
@ 2010-07-09 16:44 ` Jorge Montero <jorge_montero@homedecorators.com>
0 siblings, 0 replies; 30+ messages in thread
From: Jorge Montero @ 2010-07-09 16:44 UTC (permalink / raw)
To: Harpreet singh Wadhwa <harpreet@openbravo.com>; +Cc: pgsql-performance
If your app is running under Tomcat, connection pooling is extremely easy to set up from there: It has connection pooling mechanisms built in. Request your db connections using said mechanisms, instead of doing it manually, make a couple of changes to server.xml, and the problem goes away. Hundreds, if not thousands of concurrent users might end up running with less than 10 connections.
>>> Harpreet singh Wadhwa <harpreet@openbravo.com> 7/9/2010 3:55 AM >>>
Thanx you all for the replies.
I got a gist on where should I head towards
like I should rely a bit on postgres for performance and rest on my
tomcat and application.
And will try connection pooling on postgres part.
And if I come back for any query (related to this topic) then this
time it will be more precise (with real time data of my testing). ;-)
Regards
haps
On Fri, Jul 9, 2010 at 1:22 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 09/07/10 12:42, Tom Lane wrote:
>> Samuel Gendler <sgendler@ideasculptor.com> writes:
>>> On Thu, Jul 8, 2010 at 8:11 PM, Craig Ringer
>>> <craig@postnewspapers.com.au> wrote:
>>>> If you're not using a connection pool, start using one.
>>
>>> I see this issue and subsequent advice cross this list awfully
>>> frequently. Is there in architectural reason why postgres itself
>>> cannot pool incoming connections in order to eliminate the requirement
>>> for an external pool?
>>
>> Perhaps not, but there's no obvious benefit either. Since there's
>> More Than One Way To Do It, it seems more practical to keep that as a
>> separate problem that can be solved by a choice of add-on packages.
>
> Admittedly I'm relatively ignorant of the details, but I increasingly
> think PostgreSQL will need a different big architectural change in the
> coming years, as the typical performance characteristics of machines
> change:
>
> It'll need to separate "running queries" from "running processes", or
> start threading backends, so that one way or the other a single query
> can benefit from the capabilities of multiple CPUs. The same separation,
> or a move to async I/O, might be needed to get one query to concurrently
> read multiple partitions of a table, or otherwise get maximum benefit
> from high-capacity I/O subsystems when running just a few big, expensive
> queries.
>
> Otherwise I'm wondering if PostgreSQL will begin really suffering in
> performance on workloads where queries are big and expensive but there
> are relatively few of them running at a time.
>
> My point? *if* I'm not full of hot air and there's some truth to my
> blather above, any change like that might be accompanied by a move to
> separate query execution state from connection state, so that idle
> connections have a much lower resource cost.
>
> OK, that's my hand-waving for the day done.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 07:52 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
@ 2010-07-09 09:31 ` Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
3 siblings, 0 replies; 30+ messages in thread
From: Harpreet singh Wadhwa @ 2010-07-09 09:31 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Samuel Gendler <sgendler@ideasculptor.com>; pgsql-performance
Thanx you all for the replies.
I got a gist on where should I head towards
like I should rely a bit on postgres for performance and rest on my
tomcat and application.
And will try connection pooling on postgres part.
And if I come back for any query (related to this topic) then this
time it will be more precise (with real time data of my testing). ;-)
Regards
haps
On Fri, Jul 9, 2010 at 1:22 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 09/07/10 12:42, Tom Lane wrote:
>> Samuel Gendler <sgendler@ideasculptor.com> writes:
>>> On Thu, Jul 8, 2010 at 8:11 PM, Craig Ringer
>>> <craig@postnewspapers.com.au> wrote:
>>>> If you're not using a connection pool, start using one.
>>
>>> I see this issue and subsequent advice cross this list awfully
>>> frequently. Is there in architectural reason why postgres itself
>>> cannot pool incoming connections in order to eliminate the requirement
>>> for an external pool?
>>
>> Perhaps not, but there's no obvious benefit either. Since there's
>> More Than One Way To Do It, it seems more practical to keep that as a
>> separate problem that can be solved by a choice of add-on packages.
>
> Admittedly I'm relatively ignorant of the details, but I increasingly
> think PostgreSQL will need a different big architectural change in the
> coming years, as the typical performance characteristics of machines
> change:
>
> It'll need to separate "running queries" from "running processes", or
> start threading backends, so that one way or the other a single query
> can benefit from the capabilities of multiple CPUs. The same separation,
> or a move to async I/O, might be needed to get one query to concurrently
> read multiple partitions of a table, or otherwise get maximum benefit
> from high-capacity I/O subsystems when running just a few big, expensive
> queries.
>
> Otherwise I'm wondering if PostgreSQL will begin really suffering in
> performance on workloads where queries are big and expensive but there
> are relatively few of them running at a time.
>
> My point? *if* I'm not full of hot air and there's some truth to my
> blather above, any change like that might be accompanied by a move to
> separate query execution state from connection state, so that idle
> connections have a much lower resource cost.
>
> OK, that's my hand-waving for the day done.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 07:52 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
@ 2010-07-09 09:53 ` Craig Ringer <craig@postnewspapers.com.au>
3 siblings, 0 replies; 30+ messages in thread
From: Craig Ringer @ 2010-07-09 09:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Samuel Gendler <sgendler@ideasculptor.com>; pgsql-performance
> Otherwise I'm wondering if PostgreSQL will begin really suffering in
> performance on workloads where queries are big and expensive but there
> are relatively few of them running at a time.
Oh, I should note at this point that I'm *not* whining that "someone"
should volunteer to do this, or that "the postgresql project" should
just "make it happen".
I'm fully aware that Pg is a volunteer project and that even if these
speculations were in a vaguely reasonable direction, that doesn't mean
anyone has the time/skills/knowledge/interest to undertake such major
architectural change. I certainly know I have zero right to ask/expect
anyone to - I'm very, very grateful to all those who already spend time
helping out and enhancing Pg. With particular props to Tom Lane for
patience on the -general list and heroic bug-fixing persistence.
Sorry for the rely-to-self, I just realized my post could've been taken
as a whine about Pg's architecture and some kind of demand that someone
do something about it. That couldn't be further from my intent.
--
Craig Ringer
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 07:52 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
@ 2010-07-12 16:04 ` Bruce Momjian <bruce@momjian.us>
3 siblings, 0 replies; 30+ messages in thread
From: Bruce Momjian @ 2010-07-12 16:04 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Samuel Gendler <sgendler@ideasculptor.com>; pgsql-performance
Craig Ringer wrote:
> It'll need to separate "running queries" from "running processes", or
> start threading backends, so that one way or the other a single query
> can benefit from the capabilities of multiple CPUs. The same separation,
> or a move to async I/O, might be needed to get one query to concurrently
> read multiple partitions of a table, or otherwise get maximum benefit
> from high-capacity I/O subsystems when running just a few big, expensive
> queries.
>
> Otherwise I'm wondering if PostgreSQL will begin really suffering in
> performance on workloads where queries are big and expensive but there
> are relatively few of them running at a time.
Agreed. We certainly are going to have to go in that direction someday.
We have TODO items for these.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-07-09 12:56 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-10 01:01 ` Re: Need help in performance tuning. Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
3 siblings, 2 replies; 30+ messages in thread
From: Brad Nicholson @ 2010-07-09 12:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Fri, 2010-07-09 at 00:42 -0400, Tom Lane wrote:
> Samuel Gendler <sgendler@ideasculptor.com> writes:
> > On Thu, Jul 8, 2010 at 8:11 PM, Craig Ringer
> > <craig@postnewspapers.com.au> wrote:
> >> If you're not using a connection pool, start using one.
>
> > I see this issue and subsequent advice cross this list awfully
> > frequently. Is there in architectural reason why postgres itself
> > cannot pool incoming connections in order to eliminate the requirement
> > for an external pool?
>
> Perhaps not, but there's no obvious benefit either. Since there's
> More Than One Way To Do It, it seems more practical to keep that as a
> separate problem that can be solved by a choice of add-on packages.
This sounds similar to the approach to taken with Replication for years
before being moved into core.
Just like replication, pooling has different approaches. I do think
that in both cases, having a solution that works, easily, out of the
"box" will meet the needs of most users.
There is also the issue of perception/adoption here as well. One of my
colleagues mentioned that at PG East that he repeatedly heard people
talking (negatively) about the over reliance on add-on packages to deal
with core DB functionality.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
@ 2010-07-09 15:09 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
1 sibling, 1 reply; 30+ messages in thread
From: Kevin Grittner @ 2010-07-09 15:09 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Brad Nicholson <bnichols@ca.afilias.info> wrote:
> Just like replication, pooling has different approaches. I do
> think that in both cases, having a solution that works, easily,
> out of the "box" will meet the needs of most users.
Any thoughts on the "minimalist" solution I suggested a couple weeks
ago?:
http://archives.postgresql.org/pgsql-hackers/2010-06/msg01385.php
http://archives.postgresql.org/pgsql-hackers/2010-06/msg01387.php
So far, there has been no comment by anyone....
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-07-09 15:30 ` Matthew Wakeling <matthew@flymine.org>
2010-07-09 15:49 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 30+ messages in thread
From: Matthew Wakeling @ 2010-07-09 15:30 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Fri, 9 Jul 2010, Kevin Grittner wrote:
> Any thoughts on the "minimalist" solution I suggested a couple weeks
> ago?:
>
> http://archives.postgresql.org/pgsql-hackers/2010-06/msg01385.php
> http://archives.postgresql.org/pgsql-hackers/2010-06/msg01387.php
>
> So far, there has been no comment by anyone....
Interesting idea. As far as I can see, you are suggesting solving the too
many connections problem by allowing lots of connections, but only
allowing a certain number to do anything at a time?
A proper connection pool provides the following advantages over this:
1. Pool can be on a separate machine or machines, spreading load.
2. Pool has a lightweight footprint per connection, whereas Postgres
doesn't.
3. A large amount of the overhead is sometimes connection setup, which
this would not solve. A pool has cheap setup.
4. This could cause Postgres backends to be holding onto large amounts of
memory while being prevented from doing anything, which is a bad use of
resources.
5. A fair amount of the overhead is caused by context-switching between
backends. The more backends, the less useful any CPU caches.
6. There are some internal workings of Postgres that involve keeping all
the backends informed about something going on. The more backends, the
greater this overhead is. (This was pretty bad with the sinval queue
overflowing a while back, but a bit better now. It still causes some
overhead).
7. That lock would have a metric *($!-load of contention.
Matthew
--
Unfortunately, university regulations probably prohibit me from eating
small children in front of the lecture class.
-- Computer Science Lecturer
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
@ 2010-07-09 15:49 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 17:03 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
0 siblings, 1 reply; 30+ messages in thread
From: Kevin Grittner @ 2010-07-09 15:49 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance; Tom Lane <tgl@sss.pgh.pa.us>
In case there's any doubt, the questions below aren't rhetorical.
Matthew Wakeling <matthew@flymine.org> wrote:
> Interesting idea. As far as I can see, you are suggesting solving
> the too many connections problem by allowing lots of connections,
> but only allowing a certain number to do anything at a time?
Right.
> A proper connection pool provides the following advantages over
> this:
>
> 1. Pool can be on a separate machine or machines, spreading load.
Sure, but how would you do that with a built-in implementation?
> 2. Pool has a lightweight footprint per connection, whereas
> Postgres doesn't.
I haven't compared footprint of, say, a pgpool connection on the
database server to that of an idle PostgreSQL connection. Do you
have any numbers?
> 3. A large amount of the overhead is sometimes connection setup,
> which this would not solve. A pool has cheap setup.
This would probably be most useful where the client held a
connection for a long time, not for the "login for each database
transaction" approach. I'm curious how often you think application
software uses that approach.
> 4. This could cause Postgres backends to be holding onto large
> amounts of memory while being prevented from doing anything,
> which is a bad use of resources.
Isn't this point 2 again? If not, what are you getting at? Again,
do you have numbers for the comparison, assuming the connection
pooler is running on the database server?
> 5. A fair amount of the overhead is caused by context-switching
> between backends. The more backends, the less useful any CPU
> caches.
Would this be true while a backend was blocked? Would this not be
true for a connection pool client-side connection?
> 6. There are some internal workings of Postgres that involve
> keeping all the backends informed about something going on. The
> more backends, the greater this overhead is. (This was pretty
> bad with the sinval queue overflowing a while back, but a bit
> better now. It still causes some overhead).
Hmmm... I hadn't thought about that. Again, any numbers (e.g.,
profile information) on this?
> 7. That lock would have a metric *($!-load of contention.
Here I doubt you. It would be held for such short periods that I
suspect that collisions would be relatively infrequent compared to
some of the other locks we use. As noted in the email, it may
actually normally be an "increment and test" within an existing
locked block. Also, assuming that any "built in" connection pool
would run on the database server, why would you think the contention
for this would be worse than for whatever is monitoring connection
count in the pooler?
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 15:49 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-07-09 17:03 ` Matthew Wakeling <matthew@flymine.org>
2010-07-09 17:52 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 22:59 ` Re: Need help in performance tuning. Greg Smith <greg@2ndquadrant.com>
0 siblings, 2 replies; 30+ messages in thread
From: Matthew Wakeling @ 2010-07-09 17:03 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
On Fri, 9 Jul 2010, Kevin Grittner wrote:
>> Interesting idea. As far as I can see, you are suggesting solving
>> the too many connections problem by allowing lots of connections,
>> but only allowing a certain number to do anything at a time?
>
> Right.
I think in some situations, this arrangement would be an advantage.
However, I do not think it will suit the majority of situations, and could
reduce the performance when the user doesn't need the functionality,
either because they have a pool already, or they don't have many
connections.
No, I don't have any numbers.
>> 1. Pool can be on a separate machine or machines, spreading load.
>
> Sure, but how would you do that with a built-in implementation?
That's my point exactly. If you have an external pool solution, you can
put it somewhere else - maybe on multiple somewhere elses.
>> 3. A large amount of the overhead is sometimes connection setup,
>> which this would not solve. A pool has cheap setup.
>
> This would probably be most useful where the client held a
> connection for a long time, not for the "login for each database
> transaction" approach. I'm curious how often you think application
> software uses that approach.
What you say is true. I don't know how often that is, but it seems to be
those times that people come crying to the mailing list.
>> 4. This could cause Postgres backends to be holding onto large
>> amounts of memory while being prevented from doing anything,
>> which is a bad use of resources.
>
> Isn't this point 2 again?
Kind of. Yes. Point 2 was simple overhead. This point was that the backend
may have done a load of query-related allocation, and then been stopped.
>> 7. That lock would have a metric *($!-load of contention.
>
> Here I doubt you. It would be held for such short periods that I
> suspect that collisions would be relatively infrequent compared to
> some of the other locks we use. As noted in the email, it may
> actually normally be an "increment and test" within an existing
> locked block.
Fair enough. It may be much less of a problem than I had previously
thought.
Matthew
--
Change is inevitable, except from vending machines.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 15:49 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 17:03 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
@ 2010-07-09 17:52 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 19:27 ` Re: Need help in performance tuning. Jorge Montero <jorge_montero@homedecorators.com>
1 sibling, 1 reply; 30+ messages in thread
From: Kevin Grittner @ 2010-07-09 17:52 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance; Tom Lane <tgl@sss.pgh.pa.us>
Matthew Wakeling <matthew@flymine.org> wrote:
> On Fri, 9 Jul 2010, Kevin Grittner wrote:
>>> Interesting idea. As far as I can see, you are suggesting
>>> solving the too many connections problem by allowing lots of
>>> connections, but only allowing a certain number to do anything
>>> at a time?
>>
>> Right.
>
> I think in some situations, this arrangement would be an
> advantage. However, I do not think it will suit the majority of
> situations, and could reduce the performance when the user doesn't
> need the functionality, either because they have a pool already,
> or they don't have many connections.
Oh, totally agreed, except that I think we can have essentially nil
impact if they don't exceed a configured limit. In my experience,
pooling is more effective the closer you put it to the client. I
suppose the strongest argument that could be made against building
in some sort of pooling is that it doesn't encourage people to look
for client-side solutions. However, we seem to get a lot of posts
from people who don't do this, are not able to easily manage it, and
who would benefit from even a simple solution like this.
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 15:49 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 17:03 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 17:52 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-07-09 19:27 ` Jorge Montero <jorge_montero@homedecorators.com>
2010-07-09 19:31 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 30+ messages in thread
From: Jorge Montero @ 2010-07-09 19:27 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
If anything was built in the database to handle such connections, I'd recommend a big, bold warning, recommending the use of client-side pooling if available. For something like, say, a web-server, pooling connections to the database provides a massive performance advantage regardless of how good the database is at handling way more active queries than the hardware can handle: The assignment of a connection to a thread tends to be at least an order of magnitude cheaper than establishing a new connection for each new thread, and destroying it when it dies. This is especially true if the client architecture relies in relatively short lived threads.
While there are a few cases where pooling is counter productive, this only happens in relatively few scenarios. This is why every java application server out there wil strongly recommend using its own facilities to connect to a database: The performance is almost always better, and it provides less headaches to the DBAs.
Now, if remote clients are accessing your database directly, setting up a pool inbetween might not be as straightforward or give you the same gains across the board, and that might be the only case where letting the db do its own pooling makes sense.
>>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> 7/9/2010 12:52 PM >>>
Matthew Wakeling <matthew@flymine.org> wrote:
> On Fri, 9 Jul 2010, Kevin Grittner wrote:
>>> Interesting idea. As far as I can see, you are suggesting
>>> solving the too many connections problem by allowing lots of
>>> connections, but only allowing a certain number to do anything
>>> at a time?
>>
>> Right.
>
> I think in some situations, this arrangement would be an
> advantage. However, I do not think it will suit the majority of
> situations, and could reduce the performance when the user doesn't
> need the functionality, either because they have a pool already,
> or they don't have many connections.
Oh, totally agreed, except that I think we can have essentially nil
impact if they don't exceed a configured limit. In my experience,
pooling is more effective the closer you put it to the client. I
suppose the strongest argument that could be made against building
in some sort of pooling is that it doesn't encourage people to look
for client-side solutions. However, we seem to get a lot of posts
from people who don't do this, are not able to easily manage it, and
who would benefit from even a simple solution like this.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 15:49 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 17:03 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 17:52 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 19:27 ` Re: Need help in performance tuning. Jorge Montero <jorge_montero@homedecorators.com>
@ 2010-07-09 19:31 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 30+ messages in thread
From: Kevin Grittner @ 2010-07-09 19:31 UTC (permalink / raw)
To: Jorge Montero <jorge_montero@homedecorators.com>; +Cc: pgsql-performance
"Jorge Montero" <jorge_montero@homedecorators.com> wrote:
> If anything was built in the database to handle such connections,
> I'd recommend a big, bold warning, recommending the use of client-
> side pooling if available.
+1
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 15:49 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 17:03 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
@ 2010-07-09 22:59 ` Greg Smith <greg@2ndquadrant.com>
2010-07-10 00:31 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-10 06:33 ` Re: Need help in performance tuning. Jesper Krogh <jesper@krogh.cc>
1 sibling, 2 replies; 30+ messages in thread
From: Greg Smith @ 2010-07-09 22:59 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Matthew Wakeling wrote:
> If you have an external pool solution, you can put it somewhere else -
> maybe on multiple somewhere elses.
This is the key point to observe: if you're at the point where you have
so many connections that you need a pool, the last place you want to put
that is on the overloaded database server itself. Therefore, it must be
an external piece of software to be effective, rather than being part of
the server itself. Database servers are relatively expensive computing
hardware due to size/quantity/quality of disks required. You can throw
a pooler (or poolers) on any cheap 1U server. This is why a built-in
pooler, while interesting, is not particularly functional for how people
normally scale up real-world deployments.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 15:49 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 17:03 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 22:59 ` Re: Need help in performance tuning. Greg Smith <greg@2ndquadrant.com>
@ 2010-07-10 00:31 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 30+ messages in thread
From: Kevin Grittner @ 2010-07-10 00:31 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; Matthew Wakeling <matthew@flymine.org>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance; Tom Lane <tgl@sss.pgh.pa.us>
Greg Smith <greg@2ndquadrant.com> wrote:
> if you're at the point where you have so many connections that you
> need a pool, the last place you want to put that is on the
> overloaded database server itself. Therefore, it must be an
> external piece of software to be effective, rather than being part
> of the server itself.
It *is* the last place you want to put it, but putting it there can
be much better than not putting it *anywhere*, which is what we've
often seen.
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 15:49 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 17:03 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 22:59 ` Re: Need help in performance tuning. Greg Smith <greg@2ndquadrant.com>
@ 2010-07-10 06:33 ` Jesper Krogh <jesper@krogh.cc>
2010-07-10 12:30 ` Re: Need help in performance tuning. Greg Smith <greg@2ndquadrant.com>
1 sibling, 1 reply; 30+ messages in thread
From: Jesper Krogh @ 2010-07-10 06:33 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Matthew Wakeling <matthew@flymine.org>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
On 2010-07-10 00:59, Greg Smith wrote:
> Matthew Wakeling wrote:
> > If you have an external pool solution, you can put it somewhere
> > else - maybe on multiple somewhere elses.
>
> This is the key point to observe: if you're at the point where you
> have so many connections that you need a pool, the last place you
> want to put that is on the overloaded database server itself.
> Therefore, it must be an external piece of software to be effective,
> rather than being part of the server itself. Database servers are
> relatively expensive computing hardware due to size/quantity/quality
> of disks required. You can throw a pooler (or poolers) on any cheap
> 1U server. This is why a built-in pooler, while interesting, is not
> particularly functional for how people normally scale up real-world
> deployments.
That may be totally correct for the 10% of the userbase
that are in a squeezed situation, but for the 90% that isn't (or isn't aware
of being there), the build-in would be a direct benefit. For the 20%
living near the "edge" it may be the difference between "just working" and
extra hassle.
I think it is a fair assumption that the majority of PG's users solves
the problems without an connection pooler, and the question
is if it is beneficial to let them scale better without doing anything?
I have also provided a case where Kevin proposal "might" be a
benefit but a connection pooler cannot solve it:
http://archives.postgresql.org/pgsql-hackers/2010-06/msg01438.php
(at least as I see it, but I'm fully aware that there is stuff I dont
know of)
I dont think a build-in connection-poller (or similiar) would in any
way limit the actions and abillities of an external one?
* Both numbers wildly guessed..
--
Jesper
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 15:49 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 17:03 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 22:59 ` Re: Need help in performance tuning. Greg Smith <greg@2ndquadrant.com>
2010-07-10 06:33 ` Re: Need help in performance tuning. Jesper Krogh <jesper@krogh.cc>
@ 2010-07-10 12:30 ` Greg Smith <greg@2ndquadrant.com>
2010-07-11 22:47 ` Re: Need help in performance tuning. Pierre C <lists@peufeu.com>
0 siblings, 1 reply; 30+ messages in thread
From: Greg Smith @ 2010-07-10 12:30 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: Matthew Wakeling <matthew@flymine.org>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Jesper Krogh wrote:
> I dont think a build-in connection-poller (or similiar) would in any
> way limit the actions and abillities of an external one?
Two problems to recognize. First is that building something in has the
potential to significantly limit use and therefore advancement of work
on external pools, because of the "let's use the built in one instead of
installing something extra" mentality. I'd rather have a great external
project (which is what we have with pgBouncer) than a mediocre built-in
one that becomes the preferred way just by nature of being in the core.
If work on a core pooler was started right now, it would be years before
that reached feature/performance parity, and during that time its
existence would be a net loss compared to the current status quo for
many who used it.
The second problem is the limited amount of resources to work on
improvements to PostgreSQL. If you want to improve the reach of
PostgreSQL, I consider projects like materialized views and easy
built-in partitioning to be orders of magnitude more useful things to
work on than the marginal benefit of merging the features of the
external pool software inside the database. I consider the whole topic
a bit of a distraction compared to instead working on *any* of the
highly rated ideas at http://postgresql.uservoice.com/forums/21853-general
As a random technical note, I would recommend that anyone who is
thinking about a pooler in core take a look at how pgBouncer uses
libevent to respond to requests, a design model inspired by that of
memcached. I haven't looked at it deeply yet, but my gut guess is that
this proven successful model would be hard to graft on top of the
existing PostgreSQL process design, and doing anything but that is
unlikely to perform as well.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 15:49 ` Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 17:03 ` Re: Need help in performance tuning. Matthew Wakeling <matthew@flymine.org>
2010-07-09 22:59 ` Re: Need help in performance tuning. Greg Smith <greg@2ndquadrant.com>
2010-07-10 06:33 ` Re: Need help in performance tuning. Jesper Krogh <jesper@krogh.cc>
2010-07-10 12:30 ` Re: Need help in performance tuning. Greg Smith <greg@2ndquadrant.com>
@ 2010-07-11 22:47 ` Pierre C <lists@peufeu.com>
0 siblings, 0 replies; 30+ messages in thread
From: Pierre C @ 2010-07-11 22:47 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; Jesper Krogh <jesper@krogh.cc>; +Cc: Matthew Wakeling <matthew@flymine.org>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance@postgresql.org, "Tom Lane" <tgl@sss.pgh.pa.us>
> Two problems to recognize. First is that building something in has the
> potential to significantly limit use and therefore advancement of work
> on external pools, because of the "let's use the built in one instead of
> installing something extra" mentality. I'd rather have a great external
> project (which is what we have with pgBouncer) than a mediocre built-in
> one that becomes the preferred way just by nature of being in the core.
I would prefer having supplier A build a great product that seamlessly
interfaces with supplier B's great product, rather than having supplier M$
buy A, develop a half-working brain-dead version of B into A and market it
as the new hot stuff, sinking B in the process. Anyway, orthogonal feature
sets (like database and pooler) implemented in separate applications fit
the open source development model quite well I think. Merge everything in,
you get PHP.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 12:56 ` Re: Need help in performance tuning. Brad Nicholson <bnichols@ca.afilias.info>
@ 2010-07-10 01:01 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
1 sibling, 0 replies; 30+ messages in thread
From: Mark Kirkwood @ 2010-07-10 01:01 UTC (permalink / raw)
To: pgsql-performance
On 10/07/10 00:56, Brad Nicholson wrote:
> On Fri, 2010-07-09 at 00:42 -0400, Tom Lane wrote:
>
>>
>> Perhaps not, but there's no obvious benefit either. Since there's
>> More Than One Way To Do It, it seems more practical to keep that as a
>> separate problem that can be solved by a choice of add-on packages.
>>
> This sounds similar to the approach to taken with Replication for years
> before being moved into core.
>
> Just like replication, pooling has different approaches. I do think
> that in both cases, having a solution that works, easily, out of the
> "box" will meet the needs of most users.
>
> There is also the issue of perception/adoption here as well. One of my
> colleagues mentioned that at PG East that he repeatedly heard people
> talking (negatively) about the over reliance on add-on packages to deal
> with core DB functionality.
>
>
It would be interesting to know more about what they thought an 'over
reliance' was and which packages they meant.
While clearly in the case of replication something needed to be done to
make it better and easier, it is not obvious that the situation with
connection pools is analogous. For instance we make extensive use of
PgBouncer, and it seems to do the job fine and is ridiculously easy to
install and setup. So would having (something like) this in core be an
improvement? Clearly if the 'in core' product is better then it is
desirable... similarly if the packaged product is better... well let's
have that then!
I've certainly observed a 'fear of package installation' on the part of
some folk, which is often a hangover from the 'Big IT shop' mentality
where it requires blood signatures and child sacrifice to get anything
new installed.
regards
Mark
P.s Also note that Database Vendors like pooling integrated in the core
of *their* product because it is another thing to charge a license for.
Unfortunately this can also become an entrenched mentality of 'must be
in core' on the part of consultants etc!
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Re: Need help in performance tuning. Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Re: Need help in performance tuning. Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Re: Need help in performance tuning. Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-07-09 20:35 ` Robert Haas <robertmhaas@gmail.com>
3 siblings, 0 replies; 30+ messages in thread
From: Robert Haas @ 2010-07-09 20:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Samuel Gendler <sgendler@ideasculptor.com>; pgsql-performance
On Fri, Jul 9, 2010 at 12:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Samuel Gendler <sgendler@ideasculptor.com> writes:
>> On Thu, Jul 8, 2010 at 8:11 PM, Craig Ringer
>> <craig@postnewspapers.com.au> wrote:
>>> If you're not using a connection pool, start using one.
>
>> I see this issue and subsequent advice cross this list awfully
>> frequently. Is there in architectural reason why postgres itself
>> cannot pool incoming connections in order to eliminate the requirement
>> for an external pool?
>
> Perhaps not, but there's no obvious benefit either. Since there's
> More Than One Way To Do It, it seems more practical to keep that as a
> separate problem that can be solved by a choice of add-on packages.
I'm not buying it. A separate connection pooler increases overhead
and management complexity, and, I believe, limits our ability to
implement optimizations like parallel query execution. I'm glad there
are good ones available, but the fact that they're absolutely
necessary for good performance in some environments is not a feature.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Need help in performance tuning.
@ 2010-07-08 20:22 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 30+ messages in thread
From: Kevin Grittner @ 2010-07-08 20:22 UTC (permalink / raw)
To: Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>; +Cc: pgsql-performance
Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com> wrote:
> I want to fine tune my postgresql to increase number of connects
> it can handle in a minutes time.
> Decrease the response time per request etc.
> The exact case will be to handle around 100 concurrent requests.
I have found that connection pooling is crucial.
The "concurrent requests" phrase worries me a bit -- you should be
focusing more on "concurrent connections" and perhaps "requests per
second". With most hardware, you will get faster response time and
better overall throughput by funneling 100 connections through a
connection pool which limits the number of concurrent requests to
just enough to keep all your hardware resources busy, queuing any
requests beyond that for submission when a pending request
completes.
> Any hardware suggestions are also welcomed.
If you don't have the hardware yet, you'd need to provide a bit more
information to get advice on what hardware you need.
-Kevin
^ permalink raw reply [nested|flat] 30+ messages in thread
end of thread, other threads:[~2010-07-14 14:54 UTC | newest]
Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-08 19:20 Need help in performance tuning. Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 03:11 ` Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 04:19 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-07-09 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-07-09 05:48 ` Hannu Krosing <hannu@2ndquadrant.com>
2010-07-14 06:44 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-07-14 13:58 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-14 14:54 ` Hannu Krosing <hannu@2ndquadrant.com>
2010-07-09 07:52 ` Craig Ringer <craig@postnewspapers.com.au>
2010-07-09 08:55 ` Harpreet singh Wadhwa <harpreet@openbravo.com>
2010-07-09 16:44 ` Jorge Montero <jorge_montero@homedecorators.com>
2010-07-09 09:31 ` Harpreet singh Wadhwa <harpreetsingh.wadhwa@gmail.com>
2010-07-09 09:53 ` Craig Ringer <craig@postnewspapers.com.au>
2010-07-12 16:04 ` Bruce Momjian <bruce@momjian.us>
2010-07-09 12:56 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-07-09 15:09 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 15:30 ` Matthew Wakeling <matthew@flymine.org>
2010-07-09 15:49 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 17:03 ` Matthew Wakeling <matthew@flymine.org>
2010-07-09 17:52 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 19:27 ` Jorge Montero <jorge_montero@homedecorators.com>
2010-07-09 19:31 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-09 22:59 ` Greg Smith <greg@2ndquadrant.com>
2010-07-10 00:31 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-10 06:33 ` Jesper Krogh <jesper@krogh.cc>
2010-07-10 12:30 ` Greg Smith <greg@2ndquadrant.com>
2010-07-11 22:47 ` Pierre C <lists@peufeu.com>
2010-07-10 01:01 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-07-09 20:35 ` Robert Haas <robertmhaas@gmail.com>
2010-07-08 20:22 Re: Need help in performance tuning. Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox