Re: Limitations of DBI::ProxyServer (was: pool of DB connections?)
Hi there, On Mon, 29 Nov 1999, Tim Bunce wrote: I'd like to see a mode added to DBI::ProxyServer whereby a single server process serviced multiple clients in a round-robin manner. Obviously in this mode there's a risk of slow queries cloging up (blocking) the proxy, but for many applications it would still be very useful. Most significantly it would enable connect_cached to be used to implement a (kind-of) connection pool. I'm sure there must be someone out there willing to have a go at implementing it. It can't be that hard. If it's not that hard, I volunteer to _have a look_ at it. It looks like the sort of thing I'm going to need to do if my plans don't kill me in the execution. But time is short, I'm new to mod_perl, I never did get very on with object orientation and I've never written an SQL query. Having said that, I'm completely comfortable with assembler, C and C++, perl, Btree/ISAM, screwing around in Unix and so on, so I can give it a go. All I need is a reading list. And - because I'm an Englishman - some time. Kind regards, Ged Haywood.
pool of DB connections ?
Hi, I'm using mod_perl, DBI, ApacheDBI and was quite happy with persistent connections httpd-postgres until I used just one database. Currently I have 20 apache servers which handle 20 connections to database. If I want to work with another database I have to create another 20 connections with DB. Postgres is not multithreading DB, so I will have 40 postgres backends. This is too much. Any experience ? Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Re: pool of DB connections ?
According to Oleg Bartunov: Currently I have 20 apache servers which handle 20 connections to database. If I want to work with another database I have to create another 20 connections with DB, so I will have 40 postgres backends. This is too much. I didn't write all details but of course I already have 2 servers setup. Are you sure you need 20 concurrent backend servers? If you have enabled apache's 'server-status' reporting you can watch the backend during some busy times to see how many are doing anything. It is probably to have too few servers (the front end will wait as long as the requests don't overflow the listen queue) than so many that the machine starts paging virtual memory to disk. Les Mikesell [EMAIL PROTECTED]
Re: pool of DB connections ?
On Mon, 29 Nov 1999, Oleg Bartunov wrote: Hi, I'm using mod_perl, DBI, ApacheDBI and was quite happy with persistent connections httpd-postgres until I used just one database. Currently I have 20 apache servers which handle 20 connections to database. If I want to work with another database I have to create another 20 connections with DB. Postgres is not multithreading DB, so I will have 40 postgres backends. This is too much. Any experience ? Does postgresql not have an equivalent to Sybase's "use database" command? That would in effect be an adequate substitute if it did. Or even can you reference tables explicitly: select * from db.table -- Matt/ Details: FastNet Software Ltd - XML, Perl, Databases. Tagline: High Performance Web Solutions Web Sites: http://come.to/fastnet http://sergeant.org Available for Consultancy, Contracts and Training.
Re: Limitations of DBI::ProxyServer (was: pool of DB connections?)
On Mon, 29 Nov 1999, Tim Bunce wrote: Ignoring 'thread' (unsafe for production use) and 'debug' modes, the normal 'fork' mode means that each client gets a seperate ProxyServer process. And because of that, clients have no way to share connections with each other. Is that necessarily the case? Perhaps the parent server could handle the connection-pooling and hand off live connection to the forked child. That way you could get the best of both worlds - de-coupled clients and a shared pool of database connections. It seems to me that moving to a single-process architecture has some BIG downsides when you consider the wide variation in database query runtimes. -sam
Re: Limitations of DBI::ProxyServer (was: pool of DB connections?)
On Mon, Nov 29, 1999 at 07:46:50PM -0500, Sam Tregar wrote: On Mon, 29 Nov 1999, Tim Bunce wrote: Ignoring 'thread' (unsafe for production use) and 'debug' modes, the normal 'fork' mode means that each client gets a seperate ProxyServer process. And because of that, clients have no way to share connections with each other. Is that necessarily the case? Perhaps the parent server could handle the connection-pooling and hand off live connection to the forked child. That way you could get the best of both worlds - de-coupled clients and a shared pool of database connections. It seems to me that moving to a single-process architecture has some BIG downsides when you consider the wide variation in database query runtimes. You're quite right, but both cases need to be allowed for as some database (notably Oracle) get upset if a child process tries to use a connection established by the parent process. But mixing a connection pool with fork mode ought work for some of the DBI drivers. Thanks for pointing that out. Does that mean you're volunteering? :-) Tim.
Re: pool of DB connections ?
[watch the followups... this is going to both the modperl and the DBI list...] "Ed" == Ed Park [EMAIL PROTECTED] writes: Ed each creates a network connection to DBI::ProxyServer, which Ed creates a few persistent connections to the db server using the Ed connect_cached method. I hadn't noticed that until now. Except I also noticed this... Note that the behaviour of this method differs in several respects from the behaviour of the presistent connections implemented by Apache::DBI. Which scares me, but then doesn't go on to explain *what* those "several respects" are. While I didn't pay anything for the documentation *or* DBI, so I should be grateful, this is the kind of sentence that any editor looking over *my* stuff would flag with a big "?". :) Never leave the reader with a sense of puzzlement -- either don't say anything, finish the thought, or tell them where to read more. So, Tim, what *are* the differences, and when should we should we choose Apache::DBI vs DBI-connect_cached, and why? -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 [EMAIL PROTECTED] URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
RE: pool of DB connections ?
So, Tim, what *are* the differences, and when should we should we choose Apache::DBI vs DBI-connect_cached, and why? I think one of the big differences is that Apache::DBI overrides the disconnect method, to prevent accidentally calling disconnect from a mod_perl script. When using connect_cached you have to watch out for this yourself. Also, I had to switch from using Apache::DBI to connect_cached in my Apache::Registry script because of another subtle difference... (from Apache::DBI) # The PerlCleanupHandler is supposed to initiate a rollback after the script has finished if AutoCommit is off. # Note: the PerlCleanupHandler runs after the response has been sent to the client My registry script was using AutoCommit=0 and trying to commit via an END block, but Apache::DBI was rolling back before the END block was executed. Took a while to track this down. Chris - Chris Nokleberg + Internet Sports Network, Inc. [EMAIL PROTECTED] + http://www.SportsRocket.com/
Limitations of DBI::ProxyServer (was: pool of DB connections?)
On Mon, Nov 29, 1999 at 01:19:53PM -0800, Randal L. Schwartz wrote:
[watch the followups... this is going to both the modperl
and the DBI list...]
"Ed" == Ed Park [EMAIL PROTECTED] writes:
Ed each creates a network connection to DBI::ProxyServer, which
Ed creates a few persistent connections to the db server using the
Ed connect_cached method.
I hadn't noticed that until now. Except I also noticed this...
Note that the behaviour of this method differs in
several respects from the behaviour of the presistent
connections implemented by Apache::DBI.
Which scares me, but then doesn't go on to explain *what* those
"several respects" are. While I didn't pay anything for the
documentation *or* DBI, so I should be grateful, this is the kind of
sentence that any editor looking over *my* stuff would flag with a big
"?". :) Never leave the reader with a sense of puzzlement -- either
don't say anything, finish the thought, or tell them where to read
more.
So, Tim, what *are* the differences, and when should we should we
choose Apache::DBI vs DBI-connect_cached, and why?
The docs don't go into detail because, as they say:
The exact behaviour of this method Iis liable to change.
If you intend to use it in any production applications your
should discuss your needs in the dbi-users mailing list.
connect_cached is targeted, in part, for use with DBI::ProxyServer
to implement connection pooling. But, and it's a big but, the
current implementation of DBI::ProxyServer, as far as I know,
doesn't work in a way that would allow connect_cached to be useful.
Ignoring 'thread' (unsafe for production use) and 'debug' modes, the
normal 'fork' mode means that each client gets a seperate ProxyServer
process. And because of that, clients have no way to share connections
with each other.
I'd like to see a mode added to DBI::ProxyServer whereby a single
server process serviced multiple clients in a round-robin manner.
Obviously in this mode there's a risk of slow queries cloging up
(blocking) the proxy, but for many applications it would still be
very useful. Most significantly it would enable connect_cached to
be used to implement a (kind-of) connection pool.
Sadly I don't have the time to work on that. I'm not sure if Jochen
Wiedmann (the author of DBI::ProxyServer, DBD::Proxy and the RPC::*
modules they sit on) either has the time or is interested in doing the
work. On the other hand, given how useful this functionality would be,
I'm sure there must be someone out there willing to have a go at
implementing it. It can't be that hard. [Volunteers welcome but please
coordinate with Jochen and myself.]
Coming back to your original question, connect_cached is currently
a very simple cache:
sub connect_cached {
my $drh = shift;
my ($dsn, $user, $auth, $attr)= @_;
my $cache = $drh-FETCH('CachedKids');
$drh-STORE('CachedKids', $cache = {}) unless $cache;
my $key = join "~", $dsn, $user||'', $auth||'', $attr ? %$attr : ();
my $dbh = $cache-{$key};
return $dbh if $dbh $dbh-FETCH('Active') $dbh-ping;
$dbh = $drh-connect(@_);
$cache-{$key} = $dbh; # replace, even if it failed
return $dbh;
}
Apache::DBI's connect method is slightly more involved. It has some
Apache specifics and also reblessing the handle into a different class
in order to disable the disconnect method.
One final point, I think that connect_cached would need to be modified
slightly before it would be used as a true 'connection pool'. Right now
it'll happily give you the same live database handle that some other
part of the system may still be using. That may be, er, bad. You can
get around that by adding dummy attributes to force whatever level of
uniqueness is desired, but that's the kind of open issue that meant
connect_cached was left with a warning sign and a plea for input.
I hope that helps.
Tim.
Re: Limitations of DBI::ProxyServer (was: pool of DB connections?)
On Tue, 30 Nov 1999, Tim Bunce wrote: You're quite right, but both cases need to be allowed for as some database (notably Oracle) get upset if a child process tries to use a connection established by the parent process. Interesting. So Oracle snoops on the PID of the process calling it? How rude! Or does it have some other mechanism I'm not aware of? But mixing a connection pool with fork mode ought work for some of the DBI drivers. Thanks for pointing that out. Does that mean you're volunteering? :-) I'm willing to take a look at it... I've never played with DBD::Proxy*, although I've meant to. Let me take a look and then I'll tell you if it's something I think I could accomplish. -sam
RE: pool of DB connections ?
Have you looked at "Perl Cookbook"? It has nice discussion on prefork server. you can customize it according to your requirement. e.g. You can control exactly how many DB connection you want(background processes which keep persistance connection to database). You can move this to another server if you want or use as Unix Domain (as it's very fast compare to TCP/IP socket). Niraj -Original Message- From: Leslie Mikesell [mailto:[EMAIL PROTECTED]] Sent: Monday, November 29, 1999 11:00 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: pool of DB connections ? According to Oleg Bartunov: I'm using mod_perl, DBI, ApacheDBI and was quite happy with persistent connections httpd-postgres until I used just one database. Currently I have 20 apache servers which handle 20 connections to database. If I want to work with another database I have to create another 20 connections with DB. Postgres is not multithreading DB, so I will have 40 postgres backends. This is too much. Any experience ? Try the common trick of using a lightweight non-mod_perl apache as a front end, proxying the program requests to a mod_perl backend on another port. If your programs live under directory boundaries you can use ProxyPass directives. If they don't you can use RewriteRules with the [p] flag to selectively proxy (or [L] to not proxy). This will probably allow you to cut the mod_perl httpd's at least in half. If you still have a problem you could run two back end httpds on different ports with the front end proxying the requests that need each database to separate backends. Or you can throw hardware at the problem and move the database to a separate machine with enough memory to handle the connections. Les Mikesell [EMAIL PROTECTED]
