You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Jonathan <mo...@2xlp.com> on 2006/08/08 09:52:53 UTC
MaxClients and Apache::DBI ?
I'm running prefork as such:
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 75
MaxRequestsPerChild 10000
Using ab, I benched a page that has 3 db handles:
session
read
write
session has 1 connection of its own. read/write are currently
sharing a single connection until they cluster.
my postgres is set to 200 connections max
so here's whats odd (benching 5000 connections):
benching 10 concurrent connections- no problem
benching 100 concurrent connections- things seem fine for the first
few hundred connections.
around 400 though, i start getting DB errors.
if i do a quick "select * from pg_stat_activity " it shows me 198
connections ( 2 reserved for admin )
i can't figure out how this number is so large.
max clients (75) + max spare servers (10)= 85 (i 'm not even sure it
can jump up to 85 like that. shouldn't it max as 75? or is that
only for active clients)
85 * 2 ( 1 session , 1 read/write ) = 170
what math am i missing there that will account for another 30+
connections that its trying to make?
at first I thought I had a some errors in my perl code re:
connections, but I only have a single connection made with the
session handle via apache::session & connect_on_init .
for whatever reason, i seem to be creating 99 session and 99 read/
write handles
anyone have a clue?
Re: MaxClients and Apache::DBI ?
Posted by Perrin Harkins <pe...@elem.com>.
On Tue, 2006-08-08 at 03:52 -0400, Jonathan wrote:
> for whatever reason, i seem to be creating 99 session and 99 read/
> write handles
>
> anyone have a clue?
This usually means that your connection parameters are not always
identical, or that you loaded Apache::DBI too late. Set
$Apache::DBI::DEBUG = 2 to see debugging info in the logs.
- Perrin
Re: MaxClients and Apache::DBI ?
Posted by Jonathan Vanasco <mo...@2xlp.com>.
On Aug 8, 2006, at 4:16 AM, Philip M. Gollucci wrote:
> That says start 5 servers, I must have least 5 idle and no more
> then 10 idle.
> You can simultaneously serve 75 clients.
which means i can have an 85 server max?
> I Assume you are you're using Apache::Session::Postgres,
> I assume your passing DBH => $dbh, and LockDBH => $dbh,
yes
> Each apache child gets its own DBI connection from connect_on_init
> () if you are using it.
yep.
> Well only really 75 of them should be active at once the should
> queue according to your mpm... Which is probably
> prefork, so they just sit in the parent waiting for a child to be
> free.
prefork yes. and yes.
> I believe you should be at 75*2 = 150 asumming I read that correctly.
thats what i thought
> If you have Apache(2)::Reload on, you'll blow the Apache::DBI handle
no, i'm benching this on my production machine- no reload- which is
odd. so i can't figure out how i'm hitting the postgres max.
I'm going to look into timeout. I'm also going to look into how DBI
is pulling the lock connection-- perhaps it's sending a modified
connect string than the other handles. i didn't think to look there
before you mentioned it.
I spent a few hours rewriting code before i posted, consolidated the
misc other db handles into a single one -- i had to get them all into
autocommit=0, and make sure nothing tries to write off a read only
handle from my factory class.
i'd run the sessions off the same db handle as the rest of my app,
but im uneasy about having that clash with transactional data.
Re: MaxClients and Apache::DBI ?
Posted by Jonathan Vanasco <mo...@2xlp.com>.
On Aug 8, 2006, at 1:46 PM, Perrin Harkins wrote:
> Meaning what? That the args don't match what you use elsewhere? You
> were already assuming the sessions would use their own handle, so I
> don't see how it matters.
Based on this documentation:
tie %hash, 'Apache::Session::Postgres', $id, {
DataSource => 'dbi:Pg:dbname=sessions',
UserName => $db_user,
Password => $db_pass,
Commit => 1
};
one would think that Commit is related to the AutoCommit flag, its not
going through all of the sources for different modules, I've noticed
how the stores deviate from one another in terms of connection args
they hard code into it:
Informix
{ RaiseError => 1, AutoCommit => 0 }
MySQL
{ RaiseError => 1, AutoCommit => 1 }
Oracle
{ RaiseError => 1, AutoCommit => 0 }
Postgres
{ RaiseError => 1, AutoCommit => 0 }
That being said, my personal issue stemmed from that fact that I
would connect_on_init expecting the Postgres module to behave one
way, but it works entirely differently
So I was effectively creating a ton of handles via connect_on_init
that were never used, as I had no reason to expect RaiseError to be
set, and the word 'Commit' suggests an AutoCommit setting (neither
are in the docs, just the source)
Its kind of a mindfuck with Pg...
Commit must be set to 1
AutoCommit is hardcoded to 0
The only way to figure out the hardcode is to go through source.
I wrote the author with a documentation suggestion.
Re: MaxClients and Apache::DBI ?
Posted by Perrin Harkins <pe...@elem.com>.
On Tue, 2006-08-08 at 13:26 -0400, Jonathan Vanasco wrote:
> I"m not sure what I see. I see ton of openings and closings
Don't hit it with your site benchmark. Load 1 page and see if it opens
two handles or more. You can set it to only run one process and try
hitting that multiple times to see if it reuses the same connections or
not.
> if you set up Apache::Session::Pg with the connect args itself,
> you're screwed. Commit isn't really an 'AutoCommit' argument like in
> the other stores, and its requiring raise error.
Meaning what? That the args don't match what you use elsewhere? You
were already assuming the sessions would use their own handle, so I
don't see how it matters.
- Perrin
Re: MaxClients and Apache::DBI ?
Posted by Jonathan Vanasco <mo...@2xlp.com>.
On Aug 8, 2006, at 4:16 AM, Philip M. Gollucci wrote:
> I Assume you are you're using Apache::Session::Postgres,
Yes
> I assume your passing DBH => $dbh, and LockDBH => $dbh,
No locking in pg
On Aug 8, 2006, at 10:36 AM, Perrin Harkins wrote:
> This usually means that your connection parameters are not always
> identical, or that you loaded Apache::DBI too late. Set
> $Apache::DBI::DEBUG = 2 to see debugging info in the logs.
I"m not sure what I see. I see ton of openings and closings
my Pg args are
'Postgres'=> {
'DataSource'=> 'dbi:Pg:dbname=findmeon',
'UserName'=> 'Session_user',
'Password'=> 'Session_password',
'Commit'=> 1,
},
and i'm seeing new connections when there shouldn't be, and vice versa
though i think i've figured it out.
Digging thought Apache::Store::Postgres source, i see this:
return if (defined $self->{dbh});
=snip
if (exists $session->{args}->{Handle}) {
$self->{dbh} = $session->{args}->{Handle};
$self->{commit} = $session->{args}->{Commit};
return;
}
=snip
$self->{dbh} = DBI->connect(
$datasource,
$username,
$password,
{ RaiseError => 1, AutoCommit => 0 }
) || die $DBI::errstr;
that makes me think 2 things:
if you set up Apache::Session::Pg with a handle , you're kind of set
if you set up Apache::Session::Pg with the connect args itself,
you're screwed. Commit isn't really an 'AutoCommit' argument like in
the other stores, and its requiring raise error.
i'm going to try do a connect on init based on what Apache::Session
expects, and then rewrite my Session layer to accept handles from my
db::factory
i *think* that should work
Re: MaxClients and Apache::DBI ?
Posted by Perrin Harkins <pe...@elem.com>.
On Tue, 2006-08-08 at 01:16 -0700, Philip M. Gollucci wrote:
> If you have Apache(2)::Reload on, you'll blow the Apache::DBI handle
> cache
Really? Why would it reload Apache::DBI?
- Perrin
Re: MaxClients and Apache::DBI ?
Posted by "Philip M. Gollucci" <pg...@p6m7g8.com>.
Jonathan wrote:
> I'm running prefork as such:
>
> StartServers 5
> MinSpareServers 5
> MaxSpareServers 10
> MaxClients 75
That says start 5 servers, I must have least 5 idle and no more then 10 idle.
You can simultaneously serve 75 clients.
> Using ab, I benched a page that has 3 db handles:
> session
I Assume you are you're using Apache::Session::Postgres,
I assume your passing DBH => $dbh, and LockDBH => $dbh,
Each apache child gets its own DBI connection from connect_on_init() if you are using it.
> benching 10 concurrent connections- no problem
> benching 100 concurrent connections- things seem fine for the first few
> hundred connections.
> around 400 though, i start getting DB errors.
Well only really 75 of them should be active at once the should queue according to your mpm... Which is probably
prefork, so they just sit in the parent waiting for a child to be free.
> if i do a quick "select * from pg_stat_activity " it shows me 198
> connections ( 2 reserved for admin )
I believe you should be at 75*2 = 150 asumming I read that correctly.
If you have Apache(2)::Reload on, you'll blow the Apache::DBI handle
cache which I'll bet is your issue and the database will leave them open for $Timeout
and httpd will make new ones. The postgres max of 200 is then being triggered, capping you at 200.
HTH
------------------------------------------------------------------------
Philip M. Gollucci (pgollucci@p6m7g8.com) 323.219.4708
Consultant / http://p6m7g8.net/Resume/resume.shtml
Senior Software Engineer - TicketMaster - http://ticketmaster.com
1024D/A79997FA F357 0FDD 2301 6296 690F 6A47 D55A 7172 A799 97F
"In all that I've done wrong I know I must have done something right to
deserve a hug every morning and butterfly kisses at night."