You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Brett Randall <br...@hillsong.com> on 2005/11/14 00:46:56 UTC
Improving server performance with mod_perl 1.29 and DBI
G'day all - this is my first post to this list. Hope what I write makes
sense...
I have a mod_perl 1.29 app running on Apache 1.3.31 - it utilises
startup scripts as such:
httpd.conf:
<VirtualHost aa.bb.cc.dd>
...
PerlRequire /var/www/ors/demo/startup.pl
<Directory /var/www/ors/demo>
<Files ~ "\.pl$">
SetHandler perl-script
PerlHandler Apache::Registry
PerlSendHeader On
</Files>
...
</Directory>
</VirtualHost>
startup.pl:
use Apache::DBI;
use Apache::Registry;
use Apache::RegistryLoader;
use DBI;
use DBD::mysql;
use strict;
Apache::RegistryLoader->new->handler("/roster.pl",
"/var/www/ors/demo/roster.pl",
"demo.localhost");
Apache::DBI->connect_on_init('DBI:mysql:rostering_demo:localhost',
'username',
'password',
{ RaiseError => 0,
AutoCommit => 1,
PrintError => 1 } )
or die $DBI::errstr;
1;
My problem is that I have a growing number of virtual hosts utilising
the same application - in different physical directories, but all are
exactly the same application for different organisations, running out of
different databases. As the number of Apache processes grows to
compensate with the server's overall load, the number of open database
connections to our MySQL server increases at a ridiculous rate. In the
end, people receive "Too many connections" errors from our MySQL server
because it has hundreds of open connections and is reaching the limit
we've set (and raised time and time again) for it.
After doing some reading, I've seen that mod_perl 2 has the capability
of supporting shared database handles between Apache threads, but that
nothing really has happened with utilising that for DBI as yet.
Does anyone else have any ideas as to how I can utilise persistent
database connections to a MySQL server via DBI, in order to speed up web
queries, but without the 20 or so connections per virtual server that
sit there waiting for something to happen? Should I be doing this
differently?
Thanks in advance.
Brett Randall
Network & Systems Engineer
Technology Services Dept
Hillsong Church
www.hillsong.com <http://www.hillsong.com/>
Ph 02 8846 4800
________________________________________________________________________
The material contained in this email may be confidential, and may also
be the subject of copyright and/or privileged information. If you are
not the intended recipient, any use, disclosure or copying of this
document is prohibited. If you have received this document in error,
please advise the sender and delete the document.
This email communication does not create or vary any contractual
relationship between Hillsong and you. Internet communications are not
secure and accordingly Hillsong does not accept any legal liability
for the contents of this message.
Please note that neither Hillsong nor the sender accepts any
responsibility for viruses and it is your responsibility to scan the
email and any attachments.
Hillsong
www.hillsong.com
________________________________________________________________________
Re: Improving server performance with mod_perl 1.29 and DBI
Posted by Perrin Harkins <pe...@elem.com>.
On Mon, 2005-11-14 at 10:46 +1100, Brett Randall wrote:
> Does anyone else have any ideas as to how I can utilise persistent
> database connections to a MySQL server via DBI, in order to speed up
> web queries, but without the 20 or so connections per virtual server
> that sit there waiting for something to happen? Should I be doing this
> differently?
The first thing you should do is run a reverse proxy, if you're not
already doing it. Running without a reverse proxy means that people on
a modem downloading images, CSS, Flash, etc. from your site are tying up
database connections. A proxy can handle all the static stuff without
touching mod_perl or your database. In my experience, this can reduce
the number of database connections by an order of magnitude.
- Perrin
Re: Improving server performance with mod_perl 1.29 and DBI
Posted by "Philip M. Gollucci" <pg...@p6m7g8.com>.
Frank Wiles wrote:
> 3) Try things without Apache::DBI. In some situations Apache::DBI
> and persistant connections are crucial, but I have found with
> several apps in the past that it made little to no difference
> in the end user experience. It is at least worth a try to see
> if the performance change is something you can live with.
Adding my own two cents....
MySQL's connect() speed is insignifigant generally comapared to anything else you might do in a request cycle.
If you're using a different database such as Oracle, the connect speed is more of an issue.
Of course, if you have millions of connects() it eventually does add up.
--
--------------------------------------------------------------------------
"Love is not the one you can picture yourself marrying,
but the one you can't picture the rest of your life without."
"It takes a minute to have a crush on someone, an hour to like someone,
and a day to love someone, but it takes a lifetime to forget someone..."
Philip M. Gollucci (pgollucci@p6m7g8.com) 301.254.5198
Consultant / http://p6m7g8.net/Resume/resume.shtml
Senior Software Engineer - TicketMaster - http://ticketmaster.com
Re: Improving server performance with mod_perl 1.29 and DBI
Posted by Frank Wiles <fr...@wiles.org>.
On Mon, 14 Nov 2005 10:46:56 +1100
"Brett Randall" <br...@hillsong.com> wrote:
> Does anyone else have any ideas as to how I can utilise persistent
> database connections to a MySQL server via DBI, in order to speed up
> web queries, but without the 20 or so connections per virtual server
> that sit there waiting for something to happen? Should I be doing this
> differently?
Unfortunately Apache::DBI is doing exactly what you've told it to
do. You may be able to avoid this issue if you change how your
application works, but that really depends on what it is doing
and how.
Here are some ideas that might help:
1) Ensure that your StartServers and Min/MaxSpareServers are
setup so that there are just enough processes hanging around
to handle your load, but no more. The idea being the less
Apache children around the less database connections you are
using.
2) Stop using connect_on_init(). While this will slightly slow
down the first query per Apache child, it won't take up
database connections until the child actually needs to do
something.
3) Try things without Apache::DBI. In some situations Apache::DBI
and persistant connections are crucial, but I have found with
several apps in the past that it made little to no difference
in the end user experience. It is at least worth a try to see
if the performance change is something you can live with.
Hope this helps!
---------------------------------
Frank Wiles <fr...@wiles.org>
http://www.wiles.org
---------------------------------
Re: Improving server performance with mod_perl 1.29 and DBI
Posted by Fred Moyer <fr...@redhotpenguin.com>.
On Mon, 14 Nov 2005, Philip M. Gollucci wrote:
> Leo Lapworth wrote:
>> Put all your virtual hosts in one database, just start the table names
>> differently
>>
>> so instead of <virtual_db>.tablename have
>> application_db.<virtual>_tablename
>>
>> This way apache only have to connect to one database.
>>
>> There may be all sorts of reasons you don't want to do this - and from a
>> design point of view
>> it's a bit messy - but it would solve your issue.
>
> True, that would work, but after a schema reaches a certain number of tables
> things do start to degrade.
Namespaces are a good way of providing virtualization here. PostgreSQL
supports them and I think Oracle probably does also. Use a PerlAuthzHandler
to set the database connection namespace to $r->server_name or some
derivation thereof and you can code for one virtual just as easily as for
many in your response handlers.
Re: Improving server performance with mod_perl 1.29 and DBI
Posted by "Philip M. Gollucci" <pg...@p6m7g8.com>.
Leo Lapworth wrote:
> Put all your virtual hosts in one database, just start the table names
> differently
>
> so instead of <virtual_db>.tablename have application_db.<virtual>_tablename
>
> This way apache only have to connect to one database.
>
> There may be all sorts of reasons you don't want to do this - and from a
> design point of view
> it's a bit messy - but it would solve your issue.
True, that would work, but after a schema reaches a certain number of tables
things do start to degrade.
--
--------------------------------------------------------------------------
"Love is not the one you can picture yourself marrying,
but the one you can't picture the rest of your life without."
"It takes a minute to have a crush on someone, an hour to like someone,
and a day to love someone, but it takes a lifetime to forget someone..."
Philip M. Gollucci (pgollucci@p6m7g8.com) 301.254.5198
Consultant / http://p6m7g8.net/Resume/resume.shtml
Senior Software Engineer - TicketMaster - http://ticketmaster.com
Re: Improving server performance with mod_perl 1.29 and DBI
Posted by Clinton Gormley <cl...@traveljury.com>.
On Mon, 2005-11-14 at 09:36 +0100, Tom Schindl wrote:
> I'm uncertain what you are telling here because I can use different
> databases while connected in a connection using the
>
> 1. Connect 2 DB (e.g. test)
> 2. Query: SELECT * FROM db1.tab1
> 3. Query: SELECT * FROM db2.tab1
>
> So there's no need to move all tables into one database, simply connect
> always to one database and use fully qualified SQL-Statements
>
There is one potential gotcha here, and that is if you are using
master-slave replication on your MySQL database, with replication
limited to certain databases.
MySQL looks for the 'use db_name' statements to decide what to
replicate, so, if you want to replicate db2, but not db1, and you do:
use db1;
update db2.table....
it won't do the right thing.
You would specifically have to do:
use db2
update db2.table OR update table
For more details, see "--replicate-do-db=db_name" on
http://dev.mysql.com/doc/refman/5.0/en/replication-options.html
Re: Improving server performance with mod_perl 1.29 and DBI
Posted by Leo Lapworth <ra...@gmail.com>.
On 14 Nov 2005, at 08:36, Tom Schindl wrote:
> I'm uncertain what you are telling here because I can use different
> databases while connected in a connection using the
>
> 1. Connect 2 DB (e.g. test)
> 2. Query: SELECT * FROM db1.tab1
> 3. Query: SELECT * FROM db2.tab1
>
> So there's no need to move all tables into one database, simply
> connect
> always to one database and use fully qualified SQL-Statements
Oh - yea - that's nice - much better than what I suggested (which as I
said was messy :) - but would work )
Leo
Re: Improving server performance with mod_perl 1.29 and DBI
Posted by Tom Schindl <to...@gmx.at>.
I'm uncertain what you are telling here because I can use different
databases while connected in a connection using the
1. Connect 2 DB (e.g. test)
2. Query: SELECT * FROM db1.tab1
3. Query: SELECT * FROM db2.tab1
So there's no need to move all tables into one database, simply connect
always to one database and use fully qualified SQL-Statements
Tom
Leo Lapworth wrote:
>
> On 13 Nov 2005, at 23:46, Brett Randall wrote:
>
>> Does anyone else have any ideas as to how I can utilise persistent
>> database connections to a MySQL server via DBI, in order to speed up
>> web queries, but without the 20 or so connections /per virtual server
>> /that sit there waiting for something to happen? Should I be doing
>> this differently?
>
>
> I totally agree with the other posts - and this isn't a solution so much
> as a work around...
>
> Put all your virtual hosts in one database, just start the table names
> differently
>
> so instead of <virtual_db>.tablename have application_db.<virtual>_tablename
>
> This way apache only have to connect to one database.
>
> There may be all sorts of reasons you don't want to do this - and from a
> design point of view
> it's a bit messy - but it would solve your issue.
>
> Leo
Re: Improving server performance with mod_perl 1.29 and DBI
Posted by Leo Lapworth <ra...@gmail.com>.
On 13 Nov 2005, at 23:46, Brett Randall wrote:
> Does anyone else have any ideas as to how I can utilise persistent
> database connections to a MySQL server via DBI, in order to speed
> up web queries, but without the 20 or so connections per virtual
> server that sit there waiting for something to happen? Should I be
> doing this differently?
I totally agree with the other posts - and this isn't a solution so
much as a work around...
Put all your virtual hosts in one database, just start the table
names differently
so instead of <virtual_db>.tablename have
application_db.<virtual>_tablename
This way apache only have to connect to one database.
There may be all sorts of reasons you don't want to do this - and
from a design point of view
it's a bit messy - but it would solve your issue.
Leo