Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

MySQL Very High CPU Usage - Please Help

Discussion in 'Workarounds and Optimization' started by m1l4n, Nov 19, 2013.

  1. m1l4n

    m1l4n Member

    Joined:
    Nov 19, 2013
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I migrate my sites to new server.

    1GB Memory
    1 Core
    30GB SSD Disk
    2TB Transfer

    But MySQL usage on CPU is very high.

    algkou4eejg7wkea3ee2.png

    My proces usage ... I don't know why My SQL use hig CPU resurces, can anyone help me? I am read all topic-s on cPanel forum but I can't find answer on my question :(

    b6ico0b7dfsw29mgtrj9.png

    My MySQL config file

    svkvffi856sudn60emfs.png
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    run mysqltuner.pl and copy here result
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    43,925
    Likes Received:
    1,815
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  4. m1l4n

    m1l4n Member

    Joined:
    Nov 19, 2013
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    MySQL Tuner

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3M (Tables: 47)
    [--] Data in InnoDB tables: 44M (Tables: 65)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 8
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 23h 23m 14s (1M q [13.320 qps], 32K conn, TX: 1B, RX: 74M)
    [--] Reads / Writes: 97% / 3%
    [--] Total buffers: 184.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 589.8M (58% of installed RAM)
    [OK] Slow queries: 0% (79/1M)
    [OK] Highest usage of available connections: 13% (21/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/689.0K
    [OK] Key buffer hit rate: 97.4% (52K cached / 1K reads)
    [OK] Query cache efficiency: 56.0% (569K cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7K sorts)
    [!!] Temporary tables created on disk: 33% (1K on disk / 5K total)
    [OK] Thread cache hit rate: 99% (280 created / 32K connections)
    [!!] Table cache hit rate: 4% (64 open / 1K opened)
    [OK] Open file limit used: 0% (38/10K)
    [OK] Table locks acquired immediately: 100% (459K immediate / 459K locks)
    [OK] InnoDB data size / buffer pool: 44.0M/128.0M
     
  5. m1l4n

    m1l4n Member

    Joined:
    Nov 19, 2013
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Can anyone sugest to me MySQ: configuration? :)
     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    There is not much that can be optimized here, since you got not much queries,
    you can increase table_cache=1000

    The best thing you can do is add slow queries tracking

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes



    and restart

    after a while review mysql-slow.log for queries running slow, not utilizing indexes and using temp tables on disk - those can be optimized

    cd /root
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

    Review of slow queries will be in slow.txt
     
  7. m1l4n

    m1l4n Member

    Joined:
    Nov 19, 2013
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I do all steps but nothing change :(
    Do you have any other sugestion?

    this is output of slow.txt

    /http://pastebin.com/xMU6uNWf
     
  8. Kevinye

    Kevinye Registered

    Joined:
    Nov 19, 2013
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I haven't install the MySQL yet.I don't know why
     
  9. m1l4n

    m1l4n Member

    Joined:
    Nov 19, 2013
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I can't understan what you want to say?
     
  10. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Queries:

    pitajfm_cms

    1. SELECT * FROM odgovori WHERE uid = 7954 AND pid = 86013
    table odgovori, make sure index for uid and pid exists, if not add it in phpmyadmin

    2. SELECT id FROM brojac_posjeta WHERE ko = '185.36.84.113' AND kome = '8074'
    table brojac_posjeta, make sure index for ko and kome

    3. SELECT id FROM brojac_posjeta WHERE kome = '9464'
    table brojac_posjeta, same as above, need kome index

    4. SELECT * FROM notifikacije WHERE kome = 8443 AND vrsta = 'odgovor' ORDER BY datum DESC LIMIT 5
    notifikacije, add index kome

    5. SELECT * FROM pitanja WHERE uid = 8443 AND status = '1' ORDER BY id DESC LIMIT 0,25\G
    table pitanja, index on uid

    6. SELECT `id` FROM `lajkovi` WHERE `oid` = 63566 AND `ko` = 8443\G
    table lajkovi, index on oid and ko


    Based on top results
    top - 18:06:18 up 2 days, 21:54, 1 user, load average: 0.85, 0.84, 0.97
    Tasks: 86 total, 2 running, 84 sleeping, 0 stopped, 0 zombie
    Cpu(s): 15.7%us, 0.7%sy, 0.0%ni, 83.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

    Your server is mostly idle
     
  11. m1l4n

    m1l4n Member

    Joined:
    Nov 19, 2013
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    How I can fix that?
     
  12. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Add indexes in phpmyadmin for those columns
     
  13. m1l4n

    m1l4n Member

    Joined:
    Nov 19, 2013
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thanks, now is better :)

    Server load 0.15 (1 CPU)
     
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice