Re: mysql connection pooling

[Xinhuan Zheng <xz...@christianbook.com>]

Hi Perrin,

I am facing a similar issue with Oracle database. Tim's slides doesn’t explain details of implementation. I have Apache httpd with mod_perl and DBI. Without many code changes, what does the proxy server look like to achieve the database connection pooling? What is "Apache running DBI Gofer" as shown in the slides page 26?

Thanks,
- xinhuan

From: Perrin Harkins <ph...@gmail.com>>
Date: Thursday, June 27, 2013 3:05 PM
To: xiaolan <pr...@gmail.com>>
Cc: "modperl@perl.apache.org<ma...@perl.apache.org>" <mo...@perl.apache.org>>
Subject: Re: mysql connection pooling

Hi,

There is nothing exactly like the database pool in JDBC.  However, there are solutions for this problem.

The first thing you should do is run a reverse proxy in front of your mod_perl server.  That typically reduces the number of mod_perl processes by a factor of 10, i.e. 1000 mod_perl processes for 10,000 front-end proxy processes.  See http://perl.apache.org/docs/1.0/guide/strategy.html#Adding_a_Proxy_Server_in_http_Accelerator_Mode

Note that there are many lightweight proxy servers now that can act as a front-end for mod_perl, not just Apache httpd.

That's definitely what you should do first, but if you've done that and it's still not enough, you can also use DBD::Gofer.  There is an explanation here: http://search.cpan.org/~timb/DBI-1.627/lib/DBD/Gofer.pm#Connection_Pooling_and_Throttling

Tim's slides explaining how this was used in a real-world scenario are here:
http://www.slideshare.net/Tim.Bunce/dbdgofer-200809

- Perrin


On Thu, Jun 27, 2013 at 2:26 AM, xiaolan <pr...@gmail.com>> wrote:
Hi,

Currently I have been using Apache::DBI for long connections to Mysql.
But having the problem that, if the apache processes are 10000, they open 10000 connections to mysql, this make the DB crashed.
Is there any software for modperl working like Java's JDBC for connection pooling?

Thanks.

Re: mysql connection pooling

[Perrin Harkins <ph...@gmail.com>]

Hi,

Are you already using a reverse proxy in front of mod_perl?  That should be
your first step, and solves this for most people.

The next thing I'd recommend is looking at Oracle's connection pooling
solutions.  I haven't used Oracle recently, but I remember that they had
multiple ways to do pooling.  If they have something you can use, that's
probably more efficient than doing it at the Perl level and wouldn't
require code changes.

Finally, you could use the DBD::Gofer approach described in those slides.
 If you read the DBD::Gofer documentation you'll see that the HTTP proxying
is handled by a separate distribution here:
http://search.cpan.org/dist/GoferTransport-http/

- Perrin


On Thu, Oct 24, 2013 at 3:44 PM, Xinhuan Zheng <xz...@christianbook.com>wrote:

>  Hi Perrin,
>
>  I am facing a similar issue with Oracle database. Tim's slides doesn’t
> explain details of implementation. I have Apache httpd with mod_perl and
> DBI. Without many code changes, what does the proxy server look like to
> achieve the database connection pooling? What is "Apache running DBI Gofer"
> as shown in the slides page 26?
>
>  Thanks,
> - xinhuan
>
>   From: Perrin Harkins <ph...@gmail.com>
> Date: Thursday, June 27, 2013 3:05 PM
> To: xiaolan <pr...@gmail.com>
> Cc: "modperl@perl.apache.org" <mo...@perl.apache.org>
> Subject: Re: mysql connection pooling
>
>   Hi,
>
>  There is nothing exactly like the database pool in JDBC.  However, there
> are solutions for this problem.
>
>  The first thing you should do is run a reverse proxy in front of your
> mod_perl server.  That typically reduces the number of mod_perl processes
> by a factor of 10, i.e. 1000 mod_perl processes for 10,000 front-end proxy
> processes.  See
> http://perl.apache.org/docs/1.0/guide/strategy.html#Adding_a_Proxy_Server_in_http_Accelerator_Mode
>
>  Note that there are many lightweight proxy servers now that can act as a
> front-end for mod_perl, not just Apache httpd.
>
>  That's definitely what you should do first, but if you've done that and
> it's still not enough, you can also use DBD::Gofer.  There is an
> explanation here:
> http://search.cpan.org/~timb/DBI-1.627/lib/DBD/Gofer.pm#Connection_Pooling_and_Throttling
>
>  Tim's slides explaining how this was used in a real-world scenario are
> here:
> http://www.slideshare.net/Tim.Bunce/dbdgofer-200809
>
>  - Perrin
>
>
> On Thu, Jun 27, 2013 at 2:26 AM, xiaolan <pr...@gmail.com> wrote:
>
>> Hi,
>>
>>  Currently I have been using Apache::DBI for long connections to Mysql.
>> But having the problem that, if the apache processes are 10000, they open
>> 10000 connections to mysql, this make the DB crashed.
>> Is there any software for modperl working like Java's JDBC for connection
>> pooling?
>>
>>  Thanks.
>>
>
>