This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Poor man's connection pooling
From: Michael Peppler <mpeppler@peppler.org>
Date: Tue, 5 Sep 2000 10:38:48 -0700 (PDT)
I've come across a technique that allows modperl processes to share a
pool of database handles. It's not something that I have seen
documented, so I figured I'd throw it out here.
The idea is to create a pool of connections during the main
apache/modperl startup. Because these connections end up in the code
segment for the child processes they are completely shareable. You
populate a hash in a BEGIN block with x connections, and then provide
some calls to grab the first available one (I use IPC::Semaphore to
coordinate access to each connection).
Based on preliminary tests I was able to use a 1 in 10 ratio of
database handles per httpd child processes, which, on a large site
would cut down on the number of connections that the database server
needs to handle.
The advantage of this solution is that connections don't go down when
the apache child dies (for example because the MAX_REQUEST per child
has been reached).
The disadvantage is that the connections can only be restarted by
restarting the webserver (apachectl stop/apachectl start).
This technique works with Sybase connections using either
DBI/DBD::Sybase or Sybase::CTlib (I've not tested Sybase::DBlib, nor
any other DBD modules).
I'd be very interested in any comments, in particular if there are any
downsides that I haven't considered (which is quite possible).
===
Subject: Re: Poor man's connection pooling
From: Stas Bekman <stas@stason.org>
Date: Tue, 5 Sep 2000 20:04:43 +0200 (CEST)
On Tue, 5 Sep 2000, Michael Peppler wrote:
> I've come across a technique that allows modperl processes to share a
> pool of database handles. It's not something that I have seen
> documented, so I figured I'd throw it out here.
>
> The idea is to create a pool of connections during the main
> apache/modperl startup. Because these connections end up in the code
> segment for the child processes they are completely shareable. You
> populate a hash in a BEGIN block with x connections, and then provide
> some calls to grab the first available one (I use IPC::Semaphore to
> coordinate access to each connection).
There is no such a thing as a code segment in Perl. Everything is handled
as data, so unless you write your code in C and glue it to Perl, you
cannot ensure that some read/write variable wouldn't land on the same page
with the data that stores the connections.
> Based on preliminary tests I was able to use a 1 in 10 ratio of
> database handles per httpd child processes, which, on a large site
> would cut down on the number of connections that the database server
> needs to handle.
>
> The advantage of this solution is that connections don't go down when
> the apache child dies (for example because the MAX_REQUEST per child
> has been reached).
If there would be a way for a chile to know its processor slot as seen by
Apache::Status, there will be no need for any semaphoring technique --
each process will access the connection by asking for its slot
number. This will work because when the child being reaped off a new
process replacing it is running in the same slot. Looks a much faster way
to me if you get hold of the slot number.
===
Subject: Re: Poor man's connection pooling
From: Michael Peppler <mpeppler@peppler.org>
Date: Tue, 5 Sep 2000 11:33:44 -0700 (PDT)
Stas Bekman writes:
> On Tue, 5 Sep 2000, Michael Peppler wrote:
>
> > The idea is to create a pool of connections during the main
> > apache/modperl startup. Because these connections end up in the code
> > segment for the child processes they are completely shareable. You
> > populate a hash in a BEGIN block with x connections, and then provide
> > some calls to grab the first available one (I use IPC::Semaphore to
> > coordinate access to each connection).
>
> There is no such a thing as a code segment in Perl. Everything is handled
> as data, so unless you write your code in C and glue it to Perl, you
> cannot ensure that some read/write variable wouldn't land on the same page
> with the data that stores the connections.
I guess it's in the shared data segment then. In any case the
connections and the datastructures that go with them are visible from
all the child httpd processes.
> > The advantage of this solution is that connections don't go down when
> > the apache child dies (for example because the MAX_REQUEST per child
> > has been reached).
>
> If there would be a way for a chile to know its processor slot as seen by
> Apache::Status, there will be no need for any semaphoring technique --
> each process will access the connection by asking for its slot
> number. This will work because when the child being reaped off a new
> process replacing it is running in the same slot. Looks a much faster way
> to me if you get hold of the slot number.
I'll look into that - thanks.
===
Subject: Re: Poor man's connection pooling
From: Stas Bekman <stas@stason.org>
Date: Tue, 5 Sep 2000 21:05:24 +0200 (CEST)
On Tue, 5 Sep 2000, Michael Peppler wrote:
> Stas Bekman writes:
> > On Tue, 5 Sep 2000, Michael Peppler wrote:
> >
> > > The idea is to create a pool of connections during the main
> > > apache/modperl startup. Because these connections end up in the code
> > > segment for the child processes they are completely shareable. You
> > > populate a hash in a BEGIN block with x connections, and then provide
> > > some calls to grab the first available one (I use IPC::Semaphore to
> > > coordinate access to each connection).
> >
> > There is no such a thing as a code segment in Perl. Everything is handled
> > as data, so unless you write your code in C and glue it to Perl, you
> > cannot ensure that some read/write variable wouldn't land on the same page
> > with the data that stores the connections.
>
> I guess it's in the shared data segment then. In any case the
> connections and the datastructures that go with them are visible from
> all the child httpd processes.
The shared segments are shared until the moment, when one of the data
structures is being written to, which makes the page (1k to 4k) dirty and
unshared. See the http://perl.apache.org/guide/performance.html (the
shared memory sections) for a more detailed explanation of the memory
management under Perl.
> > > The advantage of this solution is that connections don't go down when
> > > the apache child dies (for example because the MAX_REQUEST per child
> > > has been reached).
> >
> > If there would be a way for a chile to know its processor slot as seen by
> > Apache::Status, there will be no need for any semaphoring technique --
> > each process will access the connection by asking for its slot
> > number. This will work because when the child being reaped off a new
> > process replacing it is running in the same slot. Looks a much faster way
> > to me if you get hold of the slot number.
>
> I'll look into that - thanks.
You are very welcome. It'd be cool to have yet another neat technique to
squeeze more from mod_perl :)
===
Subject: Re: Poor man's connection pooling
From: Perrin Harkins <perrin@primenet.com>
Date: Tue, 5 Sep 2000 21:38:40 -0700 (PDT)
On Tue, 5 Sep 2000, Michael Peppler wrote:
> I've come across a technique that allows modperl processes to share a
> pool of database handles. It's not something that I have seen
> documented, so I figured I'd throw it out here.
>
> The idea is to create a pool of connections during the main
> apache/modperl startup. Because these connections end up in the code
> segment for the child processes they are completely shareable. You
> populate a hash in a BEGIN block with x connections, and then provide
> some calls to grab the first available one (I use IPC::Semaphore to
> coordinate access to each connection).
People have suggested this before on the mod_perl list and the objection
raised was that this will fail for the same reason it fails to open a
filehandle in the parent process and then use it from all the children.
Basically, it becomes unshared at some point and even if they don't do
things simultaneously one process will leave the socket in a state that
the other doesn't expect and cause problems. You can cause pages to
become unshared in perl just by reading a variable, so it's almost certain
to happen sooner or later.
Can you try this some more and maybe throw some artificial loads against
it to look for possible problems? It would be cool if this worked, but
I'm very skeptical until I see it handle higher concurrency without any
problems.
===
Subject: Re: Poor man's connection pooling
From: David Hodgkinson <daveh@hodgkinson.org>
Date: 06 Sep 2000 07:11:47 +0100
Perrin Harkins <perrin@primenet.com> writes:
> On Tue, 5 Sep 2000, Michael Peppler wrote:
> > I've come across a technique that allows modperl processes to share a
> > pool of database handles. It's not something that I have seen
> > documented, so I figured I'd throw it out here.
> >
> > The idea is to create a pool of connections during the main
> > apache/modperl startup. Because these connections end up in the code
> > segment for the child processes they are completely shareable. You
> > populate a hash in a BEGIN block with x connections, and then provide
> > some calls to grab the first available one (I use IPC::Semaphore to
> > coordinate access to each connection).
>
> People have suggested this before on the mod_perl list and the objection
> raised was that this will fail for the same reason it fails to open a
> filehandle in the parent process and then use it from all the children.
> Basically, it becomes unshared at some point and even if they don't do
> things simultaneously one process will leave the socket in a state that
> the other doesn't expect and cause problems. You can cause pages to
> become unshared in perl just by reading a variable, so it's almost certain
> to happen sooner or later.
I'd rather see a version of Apache::DBI that uses "use other_db" to
flip between different databases with the same credentials...
===
Subject: Re: Poor man's connection pooling
From: Stas Bekman <stas@stason.org>
Date: Wed, 6 Sep 2000 08:58:31 +0200 (CEST)
On Tue, 5 Sep 2000, Perrin Harkins wrote:
> On Tue, 5 Sep 2000, Michael Peppler wrote:
> > I've come across a technique that allows modperl processes to share a
> > pool of database handles. It's not something that I have seen
> > documented, so I figured I'd throw it out here.
> >
> > The idea is to create a pool of connections during the main
> > apache/modperl startup. Because these connections end up in the code
> > segment for the child processes they are completely shareable. You
> > populate a hash in a BEGIN block with x connections, and then provide
> > some calls to grab the first available one (I use IPC::Semaphore to
> > coordinate access to each connection).
>
> People have suggested this before on the mod_perl list and the objection
> raised was that this will fail for the same reason it fails to open a
> filehandle in the parent process and then use it from all the children.
> Basically, it becomes unshared at some point and even if they don't do
> things simultaneously one process will leave the socket in a state that
> the other doesn't expect and cause problems. You can cause pages to
> become unshared in perl just by reading a variable, so it's almost certain
> to happen sooner or later.
Just a small correction:
You can cause pages to become unshared in perl just by writing a variable,
^^^^^^^
so it's almost certain to happen sooner or later.
Or for example calling pos() which modifies the variable internals:
http://perl.apache.org/guide/performance.html#Are_My_Variables_Shared_
===
Subject: Re: Poor man's connection pooling
From: Perrin Harkins <perrin@primenet.com>
Date: Wed, 6 Sep 2000 00:20:31 -0700 (PDT)
On Wed, 6 Sep 2000, Stas Bekman wrote:
> Just a small correction:
>
> You can cause pages to become unshared in perl just by writing a variable,
> ^^^^^^^
> so it's almost certain to happen sooner or later.
>
> Or for example calling pos() which modifies the variable internals:
> http://perl.apache.org/guide/performance.html#Are_My_Variables_Shared_
If you read a variable in a way that causes it to be converted between a
numerical value and a string and it hasn't happened before, that will
change the internal structure and unshare the memory on one or more
pages. I'm no perlguts hacker, but I think this is correct.
===
Subject: Re: Poor man's connection pooling
From: Tim Bunce <Tim.Bunce@ig.co.uk>
Date: Wed, 6 Sep 2000 10:01:04 +0100
On Tue, Sep 05, 2000 at 10:38:48AM -0700, Michael Peppler wrote:
>
> The idea is to create a pool of connections during the main
> apache/modperl startup. [...]
>
> This technique works with Sybase connections using either
> DBI/DBD::Sybase or Sybase::CTlib (I've not tested Sybase::DBlib, nor
> any other DBD modules).
For some drivers, like DBD::Oracle, connections generally don't work
right across forks, sadly. MySQL is okay. Not sure about others.
===
Subject: Re: Poor man's connection pooling
From: Michael Peppler <mpeppler@peppler.org>
Date: Wed, 6 Sep 2000 07:18:36 -0700 (PDT)
Perrin Harkins writes:
> On Tue, 5 Sep 2000, Michael Peppler wrote:
> > I've come across a technique that allows modperl processes to share a
> > pool of database handles. It's not something that I have seen
> > documented, so I figured I'd throw it out here.
> >
> > The idea is to create a pool of connections during the main
> > apache/modperl startup. Because these connections end up in the code
> > segment for the child processes they are completely shareable. You
> > populate a hash in a BEGIN block with x connections, and then provide
> > some calls to grab the first available one (I use IPC::Semaphore to
> > coordinate access to each connection).
>
> People have suggested this before on the mod_perl list and the objection
> raised was that this will fail for the same reason it fails to open a
> filehandle in the parent process and then use it from all the children.
> Basically, it becomes unshared at some point and even if they don't do
> things simultaneously one process will leave the socket in a state that
> the other doesn't expect and cause problems. You can cause pages to
> become unshared in perl just by reading a variable, so it's almost certain
> to happen sooner or later.
Yes, that's what I figured too. But in my tests, involving thousands
of database calls, I haven't seen any problems (yet).
> Can you try this some more and maybe throw some artificial loads against
> it to look for possible problems? It would be cool if this worked, but
> I'm very skeptical until I see it handle higher concurrency without any
> problems.
I will *definitely* throw as much load as I can in a test/stress
environment before I make use of this in a production environment.
I'll let y'all know how this goes...
===
Subject: Re: Poor man's connection pooling
From: "Jay Strauss" <jstrauss@bazillion.com>
Date: Wed, 6 Sep 2000 10:42:53 -0500
Being a database guy but new to Mod_Perl (disclaimer: If
these aspects have already been implemented and/or talked
about please excuse me). It seems that there are 2
different type of connections needed, Shared and Dedicated.
There would be a pool of shared connections, that can be
accessed by any child apache process, with some sort of
connection manager to dole out, reuse, start new, kill
orphaned connections. The connection manager would need to
look at which database and database user you want to connect
to give you the correct shared handle.
And dedicated connections (working hand and hand with
Apache::session) that would let one session control a
particular database handle till it was done. That would let
you start a transaction in one perl script, do updates and
deletes, and end the transaction in a different script (i.e
so that you can have make use of database locking and
concurrency management, and have multi page
transactions). Also using the connection manager.
Lastly, Tim - can you elaborate on "For some drivers, like DBD::Oracle,
connections generally don't work right across forks, sadly" ??
===
Subject: Re: Poor man's connection pooling
From: Michael Peppler <mpeppler@peppler.org>
Date: Wed, 6 Sep 2000 08:56:56 -0700 (PDT)
Jay Strauss writes:
> Being a database guy but new to Mod_Perl (disclaimer: If
> these aspects have already been implemented and/or talked
> about please excuse me). It seems that there are 2
> different type of connections needed, Shared and
> Dedicated.
>
> There would be a pool of shared connections, that can be
> accessed by any child apache process, with some sort of
> connection manager to dole out, reuse, start new, kill
> orphaned connections. The connection manager would need
> to look at which database and database user you want to
> connect to give you the correct shared handle.
Right. That would normally be handled by a dedicated connection pool
server. For example Sybase has a server called EA (Enterprise
Application) Server that handles this job. I wanted to do something
that does not require additional software.
> And dedicated connections (working hand and hand with
> Apache::session) that would let one session control a
> particular database handle till it was done. That would
> let you start a transaction in one perl script, do
> updates and deletes, and end the transaction in a
> different script (i.e so that you can have make use of
> database locking and concurrency management, and have
> multi page transactions). Also using the connection
> manager.
This is very dangerous, at least from a Sybase POV (don't know about
other dbms). If you keep a transaction over multiple cgi requests you
are likely going to be holding locks over that period of time
too. This is usually a very bad thing...
===
Subject: Re: Poor man's connection pooling
From: Barrie Slaymaker <barries@slaysys.com>
Date: Wed, 06 Sep 2000 13:22:50 -0400
Michael Peppler wrote:
>
> Based on preliminary tests I was able to use a 1 in 10 ratio of
> database handles per httpd child processes, which, on a large site
> would cut down on the number of connections that the database server
> needs to handle.
I'd be interested to see how this compares with Apache::DBI performance with
MaxRequestsPerChild >= 100. I suspect it's negligable unless you're using
a database like Oracle (with lots of connection establishment overhead) over
a *slow* link.
I don't think there's any significant memory savings here, is there? Things
you think might share physical memory probably don't after a SQL statement
or two and some perl code gets run. Oracle certainly changes the RAM in
their connection and cursor handles, for instance.
===
Subject: Re: Poor man's connection pooling
From: Michael Peppler <mpeppler@peppler.org>
Date: Wed, 6 Sep 2000 10:24:17 -0700 (PDT)
Barrie Slaymaker writes:
> Michael Peppler wrote:
> >
> > Based on preliminary tests I was able to use a 1 in 10 ratio of
> > database handles per httpd child processes, which, on a large site
> > would cut down on the number of connections that the database server
> > needs to handle.
>
> I'd be interested to see how this compares with Apache::DBI performance with
> MaxRequestsPerChild >= 100. I suspect it's negligable unless you're using
> a database like Oracle (with lots of connection establishment overhead) over
> a *slow* link.
The back-end is Sybase. The actual connect time isn't the issue here
(for me.) It's the sheer number of connections, and the potential
issue with the number of sockets in CLOSE_WAIT or TIME_WAIT state on
the database server. We're looking at a farm of 40 front-end servers,
each runnning ~150 modperl procs. If each of the modperl procs opens
one connection that's 6000 connections on the database side.
Sybase can handle this, but I'd rather use a lower number, hence the
pooling.
> I don't think there's any significant memory savings here, is there? Things
> you think might share physical memory probably don't after a SQL statement
> or two and some perl code gets run. Oracle certainly changes the RAM in
> their connection and cursor handles, for instance.
I'm not worried about RAM usage on the web servers.
===
Subject: Re: Poor man's connection pooling
From: Perrin Harkins <perrin@primenet.com>
Date: Wed, 6 Sep 2000 10:35:37 -0700 (PDT)
On Wed, 6 Sep 2000, Jay Strauss wrote:
> Being a database guy but new to Mod_Perl (disclaimer: If these aspects have
> already been implemented and/or talked about please excuse me).
Before going down this road again, I suggest reading the definitive work
on the subject, which is a post from Jeffrey Baker:
http://forum.swarthmore.edu/epigone/modperl/breetalwox/38B4DB3F.612476CE@acm.org
===
Subject: Re: Poor man's connection pooling
From: Barrie Slaymaker <barries@slaysys.com>
Date: Wed, 06 Sep 2000 13:38:06 -0400
Michael Peppler wrote:
>
> The back-end is Sybase. The actual connect time isn't the issue here
> (for me.) It's the sheer number of connections, and the potential
> issue with the number of sockets in CLOSE_WAIT or TIME_WAIT state on
> the database server. We're looking at a farm of 40 front-end servers,
> each runnning ~150 modperl procs. If each of the modperl procs opens
> one connection that's 6000 connections on the database side.
Thanks, that makes more sense. I was thrown off by the stated advantage being
that connections don't go down when the apache child dies. I thought it was
a performance-enhancer.
> I'm not worried about RAM usage on the web servers.
Cool, thanks.
===
Subject: Re: Poor man's connection pooling
From: "Jeff Horn" <jeff@intralect.com>
Date: Wed, 6 Sep 2000 13:01:29 -0500
One thing I've looked at doing is adding an LRU mechanism (or some such
discipline) to the existing Apache::DBI.
I've already modified Apache::DBI to use 'reauthenticate' if a DBD
implements it so that only 1 connection needs to be maintained per child.
This greatly improves performance with a 1:1 ratio between Apache children
and database connections.
However, some (most) DBD don't implement this method and in fact some
databases cannot even do a 'reauthenticate' on an existing connection.
Also, there are situations where performance can be greatly improved by a
judicious choice of the # of connections maintained per child. An example
of this is where one user is used to verify authentication info stored in a
database and then if authenticated a new user id is used to actually do the
work. In this case keeping 2 connections per Apache child, pins the common
"authenticating" connection and rotates the other connection, while still
keeping the total number of connections quite low.
There are other disciplines besides LRU which might work well. Keeping a
"Hot Set" of connections by keeping a count of how often they're accessed
might a useful alternative. I'm sure there's all sorts of interesting
disciplines that could be dreamed up!
I'm looking at potential changes to Apache::DBI which would allow a choice
of discipline (LRU, HotSet, etc.) along with whether or not to use
'reauthenticate' DBD function all configurable from apache config files.
I'd be interested in any input on this course of action!
===
Subject: Re: Poor man's connection pooling
From: Stas Bekman <stas@stason.org>
Date: Wed, 6 Sep 2000 20:06:42 +0200 (CEST)
On Wed, 6 Sep 2000, Perrin Harkins wrote:
> On Wed, 6 Sep 2000, Stas Bekman wrote:
> > Just a small correction:
> >
> > You can cause pages to become unshared in perl just by writing a variable,
> > ^^^^^^^
> > so it's almost certain to happen sooner or later.
> >
> > Or for example calling pos() which modifies the variable internals:
> > http://perl.apache.org/guide/performance.html#Are_My_Variables_Shared_
>
> If you read a variable in a way that causes it to be converted between a
> numerical value and a string and it hasn't happened before, that will
> change the internal structure and unshare the memory on one or more
> pages. I'm no perlguts hacker, but I think this is correct.
You are right. I've looked into it with the help of all mighty
Devel::Peek. So what actually happens is this:
Consider this script:
use Devel::Peek;
my $numerical = 10;
my $string = "10";
$|=1;
dump_numerical();
read_numerical_as_numerical();
dump_numerical();
read_numerical_as_string();
dump_numerical();
dump_string();
read_string_as_numerical();
dump_string();
read_string_as_string();
dump_string();
sub read_numerical_as_numerical {
print "\nReading numerical as numerical: ",
int($numerical), "\n";
}
sub read_numerical_as_string {
print "\nReading numerical as string: ",
$numerical, "\n";
}
sub read_string_as_numerical {
print "\nReading string as numerical: ",
int($string), "\n";
}
sub read_string_as_string {
print "\nReading string as string: ",
$string, "\n";
}
sub dump_numerical {
print "\nDumping a numerical variable\n";
Dump($numerical);
}
sub dump_string {
print "\nDumping a string variable\n";
Dump($string);
}
When running it:
Dumping a numerical variable
SV = IV(0x80e74c0) at 0x80e482c
REFCNT = 4
FLAGS = (PADBUSY,PADMY,IOK,pIOK)
IV = 10
Reading numerical as numerical: 10
Dumping a numerical variable
SV = PVNV(0x810f960) at 0x80e482c
REFCNT = 4
FLAGS = (PADBUSY,PADMY,IOK,NOK,pIOK,pNOK)
IV = 10
NV = 10
PV = 0
Reading numerical as string: 10
Dumping a numerical variable
SV = PVNV(0x810f960) at 0x80e482c
REFCNT = 4
FLAGS = (PADBUSY,PADMY,IOK,NOK,POK,pIOK,pNOK,pPOK)
IV = 10
NV = 10
PV = 0x80e78b0 "10"\0
CUR = 2
LEN = 28
Dumping a string variable
SV = PV(0x80cb87c) at 0x80e8190
REFCNT = 4
FLAGS = (PADBUSY,PADMY,POK,pPOK)
PV = 0x810f518 "10"\0
CUR = 2
LEN = 3
Reading string as numerical: 10
Dumping a string variable
SV = PVNV(0x80e78d0) at 0x80e8190
REFCNT = 4
FLAGS = (PADBUSY,PADMY,NOK,POK,pNOK,pPOK)
IV = 0
NV = 10
PV = 0x810f518 "10"\0
CUR = 2
LEN = 3
Reading string as string: 10
Dumping a string variable
SV = PVNV(0x80e78d0) at 0x80e8190
REFCNT = 4
FLAGS = (PADBUSY,PADMY,NOK,POK,pNOK,pPOK)
IV = 0
NV = 10
PV = 0x810f518 "10"\0
CUR = 2
LEN = 3
So you can clearly see that if you want the data to be shared (unless
some other variable happen to change its value, and thus cause the
whole page to get dirty) and there is a chance that the same variable
will be acccessed both as a string and numerical value, you have to
access to this variable in both value as in the above example, before
the fork happens.
===
Subject: Re: Poor man's connection pooling
From: Tim Bunce <Tim.Bunce@ig.co.uk>
Date: Wed, 6 Sep 2000 21:17:30 +0100
On Wed, Sep 06, 2000 at 10:42:53AM -0500, Jay Strauss wrote:
>
> Lastly, Tim - can you elaborate on "For some drivers, like DBD::Oracle,
> connections generally don't work right across forks, sadly" ??
Oracle database connections made by a parent process can't be reliably
used by child processes.
===