morteza3245

Well-Known Member
Sep 16, 2013
148
1
68
cPanel Access Level
Root Administrator
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.
 

morteza3245

Well-Known Member
Sep 16, 2013
148
1
68
cPanel Access Level
Root Administrator
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

Administrator
Staff member
Apr 11, 2011
47,913
2,202
363
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:

Code:
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

Well-Known Member
Sep 16, 2013
148
1
68
cPanel Access Level
Root Administrator
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

Administrator
Staff member
Apr 11, 2011
47,913
2,202
363
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.