Re: RH 7.2 connections problems w 16 web servers to 2 MySQL servers
Hi! Sam We have a very high volume site (3 million page views a day) that's run Sam on 16 Apache / PHP web servers 2 MySQL servers. We are using PHP with Sam persistent connections. Each MySQL serves 8 web servers is supposed to Sam act as a failover machine for the other group of 8 web servers. Sam Sam The failover won't work now as if one MySQL goes down the cost of the 8 Sam web servers switching over is so high the other MySQL locks up. Sam Sam Each Apache / PHP server takes up hundreds of connections even when Sam they're idle so we ran into the Linux connection limit of 1000 had to Sam recompile to get past that. Sam Sam Our actual MySQL CPU useage is low but the goes when with the connection Sam overhead when starting up or failing over a bank of machines. Sam Sam We get a mysterious MySQL lockup once a week at the same day time. Could you please describe the lookup, exactly what happens ? What does 'mysqladmin var ext' show when this happens? What do you have in your log files ? Sam Questions : Sam Sam - Is our configuration of 2 sets of 8 Apache/PHP web servers 1 MySQL Sam servers just not a good idea ? This should not be a problem. Sam - Would we better off with FreeBSD ? If you are running a CMP machine, then Linux preforms normally better than FreeBSD. To be able to give some recommendations we need to know more about this setup. Sam - Is there anyone doing any similar setups with lots of web servers a Sam few MySQLs ? We have several hundred of paying support customers with this setup. Sam - Is there any way to get Apache / PHP to use fewer connections ? Stevens Rousseys exellent answer should help you with this Sam We pay for MySQL support but haven't had much help from them. I checked our support email archive, but couldn't find anything from you or your company in it. Could you please use our supportwizard interface to make a ticket of this problem so that we can help you with it? If you have already a ticket, please email me the ticket number so that we can check this up. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.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
Re: RH 7.2 connections problems w 16 web servers to 2 MySQL servers
On Wed, Mar 06, 2002 at 11:06:18AM -0800, Sam Iam wrote: 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. The failover won't work now as if one MySQL goes down the cost of the 8 web servers switching over is so high the other MySQL locks up. Each Apache / PHP server takes up hundreds of connections even when they're idle so we ran into the Linux connection limit of 1000 had to recompile to get past that. Our actual MySQL CPU useage is low but the goes when with the connection overhead when starting up or failing over a bank of machines. Are the Web servers doing write operations on the database, or read-only? If they're read-only, pick up a cheap load-balancer (a 3 year old Alteon will work fine) and load-balance across the 2 MySQL servers. Then you'll get the best utilization. Others have addressed the other issues you raised, it seems... Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 27 days, processed 979,505,907 queries (408/sec. avg) - 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
Re: RH 7.2 connections problems w 16 web servers to 2 MySQL servers
On Thursday, March 7, 2002, at 12:38 AM, Jeremy Zawodny wrote: Are the Web servers doing write operations on the database, or read-only? read-only now. Can you talk a bit about how Yahoo Finance does it as far as # of web servers to MySQL servers ? Apache PHP tuning ? - Sam. - 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
Re: RH 7.2 connections problems w 16 web servers to 2 MySQL servers
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
Re: RH 7.2 connections problems w 16 web servers to 2 MySQL servers
Sam Iam wrote: 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. The failover won't work now as if one MySQL goes down the cost of the 8 web servers switching over is so high the other MySQL locks up. Each Apache / PHP server takes up hundreds of connections even when they're idle so we ran into the Linux connection limit of 1000 had to recompile to get past that. Our actual MySQL CPU useage is low but the goes when with the connection overhead when starting up or failing over a bank of machines. We get a mysterious MySQL lockup once a week at the same day time. Questions : - Is our configuration of 2 sets of 8 Apache/PHP web servers 1 MySQL servers just not a good idea ? - Would we better off with FreeBSD ? I am not sure that would help in this case. - Is there anyone doing any similar setups with lots of web servers a few MySQLs ? - Is there any way to get Apache / PHP to use fewer connections ? PHP and persistant connections might be causing you problems. I can't find it now, but I just read on the MySQL web site that the way PHP handles persistant connection is not good, and even very bad, for busy web sites. It might have been in the server tuning section. Basically the problem is PHP has a pool of connections per thread instead of per server. Jim -- /\ ASCII Ribbon Campaign . \ / - NO HTML/RTF in e-mail . X - NO Word docs in e-mail . / \ - [EMAIL PROTECTED] http://www.FreeBSD.org The Power to Serve [EMAIL PROTECTED] http://www.TheHousleys.net [EMAIL PROTECTED] http://www.SimTel.Net - It's always a long day, 86400 doesn't fit into a short. - 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
RE: RH 7.2 connections problems w 16 web servers to 2 MySQL servers
If you can turn on the query log the amount of disk space it takes would be well worth troubleshooting the problem. Try and discern the last queries run on them, in order to help. Perhaps do this twice and you could check for consitency. How much disk space is in use on the drives related to it? Remember that disk space can vary immensley in seconds on some larger servers. Sometimes it may not even claim the space as taken, though it really is, if you catch my drift. Does the mysqld die or lockup? Does the entire server lockup? What day and time do you get it? Are there any cron jobs running that are doing anything? Perhaps someone has a script to pull something off your site once a week, and this is causing it? (Refer to troubleshooting explained above with query log) -Original Message- From: Sam Iam [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 06, 2002 12:06 PM To: MySQL List Subject: RH 7.2 connections problems w 16 web servers to 2 MySQL servers 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. The failover won't work now as if one MySQL goes down the cost of the 8 web servers switching over is so high the other MySQL locks up. Each Apache / PHP server takes up hundreds of connections even when they're idle so we ran into the Linux connection limit of 1000 had to recompile to get past that. Our actual MySQL CPU useage is low but the goes when with the connection overhead when starting up or failing over a bank of machines. We get a mysterious MySQL lockup once a week at the same day time. Questions : - Is our configuration of 2 sets of 8 Apache/PHP web servers 1 MySQL servers just not a good idea ? - Would we better off with FreeBSD ? - Is there anyone doing any similar setups with lots of web servers a few MySQLs ? - Is there any way to get Apache / PHP to use fewer connections ? We pay for MySQL support but haven't had much help from them. - Sam. - 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 - 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
Re: RH 7.2 connections problems w 16 web servers to 2 MySQL servers
Also, I forgot these questions: - What is Apache's http.conf value of MinSpareServers? - What is Apache's http.conf value of MaxSpareServers? Steven Roussey Network54.com sql,query - 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