hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : Hosting Security and Technology : Mysqld using 100 percent of cpu
Reply

Forum Jump

Mysqld using 100 percent of cpu

Reply Post New Thread In Hosting Security and Technology Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old 04-10-2007, 03:56 PM
1EightT 1EightT is offline
WHT Addict
 
Join Date: Aug 2004
Posts: 126

Mysqld using 100 percent of cpu


I've got a strange situation here. We have some software which we run on multiple servers. As of today the software is using 100 percent (sometimes more) of the cpus. Here is the result from top. It's usually far worse using 100 percent of both cpu's almost all of the time. I just restarted, but the server load is climbing to 20+. Any idea how I can figure out what is going on? The same software on two other identically configured servers runs fine. This server has had no problems for over 6 months, but suddenly this started today. The server is a dual opteron with 4GB of ram. The databases are innodb thus the high memory usage for mysql (innodb buffer pool size)

top - 13:52:54 up 3:14, 2 users, load average: 3.39, 4.42, 6.86
Tasks: 121 total, 4 running, 108 sleeping, 9 stopped, 0 zombie
Cpu(s): 66.1% us, 1.7% sy, 32.2% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 4090568k total, 3077004k used, 1013564k free, 52748k buffers
Swap: 2096440k total, 56k used, 2096384k free, 1796632k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11871 mysql 15 0 2581m 1.0g 3844 S 96 26.2 13:05.73 mysqld
7569 root 39 19 8048 2024 1304 R 65 0.0 32:27.49 http
11984 apache 15 0 152m 14m 5860 S 12 0.4 0:24.27 httpd
11990 apache 15 0 152m 17m 8376 S 9 0.4 0:14.31 httpd
12002 apache 16 0 153m 15m 5408 R 6 0.4 0:08.60 httpd
12040 apache 15 0 152m 17m 8524 S 5 0.4 0:03.86 httpd
11966 apache 15 0 153m 17m 8280 S 3 0.4 0:22.99 httpd



Sponsored Links
  #2  
Old 04-10-2007, 04:01 PM
jhadley jhadley is offline
Web Hosting Master
 
Join Date: Jun 2006
Location: United Kingdom
Posts: 1,743
Could you paste the contents of your my.cnf file?

__________________
Put your small hosting business on auto-pilot with Loading Deck

  #3  
Old 04-10-2007, 04:06 PM
1EightT 1EightT is offline
WHT Addict
 
Join Date: Aug 2004
Posts: 126
Sure, it's been the same on all three servers for months:

[mysqld]
connect_timeout=15
interactive_timeout=100
innodb_buffer_pool_size=2300M
join_buffer_size=1M
key_buffer=16M
max_allowed_packet=16M
max_connections=800
max_connect_errors=10
myisam_sort_buffer_size=32M
read_buffer_size=2M
read_rnd_buffer_size=2M
sort_buffer_size=2M
table_cache=1024
thread_cache_size=100
thread_concurrency=4
wait_timeout=300
query_cache_size=32M
query_cache_limit=1M
query_cache_type=1

Sponsored Links
  #4  
Old 04-10-2007, 04:35 PM
gbjbaanb gbjbaanb is offline
Retired Moderator
 
Join Date: Oct 2004
Location: Southwest UK
Posts: 1,159
tricky - you can obviously see that its mysql that is using all cpu, but cannot see why!

Have you looked at the slow_query log? Are there lots of queries being run all the time that are hogging it? (ie someone is clicking 'show all' over and over...?)

Second, check your indexes. If you've got a screwed or missing index then your queries may be performing table scans instead of using them. That'll absolutely destroy performance

Try running mytop -c, and mytop and posting the results here.

Have a look at your webserver error logs and see if there's anything interesting in there.

__________________
Do not meddle in the affairs of Dragons, for you are crunchy and taste good.

  #5  
Old 04-10-2007, 07:51 PM
1EightT 1EightT is offline
WHT Addict
 
Join Date: Aug 2004
Posts: 126
I know that our indexes are ok right now. Let me check the mytop command and see if that sheds any light on things. I do log slow queries so let me look at that as well. This one is really driving me crazy here. Let me post back what I find. THanks for the suggestions.

  #6  
Old 04-10-2007, 08:11 PM
1EightT 1EightT is offline
WHT Addict
 
Join Date: Aug 2004
Posts: 126
Here are the results from mytop:

Queries: 688.8k qps: 42 Slow: 32.0k Se/In/Up/De(%): 65/05/01/00
qps now: 47 Slow qps: 2.0 Threads: 346 ( 285/ 92) 65/05/00/00
Cache Hits: 232.1k Hits/s: 14.1 Hits now: 13.5 Ratio: 51.7% Ratio now: 43.9%
Key Efficiency: 88.6% Bps in/out: 61.9k/92.9k Now in/out: 125.6k/140.2k

Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
Use of uninitialized value in substitution (s///) at /usr/bin/mytop line 958.
1 root localhost mydns 0 Sleep
2 root localhost mydns 0 Sleep
93684 simplsec localhost simplsec_s 0 Sleep
100210 simplsec localhost simplsec_s 0 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
100224 simplsec localhost simplsec_s 0 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
105544 root localhost test 0 Query show full processlist
106193 simplsec localhost simplsec_s 0 Sleep
106195 simplsec localhost simplsec_s 0 Sleep
106196 mydns localhost mydns 0 Sleep
106197 simplsec localhost simplsec_s 0 Sleep
106199 mydns localhost mydns 0 Sleep
106200 simplsec localhost simplsec_s 0 Sleep
106201 simplsec_ simplsec_s 0 Sleep
106202 mydns localhost mydns 0 Sleep
106203 simplsec localhost simplsec_s 0 Query SELECT count(*) as count FROM bot_ip_list WHERE ip_address = '71.19
106204 mydns localhost mydns 0 Sleep
106205 simplsec localhost simplsec_s 0 Query SELECT * FROM domain WHERE domain = 'tenstarwebhost.com' LIMIT 0,1
106206 mydns localhost mydns 0 Sleep
106207 simplsec localhost simplsec_s 0 Query SELECT * FROM domain WHERE domain = 'accordcivic.info' LIMIT 0,1
106156 simplsec localhost simplsec_s 1 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
106190 mydns localhost mydns 1 Sleep
106191 simplsec localhost simplsec_s 1 Sleep
106192 mydns localhost mydns 1 Sleep
106194 mydns localhost mydns 1 Sleep
100170 simplsec localhost simplsec_s 4 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
100180 simplsec localhost simplsec_s 4 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
106168 simplsec localhost simplsec_s 4 Sleep
106171 simplsec localhost simplsec_s 4 Sleep
100130 simplsec localhost simplsec_s 5 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
106165 mydns localhost mydns 5 Sleep
106167 mydns localhost mydns 5 Sleep
106155 mydns localhost mydns 8 Sleep
106137 mydns localhost mydns 10 Sleep
106140 simplsec localhost simplsec_s 10 Query SELECT keyword_id, keyword FROM main_keywords WHERE domain_id = '79
106118 simplsec localhost simplsec_s 13 Query SELECT count(*) as count FROM main_keywords LEFT JOIN sub_keywords
99990 simplsec localhost simplsec_s 14 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
100048 simplsec localhost simplsec_s 14 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
106117 mydns localhost mydns 14 Sleep
99979 simplsec localhost simplsec_s 15 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
102906 simplsec localhost simplsec_s 16 Sleep
99934 simplsec localhost simplsec_s 20 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword

  #7  
Old 04-10-2007, 08:13 PM
1EightT 1EightT is offline
WHT Addict
 
Join Date: Aug 2004
Posts: 126
this from mytop -c

MySQL on localhost (4.1.22-standard) up 0+04:42:15 [18:12:49]
Queries: 692.4k qps: 42 Slow: 32.0k Se/In/Up/De(%): 65/05/01/00
qps now: 42 Slow qps: 0.5 Threads: 355 ( 276/ 80) 70/04/01/00
Cache Hits: 233.2k Hits/s: 14.1 Hits now: 13.5 Ratio: 51.6% Ratio now: 45.6%
Key Efficiency: 88.7% Bps in/out: 62.0k/93.1k Now in/out: 61.1k/134.4k

Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
Use of uninitialized value in substitution (s///) at /usr/bin/mytop line 958.
106484 root localhost test 0 Query show full processlist
1 root localhost mydns 1 Sleep
2 root localhost mydns 1 Sleep
101054 simplsec localhost simplsec_s 1 Sleep
106504 simplsec localhost simplsec_s 1 Sleep
106664 simplsec localhost simplsec_s 1 Sleep
106665 simplsec localhost simplsec_s 1 Query SELECT keyword_id, keyword FROM sub_keywords WHERE parent_id = '377
106668 simplsec localhost simplsec_s 1 Sleep
106679 mydns localhost mydns 1 Sleep
106680 simplsec localhost simplsec_s 1 Query SELECT count(*) as count FROM bot_ip_list WHERE ip_address = '198.1
106681 mydns localhost mydns 1 Sleep
106682 simplsec localhost simplsec_s 1 Query SELECT * FROM main_keywords WHERE domain_id = '10430' AND user_id =
106683 mydns localhost mydns 1 Sleep
106684 simplsec localhost simplsec_s 1 Query SELECT * FROM main_keywords WHERE domain_id = '9718' AND user_id =
102201 simplsec localhost simplsec_s 2 Sleep
106660 mydns localhost mydns 3 Sleep
106663 mydns localhost mydns 3 Sleep
106667 mydns localhost mydns 3 Sleep
106651 simplsec localhost simplsec_s 5 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
106653 simplsec localhost simplsec_s 5 Query SELECT keyword_id, keyword FROM main_keywords WHERE domain_id = '12
106652 mydns localhost mydns 6 Sleep
106650 mydns localhost mydns 7 Sleep
106543 simplsec localhost simplsec_s 9 Sleep
106625 simplsec localhost simplsec_s 9 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
106645 simplsec_ simplsec_s 9 Sleep
103692 simplsec localhost simplsec_s 10 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
106611 simplsec localhost simplsec_s 10 Query SELECT main_keywords.keyword as main_keyword, sub_keywords.keyword
106632 simplsec localhost simplsec_s 10 Query SELECT count(*) as count FROM main_keywords LEFT JOIN sub_keywords
106634 mydns localhost mydns 10 Sleep
106636 simplsec localhost simplsec_s 10 Query SELECT keyword_id, keyword FROM main_keywords WHERE domain_id = '95
106619 simplsec localhost simplsec_s 11 Sleep
106624 mydns localhost mydns 11 Sleep
106626 mydns localhost mydns 11 Sleep
106618 mydns localhost mydns 12 Sleep
106616 mydns localhost mydns 13 Sleep
106617 simplsec localhost simplsec_s 13 Sleep
106610 mydns localhost mydns 14 Sleep
106596 simplsec localhost simplsec_s 16 Query SELECT keyword_id, keyword FROM main_keywords WHERE domain_id = '97
106598 simplsec localhost simplsec_s 16 Query SELECT keyword_id, keyword FROM main_keywords WHERE domain_id = '12
106595 mydns localhost mydns 17 Sleep
106597 mydns localhost mydns 17 Sleep

  #8  
Old 04-10-2007, 08:23 PM
1EightT 1EightT is offline
WHT Addict
 
Join Date: Aug 2004
Posts: 126
I also noticed this one in top :

7569 root 39 19 8272 2244 1304 R 97 0.1 67:26.68 http

My regular httpd processes run as user apache, but what is this process? It's running as http instead of httpd and as root instead of as apache. THis one seems to hover around 99 percent the majority of the time as well.

  #9  
Old 04-10-2007, 08:46 PM
Patrick Patrick is offline
Security Ninja
 
Join Date: Mar 2003
Location: Canada
Posts: 8,608
Quote:
Originally Posted by 1EightT View Post
I also noticed this one in top :

7569 root 39 19 8272 2244 1304 R 97 0.1 67:26.68 http

My regular httpd processes run as user apache, but what is this process? It's running as http instead of httpd and as root instead of as apache. THis one seems to hover around 99 percent the majority of the time as well.
One Apache process always runs as root.

ps aux | grep apache | grep -v grep

There should only be one process owned by root running.

__________________
Patrick William | Rack911 Research Labs | Software Security Auditing
250+ Vulnerabilities Found - Get a quote on a professional audit @ Rack911.com

www.HostingSecList.com - Security notices for the hosting community.

  #10  
Old 04-10-2007, 09:05 PM
1EightT 1EightT is offline
WHT Addict
 
Join Date: Aug 2004
Posts: 126
I thought of that right after I posted. Any idea why it would consume so much of the processors time? Perhaps my min servers are too low in my httpd.conf and it is constantly creating more?

  #11  
Old 04-10-2007, 09:13 PM
ub3r ub3r is offline
Disabled
 
Join Date: Dec 2002
Location: chica go go
Posts: 11,858
Just an idea, could you try running this? It will take a little while to execute, but it may help.

Code:
mysqlcheck -1A

  #12  
Old 04-10-2007, 09:20 PM
1EightT 1EightT is offline
WHT Addict
 
Join Date: Aug 2004
Posts: 126
I'll have to try that one late tonight. The main database is over 90GB so who knows how long that will run lol .

Our software guy is looking at a couple queries we run based on the mytop results. Thanks so much for the help here everyone

  #13  
Old 04-10-2007, 11:23 PM
1EightT 1EightT is offline
WHT Addict
 
Join Date: Aug 2004
Posts: 126
Are there any settings to directly set something equivalent to the myisam join buffer size for innodb? It seems that one of our big left joins is suddenly causing an issue (it hasn't been a problem for the past 6 months, but is today for whatever reason)

  #14  
Old 04-11-2007, 12:39 AM
1EightT 1EightT is offline
WHT Addict
 
Join Date: Aug 2004
Posts: 126
Turns out the queries were fine. Back to square 1 again. ANy ideas?

  #15  
Old 04-11-2007, 01:51 PM
gbjbaanb gbjbaanb is offline
Retired Moderator
 
Join Date: Oct 2004
Location: Southwest UK
Posts: 1,159
I do not know what that http process is, all apache processes (even the parent, owned by root) should be httpd. That looks a bit strange to me.

__________________
Do not meddle in the affairs of Dragons, for you are crunchy and taste good.

Reply

Related posts from TheWhir.com
Title Type Date Posted
Web Hosting Sales and Promos Roundup - April 17, 2014 Web Hosting News 2014-04-17 16:12:29
Web Hosting Sales and Promos Roundup – April 11, 2014 Web Hosting News 2014-04-11 14:59:29
Web Hosting Sales and Promos Roundup - November 8, 2013 Web Hosting News 2013-11-11 11:36:22
Web Hosting Sales and Promos Roundup - October 18, 2013 Web Hosting News 2013-10-21 09:55:18
Web Hosting Sales and Promos Roundup - October 4, 2013 Web Hosting News 2013-10-04 16:09:46


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

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

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
Advertisement:
Web Hosting News:



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?