The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  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