Results 1 to 16 of 16
  1. #1

    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

  2. #2
    Join Date
    Jun 2006
    Posts
    1,767
    Could you paste the contents of your my.cnf file?

  3. #3
    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

  4. #4
    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.

  5. #5
    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. #6
    Here are the results from mytop:

    Queries: 688.8k qps: 42 Slow: 32.0k Se/In/Up/De(&#37: 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. #7
    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(&#37: 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. #8
    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. #9
    Join Date
    Mar 2003
    Location
    Canada
    Posts
    9,072
    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.
    RACK911 Labs | Penetration Testing | https://www.RACK911Labs.ca

    www.HostingSecList.com - Security Notices for the Hosting Community.

  10. #10
    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. #11
    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

  12. #12
    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. #13
    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. #14
    Turns out the queries were fine. Back to square 1 again. ANy ideas?

  15. #15
    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.

  16. #16
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •