> We pay for MySQL support but haven't had much help from them.
You could pay me, I need work... :) Seriously...
> We have a very high volume site (3 million page views a day) that's
run
> on 16 Apache / PHP web servers & 2 MySQL servers. We are using PHP
with
> persistent connections. Each MySQL serves 8 web servers & is supposed
to
> act as a failover machine for the other group of 8 web servers.
Nice. We have over 3 million page views a day also (for forums -- a
recursive query application if there ever was one -- that is, lots of
queries per page), though we use two hand-made dual Athlon machines (one
apache/php, the other mysql). Its amazing what you can do with less than
$10K.
Once upon a time we had 4 web servers in front of 2 mysql servers. I've
gone through this before. More below....
> We get a mysterious MySQL lockup once a week at the same day & time.
No idea. Does it just pause for a bit? Are there any cronjobs? Can you
get a mysql process list?
> Questions :
>
> - Is our configuration of 2 sets of 8 Apache/PHP web servers & 1 MySQL
> servers just not a good idea ?
Just will need some work...
> - Would we better off with FreeBSD ?
No, that is not the issue.
> - Is there anyone doing any similar setups with lots of web servers &
a
few MySQLs ?
We had 4 per mysql server before, not as big but has the same issues.
> - Is there any way to get Apache / PHP to use fewer connections ?
Yes.
First a few questions:
- How many Apache servers?
8.
- Are you using HTTP Keep-Alives?
- Are you using persistent connections?
Yes.
- What is connecting to mysql (PHP, an Apache module, Perl, JSP)?
- What is Apache's http.conf value of MaxClients?
- What is Apache's http.conf value of StartServers?
- What is Apache's http.conf value of MaxRequestsPerChild?
Multiply these three numbers:
# of apache servers
# of apache's MaxClients
# of unique server/user/pass pairs
The result is the ceiling on the number of connections needed for mysql.
When one goes down, then you will multiply by two, since you have twice
as many web servers in that situation. Note this number and work it into
MySQL's documentation for memory requirements for the MySQL server. You
may be shocked.
If you are in an emergency, do this:
A. Turn off apache's HTTP Keep-Alive
B. Turn off persistent connections
C. Up the number of cached threads for mysql
That should immediately help. There is a more optimal solution, but the
amount of work to properly tune will take at least another email and the
answers to the above questions. I can write a chapter Apache/PHP/MySQL
optimizations....
Steven Roussey
Network54.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
