Handle.pm too many clients

Using: rt ver. 1.3.57 with postgres on Linux

Problem:
When using the web interface, I regularily get an ‘Internal configuration error’ message. This doesn’t happen with any consistency, and reloading will fix it usually. At present, I’m the only user.

Apache error log says:

DBI->connect(dbname=rt2;host=localhost) failed: Sorry, too many clients already
at /usr/lib/perl5/site_perl/5.005/DBIx/SearchBuilder/Handle.pm line 118
[Wed Mar 21 00:52:17 2001] [error] Connect Failed Sorry, too many clients already
at /usr/local/rt2/lib/RT.pm line 14

Has anybody else encountered this? I was looking for documentation about SearchBuilder, until I realised that Jesse wrote it… :slight_smile:

-Feargal.

_/_/_/ _/_/_/  _/_/_/  Feargal Reilly,

_/ _/ / / Systems Administrator,
/ / //// The Communications Interactive Agency.
//
/ //
/ _/ _/ Phone: +353-86-8157621.

| DBI->connect(dbname=rt2;host=localhost) failed: Sorry, too many clients
| already at /usr/lib/perl5/site_perl/5.005/DBIx/SearchBuilder/Handle.pm
±–>8

That error is from postgres; the server is complaining about too many
clients. You should check the postgres server configuration to make sure
it’s not limited to some tiny number of simultaneous connections / clients.

brandon s. allbery [os/2][linux][solaris][japh] allbery@kf8nh.apk.net
system administrator [WAY too many hats] allbery@ece.cmu.edu
electrical and computer engineering KF8NH
carnegie mellon university [“better check the oblivious first” -ke6sls]

You need to make sure RT won’t open more database connections than your
database will accept.

The maximum number of connections opened by the web interface is defined
by MaxClients in your apache config. The mailgate opens one connection
per delivery. qmail and Postfix have the option to set a maximum number
of concurrent local deliveries (concurrencylocal for qmail; see
Postfix Rate Controls for postfix). Sendmail users
have to cripple the entire MTA with MaxDaemonChildren. Also don’t forget
to allow for other uses of the same database backend.

which may very well lead you to:

http://www.postgresql.org/devel-corner/docs/postgres/kernel-resources.htmlOn Wed, Mar 21, 2001 at 01:30:40AM +0000, Feargal Reilly wrote:

Using: rt ver. 1.3.57 with postgres on Linux

Problem:
When using the web interface, I regularily get an ‘Internal configuration error’ message. This doesn’t happen with any consistency, and reloading will fix it usually. At present, I’m the only user.

Apache error log says:

DBI->connect(dbname=rt2;host=localhost) failed: Sorry, too many clients already
at /usr/lib/perl5/site_perl/5.005/DBIx/SearchBuilder/Handle.pm line 118
[Wed Mar 21 00:52:17 2001] [error] Connect Failed Sorry, too many clients already
at /usr/local/rt2/lib/RT.pm line 14

Has anybody else encountered this? I was looking for documentation about SearchBuilder, until I realised that Jesse wrote it… :slight_smile:

-Feargal.

_/_/_/ _/_/_/  _/_/_/  Feargal Reilly,

_/ _/ / / Systems Administrator,
/ / //// The Communications Interactive Agency.
//
/ //
/ _/ _/ Phone: +353-86-8157621.


Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

meow
_ivan

That was my first guess too, but I checked, and that’s not it.
ps reveals something a bit more interesting though.
Everytime I follow a link, two processes are created:

5757 pts/4 S 0:00 /usr/local/pgsql/bin/postgres 127.0.0.1 sql rt2 idle
5758 pts/4 S 0:00 /usr/local/pgsql/bin/postgres 127.0.0.1 sql rt2 idle

These persist indefinately, it doesn’t matter if I’ve logged out etc, etc.
The CLI cleans up after itself incidentally, so it’s something that’s done by the UI, but not the CLI that is causing this.

Ideas?

-Feargal.

_/_/_/ _/_/_/  _/_/_/  Feargal Reilly,

_/ _/ / / Systems Administrator,
/ / //// The Communications Interactive Agency.
//
/ //
/ _/ _/ Phone: +353-86-8157621.On Wednesday, March 21, 2001 01:30:40 +0000, Feargal Reilly feargal@online.thecia.ie wrote:
| DBI->connect(dbname=rt2;host=localhost) failed: Sorry, too many clients
| already at /usr/lib/perl5/site_perl/5.005/DBIx/SearchBuilder/Handle.pm
±–>8

 That error is from postgres; the server is complaining about too many 
 clients.  You should check the postgres server configuration to make sure 
 it's not limited to some tiny number of simultaneous connections / clients
 .
 
 brandon s. allbery     [os/2][linux][solaris][japh]   allbery@kf8nh.apk.ne
 t
 system administrator        [WAY too many hats]         allbery@ece.cmu.ed
 u
 electrical and computer engineering                                   KF8N
 H
 carnegie mellon university     ["better check the oblivious first" -ke6sls
 ]
 
 
 Rt-devel mailing list
 Rt-devel@lists.fsck.com
 http://lists.fsck.com/mailman/listinfo/rt-devel

That was my first guess too, but I checked, and that’s not it.
ps reveals something a bit more interesting though.
Everytime I follow a link, two processes are created:

5757 pts/4 S 0:00 /usr/local/pgsql/bin/postgres 127.0.0.1 sql rt2 idle
5758 pts/4 S 0:00 /usr/local/pgsql/bin/postgres 127.0.0.1 sql rt2 idle

These persist indefinately, it doesn’t matter if I’ve logged out etc, etc.

Apache keeps its database connections open, because the construction/teardown
is so damn expensive. Apache is designed to always have at least one spare process on hand, in case it gets multiple hits at the same time, iirc.

The CLI cleans up after itself incidentally, so it’s something that’s done by the UI, but not the CLI that is causing this.

The cli will clean up after itself because the program which talks to the DB
exits and closes its DB connection.

Ideas?

-Feargal.

_/_/_/ _/_/_/  _/_/_/  Feargal Reilly,

_/ _/ / / Systems Administrator,
/ / //// The Communications Interactive Agency.
//
/ //
/ _/ _/ Phone: +353-86-8157621.

 +-----
 | DBI->connect(dbname=rt2;host=localhost) failed: Sorry, too many clients
 | already at /usr/lib/perl5/site_perl/5.005/DBIx/SearchBuilder/Handle.pm
 +--->8
 
 That error is from postgres; the server is complaining about too many 
 clients.  You should check the postgres server configuration to make sure 
 it's not limited to some tiny number of simultaneous connections / clients
 .
 
 -- 
 brandon s. allbery     [os/2][linux][solaris][japh]   allbery@kf8nh.apk.ne
 t
 system administrator        [WAY too many hats]         allbery@ece.cmu.ed
 u
 electrical and computer engineering                                   KF8N
 H
 carnegie mellon university     ["better check the oblivious first" -ke6sls
 ]
 
 
 _______________________________________________
 Rt-devel mailing list
 Rt-devel@lists.fsck.com
 http://lists.fsck.com/mailman/listinfo/rt-devel

Rt-devel mailing list
Rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

jesse reed vincent – root@eruditorum.orgjesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

I admit that X is the second worst windowing system in the world, but all the
others I’ve used are tied for first.

It’s not so much apache keeping the connections I’d suspect, as mod_perl.

 Sorry. I spoke imprecisely.
 
 > The problem is, each new client (read click) opens two new connections, so with 5 clients (clicks), I've 10 persistent connection processes running.
 > Five hours later, they're still running. Now all I have to do is login again, view a couple more tickets, and I max out my clients, even though I'm the only user.
 > Instead of reusing an existing connection, it's gone and done the whole construction all over again. When does it decide to open a new connection in the code?
 
 It gets one and only one per child. I don't know how apache decides to hand
 your request to a specific child...
 
 > For postgres, I am allowing 64 open connections, double the default, and way more than neccessary. 
 > For apache, I've allowed 250 MaxClients, a figure which should never, ever be reached. 
 
 If you want to have up to 250 apache processes running, then you need to tune
 your database to handle that.

There’s no correlation between apache processes and database connections - not everything requires a db connection.

 > At most, I would expect 5-10 clients to be connecting to the web UI at a time.
 
 Then you'd be much better off all around telling apache to set a MaxClients
 of 20 or so.

The server is also running a public info site, which generates most of the traffic, plus a few other sites such as a mirror or two, and some stats info. These could realistically use 100 Clients at a time. But only 5-10 would ever need a postgres connection.

 > Am I to believe that mod_perl wants to open 250 postgres connections before it'll start reusing connections?
 
 I don't believe that apache children 'talk' enough to properly do connection
 pooling _between_ processes.

Hmm. Can you just check for me, and tell me how many postgres processes are running on your server?
I really don’t want 240 idle processes sitting around saving overheads.

 > -Feargal.
 
 Jesse.  
 
 ps. you replied to me personally, but I suspect that this info would be useful
 for the general community. can I bounce this reply to rt-devel?

Of course. Silly me, meant to repl to devel, not you anyway… :slight_smile:

 jesse reed vincent -- root@eruditorum.org -- jesse@fsck.com 
 70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90
 
 They'll take my private key when they pry it from my cold dead fingers!

The server is also running a public info site, which generates most of the traffic, plus a few other sites such as a mirror or two, and some stats info. These could realistically use 100 Clients at a time. But only 5-10 would ever need a postgres connection.

Ah. then what you probably want is the mod_perl proxy trick. Basically,
you don’t want RT bloating your front-end web server. Instead, use
mod proxypass to hand off anything destined for /rt/ to an apache
process tuned for RT running on a high port bound to the local interface.

This is actually what I do for fsck.com/rt2/

In my main httpd.conf, I have this:

ProxyPass /rt2/ http://localhost:9876/rt2/

Then I have a seperate apache process running on port 9876 on localhost.
It works like a charm.

Hmm. Can you just check for me, and tell me how many postgres processes are running on your server?
I really don’t want 240 idle processes sitting around saving overheads.

I run mysql here, actually.

 > -Feargal.

jesse reed vincent – root@eruditorum.orgjesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

After all, it’s not every day you meet up with an evil power
-M. Bulgakov

 > It's not so much apache keeping the connections I'd suspect, as mod_perl. 
 
 Sorry. I spoke imprecisely.

Apache::DBI, in fact, included in bin/webmux.pl. I kinda think it
shouldn’t be there, and the local admin or packager (for .rpm or .deb or
whatnot) should enable it with `PerlModule Apache::DBI’ in their Apache
config. I know it threw me for a loop when removing it from my httpd.conf
didn’t work (I’m used to swapping out Apache::DBI for DBIx::Profile in my
Apache config) Anyway, I have to give this some more thought.

I’ve actually been meaning to comment on other connection caching stuff
that I am a bit more sure of:

DBIx::SearchBuilder calls the DBI method connect_cached in Handle.pm and
Handle/Oracle.pm. I suggest this be changed to the regular DBI connect
method. The reasons for this are as follows:

  • See the connect_cached documentation in the DBI manpage, which warns
    that connect_cached is different than Apache::DBI and additionally should
    be used with care. Since DBIx::SearchBuilder is intended as general
    purpose poop[1] it should default to use connect, not connect_cached.

  • Since the only persistant context for RT is the web interface (the
    mailgate and CLI start up a process and a new database connection), and
    persistance in this context is better handled by Apache::DBI, RT should
    have no need to ask DBIx::SearchBuilder for connect_cached.

 > The problem is, each new client (read click) opens two new connections, so with 5 clients (clicks), I've 10 persistent connection processes running.
 > Five hours later, they're still running. Now all I have to do is login again, view a couple more tickets, and I max out my clients, even though I'm the only user.
 > Instead of reusing an existing connection, it's gone and done the whole construction all over again. When does it decide to open a new connection in the code?
 
 It gets one and only one per child. I don't know how apache
 decides to hand your request to a specific child...

This has actually been a subject of much discussion. It’s called the
“thundering herd problem” - see
http://www.citi.umich.edu/projects/linux-scalability/reports/accept.html
for example.

 > For postgres, I am allowing 64 open connections, double the default, and way more than neccessary. 
 > For apache, I've allowed 250 MaxClients, a figure which should never, ever be reached. 
 
 If you want to have up to 250 apache processes running, then you need to tune
 your database to handle that.

There’s no correlation between apache processes and database connections - not everything requires a db connection.

I think a better architecture is a “backend” mod_perl Apache server for
RT, with a much smaller MaxClients than your regular Apache.
mod_perl: Real World Scenarios has examples for a variety of
multiple-Apache configurations.

Personally, since I don’t have the need to have RT show up in the regular
document tree or anything fancy like that, I just run an Apache process
for RT on a separate IP address (you could get the same functionality
using a separate port).

On the subject of tuning your database (since I’ve been bitten by this
problem): you want to set your database concurrent connections to:
MaxClients from the Apache config, plus the maximum number of concurrent
local processes spawned by your MTA (i.e. maximum number of concurrent RT
mailgate processes), plus enough additional connections for command-line
processes and anything else that uses the same database.

To control the maximum number of processes spawned by your MTA:

qmail: concurrencylocal (see the qmail-send manpage)
postfix: Postfix Rate Controls (you want to set the
process limit for local delivery)
courier: Transport Modules (you want to set MAXDELS
for the `local’ module)
sendmail: no way to control this specifically. you are forced to use
sendmail’s limit on the total number of
processes (for any purpose): MaxDaemonProcesses in sendmail.cf

[1] “perl object oriented persistance” - see
http://sourceforge.net/mail/?group_id=4582. What were you
thinking?

meow
_ivan

Apache::DBI, in fact, included in bin/webmux.pl. I kinda think it
shouldn’t be there, and the local admin or packager (for .rpm or .deb or
whatnot) should enable it with `PerlModule Apache::DBI’ in their Apache
config. I know it threw me for a loop when removing it from my httpd.conf
didn’t work (I’m used to swapping out Apache::DBI for DBIx::Profile in my
Apache config) Anyway, I have to give this some more thought.

That makes sense to me. Sometime in the beta2-beta3 timeframe, this will
change.

I’ve actually been meaning to comment on other connection caching stuff
that I am a bit more sure of:

DBIx::SearchBuilder calls the DBI method connect_cached in Handle.pm and
Handle/Oracle.pm. I suggest this be changed to the regular DBI connect
method.

Indeed. this will be fixed in the next release of DBIx::SearchBuilder.

Thanks,
j

jesse reed vincent – root@eruditorum.orgjesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

“Bother,” said Pooh, “Eeyore, ready two photon torpedoes and lock
phasers on the Heffalump, Piglet, meet me in transporter room three”