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
     
    #1 m1l4n, Nov 19, 2013
  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
     
    #2 thinkbot, Nov 19, 2013
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    36,331
    Likes Received:
    1,209
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    #3 cPanelMichael, Nov 20, 2013
  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
     
    #4 m1l4n, Nov 20, 2013
  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? :)
     
    #5 m1l4n, Nov 21, 2013
  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
     
    #6 thinkbot, Nov 21, 2013
  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
     
    #7 m1l4n, Nov 21, 2013
  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
     
    #8 Kevinye, Nov 22, 2013
  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?
     
    #9 m1l4n, Nov 22, 2013
  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
     
    #10 thinkbot, Nov 24, 2013
  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?
     
    #11 m1l4n, Nov 25, 2013
  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
     
    #12 thinkbot, Nov 25, 2013
  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)
     
    #13 m1l4n, Nov 26, 2013
(You must log in or sign up to post here.)
Loading...
Similar Threads - MySQL Very High
  1. laobuod

    Very high mysql load

    laobuod, Aug 20, 2015, in forum: Workarounds and Optimization
    Replies:
    1
    Views:
    569
    cPanelMichael
    Aug 20, 2015
  2. mlody69

    Very slow mysql queryies

    mlody69, Feb 9, 2016, in forum: Workarounds and Optimization
    Replies:
    5
    Views:
    834
    cPanelMichael
    Feb 23, 2016
  3. unicornication

    MySQL crashes every other day, tuning ideas?

    unicornication, Oct 20, 2015, in forum: Workarounds and Optimization
    Replies:
    3
    Views:
    649
    cPanelMichael
    Oct 27, 2015
  4. Motamedi

    MySQL Usage and crashes every few hours

    Motamedi, Aug 9, 2015, in forum: Workarounds and Optimization
    Replies:
    4
    Views:
    678
    cPanelMichael
    Aug 11, 2015
  5. Skhilled44

    Installing mySQL and MariaDB

    Skhilled44, Jul 22, 2017 at 6:53 PM, in forum: Database Discussions
    Replies:
    1
    Views:
    15
    cPanelMichael
    Jul 24, 2017 at 11:00 AM

Share This Page