Re: Scalability in postgres

On Fri, 29 May 2009, Fabrix wrote:

> In this application is not closing the connection, the development team
> is makeing the change for close the connection after getting the job
> done. So most connections are in idle state.  How much would this help?
> Does this could be the real problem?

Ah, now you're getting somewhere. This is actually another subtle problem
with making max_connections really high. It lets developers get away with
being sloppy in ways that waste large amount of server resources.

Fix that problem, re-test, and then think about changing other things.
There's no need to go through kernel changes and the lot if you can nail
the problem at its true source and get acceptable performance.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>From pgsql-performance-owner(at)postgresql(dot)org Fri May 29 15:20:34 2009
Received: from localhost (unknown [200.46.208.211])
by mail.postgresql.org (Postfix) with ESMTP id 63EBD634654
for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>; Fri, 29 May 2009 15:20:34 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
with ESMTP id 08632-08
for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>;
Fri, 29 May 2009 15:20:28 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from exprod7og108.obsmtp.com (exprod7og108.obsmtp.com [64.18.2.169])
by mail.postgresql.org (Postfix) with SMTP id 9A950634652
for <pgsql-performance(at)postgresql(dot)org>; Fri, 29 May 2009 15:20:30 -0300 (ADT)
Received: from source ([209.85.222.202]) by exprod7ob108.postini.com ([64.18.6.12]) with SMTP
ID DSNKSiAnbUx3KBAdbfe5nDESpqnrHLTqD++I(at)postini(dot)com; Fri, 29 May 2009 11:20:30 PDT
Received: by pzk40 with SMTP id 40so5537265pzk.22
for <pgsql-performance(at)postgresql(dot)org>; Fri, 29 May 2009 11:20:29 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.142.43.7 with SMTP id q7mr902169wfq.160.1243621228997; Fri, 29
May 2009 11:20:28 -0700 (PDT)
In-Reply-To: <alpine(dot)GSO(dot)2(dot)01(dot)0905291316010(dot)10873(at)westnet(dot)com>
References: <10004459(dot)141243560273700(dot)JavaMail(dot)flavio(at)presente>
<alpine(dot)GSO(dot)2(dot)01(dot)0905282132090(dot)14027(at)westnet(dot)com>
<2f4958ff0905290345p56625f6fsef719f134559a7a0(at)mail(dot)gmail(dot)com>
<alpine(dot)GSO(dot)2(dot)01(dot)0905291316010(dot)10873(at)westnet(dot)com>
Date: Fri, 29 May 2009 14:20:28 -0400
Message-ID: <d3ab2ec80905291120u370baf12je569e70a1c1e7467(at)mail(dot)gmail(dot)com>
Subject: Re: Scalability in postgres
From: Scott Mead <scott(dot)lists(at)enterprisedb(dot)com>
To: Greg Smith <gsmith(at)gregsmith(dot)com>
Cc: =?ISO-8859-2?Q?Grzegorz_Ja=B6kiewicz?= <gryzman(at)gmail(dot)com>,
Flavio Henrique Araque Gurgel <flavio(at)4linux(dot)com(dot)br>, Fabrix <fabrixio1(at)gmail(dot)com>,
pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Content-Type: multipart/alternative; boundary=000e0cd3047096ed9b046b1121ac
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200905/431
X-Sequence-Number: 34218

--000e0cd3047096ed9b046b1121ac
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

2009/5/29 Greg Smith <gsmith(at)gregsmith(dot)com>

> On Fri, 29 May 2009, Grzegorz Ja?kiewicz wrote:
>
> if it is implemented somewhere else better, shouldn't that make it
>> obvious that postgresql should solve it internally ?
>>
>
> Opening a database connection has some overhead to it that can't go away
> without losing *something* in the process that you want the database to
> handle. That something usually impacts either security or crash-safety.
> This is why every serious database product in the world suggests using
> connection pooling; examples:
>
> http://blogs.oracle.com/opal/2006/10/oracle_announces_new_connectio.html
>
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/conn/c0006170.htm
> http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
>
> http://dev.mysql.com/tech-resources/articles/connection_pooling_with_connectorj.html
>

Exactly, here's the thing, if you have an open transaction somewhere to
the system, there may be a REALLY good reason for it. If you're app or dev
team is keeping those open, it's very possible that 'reaping' them is going
to cause some kind of data integrity issue in your database. I would
investigate the application and make sure that everything is actually
rolling back or commiting. If you're using an ORM, make sure that it's
using autocommit, this usually makes the issue go away.
As to the context switching point -- A connection pooler is what you need.
Why make your database server dedicate cycles to having to figure out who
gets on the CPU next? Why not lower the number of connections, and let a
connection pool decide what to use. That usually helps with your open
transactions too (if they indeed are just abandoned by the application).

>
> The only difference here is that some of the commercial products bundle the
> connection pooler into the main program. In most cases, you're still stuck
> with configuring a second piece of software, the only difference is that
> said software might already be installed for you by the big DB installer.
> Since this project isn't in the business of bundling every piece of
> additional software that might be useful with the database, it's never going
> to make it into the core code when it works quite happily outside of it.
> The best you could hope for is that people who bundle large chunks of other
> stuff along with their PostgreSQL installer, like Enterprise DB does, might
> include one of the popular poolers one day.

This sounds like a dirty plug (sorry sorry sorry, it's for informative
purposes only)...

Open Source:

One-Click installers : No connection pool bundled (will be
included in 8.4 one-click installers)
PostgresPlus Standard Edition : pgBouncer is bundled

Proprietary:

PostgresPlus Advanced Server: pgBouncer is bundled

That being said, the well known connection pools for postgres are pretty
small and easy to download / build / configure and get up and running.

https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer
http://pgfoundry.org/projects/pgpool/

--Scott

--000e0cd3047096ed9b046b1121ac
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote">2009/5/29 Greg Smith <span dir=3D"ltr">&lt;<a hr=
ef=3D"mailto:gsmith(at)gregsmith(dot)com">gsmith(at)gregsmith(dot)com</a>&gt;</span><br><=
blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px=
#ccc solid;padding-left:1ex;">
<div class=3D"im">On Fri, 29 May 2009, Grzegorz Ja?kiewicz wrote:<br>
<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
if it is implemented somewhere else better, shouldn&#39;t that make it<br>
obvious that postgresql should solve it internally ?<br>
</blockquote>
<br></div>
Opening a database connection has some overhead to it that can&#39;t go awa=
y without losing *something* in the process that you want the database to h=
andle. =A0That something usually impacts either security or crash-safety. T=
his is why every serious database product in the world suggests using conne=
ction pooling; examples:<br>

<br>
<a href=3D"http://blogs.oracle.com/opal/2006/10/oracle_announces_new_connec=
tio.html" target=3D"_blank">http://blogs.oracle.com/opal/2006/10/oracle_ann=
ounces_new_connectio.html</a><br>
<a href=3D"http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?top=
ic=3D/com.ibm.db2.udb.doc/conn/c0006170.htm" target=3D"_blank">http://publi=
b.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=3D/com.ibm.db2.udb.d=
oc/conn/c0006170.htm</a><br>

<a href=3D"http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx" target=3D=
"_blank">http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx</a><br>
<a href=3D"http://dev.mysql.com/tech-resources/articles/connection_pooling_=
with_connectorj.html" target=3D"_blank">http://dev.mysql.com/tech-resources=
/articles/connection_pooling_with_connectorj.html</a><br></blockquote><div>
<br></div><div><br></div><div><br></div><span class=3D"Apple-style-span" st=
yle=3D"border-collapse: collapse; color: rgb(68, 68, 68); "><div>=A0=A0Exac=
tly, here&#39;s the thing, if you have an open transaction somewhere to the=
system, there may be a REALLY good reason for it. =A0If you&#39;re app or =
dev team is keeping those open, it&#39;s very possible that &#39;reaping&#3=
9; them is going to cause some kind of data integrity issue in your databas=
e. =A0I would investigate the application and make sure that everything is =
actually rolling back or commiting. =A0If you&#39;re using an ORM, make sur=
e that it&#39;s using autocommit, this usually makes the issue go away.</di=
v>
</span><div><span class=3D"Apple-style-span" style=3D"border-collapse: coll=
apse; color: rgb(68, 68, 68); ">=A0As to the context switching point -- A c=
onnection pooler is what you need. =A0Why make your database server dedicat=
e cycles to having to figure out who gets on the CPU next? =A0Why not lower=
the number of connections, and let a connection pool decide what to use. =
=A0That usually helps with your open transactions too (if they indeed are j=
ust abandoned by the application). =A0</span></div>
<div><br></div><div>=A0</div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<br>
The only difference here is that some of the commercial products bundle the=
connection pooler into the main program. =A0In most cases, you&#39;re stil=
l stuck with configuring a second piece of software, the only difference is=
that said software might already be installed for you by the big DB instal=
ler. Since this project isn&#39;t in the business of bundling every piece o=
f additional software that might be useful with the database, it&#39;s neve=
r going to make it into the core code when it works quite happily outside o=
f it. =A0The best you could hope for is that people who bundle large chunks=
of other stuff along with their PostgreSQL installer, like Enterprise DB d=
oes, might include one of the popular poolers one day.</blockquote>
<div><br></div><span class=3D"Apple-style-span" style=3D"border-collapse: c=
ollapse; color: rgb(68, 68, 68); "><div>=A0This sounds like a dirty plug (s=
orry sorry sorry, it&#39;s for informative purposes only)...=A0</div><div><=
br></div>
<div>Open Source:</div><div>=A0=A0</div><div>=A0=A0 =A0 =A0One-Click instal=
lers : =A0 =A0No connection pool bundled =A0(will be included in 8.4 one-cl=
ick installers)</div><div>=A0=A0 =A0 =A0PostgresPlus Standard Edition : =A0=
pgBouncer is bundled</div>
<div><br></div><div>Proprietary:</div><div><br></div><div>=A0=A0 =A0 =A0Pos=
tgresPlus Advanced Server: pgBouncer is bundled</div><div><br></div><div>=
=A0=A0That being said, the well known connection pools for postgres are pre=
tty small and easy to download / build / configure and get up and running.<=
/div>
<div><br></div><div><a href=3D"https://developer.skype.com/SkypeGarage/DbPr=
ojects/PgBouncer" target=3D"_blank" style=3D"color: rgb(34, 34, 34); ">http=
s://developer.skype.com/SkypeGarage/DbProjects/PgBouncer</a></div></span><d=
iv>
<span class=3D"Apple-style-span" style=3D"border-collapse: collapse; color:=
rgb(68, 68, 68); "><a href=3D"http://pgfoundry.org/projects/pgpool/" targe=
t=3D"_blank" style=3D"color: rgb(34, 34, 34); ">http://pgfoundry.org/projec=
ts/pgpool/</a></span>=A0</div>
<div><br></div><div>--Scott</div></div><br>

--000e0cd3047096ed9b046b1121ac--