Hello All, I believe I've found a reason for and a fix to a problem I've been experiencing for some time now. My MySQL server has been restarting frequently - once, twice, even three times a day for the past few months. If I shutdown MySQL and run myisamchk on the tables, I'd get:
myisamchk: MyISAM file database/table.MYI myisamchk: warning: ## clients is using or hasn't closed the table properly Where ## could be anywhere from 1 to 2000, and this happened on almost every table. Looking at the error log, I also saw a lot of these: 011127 8:36:27 Aborted connection 5992 to db: 'database' user: 'root' host: `localhost' (Got an error reading communication packets) Usually happening every few minutes. After about 3-4 of them, the error log would say that mySQL suddenly got signal 11 and would restart without warning. The box was used exclusively for a large, cumbersome PHP application. The PHP application periodically restarts Apache for changes to its configuration file to take effect. This is where the problems all stem from. The PHP runs a sudo command to "apachectl restart". When someone was surfing some of the hosted sites using MySQL, and Apache just restarted immediately, the PHP would not reach the end of its script and terminate its connection to MySQL. So, after a while, these dead connections accumulated, tables weren't being closed properly, and there were frequent errors when browsing the sites. I believe this caused corrupted indexes, and MySQL would attempt to restart. I changed the PHP->Sudo command to run "apachectl graceful" instead, allowing PHP scripts to finish completely and close their connections correctly. Since this morning, when this change was made, I have not had a single error for a couple hours. In the past, I would be expecting errors at least every few minutes. Now, here's ANOTHER possible problem that we fixed. We noticed that if there was a high CPU usage (99-100%), MySQL would restart about every 15 seconds. We also noticed that there were some queries that were not defined as being "slow" but they DID take up 50-60% CPU every time the queries were run. We looked at the tables, noticed that there were some useless indexes, and re-indexed one of our main tables, and now, MySQL remains below 12% at all times - usually below 2%. The pages load faster and MySQL is generally a lot faster overall. The trick was simply to index columns used frequently in WHERE conditions in queries. My two cents - hope it helps someone. - Jonathan --------------------------------------------------------------------- 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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
