Question about using Apache::DBI with Postgres/MySQL

[Jonathan Vanasco <jv...@mastersofbranding.com>]

Instead of connecting to the DB and having DBI cache the handle 
after-the-fork, would it make sense to subclass Apache::DBI, have the 
parent process create a defined number of connections, and then use 
some sort of roundrobin/checkout to sign out a handle during a request?

You're asking "why?" and i'm responding that i dont really know

i just remembered that i'll likely have to up my connection limit to my 
db based on the number of apache children - and i thought  it would be 
nice, in a way, to have something that forces me to be cognizant of how 
many children i'm spawning within my webapp, not just in apache,  so i 
can just look at the application code and say "hm. i need x 
connections" instead of looking through the apache configuration

Re: Question about using Apache::DBI with Postgres/MySQL

[Jonathan Vanasco <jv...@mastersofbranding.com>]

On Aug 18, 2005, at 5:55 PM, David Hodgkinson wrote:

> But you're still trying to optimise prematurely.

I'll need to to a lot of optimizing in the future - but I just want to  
avoid as many obvious mistakes as possible and compartmentalize where  
the mistakes are made so I can find them quicker.

> Oh, and in this situation, consider running separate apaches for
> your different applications. Tune your startup.pl for maximum sharing,
> keep MaxClients low and therefore your memory usage predictable.

Done, done and done.

If anyone is in need, i have a partly functional howto on it using  
FreeBSD Ports, where a standard apache install using DSOs ends up  
running 3 seperate servers ( 1 frontend proxy + static, 1 mod_perl app  
server, 1 php/python/misc server)

	http://dev.2xlp.com/trac/wiki/ 
FreeBsdApacheMultiServerSetupUsingStandardPorts


I've since decided to use lighttpd for the frontend proxy/imageserver,  
but the current version doesn't proxy webdav - so I'm waiting for the  
next release to come out before I finish that howto (because I run  
trac/svn off the server)

When both were done I was going to share them with the list -- but now  
remembering the hell I went through figuring it all out, I'll share  
prematurely

Re: Question about using Apache::DBI with Postgres/MySQL

[David Hodgkinson <da...@hodgkinson.org>]

On 18 Aug 2005, at 18:58, Jonathan Vanasco wrote:
> i'm thinking about moving to pgsql for stored procedures too (yes  
> my5 has it, but its beta), but thats an entirely OT discussion.

And you have pgpool too.

But you're still trying to optimise prematurely.

Treat your fat apaches as application servers and have few enough
that if they're all running, they max out your CPU. For example,
I have a MaxClients of 2 on one box that only renders Template::Toolkit,
but 10 on a server where the PHP has to wait for a few SQL requests
to come back.

Oh, and in this situation, consider running separate apaches for
your different applications. Tune your startup.pl for maximum sharing,
keep MaxClients low and therefore your memory usage predictable.

Re: Question about using Apache::DBI with Postgres/MySQL

[Jonathan Vanasco <jv...@mastersofbranding.com>]

On Aug 18, 2005, at 1:42 PM, Perrin Harkins wrote:

>  If you can figure out a way to do
> that, you could theoretically have a separate process managing a pool 
> of
> them.  I doubt it would be worth it though.

That's pretty much what I was thinking, but I didn't really want to do 
this in a situation where one has 200 apaches and 100 mysqls - it was 
more along the lines of 100 mysql and 100 apache, and just having code 
there that makes me consciously adjust things in mod_perl so  i 
remember to adjust them in httpd.conf or sql.conf.

i might just write something for the startup file that polls 
configuration files or the sql db to make sure that it has enough 
connections for the intended amount of max_clients - and print a little 
"Hey, fix this!" warning

> With MySQL, connection times are low enough that you can get rid of
> persistent connections if scalability becomes a big issue.  Typically
> though, once you have a front-end proxy your remaining mod_perl
> processes really do all need their own database connection, so this
> might just result in some of them sitting around waiting for a
> connection.  Better to increase the allowed connections to your 
> database
> server or reduce the allowed connections to your mod_perl server.

Right now thats how everything is working. I was just thinking of ways 
to make me more aware of how many connections i need to have w/the db.

I'm also looking into going from 1 box w/apache/sql to 3 boxes 
(2apache+1sql ) as i'm moving from running 1 project on this system to 
3 projects and need more mem for apache and sql

i'm thinking about moving to pgsql for stored procedures too (yes my5 
has it, but its beta), but thats an entirely OT discussion.

Re: Question about using Apache::DBI with Postgres/MySQL

[Perrin Harkins <pe...@elem.com>]

On Thu, 2005-08-18 at 13:31 -0400, Jonathan Vanasco wrote:
> I didn't see anything about DBI connections in the guide about that.

It's one of the primary reasons to use a front-end server.

I'm afraid your idea won't work because most databases don't support
passing handles across processes.  If you can figure out a way to do
that, you could theoretically have a separate process managing a pool of
them.  I doubt it would be worth it though.

With MySQL, connection times are low enough that you can get rid of
persistent connections if scalability becomes a big issue.  Typically
though, once you have a front-end proxy your remaining mod_perl
processes really do all need their own database connection, so this
might just result in some of them sitting around waiting for a
connection.  Better to increase the allowed connections to your database
server or reduce the allowed connections to your mod_perl server.

- Perrin

Re: Question about using Apache::DBI with Postgres/MySQL

[Jonathan Vanasco <jv...@mastersofbranding.com>]

I'm front-ending with lighttpd right now (originally was apache, but I 
moved over) and limiting with maxclients

I didn't see anything about DBI connections in the guide about that.

i guess i was just thinking about making myself more aware of the 
number of connections.


On Aug 18, 2005, at 1:28 PM, David Hodgkinson wrote:

> No, don't do that. Front-end your fat apache with squid or a thin 
> apache and
> MaxClients-limit the backend. Keeps concurrency issues on the backend 
> server
> to  a minimum too. It's all in the guide.

Re: Question about using Apache::DBI with Postgres/MySQL

[David Hodgkinson <da...@hodgkinson.org>]

On 18 Aug 2005, at 18:18, Jonathan Vanasco wrote:

> Instead of connecting to the DB and having DBI cache the handle  
> after-the-fork, would it make sense to subclass Apache::DBI, have  
> the parent process create a defined number of connections, and then  
> use some sort of roundrobin/checkout to sign out a handle during a  
> request?
>
> You're asking "why?" and i'm responding that i dont really know
>
> i just remembered that i'll likely have to up my connection limit  
> to my db based on the number of apache children

No, don't do that. Front-end your fat apache with squid or a thin  
apache and
MaxClients-limit the backend. Keeps concurrency issues on the backend  
server
to  a minimum too. It's all in the guide.