Re: MySQL problem with Traffic and Updates
On Thu, Oct 25, 2001 at 08:27:00AM -0700, Bill Adams wrote: At http://www.mysql.com/doc/ do a search for multiple processors. There's a refence to http://www.mysql.com/Downloads/Patches/linux-fork.patch that might help. And don't forget to make sure you have good/needed indexes. (e.g See the manual entry on EXPLAIN, run myisamchk -a, etc..). And make sure you're logging slow queries. Then take each of the logged queries (I log anything that takes more than two seconds) and run them through EXPLAIN to see where you could modify the query and/or change indexes. -- Michael T. Babcock CTO, FibreSpeed Ltd. - 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: MySQL problem with Traffic and Updates
Today after my server got a real traffic hit for the first time since I installed PhpAdsNew (a MySQL/PHP Ad software), MySQL was crashed. Here's how it happened: watching the top load levels, I saw it slow creep up about to 3, then rockets quickly to 30s, 40s, and on!! MySQL was at this time taking up 100% of both of the processors in the server. There were about 40 or so MySQL processes spawned at this point. Doing a 'mysqladmin processlist' command on the server showed me a VERY LONG LIST of processes that were open, a lot of which were PhpAdsNew UPDATE commands. This can happen with MyISAM tables, where table locking is an issue. It should only be a problem if some ugly queries are happening though... Here's the rundown (all queries are assumed to be on the same table): 1. Somebody runs a SELECT that takes a while to complete 2. Somebody runs an UPDATE or DELETE (or possibly INSERT, depending on the circumstances) 3. The UPDATE has to wait for the SELECT to finish to begin processing 4. All subsequent queries have to wait for the UPDATE to finish 5. Depending on configuration, you may run out of active MySQL threads to handle the incoming queries 6. The CPU spends all of it's time spawning new threads to handle the incoming connections, instead of finishing the SELECT that's holding up traffic 7. The increase in memory usage is enormous... 8. If things are really bad, the server will eventually crash Possible solutions: 1. Get rid of all long SELECT statements 2. Add proper indexes so that long SELECT statements become short 3. Switch to InnoDB if #1 and #2 aren't feasible, the multi-versioning and row-level locking will eliminate those problems There are other possible solutions. I've done some work in similar environments, if you'd like I can help you find the best way to get your database working smoothly. Steve Meyers - 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: MySQL problem with Traffic and Updates
Hi Ryan! Today after my server got a real traffic hit for the first time since I installed PhpAdsNew (a MySQL/PHP Ad software), MySQL was crashed. Here's how it happened: watching the top load levels, I saw it slow creep up about to 3, then rockets quickly to 30s, 40s, and on!! MySQL was at this time taking up 100% of both of the processors in the server. There were about 40 or so MySQL processes spawned at this point. Doing a 'mysqladmin processlist' command on the server showed me a VERY LONG LIST of processes that were open, a lot of which were PhpAdsNew UPDATE commands. The system it is on is a Dual-Athlon 1.2 GHz with 1 GB of memory. I have heard of systems that run at 450 MHz to be able to handle more ad views than this system is taking. I have the latest MySQL and PHP, all on Red Hat 7.1. This is the second ad software that has done this, so I don't think the problem is so much in the software, but something about my MySQL config or setup. Obviously the system should handle the load. At http://www.mysql.com/doc/ do a search for multiple processors. There's a refence to http://www.mysql.com/Downloads/Patches/linux-fork.patch that might help. Have a :) day! jb ob-filter-words: table sql database -- jim barchuk [EMAIL PROTECTED] - 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: MySQL problem with Traffic and Updates
jim barchuk wrote: Hi Ryan! Today after my server got a real traffic hit for the first time since I installed PhpAdsNew (a MySQL/PHP Ad software), MySQL was crashed. Here's how it happened: watching the top load levels, I saw it slow creep up about to 3, then rockets quickly to 30s, 40s, and on!! MySQL was at this time taking up 100% of both of the processors in the server. There were about 40 or so MySQL processes spawned at this point. Doing a 'mysqladmin processlist' command on the server showed me a VERY LONG LIST of processes that were open, a lot of which were PhpAdsNew UPDATE commands. The system it is on is a Dual-Athlon 1.2 GHz with 1 GB of memory. I have heard of systems that run at 450 MHz to be able to handle more ad views than this system is taking. I have the latest MySQL and PHP, all on Red Hat 7.1. This is the second ad software that has done this, so I don't think the problem is so much in the software, but something about my MySQL config or setup. Obviously the system should handle the load. At http://www.mysql.com/doc/ do a search for multiple processors. There's a refence to http://www.mysql.com/Downloads/Patches/linux-fork.patch that might help. And don't forget to make sure you have good/needed indexes. (e.g See the manual entry on EXPLAIN, run myisamchk -a, etc..). b. - 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
