Re: TIPool / multiple database connections
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 Richterecos electronic communication services gmbh
Internetconnect * Webserver/-design/-datenbanken * Consulting
Post: Tulpenstrasse 5 D-55276 Dienheim b. Mainz
E-Mail: [EMAIL PROTECTED] Voice:+49 6133 925131
WWW:http://www.ecos.de Fax: +49 6133 925152
-
- Original Message -
From: Elizabeth Mattijsen [EMAIL PROTECTED]
To: Stas Bekman [EMAIL PROTECTED]; Perrin Harkins [EMAIL PROTECTED]
Cc: Tim Keefer [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, July 16, 2002 11:17 AM
Subject: Re: TIPool / multiple database connections
At 02:57 PM 7/16/02 +, 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
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 BekmanJAm_pH -- Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide --- http://perl.apache.org
mailto:[EMAIL PROTECTED] http://use.perl.org http://apacheweek.com
http://modperlbook.org http://apache.org http://ticketmaster.com
Re: TIPool / multiple database connections
At 02:57 PM 7/16/02 +, 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
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 BekmanJAm_pH -- Just Another mod_perl Hacker http://stason.org/ mod_perl Guide --- http://perl.apache.org mailto:[EMAIL PROTECTED] http://use.perl.org http://apacheweek.com http://modperlbook.org http://apache.org http://ticketmaster.com
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
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 BekmanJAm_pH -- Just Another mod_perl Hacker http://stason.org/ mod_perl Guide --- http://perl.apache.org mailto:[EMAIL PROTECTED] http://use.perl.org http://apacheweek.com http://modperlbook.org http://apache.org http://ticketmaster.com
Re: TIPool / multiple database connections
At 12:18 AM 7/16/02 +, 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
Elizabeth Mattijsen wrote: At 12:18 AM 7/16/02 +, 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 BekmanJAm_pH -- Just Another mod_perl Hacker http://stason.org/ mod_perl Guide --- http://perl.apache.org mailto:[EMAIL PROTECTED] http://use.perl.org http://apacheweek.com http://modperlbook.org http://apache.org http://ticketmaster.com
Re: TIPool / multiple database connections
At 01:14 AM 7/16/02 +, 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
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
