Re: Re[2]: RH 7.2 connections problems w 16 web servers to 2 MySQLservers
The good solution for this would be to put OOPS behind of apache. What is OOPS ? Is it like SQL Relay ? Others have said that we should be using a connection pooler that it's a PHP/Apache config problem that we're running into. We may also move to BSD from Redhat. P.S Are you using mysql binary or version compiled with patched GLIBC if not the threads limit should be the cause. We had to use the patched glibc. We have over 1,000 connections supported now. - Sam. On Monday, March 11, 2002, at 06:06 AM, Peter Zaitsev wrote: Hello Michael, Monday, March 11, 2002, 3:38:28 PM, you wrote: I had a close problem once - then having many active connections (and so threads) mysql speed may degrade a lot because of scheduling and convergency problem. This does not explain the mysql lock itself, but may be the reason (i.e too many threads may make mysql to lock or crush because of GLIBC limits) The good solution for this would be to put OOPS behind of apache. This gives two positive issues: - your apache server will have much less children and so will require much less memory and will basically work faster. In my case the number of children have dropped from 150 to 16 and required memory from about 1G to 200M (I'm running very complicated PHP scripts) - you will need much less number of connections for mysql. In my case the number have dropped from about 500 connections from web server to 50, and load average on mysql server fell from 3.0-4.0 to 0.5-1.0. P.S Are you using mysql binary or version compiled with patched GLIBC if not the threads limit should be the cause. MW 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. MW Could you please describe the lookup, exactly what happens ? MW What does 'mysqladmin var ext' show when this happens? MW 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 ? MW This should not be a problem. Sam - Would we better off with FreeBSD ? MW If you are running a CMP machine, then Linux preforms normally better MW than FreeBSD. MW To be able to give some recommendations we need to know more about MW this setup. Sam - Is there anyone doing any similar setups with lots of web servers a Sam few MySQLs ? MW We have several hundred of paying support customers with this setup. Sam - Is there any way to get Apache / PHP to use fewer connections ? MW Stevens Rousseys exellent answer should help you with this Sam We pay for MySQL support but haven't had much help from them. MW I checked our support email archive, but couldn't find anything from MW you or your company in it. MW Could you please use our supportwizard interface to make a ticket of MW this problem so that we can help you with it? MW If you have already a ticket, please email me the ticket number so MW that we can check this up. MW Regards, MW Monty -- Best regards, Petermailto:[EMAIL PROTECTED]
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
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
Re: Can you skip ORDER BY get rows back in inserted order ?
Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ? No That is the nature of relational databases Is this because the index cache in ram may not be loaded in order ? You might want to consider adding an AUTO_INCREMENT column, then inserting in the order you want them back Then you can retrieve in that order by using an ORDER BY on the AUTO_INCREMENT column My rank column is the order that I want them back in Would you agree that sorting small row sets on the client with say PHP is more efficient than asking a busy MySQL server with many clients to do ORDER BY so that FILESORT or TEMPORARY can be avoided ? thanks, - Sam PS : I bought your book by the way Great read On Tuesday, March 5, 2002, at 09:12 AM, Paul DuBois wrote: At 8:16 -0800 3/5/02, Sam Lam wrote: I have a table like so : CREATE TABLE album_rank( album_id INT NOT NULL, rank INT NOT NULL, KEY(album_id) ) I want to query on the album_id get the results ordered by rank but I want to avoid doing an ORDER BY in the query because of the filesort that it usually triggers so I pre-ordered the rows in my table by inserting them in the order I wanted with a CREATE TABLE ordered SELECT * FROM unordered ORDER BY album_id, rank ASC For some reason I get the data back in a different order I've tried ORDER BY album_id,rank ASC DESC in case it's a FILO or FIFO Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ? No That is the nature of relational databases You might want to consider adding an AUTO_INCREMENT column, then inserting in the order you want them back Then you can retrieve in that order by using an ORDER BY on the AUTO_INCREMENT column
