LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 11-14-2012, 02:15 AM   #1
eyanu
Member
 
Registered: Jul 2012
Location: kampala,Uganda
Distribution: backtrack
Posts: 87

Rep: Reputation: Disabled
MYSQL takes up 500% CPU


Hello of recent my load has really been high and one of the causes i think is mysql, here is my "top" results:
Quote:
top - 08:08:29 up 27 days, 16:19, 2 users, load average: 92.15, 63.59, 37.66
Tasks: 660 total, 17 running, 636 sleeping, 6 stopped, 1 zombie
Cpu(s): 38.5%us, 48.4%sy, 12.7%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Mem: 32814456k total, 9048384k used, 23766072k free, 201140k buffers
Swap: 13631376k total, 680332k used, 12951044k free, 1385132k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4424 mysql 26 6 5014m 831m 7932 S 601.8 2.6 1559:03 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plu
10686 root 20 0 1483m 643m 660 S 11.4 2.0 2036:14 /usr/sbin/sw-collectd -C /etc/sw-collectd/collectd.conf
1747 root 20 0 4476 1160 552 R 7.4 0.0 0:00.24 fuser -s ./sess_jejh8pvk9i3u3896q7rihopne7
and also my my.cnf:
Quote:
[mysqld]
#bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
wait_timeout=30
connect_timeout=50
max_connections= 500
key_buffer=384M
max_allowed_packet=64M
table_cache=1800
sort_buffer_size=2M
read_rnd_buffer_size=524288
myisam_sort_buffer_size=64M
thread_cache_size=384
query_cache_size=128M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
I ran a mysqltuner.pl and this were it's recommendations:
Quote:
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 128M)
sort_buffer_size (> 2M)
read_rnd_buffer_size (> 512K)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 1800)
but still the load still remains high and below is mytop results:
Quote:
MySQL on localhost (5.5.28) up 0+11:01:26 [08:13:17]
Queries: 5.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 62917380/00/00/00
qps now: 0 Slow qps: 0.0 Threads: 22 ( 14/ 118) 25100/00/00/00
Key Efficiency: 100.0% Bps in/out: 0.0/ 0.7 Now in/out: 8.2/ 3.2k

Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
2587588 admin localhost test 0 Query show full processlist
2587885 umempltd_ localhost umempltd_p 0 Query delete from ASB_TBL_g_tc5aI7z96Q28j06SN1o_9XPEfDYsUwTr3i_0B4_135
2587886 codeca localhost codeaorg_c 0 Query SELECT * FROM jos_session WHERE session_id = '3av2um8gnosots40pi
2585090 ufwgadmin localhost envatorg_u 1 Sleep
2585691 urbag_roc localhost urbagcom_u 1 Sleep
2587647 codeca localhost codeaorg_c 1 Query SELECT * FROM jos_phocaguestbook_items WHERE catid = 1 AND publi
2587649 codeca localhost codeaorg_c 1 Query SELECT * FROM jos_phocaguestbook_items WHERE catid = 1 AND publi
2587815 webdb localhost actvaorg_w 1 Sleep
2587883 codeca localhost codeaorg_c 1 Query SELECT * FROM jos_phocaguestbook_items WHERE catid = 1 AND publi
2587884 codeca localhost codeaorg_c 1 Query UPDATE `jos_session` SET `time`='1352877196',`userid`='0',`usert
2587758 codeca localhost codeaorg_c 2 Query SELECT * FROM jos_phocaguestbook_items WHERE catid = 1 AND publi
2587760 codeca localhost codeaorg_c 2 Query SELECT * FROM jos_phocaguestbook_items WHERE catid = 1 AND publi
2587591 codeca localhost codeaorg_c 3 Query SELECT * FROM jos_phocaguestbook_items WHERE catid = 1 AND publi
2587703 bigeye localhost big10000_b 3 Sleep
2587759 bigeye localhost big10000_b 3 Sleep
2587562 codeca localhost codeaorg_c 4 Query SELECT * FROM jos_phocaguestbook_items WHERE catid = 1 AND publi
2587644 bigeye localhost big10000_b 5 Sleep
2587283 bigeye localhost big10000_b 11 Sleep
2585152 webdb localhost actvaorg_w 26 Sleep
2464558 admin localhost 2539 Query SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_schema.TABLE
2464438 admin localhost 2541 Query SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TA
2426522 admin localhost 3252 Query SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_sc
Could you please advise on how i should go about this.
 
Old 11-14-2012, 02:52 AM   #2
eyanu
Member
 
Registered: Jul 2012
Location: kampala,Uganda
Distribution: backtrack
Posts: 87

Original Poster
Rep: Reputation: Disabled
Also there is a user who takes up 100% of CPU
Quote:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2796 umempltd 24 4 757m 77m 11m R 99.9 0.2 0:21.53 /usr/bin/php-cgi
I tried killing that user with:
Quote:
skill -STOP umempltd
but his processes keep popping up.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
CPU Overheating in TOSHIBA M-500 please help go4mihir Linux - Newbie 1 03-23-2011 01:43 AM
Firefox takes 99% CPU?? nbcohen Fedora 2 10-21-2009 09:20 PM
ksoftirqd_CPU takes 99% CPU jayarajmohan Red Hat 0 07-22-2009 07:27 AM
FC 2 GNOME takes 99.9% of cpu. bruse Fedora 6 04-26-2005 08:24 PM
events/0 takes over my cpu tomhemba Fedora 4 08-01-2004 05:05 AM


All times are GMT -5. The time now is 07:19 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration