At 11:16 28-1-2000 +0100, Marko van der Puil wrote:
>Hello,
>
>In response to Stas's question about improving performance by splitting your
>SQL and Apache over diffent machines... Please reads Stas's original posting
>for this discussion.
>
>There has been an discussion in the Mod_Perl mailing list about whether you
>would profit from splitting your Mod_Perl enabled Apache server and a SQL
>database like MySQL over multiple machines. To give this discussion some
>technical and scientific background I've run some benchmarks.
>
>They're available in HTML on
>http://cztb.zeelandnet.nl/rzweb/benchmarks/splitservers.html
>
>--
>Conclusions: (for the benchmarks see the web-page above)
>By this testing I'd say the difference in CPU performance (47%) is the key
>here. Where does the other 16 % go ? Probably in the 0,30 load of the MySql
>machine. And maybe in the slighly older version of MySql. I don't realy see
>any significant decrease in performance here, by using a separate MySql server
>for your Database.
>
>But You Should Be asking yourself what 3 Tier actually means.
>
>This has been figured out by all sorts of University buffs, and some mayor
>soft and hardware firms as well. Why would they invent 3 Tier? Because its
>faster, and easier to administrate even more secure.
>
>Now to prove that, I did the same test again, but then with ab doing a
>thousand requests over 20 simultaneous connections. The Mysql deamon on the
>mod_perl machine was bogging up over 95% of the CPU time, in addition to
>taking up over 20 Megs of memory!!!! The mod_perl httpd's are taking up to 10
>Megs each! The httpd's and mysqld are fighting for Memory and CPU time and
>take the combined server to its limits. By splitting the tasks over two
>servers both do, what the're good at. The MySQL server has all it's resources
>available for doing query's, the Mod_Perl server has all the memory it needs
>to server the requests.
>So what happens where your site is (very) busy? You actually get a *MAYOR*
>speed increase! Even when the mysql server is a MUCH SLOWER machine than the
>mod_perl apache server!
>
>So you can definetly benefit from splitting your Mod_perl and MySql Servers
>over diffent machines, even slower ones...
>
>With the price of computer hardware these days, state of the art being more
>than double the price of a system with some slightly lower spec. If you
>anticipate a lot of traffic, go for two cheap machines in stead of one state
>of the art one... UW2 SCSI in the sql machine, loads of RAM in the mod_perl
>server.
>
>What would work great would be these specs.
>
>Mod_Perl:
>400 Mhz machine
>256 MB Ram or better
>Small IDE disk
>
>MySQL:
>500 Mhz machine
>128 MB Ram
>Large UW2 SCSI disk
Some interesting points, Marko.
I haven't done any actual testing in this area myself, although I do
have a couple of observations to make. BTW: 'my' mod_perl enabled
server is currently a Sun Ultra Enterprise II (2 x 300MHz) with 512MB,
and it handles some 200K purely dynamic pageviews a day all by itself.
It has no static content to serve. Most of its (Dutch only, sorry :)
dynamic content is here: http://cgi5.planet.nl/aanbod/planet/
Initially we've had this mod_perl system running on a slower [but
with more memory] Sun server, with a lot more different scripts
and databases and doing about 5-7 MySQL queries per page. That
ended up to about 20K to 50K SQL queries per 5 minutes during peak
hours (!). With the MySQL server running on the *same* machine
as the Apache server you'll get a lot of resource contention.
The system wasn't unresponsive at all (fortunately) but still
doing 100% CPU on both CPU's for 75% of the day, ie HIGHLY loaded.
Swap usage (without much swap activity btw) could run up to 800MB
or more ... and MySQL was constantly showing some 65% cpu usage
in the top overview. At no time did the machine have room for
breathing.
I rewrote the lot (moving from Apache::Registry to HTML::Mason) to
benefit from some caching and with that I managed to dramatically
drop the number of SQL queries needed per request. We've moved from
20-50K queries per 5 minutes down to 3K-5K queries per 5 minutes.
A significant improvement I'd say. :) When I moved the lot to
HTML::Mason I also switched servers. Now this whole dynamic content
system is doing about 50% usage on both CPU's during peak hours, so
I have lots of room to spare still. But nowadays MySQL barely shows
up in CPU usage overviews - and it's still running together with
Apache on one machine.
With any system there's a certain point at which it will 'tip over',
i.e. resource contention will cause a sudden dramatic drop in
performance at that point through some snowball effect. There could
be several reasons for the cause of a tremendous drop of performance
at that specific point, but be sure it's there. (For example, your
system gets to be so busy that it can't serve requests in 'real'
time anymore and they queue up and thus worsen your already bad
situation ... Marko mentions this above too). You've got to ensure
that your parameters are configured so that your tip-over points are
never reached.
I've seen a horrible drop in performance and tip-over point on my
current mod_perl server: when I set it to 70 or more Apache
processes and run ApacheBench against the HTML::Mason components
my Apache server will get instable [!] and the whole machine will
slow to a crawl. Probably starts using a tiny bit too much
memory ... At 60 Apache processes I can't reproduce those problems
at all. Not a problem though, as 60 processes is enough for my
needs at the moment. (No lightweight proxy frontend as of yet,
which should improve things even further).
It totally depends on your situation whether you need or could make
good use of a separate database server or not. Probably the most
deciding factors are:
(a) Do you have enough memory to run both on the same machine?
(b) How many queries does the database actually get?
If you're low on memory getting your machine to swap won't help
your performance at all. And if you do have a database on the same
machine but most of your scripts don't access the database much at
all those few queries going to a different machine will not relieve
your mod_perl server a whole lot.
Tuning a system takes a lot of time and patience. You have to try
and experiment a lot, as it totally depends on what the system is
doing to find the optimal configuration. Monitor the system and
try to get rid of your bottlenecks in the first place, that's the
best advice I can give. If you can optimize or take away some of
your heavy queries that's a good start, as any database connection
can be potentially quite heavy.
BTW, there is at least one advantage of having MySQL on the same
machine as your webserver, if you connect without specifying a
hostname (not even localhost!) you'll get unix sockets instead of
TCP sockets, and they're supposedly a bit faster. By how much I do
not know. If anyone wants to benchmark this I'd love to hear
about your findings. :)
Grtz,
Pascal
--
Pascal Eeftinck - arcade^planet.nl
arcade^xs4all.nl - Perl is not a language, it's a way of
life