Thread: FATAL 1: Sorry, too many clients already
Having a lot of problems using ODBC at the moment, from IIS on an NT4 box. I keep getting an ODBC error of: FATAL 1: Sorry, too many clients already I've upped the connections since getting this error (now to 38) a bit but it seems to consume whatever I throw at it, and I can't keep upping this indefinitely, in fact what I have upped it to seems to already be causing resource problems. Does the Postgresql ODBC driver support Connection Pooling at all? I set it in the registry for IIS4 to use this, but I don't think it is using it. Should I lower the shared memory I've allocated an increase connections instead? Yours Unwhettedly, Robert John Shepherd. Editor DVD REVIEWER The UK's BIGGEST Online DVD Magazine http://www.dvd.reviewer.co.uk For a copy of my Public PGP key, email: pgp@robertsworld.org.uk
Robert John Shepherd wrote:
>
> Having a lot of problems using ODBC at the moment, from IIS on an NT4
> box. I keep getting an ODBC error of:
>
> FATAL 1: Sorry, too many clients already
>
> I've upped the connections since getting this error (now to 38) a bit
> but it seems to consume whatever I throw at it, and I can't keep upping
> this indefinitely, in fact what I have upped it to seems to already be
> causing resource problems.
>
> Does the Postgresql ODBC driver support Connection Pooling at all? I set
> it in the registry for IIS4 to use this, but I don't think it is using
> it.
Which driver are you using ?
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
> Which driver are you using ? I was using v7.02.0003, but when I first encountered the problem I upgraded to v7.02.0004 (the one uploaded on 8th Nov) and have also tried both the normal and the beta + driver. Yours Unwhettedly, Robert John Shepherd. Editor DVD REVIEWER The UK's BIGGEST Online DVD Magazine http://www.dvd.reviewer.co.uk For a copy of my Public PGP key, email: pgp@robertsworld.org.uk
> -----Original Message----- > From: Robert John Shepherd > > > Which driver are you using ? > > I was using v7.02.0003, but when I first encountered the problem I > upgraded to v7.02.0004 (the one uploaded on 8th Nov) and have also tried > both the normal and the beta + driver. Could you see suspicious error messages in e.g. qlog ? regards, Hiroshi Inoue
> > I was using v7.02.0003, but when I first encountered the problem I > > upgraded to v7.02.0004 (the one uploaded on 8th Nov) and have also tried > > both the normal and the beta + driver. > Could you see suspicious error messages in e.g. qlog ? Nothing really. But I have noticed some very odd behaviour, the website seems very happy for long periods, with no more than 8-10 postgresql processes running. Then for no apparent reason, the number of processes increases dramatically to the maximum and they seem to all be either idle or sleeping, none are consuming any cpu at all. Yours Unwhettedly, Robert John Shepherd. Editor DVD REVIEWER The UK's BIGGEST Online DVD Magazine http://www.dvd.reviewer.co.uk For a copy of my Public PGP key, email: pgp@robertsworld.org.uk
"Robert John Shepherd" <robert@reviewer.co.uk> writes:
> Nothing really. But I have noticed some very odd behaviour, the website
> seems very happy for long periods, with no more than 8-10 postgresql
> processes running. Then for no apparent reason, the number of processes
> increases dramatically to the maximum and they seem to all be either
> idle or sleeping, none are consuming any cpu at all.
Two plausible theories:
1. Something wacko in client-side connection pooling logic, causing
new connections to be opened when not really necessary.
2. Client transactions are all waiting on a lock held by some
transaction that's not doing anything but not releasing the lock either.
It's rather difficult to debug case #2 in current releases, because
there's no easy way to look at the locking status. (In 7.3 there's
a new pg_locks system view that allows you to determine exactly who's
waiting for whom and why. Dunno if you'd like to try your site on
7.3rc1, but the option is there.) One thing you can look at is "ps"
status: if there is one guy who's "idle in transaction" and the others
are all "SELECT waiting" (or any-operation waiting), then it's a good
bet that the first one has got the lock the others are waiting for.
regards, tom lane
> -----Original Message----- > From: Robert John Shepherd > > > > I was using v7.02.0003, but when I first encountered the problem I > > > upgraded to v7.02.0004 (the one uploaded on 8th Nov) and have also > tried > > > both the normal and the beta + driver. > > > Could you see suspicious error messages in e.g. qlog ? > > Nothing really. But I have noticed some very odd behaviour, the website > seems very happy for long periods, with no more than 8-10 postgresql > processes running. Then for no apparent reason, the number of processes > increases dramatically to the maximum and they seem to all be either > idle or sleeping, none are consuming any cpu at all. Could you turn on (or Are you already turning) the CommLog DSN option and see the output when the trouble occurs ? regards, Hiroshi Inoue
I think for me to locate the source of this problem, I need to understand a little more about how ODBC 3.0 and the PostgreSQL driver deals with connection pooling. Which is responsible for setting the maximum number of connections that can be available in the pool? What happens if a request is made for a connection from the pool when none are available for use from that pool, AND no more connections are available to the server? Yours Unwhettedly, Robert John Shepherd. Editor DVD REVIEWER The UK's BIGGEST Online DVD Magazine http://www.dvd.reviewer.co.uk For a copy of my Public PGP key, email: pgp@robertsworld.org.uk