MySQL Stability

[Hostmaster]

>>I'm running Centos 5.2 with mysql-5.0.45-7.el5

>>Every few days my MySQL seems to die and in the logs I'm seeing the 
>>following errors.

>>090528 14:05:37 [ERROR] /usr/libexec/mysqld: Sort aborted
>>090528 14:07:56 [ERROR] Error in accept: Too many open files
>>090528 14:11:52 [ERROR] /usr/libexec/mysqld: Sort aborted
>>090528 14:12:13 [ERROR] Error in accept: Too many open files
>>090528 14:12:54 [ERROR] /usr/libexec/mysqld: Sort aborted
>>090528 14:14:26 [ERROR] /usr/libexec/mysqld: Sort aborted
>>090528 14:18:29 [Note] /usr/libexec/mysqld: Normal shutdown

I had a very similar problem with MySQL quite some time ago, although I think it
was with 5.0.67. When you hit the "too many open files" problem, do a "ps ax
|grep mysql" to find MySQL's PID, and then do "lsof -p PID |grep deleted" and
see if you get a long list of open file handles on deleted files. If you do, the
answer is to upgrade MySQL - I moved to 5.0.77 and it cured the problem
immediately.

>>Then I stop it used the init script and it shutsdown properly.

>>Then I start it and the problem is gone for the next few days.

>>The box is processing quite a few mails daily, but I don't think it's 
>>exceptional.

>>Processed:    10,277    409.3Mb
>>Clean:    3,371    32.8%
>>Spam:    551    5.4%
>>High Scoring Spam:    6,355    61.8%

Mine handles around 25k emails a day, so this isn't a huge install

>>My maillog.MYD is 3.4Gigs, is this too large?

You might want to check the db-clean.php and the values it has configured for
retention. My current database is around 1.2-1.5 GB, and holds 90 days of mail
at 25k emails a day, so yours goes back a long way at my guess. There are some
details around about setting up an "archive" of MailWatch databases so that your
main database can be a bit smaller.

>>Sorry I realise this should probably be sent to a MySQL mailing list, 
>>but I'm sure people must have some huge MySQL DB's with some of their 
>>own MailScanner implementations I just want to make sure that we have 
>>nothing exceptional.

Yep, you'll find plenty of people on list who are using MailScanner. Just as a
suggestion, you might want to review your MySQL settings - Search for
"tuning-primer.sh" to run against your install of MySQL for some pointers.

>>Thanks, any tips will help.

Pleasure


Best Regards, 
Richard Garner (A+, N+, AMBCS, MOS-O) 

Hostmaster 
Computer Service Centre 
 
http://www.computerservicecentre.com