Re: pool of DB connections ?

1999-11-29 Thread Leslie Mikesell

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 ?

1999-11-29 Thread Matt Sergeant

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: pool of DB connections ?

1999-11-29 Thread Randal L. Schwartz


[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 ?

1999-11-29 Thread Chris Nokleberg


 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/



RE: pool of DB connections ?

1999-11-29 Thread Sheth, Niraj

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]