Mysql server is going down very frequently

Dear all,

I am facing a problem from past few hours. I have a powerful dedicated server. My sql server has started going down as the traffic has started increasing.

You can not login and sql server becomes completely unresponsive. It works only after rebooting. I get a lot of errors but pasting initial few lines of one of the errors below. Can anybody tell me what the problem is?

Mysql error: Out of resources when opening file '/tmp/#sql_dbd_0.MYD' (Errcode: 24)

Found error in the file '/home/site/public_html/inc/classes/BxDolFilesDb.php' at line 290.
Called 'getRow' function with erroneous argument #0.

Below is the server log before the server went down.

It appears that MySQL had too many open files. The following is from the server's MySQL error log (/var/lib/mysql/host.site.com.err:

140623 8:57:49 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:49 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:50 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:50 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:50 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:50 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:51 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:51 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:51 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:51 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:52 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:52 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:52 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:53 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:57:57 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:58:14 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_privacy_actions.frm' (errno: 24)
140623 8:58:15 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:58:36 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:58:37 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:58:38 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 8:58:39 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_ads_main.frm' (errno: 24)
140623 9:00:13 [ERROR] Error in accept: Too many open files
140623 9:00:58 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_ip_members_visits.frm' (errno: 24)
140623 9:01:07 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_privacy_actions.frm' (errno: 24)
140623 9:01:10 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_privacy_actions.frm' (errno: 24)
140623 9:01:11 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_privacy_actions.frm' (errno: 24)
140623 9:01:11 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_privacy_actions.frm' (errno: 24)
140623 9:01:13 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_privacy_actions.frm' (errno: 24)
140623 9:01:14 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_privacy_actions.frm' (errno: 24)
140623 9:01:50 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_privacy_actions.frm' (errno: 24)
140623 9:01:51 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_privacy_actions.frm' (errno: 24)
140623 9:01:52 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_privacy_actions.frm' (errno: 24)
140623 9:04:01 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_photos_cmts_albums.frm' (errno: 24)
140623 9:04:08 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/bx_photos_cmts_albums.frm' (errno: 24)
140623 9:04:22 [ERROR] /usr/sbin/mysqld: Can't open file: './site_db/sys_privacy_actions.frm' (errno: 24)
140623 9:04:29 [ERROR]

First of all, make sure that you have set proper resources for Dolphin in the mysql config file; my.ini.  Dolphin is a resource hungry platform; which is the case for all such platforms and is just the way it is.  I would post my config file but I am away from the server at the moment.  There are settings beyond just what Boonex has listed in the HELP guide above but the HELP guide is a start.  You can check some of the recommended settings by looking at your Host Tools in the admin.

The error message tells you that you are bumping up against mysql resource limits.

 Thanks for the reply. I have set mysql limits exactly the way it is mentioned on "http://www.boonex.com/trac/dolphin/wiki/HostingServerSetupRecommendations#MySQL". And I received this error when there were just 4-5 simultaneous users on the site. So is this normal? Or is there something in the script which is eating extra resources unnecessarily?

If this is normal then how can I increase the resource limit? I have a very strong machine with 32GB dedicated RAM.

In /etc/my.cnf, add or update:

open_files_limit=51200

or some other large value. Save and restart MySQL.

 Thanks. I should change it from whm/sql services/phpmyadmin> variables also right?

But is this condition normal?

my.cnf is enough, as that's the global MySQL configuration file. Yes, these kind of issues are normal when you have larger sites; MySQL needs extra tuning sometimes.

Thanks. I just did this. Will update you the outcome soon.

 So far its working fine. Thank you for helping me out. Can I also update the values of other parameters for faster performance?

innodb_file_per_table=1
key_buffer_size = 48M
tmp_table_size = 16M
max_heap_table_size = 16M
wait_timeout = 20
connect_timeout = 10
join_buffer_size = 1M
read_buffer_size = 1M
max_allowed_packet=268435456
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
key_buffer_size = 64M
max_heap_table_size = 256M
tmp_table_size = 256M
thread_cache = 128

This is what I am overriding default values of in my my.cnf 

query_cache_limit = 1M
query_cache_type = ON
query_cache_limit = 1M
query_cache_size = 36M
key_buffer_size = 64M
max_heap_table_size = 256M
tmp_table_size = 256M
thread_cache = 128