Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 98C0B6340F7 for ; Fri, 9 Jul 2010 13:54:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 52415-04 for ; Fri, 9 Jul 2010 16:54:10 +0000 (UTC) X-Greylist: delayed 00:09:14.479713 by SQLgrey-1.7.6 Received: from bill.kwcorp.com (mailx.kwcorp.com [65.118.99.196]) by mail.postgresql.org (Postfix) with ESMTP id 3201A632510 for ; Fri, 9 Jul 2010 13:54:10 -0300 (ADT) X-TM-IMSS-Message-ID: <7cea702a000bb033@kwcorp.com> Received: from gw.homedecorators.com ([172.23.217.142]) by kwcorp.com ([192.168.85.25]) with ESMTP (TREND IMSS SMTP Service 7.0) id 7cea702a000bb033 ; Fri, 9 Jul 2010 11:44:49 -0500 Received: from HDC_MTA-MTA by gw.homedecorators.com with Novell_GroupWise; Fri, 09 Jul 2010 11:44:53 -0500 Message-Id: <4C370B8C.2E1C.0042.0@homedecorators.com> X-Mailer: Novell GroupWise Internet Agent 7.0.2 HP Date: Fri, 09 Jul 2010 11:44:12 -0500 From: "Jorge Montero" To: "Harpreet singh Wadhwa" Cc: Subject: Re: Need help in performance tuning. References: <4C369346.6010501@postnewspapers.com.au> <8760.1278650566@sss.pgh.pa.us> <4C36D537.1090709@postnewspapers.com.au> In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part8EA3F8CC.1__=" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201007/155 X-Sequence-Number: 39544 This is a MIME message. If you are reading this text, you may want to consider changing to a mail reader or gateway that understands how to properly handle MIME multipart messages. --=__Part8EA3F8CC.1__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable 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. =20 >>> Harpreet singh Wadhwa 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 wrote: > On 09/07/10 12:42, Tom Lane wrote: >> Samuel Gendler writes: >>> On Thu, Jul 8, 2010 at 8:11 PM, Craig Ringer >>> 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=20 > --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance --=__Part8EA3F8CC.1__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
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&= gt; 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@postnewspaper= s.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@post= newspapers.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
&= gt;>> 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.
>
&= gt; 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.o= rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-perf= ormance
--=__Part8EA3F8CC.1__=--