RE: Connection pool with Apache::DBI and Oracle
I don't understand. The Apache server I am using has 50 ThreadsPerChild (default in httpd.conf). Thus, Apache is able to handle quite a few requests simultaneously. However, Oracle has only one session for the job. As a consequence, starting two identical requests from distinct clients leads to blocking the second request until the first one is served. Now, to my modest understanding, this is bottleneck. But maybe I'm doing something wrong here. Or perhaps I have misunderstood something. -Original Message- From: Perrin Harkins [SMTP:[EMAIL PROTECTED]] Sent: Friday, January 24, 2003 7:30 PM To: Georg Botorog Cc: [EMAIL PROTECTED] Subject: Re: Connection pool with Apache::DBI and Oracle Georg Botorog wrote: More precisely, I am using Apache::DBI to create and cache the connection to the DB. As this connection uses a single Oracle session, it is obvious that it becomes a bottleneck. Apache::DBI uses one connection per process. There is no bottleneck there. Each process only handles one request at a time so it would have no use for more than one database connection. Ideally, one would have a pool of DB sessions (= DB handles) that the Apache threads would use in serving the requests coming from clients. That's only ideal if your code spends a lot of time doing things that don't involve a database. You shgould be running with a reverse proxy in front, so that mod_perl processed will not be serving static files. Alternatively, is it possible to convince several Apache servers running concurrently to cooperate (i.e., load balance) in answering incoming requests? If yes, how? Any load-balancer will do that. There are dozens, both commercial and open source. See the mod_perl documentation for a list you can start with. - Perrin
Re: [mp2] Connection pool with Apache::DBI and Oracle
Georg Botorog wrote: I don't understand. The Apache server I am using has 50 ThreadsPerChild If you want help with mod_perl 2, you need to say so. Otherwise it is assumed you are using 1.x. Put [mp2] in your subject line. Thus, Apache is able to handle quite a few requests simultaneously. However, Oracle has only one session for the job. Each thread has its own Perl interpreter and each one of those will have a persistent DBI connection when using Apache::DBI. Nothing is shared between threads unless you explicitly make it shared. There is currently no way to share DBI handles between threads. If you're interested in working on that problem, you can read the archived posts on the topic. As I mentioned in the last message, sharing doesn't help much unless you have a lot of mod_perl code that doesn't use database connections. As a consequence, starting two identical requests from distinct clients leads to blocking the second request until the first one is served. Absolutely not. There is no sharing and thus no blocking. - Perrin
Re: [mp2] Connection pool with Apache::DBI and Oracle
Georg Botorog wrote: Actuallly, I had no idea I am using mp2. I have downloaded some time ago a bundle with Perl, Apache, etc. (for NT), and I'm still using it today. Ah, okay NT is another key piece of information. Sorry, I don't use apache on NT so I thought that was a mp2 directive. So, you are using 1.x on NT. With 1.x, all requests are handled serially on NT, which explains the behavior you're getting. It's actually not related to DBI at all. See http://perl.apache.org/docs/1.0/os/win32/multithread.html for the full story. - Perrin
Connection pool with Apache::DBI and Oracle
Hi, I would like to know if there is a module that implements a pool of connections to an Oracle database. More precisely, I am using Apache::DBI to create and cache the connection to the DB. As this connection uses a single Oracle session, it is obvious that it becomes a bottleneck. On the other hand, opening and closing a connection for each request (i.e., using DBI instead of Apache::DBI) is not acceptable. Ideally, one would have a pool of DB sessions (= DB handles) that the Apache threads would use in serving the requests coming from clients. Was such functionality implemented? Alternatively, is it possible to convince several Apache servers running concurrently to cooperate (i.e., load balance) in answering incoming requests? If yes, how? Thanks, George
Re: Connection pool with Apache::DBI and Oracle
Georg Botorog wrote: More precisely, I am using Apache::DBI to create and cache the connection to the DB. As this connection uses a single Oracle session, it is obvious that it becomes a bottleneck. Apache::DBI uses one connection per process. There is no bottleneck there. Each process only handles one request at a time so it would have no use for more than one database connection. Ideally, one would have a pool of DB sessions (= DB handles) that the Apache threads would use in serving the requests coming from clients. That's only ideal if your code spends a lot of time doing things that don't involve a database. You shgould be running with a reverse proxy in front, so that mod_perl processed will not be serving static files. Alternatively, is it possible to convince several Apache servers running concurrently to cooperate (i.e., load balance) in answering incoming requests? If yes, how? Any load-balancer will do that. There are dozens, both commercial and open source. See the mod_perl documentation for a list you can start with. - Perrin