Mod_perl: can I share a database connection by putting it in thestartup-script?

[Oliver Green]

Hi,

I'm using Apache::Registry to speed up my cgi scripts. So far I've not
modified the scripts themselves apart from moving most of the common
"use <Module>" to the startup-script so Apache caches them.

As all scripts connect to a database (using DBI), I might as well move
the connect statement to the startup-script. This works, and apparently
the connection is now shared by all the children that Apache forks.

However, I have a hard time telling if it will work correctly under
load. I suspect that by re-using the same connection over and over I
might get some concurrency issues, or inconsistency errors.

How would I setup a test on my (modest) development system simulating
high usage?

And I would love to hear from someone that actually knows if this
connection sharing is at all advisable.

I even thought about sharing the statement handles (make a hash in the
startup script, and add the statements with their handles and just
re-execute them), but haven't tried that yet.

Any info is appreciated,
Oliver Green

 

[Brian McCauley]

I'm using Apache::Registry to speed up my cgi scripts. So far I've not
modified the scripts themselves apart from moving most of the common
"use <Module>" to the startup-script so Apache caches them.

As all scripts connect to a database (using DBI), I might as well move
the connect statement to the startup-script. This works, and apparently
the connection is now shared by all the children that Apache forks.

However, I have a hard time telling if it will work correctly under
load.

It will not.

I suspect that by re-using the same connection over and over I
might get some concurrency issues, or inconsistency errors.

You will.

How would I setup a test on my (modest) development system simulating
high usage?

Don't bother.

And I would love to hear from someone that actually knows if this
connection sharing is at all advisable.

It is not.

In mod_perl2 with the next generation of Apache:BI connection sharing
(or rather pooling) will be apparently supported eventually by some DBDs
- but it will be manged transparently by Apache:BI.

 

[xhoster]

How would I setup a test on my (modest) development system simulating
high usage?

Apache comes with a CLI tool, "ab", which is useful for simple load
testing. And LWP Perl module may also be useful.

Xho

 

[Keith Keller]

In mod_perl2 with the next generation of Apache:BI connection sharing
(or rather pooling) will be apparently supported eventually by some DBDs
- but it will be manged transparently by Apache:BI.

In mod_perl1, Apache:BI can be configured so that each httpd process
opens its own connection to databases that is shared across scripts
hitting that httpd. It's obviously not connection pooling, but will at
least eliminate the overhead of opening a connection to the database
each time a script is run.

I strongly suggest to the OP:

1) Install Apache:BI for your version of mod_perl, and carefully read
its documentation.

2) Subscribe to the mod_perl mailing list, where more people are likely
to have suggestions for what you want to do. (Not everyone in this
newsgroup uses mod_perl.)

--keith

 

[Brian McCauley]

In mod_perl1, Apache:BI can be configured so that each httpd process
opens its own connection to databases that is shared across scripts
hitting that httpd.

When you say 'can be configured' this implies that it is not the default
behaviour of Apache:BI, but AFIAK this is the default behaviour of
Apache:BI, indeed what else doe Apache:BI do?

1) Install Apache:BI for your version of mod_perl,

Oooh, is Apache:BI for mod_perl2 available? I must go get it.

 

[Keith Keller]

When you say 'can be configured' this implies that it is not the default
behaviour of Apache:BI, but AFIAK this is the default behaviour of
Apache:BI, indeed what else doe Apache:BI do?

Well, yes, you're correct, it's all Apache:BI does. When I said 'can
be configured', I meant that you do need to modify your httpd.conf file
or the startup file configured in httpd.conf. Simply installing
Apache:BI and even using it in your scripts (which the docs tell you
not to do anyway) isn't sufficient. (I certainly should have been more
clear, and apologize for the misunderstanding.)

Oooh, is Apache:BI for mod_perl2 available? I must go get it.

Gaah, another misstatement by me! I don't know whether it is or not.
Allegedly you can use Apache::compat to use mod_perl1 modules with
mod_perl2, but I've never actually tried it. That'd be another good
question for the mod_perl mailing list.

--keith

 

[Oliver Green]

1) Install Apache:BI for your version of mod_perl, and carefully read
its documentation.

Thank you all for responding. I've installed Apache:BI and
performance is sufficient for now. I'll rewrite parts of the
application to make better use of mod_perl, but as said, it
works for now.

Oliver