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
     
    #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 Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,730
    Likes Received:
    1,992
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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. Gareth-AWD

    Remote MySQL latency very high

    Gareth-AWD, Apr 24, 2018, in forum: Database Discussion
    Replies:
    3
    Views:
    384
    cPanelMichael
    Apr 25, 2018
  2. baowesome

    MySQL Very High CPU Usage issue

    baowesome, Apr 2, 2018, in forum: Workarounds and Optimization
    Replies:
    1
    Views:
    1,091
    cPanelMichael
    Apr 2, 2018
  3. CK2017

    MySQL fails and recovers every morning

    CK2017, Jul 28, 2017, in forum: Database Discussion
    Replies:
    1
    Views:
    490
    cPanelMichael
    Aug 1, 2017
  4. greektranslator

    New Thread Upgrade MariaDB to 10.3: backup without mysqldump?

    greektranslator, Jan 16, 2019 at 1:57 AM, in forum: Database Discussion
    Replies:
    0
    Views:
    37
    greektranslator
    Jan 16, 2019 at 1:57 AM
  5. jimtrouble

    MySQL 200% CPU Load

    jimtrouble, Jan 6, 2019, in forum: Workarounds and Optimization
    Replies:
    4
    Views:
    147
    cPanelMichael
    Jan 9, 2019

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.
    Accept Learn More...
    Dismiss Notice