Results 1 to 16 of 16
Thread: Mysqld using 100 percent of cpu
-
04-10-2007, 03:56 PM #1WHT Addict
- Join Date
- Aug 2004
- Posts
- 156
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
-
04-10-2007, 04:01 PM #2Web Hosting Master
- Join Date
- Jun 2006
- Posts
- 1,767
Could you paste the contents of your my.cnf file?
-
04-10-2007, 04:06 PM #3WHT Addict
- Join Date
- Aug 2004
- Posts
- 156
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
-
04-10-2007, 04:35 PM #4Retired Moderator
- Join Date
- Oct 2004
- Location
- Southwest UK
- Posts
- 1,175
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.
-
04-10-2007, 07:51 PM #5WHT Addict
- Join Date
- Aug 2004
- Posts
- 156
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.
-
04-10-2007, 08:11 PM #6WHT Addict
- Join Date
- Aug 2004
- Posts
- 156
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
-
04-10-2007, 08:13 PM #7WHT Addict
- Join Date
- Aug 2004
- Posts
- 156
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
-
04-10-2007, 08:23 PM #8WHT Addict
- Join Date
- Aug 2004
- Posts
- 156
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.
-
04-10-2007, 08:46 PM #9Web Hosting Master
- Join Date
- Mar 2003
- Location
- Canada
- Posts
- 9,072
RACK911 Labs | Penetration Testing | https://www.RACK911Labs.ca
www.HostingSecList.com - Security Notices for the Hosting Community.
-
04-10-2007, 09:05 PM #10WHT Addict
- Join Date
- Aug 2004
- Posts
- 156
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?
-
04-10-2007, 09:13 PM #11Disabled
- Join Date
- Dec 2002
- Location
- chica go go
- Posts
- 11,876
Just an idea, could you try running this? It will take a little while to execute, but it may help.
Code:mysqlcheck -1A
-
04-10-2007, 09:20 PM #12WHT Addict
- Join Date
- Aug 2004
- Posts
- 156
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
-
04-10-2007, 11:23 PM #13WHT Addict
- Join Date
- Aug 2004
- Posts
- 156
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)
-
04-11-2007, 12:39 AM #14WHT Addict
- Join Date
- Aug 2004
- Posts
- 156
Turns out the queries were fine. Back to square 1 again. ANy ideas?
-
04-11-2007, 01:51 PM #15Retired Moderator
- Join Date
- Oct 2004
- Location
- Southwest UK
- Posts
- 1,175
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.
-
04-12-2007, 07:38 AM #16cholesterol dependent
- Join Date
- Oct 2000
- Location
- Israel
- Posts
- 1,288
From experience I would suggest checking the queries again. Are you using the limit clause? Does your script/program have a query in a loop that's in another loop with a query.
Before I discovered my query problems, mySQL was routinely chewing up 100% CPU and eventually killing my server. Now the server cruises along with the CPU just taking it easy.