After MySQL 5.1 -> 5.5 upgrade, chkservd HANG, MySQL goes away issues

[Trane Francks]

Greetings, all.

A few days ago, we upgraded MySQL to 5.5 and since then have been in a world of hurt.

The first issue is that during the daily upcp processing, MySQL was "going away" and all our db-based sites went offline. Based on a different thread regarding dump failures found here, I commented out a couple of lines in my.cnf and our problem initially looked to be solved:

#default-storage-engine=MyISAM
#innodb_file_per_table=1

Alas, now I'm beginning to receive chkservd HANG notifications alongside excessive resource usage notifications. At first, I rebooted the server (it'd been up 376 days, so it was maybe due), but now I just awoke to find the same thing and all our sites were unavailable with database errors. Restarting mysqld got the show back on the road.

I'm seriously ruing the day we upgraded from 5.1, which had been amazingly stable for us. MySQL 5.5.27 has not been a successful upgrade. (Running WHM 11.34.0.8.)

Ideas?

 

[Trane Francks]

I found this tidbit in the tailwatchd.log file:

[5300] [2012-11-07 21:00:00 +0000] [Cpanel::TailWatch::Eximstats] Resetting email limits to new starttime of 1352322000Notification => [email protected] via EMAIL [level => 1]DBD::mysql::st execute failed: Out of resources when opening file './eximstats/failures.MYD' (Errcode: 24) at /usr/local/cpanel/Cpanel/TailWatch/Eximstats.pm line 1005, <GEN0> line 7124.


Is 5.5.27 less friendly with system resources than 5.1? Open files via ulimit -a is 1024.

 

[Trane Francks]

Hey, all.

We seem to have gotten this under control. Here are the changes I made to the system. Twenty-four-plus hours later, the system seems quick and stable. Barring unexpected new troubles, I'd say we're good.

/etc/my.cnf:

#default-storage-engine=MyISAM
#innodb_file_per_table=1
open_files_limit=20000


/etc/security/limits.conf:

mysql soft nofile 10000
mysql hard nofile 20000


With the changes commenting out the two lines in my.cnf, performance of Drupal 7 MySQL databases is much snappier. I'm not entirely understanding why it would make a difference, but there's no question that the response time is much, much better. The other changes allow MySQL to use more file handles. It's interesting to note that while the file table usage is larger, process counts, system load and other indicators are reduced.

We've now got a much happier server. No more crashes and no more chkservd hangs. We're snappy and, boy, are we handsome.