You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Tim Keefer <tk...@allenpress.com> on 2002/07/15 17:36:55 UTC
TIPool / multiple database connections
I need to have several database connections within one modperl application
(sharing the connections across all modules would ideal). Could anyone
suggest a way to cache these connection with Apache2/modperl2 on win32?
The modperl2 docs talk about a TIPool module along with a DBIPool module.
Has anyone had success using these modules? I can't seem to find them in
the apache2/modperl2 distribution from Randy Kobes.
Thanks for any help,
Tim Keefer
Allen Press, Inc.
Lawrence, Kansas
Re: TIPool / multiple database connections
Posted by Stas Bekman <st...@stason.org>.
Liz, should we move this thread to the perl-ithreads list so we can get
some answers from the threads gurus? or p5p?
I doubt Arthur is listening on this list.
__________________________________________________________________
Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide ---> http://perl.apache.org
mailto:stas@stason.org http://use.perl.org http://apacheweek.com
http://modperlbook.org http://apache.org http://ticketmaster.com
Re: TIPool / multiple database connections
Posted by Gerald Richter <ri...@ecos.de>.
Hi,
I am a little late for the thread, nevertheless I like to give experiences I
gathered trying to get DBI threadafe. As Stas already said before in this
thread I have made a patch to DBI which makes DBI threadsafe. The result is
released in DBI 1.30. This doesn't mean that the DBD driver can handles
this, but mostly it's not hard to add.
Threadsafe means not that you can share any sort of DBI handles. It's means
that it's safe to use DBI with threads and that it is made sure that every
intances of the Perl interpreter (i.e. every thread) gets it own instance of
DBI.
My inital goal was to share DBI handles between threads, but this would need
very much work inside DBI. Additionaly Perl does not yet support sharing of
objects (BTW share does not deep share data structures). So Tim suggested to
first make DBI work correctly with threads at all. That's what we have done.
Gerald
P.S. I have some experimental code to share database handles inside of
DBD::Oracle, since this was much easier to implement, but this is not quite
ready yet.
-------------------------------------------------------------
Gerald Richter ecos electronic communication services gmbh
Internetconnect * Webserver/-design/-datenbanken * Consulting
Post: Tulpenstrasse 5 D-55276 Dienheim b. Mainz
E-Mail: richter@ecos.de Voice: +49 6133 925131
WWW: http://www.ecos.de Fax: +49 6133 925152
-------------------------------------------------------------
----- Original Message -----
From: "Elizabeth Mattijsen" <li...@dijkmat.nl>
To: "Stas Bekman" <st...@stason.org>; "Perrin Harkins" <pe...@elem.com>
Cc: "Tim Keefer" <tk...@allenpress.com>; <mo...@perl.apache.org>
Sent: Tuesday, July 16, 2002 11:17 AM
Subject: Re: TIPool / multiple database connections
> At 02:57 PM 7/16/02 +0000, Stas Bekman wrote:
> >Perrin Harkins wrote:
> >>Hmmm... That could really throw a wrench in things. If you have an
> >>object based on a hash, and you share that hash, and you re-bless the
> >>object in each thread, does that work? What if the hash contains
> >>references to other variables. Do they need to be explicity shared as
well?
> >That's what I meant. Probably non need for Thread::Pool, at all. use a
> >shared datastructure, maintain a list of free and busy items and simply
> >hand pointers inside this datastructure to the threads asking for an
item.
> >e.g.:
> >
> >package DBI::Pool;
> >use threads::shared;
> >my @pool : shared;
> >sub init {} # pre-populate pool with N connections
> >sub get {} # return a ref to $dbh, grow the pool if needed
> >sub put {} # move the pointer from the busy list to the free list
>
> Hmmm... as long as you do this _before_ the (Apache) threads get started,
> this might work. I still haven't got my mind entirely around what one is
> allowed to do, what you can do and is allowed, what you can't do but is
> allowed and crashes, and what in principle is possible but you're barred
> from because of e.g. prototyping getting in the way.
>
>
> >won't this work? I guess Perrin is right in respect that the whole item
> >needs to be shared (deep-shared). can we have such an attribute/function
> >that will automatically traverse the datastructure and share it all? or
is
> >this the case already with 'shared'?
>
> Good question. I don't think it is deep shared and that's why it probably
> doesn't work. The way Thread::Queue::Any (which is the transport medium
> for Thread::Pool) handles this, is by serializing any data structure with
> Storable, pass that around and deserialize that on the other end.
>
>
> >Now since we want to have various connections, it can be:
> >my %pools : shared;
> >where each key is a unique identifier, "compiled" from the dbi connect's
> >DSN string and a value is the actual pool.
>
> That's an approach. If you could actually share the $sth objects. About
> which I have my doubts.
>
>
> >BTW, there is no more need for Apache prefix in Apache::DBI, this can be
a
> >generic Pool class. I guess Apache::DBI can subclass DBI::Pool and add
> >things like connect_on_init(), but just to build the initial pool when
the
> >process starts.
>
> DBI::Pool would be ok. But unless I'm wrong about the sharing issues,
> you're going to be stuck, at least with this version of Perl, with
> serializing between threads.
>
>
> Liz
>
>
Re: TIPool / multiple database connections
Posted by Elizabeth Mattijsen <li...@dijkmat.nl>.
At 02:57 PM 7/16/02 +0000, Stas Bekman wrote:
>Perrin Harkins wrote:
>>Hmmm... That could really throw a wrench in things. If you have an
>>object based on a hash, and you share that hash, and you re-bless the
>>object in each thread, does that work? What if the hash contains
>>references to other variables. Do they need to be explicity shared as well?
>That's what I meant. Probably non need for Thread::Pool, at all. use a
>shared datastructure, maintain a list of free and busy items and simply
>hand pointers inside this datastructure to the threads asking for an item.
>e.g.:
>
>package DBI::Pool;
>use threads::shared;
>my @pool : shared;
>sub init {} # pre-populate pool with N connections
>sub get {} # return a ref to $dbh, grow the pool if needed
>sub put {} # move the pointer from the busy list to the free list
Hmmm... as long as you do this _before_ the (Apache) threads get started,
this might work. I still haven't got my mind entirely around what one is
allowed to do, what you can do and is allowed, what you can't do but is
allowed and crashes, and what in principle is possible but you're barred
from because of e.g. prototyping getting in the way.
>won't this work? I guess Perrin is right in respect that the whole item
>needs to be shared (deep-shared). can we have such an attribute/function
>that will automatically traverse the datastructure and share it all? or is
>this the case already with 'shared'?
Good question. I don't think it is deep shared and that's why it probably
doesn't work. The way Thread::Queue::Any (which is the transport medium
for Thread::Pool) handles this, is by serializing any data structure with
Storable, pass that around and deserialize that on the other end.
>Now since we want to have various connections, it can be:
>my %pools : shared;
>where each key is a unique identifier, "compiled" from the dbi connect's
>DSN string and a value is the actual pool.
That's an approach. If you could actually share the $sth objects. About
which I have my doubts.
>BTW, there is no more need for Apache prefix in Apache::DBI, this can be a
>generic Pool class. I guess Apache::DBI can subclass DBI::Pool and add
>things like connect_on_init(), but just to build the initial pool when the
>process starts.
DBI::Pool would be ok. But unless I'm wrong about the sharing issues,
you're going to be stuck, at least with this version of Perl, with
serializing between threads.
Liz
Re: TIPool / multiple database connections
Posted by Stas Bekman <st...@stason.org>.
Perrin Harkins wrote:
> Elizabeth Mattijsen wrote:
>
>> Hmm... but you won't be able to "fetch" the $dbh from the thread. It
>> can only live in _that_ thread. You cannot "pass" objects between
>> threads. But you _can_ send queries to that thread, fetch a jobid for
>> that job and then obtain whatever was returned as a Perl datastructure.
>>
>> (if anyone knows of a way to pass objects between threads, I'd really
>> would like to know)
>
>
> Hmmm... That could really throw a wrench in things. If you have an
> object based on a hash, and you share that hash, and you re-bless the
> object in each thread, does that work? What if the hash contains
> references to other variables. Do they need to be explicity shared as
> well?
That's what I meant. Probably non need for Thread::Pool, at all. use a
shared datastructure, maintain a list of free and busy items and simply
hand pointers inside this datastructure to the threads asking for an
item. e.g.:
package DBI::Pool;
use threads::shared;
my @pool : shared;
sub init {} # pre-populate pool with N connections
sub get {} # return a ref to $dbh, grow the pool if needed
sub put {} # move the pointer from the busy list to the free list
won't this work? I guess Perrin is right in respect that the whole item
needs to be shared (deep-shared). can we have such an attribute/function
that will automatically traverse the datastructure and share it all? or
is this the case already with 'shared'?
Now since we want to have various connections, it can be:
my %pools : shared;
where each key is a unique identifier, "compiled" from the dbi connect's
DSN string and a value is the actual pool.
BTW, there is no more need for Apache prefix in Apache::DBI, this can be
a generic Pool class. I guess Apache::DBI can subclass DBI::Pool and add
things like connect_on_init(), but just to build the initial pool when
the process starts.
__________________________________________________________________
Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide ---> http://perl.apache.org
mailto:stas@stason.org http://use.perl.org http://apacheweek.com
http://modperlbook.org http://apache.org http://ticketmaster.com
Re: TIPool / multiple database connections
Posted by Perrin Harkins <pe...@elem.com>.
Elizabeth Mattijsen wrote:
> Hmm... but you won't be able to "fetch" the $dbh from the thread. It
> can only live in _that_ thread. You cannot "pass" objects between
> threads. But you _can_ send queries to that thread, fetch a jobid for
> that job and then obtain whatever was returned as a Perl datastructure.
>
> (if anyone knows of a way to pass objects between threads, I'd really
> would like to know)
Hmmm... That could really throw a wrench in things. If you have an
object based on a hash, and you share that hash, and you re-bless the
object in each thread, does that work? What if the hash contains
references to other variables. Do they need to be explicity shared as well?
> Thread::Pool doesn't work that way. You could have 1 database
> connection in one worker thread and 40 threads submitting jobs: they
> would be handled in the order they were submitted. This effectively
> serializes access (which could be an approach for DBI drivers that do
> not support _any_ threading at all).
It could be useful for people who design their applications to use
different database logins for each end user. This would allow the
server to maintain a single persistent connection to the database for
that user, rather than one in each process.
> Or you could have 10 worker threads with 40 threads submitting jobs.
> That would work faster if your database is threaded as well ;-)
That would work well for the more common case, once the DBI threading
issues are worked out. But does this mean we would need to create a
whole new interface for sending in queries and getting results back,
because the actual $sth objects can't be shared? That would be painful.
Maybe some kind of local proxy object could handle this, forwarding all
method calls to the worker thread and returning all results. It would
be kind of like a transparent RPC mechanism.
- Perrin
Re: TIPool / multiple database connections
Posted by Elizabeth Mattijsen <li...@dijkmat.nl>.
At 01:14 AM 7/16/02 +0000, Stas Bekman wrote:
>>Hmmm... I guess you're right. I hadn't thought of applying Thread::Pool
>>in this situation, but it sure makes sense. This would however imply
>>that jobs would be submitted from different threads. That _should_ work,
>>but I just realised that I don't have a test-case for that. Will work on
>>one and let you know the result.
>I think that's a reverse case, the pool creates the dbh items (tools) and
>workers pick the items use them and then put back when they are done with
>them. So it's the pool who creates the "things".
Hmm... but you won't be able to "fetch" the $dbh from the thread. It can
only live in _that_ thread. You cannot "pass" objects between
threads. But you _can_ send queries to that thread, fetch a jobid for that
job and then obtain whatever was returned as a Perl datastructure.
(if anyone knows of a way to pass objects between threads, I'd really would
like to know)
With Thread::Pool you would do something like this:
use Thread::Pool;
my $pool = Thread::Pool->new(
{
workers => 10,
pre => \&pre,
do = \&do,
},
database parameters );
@result = $pool->wait( query parameters );
sub pre {
my $dbh = (make database connection with @_);
# maybe "prepare" any statements
return $dbh;
}
sub do {
my $pool = shift;
my ($dbh) = $pool->pre;
# do whatever you want to do in the database, dependent on @_
# could be any standard list, data-structure, etc, but _not_ an object!
return @result;
}
>btw, one thread should be able to pick more than one item at once. but in
>this particular case of DBI, I think there should be a different pool for
>each connectin group. similar to what Doug has suggested in his original
>TIPool prototype in the overview doc.
Thread::Pool doesn't work that way. You could have 1 database connection
in one worker thread and 40 threads submitting jobs: they would be handled
in the order they were submitted. This effectively serializes access
(which could be an approach for DBI drivers that do not support _any_
threading at all).
Or you could have 10 worker threads with 40 threads submitting jobs. That
would work faster if your database is threaded as well ;-)
Liz
Re: TIPool / multiple database connections
Posted by Stas Bekman <st...@stason.org>.
Elizabeth Mattijsen wrote:
> At 12:18 AM 7/16/02 +0000, Stas Bekman wrote:
>
>> ...A few folks at p5p are creating a bunch of new modules around
>> threads:: and threads::shared::, just yesterday a new module:
>> Thread::Pool was released by Elizabeth Mattijsen. Which seems to be
>> what's needed for Apache::DBITPool.
>
>
> Hmmm... I guess you're right. I hadn't thought of applying
> Thread::Pool in this situation, but it sure makes sense. This would
> however imply that jobs would be submitted from different threads. That
> _should_ work, but I just realised that I don't have a test-case for
> that. Will work on one and let you know the result.
I think that's a reverse case, the pool creates the dbh items (tools)
and workers pick the items use them and then put back when they are done
with them. So it's the pool who creates the "things".
btw, one thread should be able to pick more than one item at once. but
in this particular case of DBI, I think there should be a different pool
for each connectin group. similar to what Doug has suggested in his
original TIPool prototype in the overview doc.
__________________________________________________________________
Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide ---> http://perl.apache.org
mailto:stas@stason.org http://use.perl.org http://apacheweek.com
http://modperlbook.org http://apache.org http://ticketmaster.com
Re: TIPool / multiple database connections
Posted by Elizabeth Mattijsen <li...@dijkmat.nl>.
At 12:18 AM 7/16/02 +0000, Stas Bekman wrote:
>...A few folks at p5p are creating a bunch of new modules around threads::
>and threads::shared::, just yesterday a new module: Thread::Pool was
>released by Elizabeth Mattijsen. Which seems to be what's needed for
>Apache::DBITPool.
Hmmm... I guess you're right. I hadn't thought of applying Thread::Pool
in this situation, but it sure makes sense. This would however imply that
jobs would be submitted from different threads. That _should_ work, but I
just realised that I don't have a test-case for that. Will work on one and
let you know the result.
Liz
Re: TIPool / multiple database connections
Posted by Stas Bekman <st...@stason.org>.
Tim Keefer wrote:
> I need to have several database connections within one modperl
> application (sharing the connections across all modules would ideal).
> Could anyone suggest a way to cache these connection with
> Apache2/modperl2 on win32?
> The modperl2 docs talk about a TIPool module along with a DBIPool
> module. Has anyone had success using these modules? I can't seem to find
> them in the apache2/modperl2 distribution from Randy Kobes.
> Thanks for any help,
If you are talking about prefork, Apache::compat with Apache::DBI should do.
If you are talking about threaded mpms, the TIPool idea stays just as an
idea, because now we have threads::shared that will do. Just give it
some time because there is lot of work being done in that direction as
you read this. Gerald Richter and Tim Bunce are working on the
thread-safe DBI. A few folks at p5p are creating a bunch of new modules
around threads:: and threads::shared::, just yesterday a new module:
Thread::Pool was released by Elizabeth Mattijsen. Which seems to be
what's needed for Apache::DBITPool. Feel free to join the efforts and
make things happen faster.
__________________________________________________________________
Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide ---> http://perl.apache.org
mailto:stas@stason.org http://use.perl.org http://apacheweek.com
http://modperlbook.org http://apache.org http://ticketmaster.com