Apache::DBI, exceeding max connections and Perl CPU usage

[Mark Stosberg <ma...@summersault.com>]

Hello,

Yesterday we noticed two things happening about the same time with a
mod-perl driven website we managed, and I'm trying to figure out and if
how they were related.

On the front end web server machine, the CPU spiked up well beyond
average and stayed there. On the backend PostgreSQL 7.4 server, we saw
a number or errors that that the number of max connections had been
exceeded. 

Not surprisingly, this resulted in user visible errors on the web server.

I'm curious: What is the expected behavior from Apache::DBI in this
case? Would it quickly give up and move on, or perhaps continuously ping
the database server and wait, creating a high CPU load in the Perl
process? 

	Mark

Re: Apache::DBI, exceeding max connections and Perl CPU usage

[Perrin Harkins <pe...@elem.com>]

On Wed, 2005-07-06 at 20:57 +0000, Mark Stosberg wrote:
> Unrelated, I was just reviewing your slides from MVC presentation I
> missed at YAPC. I would have liked to met you there. I did get to met
> Michael Peters, another fine representative of your firm.

I'm glad you and Michael got a chance to meet.  I did want to talk with
you and the rest of the CGI::Application gang, but didn't quite have
time at this one.  Maybe at OSCON, if you're going.

> Thanks for the suggestion. I see we had it at 250, while our other front
> end server had 180. The database server has a maximum of 400
> connections, so it makes sense that our total count of Apache processes
> should be less than that. 

You should also check to make sure that you are really opening only one
connection per process.  You can use the Apache::DBI debugging to check
that on your dev server.

> We are also investigating if the Cache::FileCache caching we are doing
> may have played a part, but we haven't found any strong evidence to
> support that angle so far.

I'd recommend that you stop using Cache::FileCache and replace it with
something like Cache::FastMmap.  It's at least an order of magnitude
faster.

> Oddly, I tried using Apache::DProf, but the Perl processes we noticed
> that were using the CPU never produced the debugging output.

Maybe they got caught in some kind of tight loop.  You can sometimes get
useful info by running strace on processes like that.

- Perrin

Re: Apache::DBI, exceeding max connections and Perl CPU usage

[Mark Stosberg <ma...@summersault.com>]

Thanks for the response Perrin. 

Unrelated, I was just reviewing your slides from MVC presentation I
missed at YAPC. I would have liked to met you there. I did get to met
Michael Peters, another fine representative of your firm.  

On 2005-07-06, Perrin Harkins <pe...@elem.com> wrote:
> On Wed, 2005-07-06 at 18:11 +0000, Mark Stosberg wrote:
>> On the front end web server machine, the CPU spiked up well beyond
>> average and stayed there. On the backend PostgreSQL 7.4 server, we saw
>> a number or errors that that the number of max connections had been
>> exceeded.
>
> The CPU spike sounds a bit unusual, but there are ways it could result
> from failing to connect to your db.  Was it really CPU that spiked, or
> just load?  The load can spike in this case because the server spawns
> too many processes.  Make sure your MaxClients is tuned to something you
> can actually support.

Thanks for the suggestion. I see we had it at 250, while our other front
end server had 180. The database server has a maximum of 400
connections, so it makes sense that our total count of Apache processes
should be less than that. 

>> I'm curious: What is the expected behavior from Apache::DBI in this
>> case? Would it quickly give up and move on, or perhaps continuously ping
>> the database server and wait, creating a high CPU load in the Perl
>> process?
>
> Apache::DBI doesn't change the behavior of your app in this case.  It
> will either fail to make a new connection or fail to ping at the
> beginning of a request.  Either way, you'll get no db handle back, and
> if you have RaiseError on you should see an exception.  Maybe your code
> for handling that exception is not well-behaved?  It should probably
> just send an error page and get out.

I think that part of the code was working well. We can see that our
custom "Technical Failure" page was being returned quickly in such
cases.

We are also investigating if the Cache::FileCache caching we are doing
may have played a part, but we haven't found any strong evidence to
support that angle so far. 

Oddly, I tried using Apache::DProf, but the Perl processes we noticed
that were using the CPU never produced the debugging output. Many other
perl processes did. 

We'll keep tuning and see what we can find. If we find a smoking gun,
I'll be happy to share the results with the list. 

Thanks!

	Mark

Re: Apache::DBI, exceeding max connections and Perl CPU usage

[Perrin Harkins <pe...@elem.com>]

On Wed, 2005-07-06 at 18:11 +0000, Mark Stosberg wrote:
> On the front end web server machine, the CPU spiked up well beyond
> average and stayed there. On the backend PostgreSQL 7.4 server, we saw
> a number or errors that that the number of max connections had been
> exceeded.

The CPU spike sounds a bit unusual, but there are ways it could result
from failing to connect to your db.  Was it really CPU that spiked, or
just load?  The load can spike in this case because the server spawns
too many processes.  Make sure your MaxClients is tuned to something you
can actually support.

> I'm curious: What is the expected behavior from Apache::DBI in this
> case? Would it quickly give up and move on, or perhaps continuously ping
> the database server and wait, creating a high CPU load in the Perl
> process?

Apache::DBI doesn't change the behavior of your app in this case.  It
will either fail to make a new connection or fail to ping at the
beginning of a request.  Either way, you'll get no db handle back, and
if you have RaiseError on you should see an exception.  Maybe your code
for handling that exception is not well-behaved?  It should probably
just send an error page and get out.

- Perrin