Re: How to handle missing pgsql.connect_timeout

Hi,

i use persitent connections and limit them for example to
pgsql.max_persistent = 32

if all connections are in use a new http call to my script's pg_pconnect
results in waiting for a free slot. That's fine, because usually a slot is
getting free within a second.

But if my server is much more overloaded and it would take more than 5 seconds
to wait for a free slot it doesnt make sense to wait, because the user would
hit stop anyway without getting a serious error message.

it would be nice to have a timeout value after which pg_pconnect returns with
an error, so could show a nice error page telling the user to try again in a
few minutes.

i found the php directive mysql.connect_timeout which does exactly what i
want. There is no such directive for pgsql.

I tried pgpool searching in pgpool for a connection timeout directive, but i
didn't found one.

It would be so nice to have a timeout of 3 seconds and after it display an
error message to the user instead of let him waiting and waiting.

Does anybody has any hints to solve this problem?

kind regards
janning

On Tue, 2005-02-01 at 09:38 +0100, Janning Vygen wrote:
> Does anybody has any hints to solve this problem?

This is a bit of a non-solution, but it is what worked for us. We
simply stopped using pconnect() altogether. This seemed to have solved
connection timeouts and delays. Our site gets 500k page hits per day
without any problems.

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017

Am Dienstag, 1. Februar 2005 14:18 schrieben Sie:
> On Tue, 2005-02-01 at 09:38 +0100, Janning Vygen wrote:
> > Does anybody has any hints to solve this problem?
>
> This is a bit of a non-solution, but it is what worked for us. We
> simply stopped using pconnect() altogether. This seemed to have solved
> connection timeouts and delays. Our site gets 500k page hits per day
> without any problems.

hm. i didn't get the point. Do you mean you don't use persistent connections
just plain pg_connect instead of pg_pconnect??

kind regards,
janning

On Tue, 2005-02-01 at 17:40 +0100, Janning Vygen wrote:
> Am Dienstag, 1. Februar 2005 14:18 schrieben Sie:
> > On Tue, 2005-02-01 at 09:38 +0100, Janning Vygen wrote:
> > > Does anybody has any hints to solve this problem?
> >
> > This is a bit of a non-solution, but it is what worked for us. We
> > simply stopped using pconnect() altogether. This seemed to have solved
> > connection timeouts and delays. Our site gets 500k page hits per day
> > without any problems.
>
> hm. i didn't get the point. Do you mean you don't use persistent connections
> just plain pg_connect instead of pg_pconnect??

Exactly. pg_pconnect() is (or was the last time I tried it) broken in
PHP. We were constantly getting connections that wouldn't close, and
sometimes not time out. Ever since we gave up on them, we've had no
problems.

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017

Am Dienstag, 1. Februar 2005 18:10 schrieb Christopher Murtagh:
> On Tue, 2005-02-01 at 17:40 +0100, Janning Vygen wrote:
> > Am Dienstag, 1. Februar 2005 14:18 schrieben Sie:
> > > On Tue, 2005-02-01 at 09:38 +0100, Janning Vygen wrote:
> > > > Does anybody has any hints to solve this problem?
> > >
> > > This is a bit of a non-solution, but it is what worked for us. We
> > > simply stopped using pconnect() altogether. This seemed to have solved
> > > connection timeouts and delays. Our site gets 500k page hits per day
> > > without any problems.
> >
> > hm. i didn't get the point. Do you mean you don't use persistent
> > connections just plain pg_connect instead of pg_pconnect??
>
> Exactly. pg_pconnect() is (or was the last time I tried it) broken in
> PHP.

It's not broken anymore! And it speeds up your website.

> We were constantly getting connections that wouldn't close, and
> sometimes not time out. Ever since we gave up on them, we've had no
> problems.

Thats not exactly my problem. I just want to have say

20 MaxClients to wait for http Connections
but only 5 possible DB Connections

I http server no. 6 tries to connect i want pg_pconnect (or pg_connect; that
doesn't matter) to wait maximal 1 second and then return an error.

At the moment it waits "forever" (until max_execution_time i guess) and you
have no chance to show an error message like: DB is overloaded, please try
again.

php_pgsql module doesn't have a directive like mysql which is
mysql_connection_timeout.

i tried pgpool but it doesn't have a timeout value for establishing a
connection.

That's my problem. My DB is overloaded once a week to a special peek time and
i can't afford more hardware. I just want a simple and kind error message.

kind regards
janning

On Wed, 2005-02-02 at 09:49 +0100, Janning Vygen wrote:
> It's not broken anymore! And it speeds up your website.

Well it might not be broken anymore, but I haven't seen any evidence of
a speed increase (quite the opposite in fact). What we did instead was
write a very small abstraction layer so that any page request would only
use 1 database connection. We're running on dual 2.8 GHz Xeons for our
web server and db machine and taking on over 500,000 hits per day. Some
of our pages are very data intensive (http://www.mcgill.ca/calendar/ for
example).

> At the moment it waits "forever" (until max_execution_time i guess) and you
> have no chance to show an error message like: DB is overloaded, please try
> again.

maybe pg_pconnect() isn't all it is cracked up to be? Anyone else here
have good experiences with it?

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017

on 2/2/05 1:49 AM, vygen(at)planwerk6(dot)de purportedly said:

> It's not broken anymore! And it speeds up your website.

This is true, insofar as you avoid the connection overhead, but in my
experience if your DB is local (i.e. on the same machine as your web site),
the difference amounts to fractions of a second. There are also many
pitfalls to using persistent connections, including greater overhead since
you are far more likely to have idle connections, unless every single HTTP
call includes a database call--and I mean *every* call: images, css,
scripts, etc.

You may also want to set pgsql.auto_reset_persistent to handle broken
persistent links.

>> We were constantly getting connections that wouldn't close, and
>> sometimes not time out. Ever since we gave up on them, we've had no
>> problems.
>
> Thats not exactly my problem. I just want to have say
>
> 20 MaxClients to wait for http Connections
> but only 5 possible DB Connections

In this case, you probably don't want persistent connections at all. To use
persistent connections properly under moderate loads your max clients and
max db connections should be the same, or you will definitely have idle
(i.e. unused) db connections.

> I http server no. 6 tries to connect i want pg_pconnect (or pg_connect; that
> doesn't matter) to wait maximal 1 second and then return an error.

AFAIK, if your max_connections setting in the PG server is 5, the 6th
connection should return with an immediate error. Perhaps this has changed
since I experienced it last--I will leave confirmation up to those who know
PG better.

Best regards,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"

On Wed, 2 Feb 2005, Christopher Murtagh wrote:

> On Wed, 2005-02-02 at 09:49 +0100, Janning Vygen wrote:
>> It's not broken anymore! And it speeds up your website.
>
> Well it might not be broken anymore, but I haven't seen any evidence of
> a speed increase (quite the opposite in fact). What we did instead was
> write a very small abstraction layer so that any page request would only
> use 1 database connection. We're running on dual 2.8 GHz Xeons for our
> web server and db machine and taking on over 500,000 hits per day. Some
> of our pages are very data intensive (http://www.mcgill.ca/calendar/ for
> example).
>
>> At the moment it waits "forever" (until max_execution_time i guess) and you
>> have no chance to show an error message like: DB is overloaded, please try
>> again.
>
> maybe pg_pconnect() isn't all it is cracked up to be? Anyone else here
> have good experiences with it?

You have to evaluate carefully your setup. I think there's little hope
to plan this in advance, you'll have to monitor your system at runtime.

I've been using persistent connections a lot. First, think of what kind
of problem you're trying to solve... decrease the connection overhead.
One connection made to the DB, per page. Now consider the following:

- PostgreSQL forks a backend process for each connection: this is
almost universally considered a huge overhead. But on modern operating
systems, this is no longer the case.

- network latency: if the PG server runs on a different host, it may be
an issue. If it runs on the same host, and is accessed via Unix sockets,
latency is negligible.

- in order to implement connect semantic, PHP has to 'reset' the session,
which is a kind of overhead per se. Consider the same apache backend
serving two different PG users! The connection may be the same, but
all local and remote state has to be removed.

- if your pages use multiple db connections, you'll get many PG backends
per apache backend.

- each PG backend uses up some resources (RAM mostly): there are a number
of 'knobs' you can turn to control that, but of course, unless your RAM
in infinite, you may have to _reduce_ them in order to increase the
number of concurrent backends.

To put it very simply (maybe too simply) it's a CPU vs RAM tradeoff.
Using persistent connections turns a little (very OS and setup dependant)
CPU overhead into a not so little RAM overhead on the PG server side.

I wasn't able to find a definitive answer. I have many concurrent accesses
(say 200-300), with simple (read and write) queries, but on a large data
set. I have two apache frontends, and one PG server (same LAN). I wasn't
able to choose between pconnect or connect. At times, some PHP programmer
kills the DB, and I have to teach him not to perform 300 different queries
_per page_ with a PHP for loop and to learn how to use table joins instead.
I can tell you that when that happens, you'll forget about pconnect or
connect, and thank PG developers for writing such a robust application
(I've seen load averages up to 100, still PG was doing its job, even
if very slowly). :-)

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo(at)ESI(dot)it