> 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
