100% of cpu used (mysql)

raitoringo · Post time: 2013-12-26 22:47

Hello, guys! I have a serious problem!

I'm with problems of excessive CPU usage. My server have AMD dual core 2ghz and the CPU usage is at 100% with a DataBase 80mb and 1800 Users.

My discuz is x3ML

ravipratap95 · Post time: 2013-12-27 03:32

Admin center>database >optimise table
this will reduce the size me the table.try that

vot · Post time: 2013-12-27 07:26

Try to discover what the sql processes are slow!
Edit the mysql config and add such type settings:
log-slow-file = /var/log/mysql/slow.log # full path to the log file
log-slow-time = 2 # long time, seconds

Then restart mysql.

raitoringo · Post time: 2013-12-27 22:03

Edited by raitoringo at 2013-12-27 23:05

This is the log

/usr/sbin/mysqld, Version: 5.5.34-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 131227 7:15:14
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 2.109603 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use XXXX_dz25new;
SET timestamp=1388153714;
SELECT * FROM pre_common_syscache WHERE `cname` IN('plugin','diytemplatename','setting','style_default','cronnextrun');
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 2.289847 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1388153714;
SELECT * FROM pre_common_syscache WHERE `cname` IN('plugin','diytemplatename','setting','style_default','cronnextrun');
/usr/sbin/mysqld, Version: 5.5.34-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 131227 8:30:57
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 2.036145 Lock_time: 0.000030 Rows_sent: 0 Rows_examined: 0
use XXXX_dz25new;
SET timestamp=1388158257;
REPLACE INTO pre_common_syscache SET `cname`='cronnextrun' , `ctype`='0' , `dateline`='1388158254' , `data`='1388160000';
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 2.091688 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1388158257;
SELECT * FROM pre_common_syscache WHERE `cname` IN('plugin','diytemplatename','setting','style_default','cronnextrun');
# Time: 131227 8:31:00
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 4.785064 Lock_time: 1.982433 Rows_sent: 5 Rows_examined: 5
SET timestamp=1388158260;
SELECT * FROM pre_common_syscache WHERE `cname` IN('plugin','diytemplatename','setting','style_default','cronnextrun');
/usr/sbin/mysqld, Version: 5.5.34-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
/usr/sbin/mysqld, Version: 5.5.34-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
/usr/sbin/mysqld, Version: 5.5.34-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 131227 10:15:14
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 4.899686 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1388164514;
SET character_set_connection=utf8, character_set_results=utf8, character_set_client=binary,sql_mode='';
# Time: 131227 10:15:17
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 3.028333 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use XXXX_dz25new;
SET timestamp=1388164517;
SELECT * FROM pre_common_member WHERE `uid`='29';
# Time: 131227 10:15:21
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 3.705166 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1388164521;
# administrator command: Init DB;
# Time: 131227 10:15:41
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 19.589375 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1388164541;
SELECT * FROM pre_common_syscache WHERE `cname` IN('plugin','diytemplatename','setting','style_default','cronnextrun');
# User@Host: root[root] @ localhost []
# Query_time: 19.655010 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1388164541;
show processlist;
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 19.852219 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1388164541;
SELECT * FROM pre_common_syscache WHERE `cname` IN('bbcodes','smilies','smileytypes');
# Time: 131227 10:15:42
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 19.951472 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1388164542;
SELECT * FROM pre_common_syscache WHERE `cname` IN('plugin','diytemplatename','setting','style_default','cronnextrun');
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 20.035670 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1388164542;
SELECT * FROM pre_common_syscache WHERE `cname` IN('plugin','diytemplatename','setting','style_default','cronnextrun');
/usr/sbin/mysqld, Version: 5.5.34-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 131227 10:41:05
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 17.902020 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use XXXX_dz25new;
SET timestamp=1388166065;
SELECT * FROM pre_common_syscache WHERE `cname` IN('plugin','diytemplatename','setting','style_default','cronnextrun');
# Time: 131227 10:41:26
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 20.899918 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1388166086;
SELECT * FROM pre_common_syscache WHERE `cname` IN('pluginlanguage_system');
/usr/sbin/mysqld, Version: 5.5.34-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 131227 11:45:20
# User@Host: XXXX_dz25new[XXXX_dz25new] @ localhost []
# Query_time: 5.271508 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use XXXX_dz25new;
SET timestamp=1388169920;
SELECT * FROM pre_common_syscache WHERE `cname` IN('bbcodes','smilies','smileytypes');

Copy the Code

vot · Post time: 2013-12-28 16:16

That log records means that the server (or the mySQL daemon) is very busy.
Run mySQL shell and check what the processes runs at the same tome:
show processlist;

And the second:
I always recomend to NOT USE the Sql Cache!
Use the FILE Cache instead!
config_global.php:

// --------------------------- CONFIG CACHE --------------------------- //
$_config['cache']['type'] = 'file';

Copy the Code

raitoringo · Post time: 2013-12-30 16:04

my problem remains the same

Repair a MySQL Database
Repairing XXXX_dz25new
XXXX_dz25new.pre_badge_log_7ree OK
X
XXXX_dz25new.pre_common_plugin_luckypacket OK
XXXX_dz25new.pre_common_plugin_luckypacketlog OK
XXXX_dz25new.pre_common_plugin_trthank OK
XXXX_dz25new.pre_common_plugin_trthanklog OK
XXXX_dz25new.pre_common_pluginvar OK
XXXX_dz25new.pre_common_process note : The storage engine for the table doesn't support repair
XXXX_dz25new.pre_common_regip OK
XXXX_dz25new.pre_common_relatedlink OK
XXXX_dz25new.pre_common_remote_port OK
XXXX_dz25new.pre_common_report OK
XXXX_dz25new.pre_common_searchindex OK
XXXX_dz25new.pre_common_secquestion OK
XXXX_dz25new.pre_common_session note : The storage engine for the table doesn't support repair
XXXX_dz25new.pre_common_setting OK
XXXX_dz25new.pre_common_smiley OK
XXXX_dz25new.pre_common_sphinxcounter OK
XXXX_dz25new.pre_common_stat OK
XXXX_dz25new.pre_common_statuser OK
XXXX_dz25new.pre_common_style OK
XXXX_dz25new.pre_common_stylevar OK
XXXX_dz25new.pre_common_syscache OK
XXXX_dz25new.pre_common_tag OK
XXXX_dz25new.pre_common_tagitem OK
XXXX_dz25new.pre_common_task OK
XXXX_dz25new.pre_common_taskvar OK
XXXX_dz25new.pre_common_template OK
XXXX_dz25new.pre_common_template_block OK
XXXX_dz25new.pre_common_template_permission OK
XXXX_dz25new.pre_common_uin_black OK
XXXX_dz25new.pre_common_usergroup OK
XXXX_dz25new.pre_connect_memberbindlog OK
XXXX_dz25new.pre_connect_postfeedlog OK
XXXX_dz25new.pre_forum_activityapply OK
XXXX_dz25new.pre_forum_announcement OK
XXXX_dz25new.pre_forum_threadclass OK
XXXX_dz25new.pre_forum_threadclosed OK
XXXX_dz25new.pre_forum_threaddisablepos note : The storage engine for the table doesn't support repair
XXXX_dz25new.pre_forum_threadhot OK
XXXX_dz25new.pre_forum_threadimage OK
XXXX_dz25new.pre_home_feed OK
XXXX_dz25new.pre_home_feed_app OK
XXXX_dz25new.pre_home_follow OK
XXXX_dz25new.pre_home_follow_feed OK
XXXX_dz25new.pre_home_pic OK
XXXX_dz25new.pre_home_pic_moderate OK
XXXX_dz25new.pre_home_picfield OK
XXXX_dz25new.pre_ucenter_protectedmembers OK
XXXX_dz25new.pre_ucenter_settings OK
XXXX_dz25new.pre_ucenter_sqlcache OK
XXXX_dz25new.pre_ucenter_tags OK
XXXX_dz25new.pre_ucenter_vars note : The storage engine for the table doesn't support repair
Done.

Copy the Code

vot · Post time: 2013-12-31 10:38

Check the server load average. (shell "top" command in unix/linux).

P.S.
What the hosting plan used?

raitoringo · Post time: 2013-12-31 20:53

Editado por raitoringo en 2014-1-2 14:24

VPS - 2Ghs - 1,5Gb ram

CPU
99.89%
Load Average 9.15, 6.38, 2.88

vot · Post time: 2014-01-02 13:50

Try to discover what the processes are running as "php-cgi".
May be these are some plugin cron scripts?
Try to stop all non-default plugins and their cron-scripts.

raitoringo · Post time: 2014-01-03 15:24

Yes, the problem was a plugin

The strange thing is that before it worked well, and the error appeared suddenly. Problem solved. Thank you very much!

Account		Remember me	Forgot password?
Password			Register