important -mysql problem- down

[morteza3245]

Hi
im sorry for not good subject, forgive me

today i see my site's not load, after check, i see mysql is down, then try for start and restart,
then i see a table from basic site (myself hosting site) have 38GB size, (that was size from log sms)

then i try faster empty that,

now my problem is:
when i suspend same user, all other sites work fine,
but when i unsuspend same user, i see Slowly all site have error (cant connect to database), then i suspend same user, i see faster site's work fine.

Please help me

i do below action, but without different:

/etc/rc.d/init.d/mysql stop

2) Repair all SQL databases:
myisamchk -r /var/lib/mysql/*/*.MYI

3) Start mysql again:
/etc/rc.d/init.d/mysql start

mysqlcheck --all-databases -r #repair
mysqlcheck --all-databases -a #analyze
mysqlcheck --all-databases -o #optimize

mysqlcheck -A --auto-repair

repair and optimize from phpmyadmun

but again i have same problem,

i have below log:

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002124 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002123 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002122 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002121 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002120 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002119 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002117 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002116 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002114 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002113 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002112 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002111 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002110 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002109 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002108 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002106 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002105 user: 'user_new'

131030 19:03:51 [Warning] /usr/sbin/mysqld: Forcing close of thread 2002104 user: 'user_new'

131030 23:01:01 [Warning] /usr/sbin/mysqld: Forcing close of thread 12 user: 'root'

131030 23:01:01 [Warning] /usr/sbin/mysqld: Forcing close of thread 10 user: 'root'

131030 23:01:01 [Warning] /usr/sbin/mysqld: Forcing close of thread 8 user: 'root'

131030 19:59:36 InnoDB: Starting shutdown...
131030 19:59:38 InnoDB: Shutdown completed; log sequence number 940323341
131030 19:59:38 [Note] /usr/sbin/mysqld: Shutdown complete

Please help me.

Thanks.

 

[cPanelMichael]

Hello

Could you let us know the contents of your /etc/my.cnf file?

Thank you.

 

[morteza3245]

Hi
below is my.cnf config:

[mysqld]
local-infile=0
myisam_use_mmap=1

join_buffer_size=1M
read_rnd_buffer_size=2M

query_cache_type = 1
query_cache_size=50M
query_cache_limit=1M

tmp_table_size=50M
max_heap_table_size=50M

thread_cache_size=30
table_open_cache = 5000
table_definition_cache = 2500

max_allowed_packet=32M
key_buffer_size=5G

open_files_limit=50000

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1


innodb_buffer_pool_size = 200M

 

[cPanelMichael]

If the issue only occurs when one account is active, it's likely that account that is causing MySQL to fail. You can run the following command when the account is active to see what tables are causing the problem:

mysqladmin processlist;

Also, the following thread is helpful for obtaining statistics that will better help others give you advice on optimizing the /etc/my.cnf values:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[morteza3245]

Ah, yes, i see same user have much mysql sleep, see below pleaase:

Note: this "sameuser_new" is same user have problem

what i must do?

[email protected] [~]# mysqladmin processlist;
+-------+------------------+-----------+--------------------+----------------+------+--------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+-----------+--------------------+----------------+------+--------------------+------------------+
| 1 | eximstats | localhost | eximstats | Sleep | 11 | | |
| 228 | root | localhost | information_schema | Sleep | 8 | | |
| 11895 | root | localhost | information_schema | Sleep | 1506 | | |
| 18978 | DELAYED | localhost | eximstats | Delayed insert | 11 | Waiting for INSERT | |
| 19026 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19027 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19028 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19029 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19030 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19031 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19032 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19033 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19034 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19035 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19037 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19038 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19039 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19040 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19041 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19042 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19043 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19044 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19045 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19046 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19047 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19048 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19049 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19050 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19051 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19052 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19053 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19054 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19055 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19056 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19057 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19058 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19059 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19060 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19061 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19063 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19064 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19065 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19066 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19067 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19068 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19069 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19070 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19071 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19072 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19073 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19074 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19075 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19076 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19077 | sameuser_new | localhost | sameuser_new | Sleep | 8 | | |
| 19078 | sameuser_new | localhost | sameuser_new | Sleep | 7 | | |
| 19088 | sameuser_new | localhost | sameuser_new | Sleep | 5 | | |
| 19089 | sameuser_new | localhost | sameuser_new | Sleep | 5 | | |
| 19092 | sameuser_new | localhost | sameuser_new | Sleep | 5 | | |
| 19098 | sameuser_new | localhost | sameuser_new | Sleep | 4 | | |
| 19099 | sameuser_new | localhost | sameuser_new | Sleep | 4 | | |
| 19103 | sameuser_new | localhost | sameuser_new | Sleep | 3 | | |
| 19107 | sameuser_new | localhost | sameuser_new | Sleep | 3 | | |
| 19108 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19109 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19110 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19111 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19112 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19113 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19114 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19115 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19117 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19118 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19119 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19120 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19121 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19122 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19123 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19124 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19126 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19127 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19129 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19130 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19131 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19132 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19133 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19134 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19136 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19137 | sameuser_new | localhost | sameuser_new | Sleep | 2 | | |
| 19138 | sameuser_new | localhost | sameuser_new | Sleep | 1 | | |
| 19139 | sameuser_new | localhost | sameuser_new | Sleep | 1 | | |
| 19140 | sameuser_new | localhost | sameuser_new | Sleep | 1 | | |
| 19142 | sameuser_new | localhost | sameuser_new | Sleep | 1 | | |
| 19143 | sameuser_new | localhost | sameuser_new | Sleep | 1 | | |
| 19144 | sameuser_new | localhost | sameuser_new | Sleep | 1 | | |
| 19145 | sameuser_new | localhost | sameuser_new | Sleep | 1 | | |
| 19146 | sameuser_new | localhost | sameuser_new | Sleep | 1 | | |
| 19147 | sameuser_new | localhost | sameuser_new | Sleep | 1 | | |
| 19148 | sameuser_new | localhost | sameuser_new | Sleep | 1 | | |
| 19152 | sameuser_new | localhost | sameuser_new | Sleep | 0 | | |
| 19153 | musicnte_hojjat | localhost | musicnte_wp | Sleep | 0 | | |
| 19154 | sameuser_new | localhost | sameuser_new | Sleep | 0 | | |
| 19160 | otheruser_other12 | localhost | otheruser_other12 | Sleep | 0 | | |
| 19163 | sameuser_new | localhost | sameuser_new | Sleep | 0 | | |
| 19164 | sameuser_new | localhost | sameuser_new | Sleep | 0 | | |
| 19166 | socksira_talar | localhost | socksira_talar | Sleep | 0 | | |
| 19167 | root | localhost | | Query | 0 | | show processlist |
+-------+------------------+-----------+--------------------+----------------+------+--------------------+------------------+
[email protected] [~]#

Thanks.

 

[cPanelMichael]

You will likely want to suspend the account and restart MySQL. Or, contact the user to alert them of the issue with their database. Otherwise, the following thread is helpful for obtaining statistics that will better help others give you advice on optimizing the /etc/my.cnf values:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[morteza3245]

Thanks, user is for myself
that was very very good 5years old, now have this problem
how i can know which table is sleep?

 

[cPanelMichael]

Thanks, user is for myself
that was very very good 5years old, now have this problem
how i can know which table is sleep?

The table is not listed. You will need to manually review the database. You will likely find one specific table with a large size or with several records. phpMyAdmin is useful for this if you are not comfortable on the command line.

Thank you.