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 High CPU consumption

Discussion in 'Workarounds and Optimization' started by oswgarcia, Dec 4, 2014.

  1. oswgarcia

    oswgarcia Member

    Joined:
    Oct 1, 2014
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Hi all,

    I am getting problems in a couple of hosts where mysqld eats a lot of cpu resources up to leave the server unresponsive. We recently increased physical memory to 8gb and updated the kernel to PAE since we are running under 32bits architecture.

    Today, I was able to capture this in one of my servers:


    Code:
    root@host [~]# uptime
     11:08:16 up 2 days,  1:01,  1 user,  load average: 208.70, 206.76, 163.35
    
    root@host [~]# top
    top - 11:09:12 up 2 days,  1:02,  1 user,  load average: 224.49, 210.20, 166.95
    Tasks: 689 total,   6 running, 680 sleeping,   0 stopped,   3 zombie
    Cpu(s):  6.4%us, 92.6%sy,  0.1%ni,  0.6%id,  0.0%wa,  0.1%hi,  0.3%si,  0.0%st
    Mem:   8309024k total,  8016416k used,   292608k free,   387928k buffers
    Swap:  8385888k total,        0k used,  8385888k free,  5100948k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
     6277 mysql     15   0  132m  70m 3632 S 388.9  0.9   1342:25 mysqld
    22538 XXXXXX  16   0 91448  10m 5796 S  1.3  0.1   0:00.21 php
    21065 XXXXXX  18   0 94936  28m  22m R  1.0  0.3   0:00.73 php
    22471 XXXXXX  16   0 97008  15m 6268 S  1.0  0.2   0:01.86 php
    

    As you can see load was very high, I checked mysql process list and there was only one user doing a select sentence. I restarted mysql and load dropped immediately.


    I am not a an expert, so I would like to have your recommendations to tune mysql on my servers. I'm running version 5.0.95 on RHEL 5.9 on both boxes.

    Here is my.cnf file:
    Code:
    [mysqld]
    skip-bdb
    skip-innodb
    set-variable = max_connections=500
    tmpdir = /dev/shm
    safe-show-database
    max_allowed_packet=16M
    and here the result of mysqltuner.pl:

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.95-community
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 2549)
    [--] Data in MEMORY tables: 0B (Tables: 14)
    [!!] Total fragmented tables: 414
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 27m 26s (208K q [39.673 qps], 9K conn, TX: 578M, RX: 25M)
    [--] Reads / Writes: 94% / 6%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.3G (16% of installed RAM)
    [OK] Slow queries: 0% (79/208K)
    [OK] Highest usage of available connections: 3% (17/500)
    [!!] Key buffer size / total MyISAM indexes: 8.0M/347.7M
    [!!] Key buffer hit rate: 87.7% (40M cached / 4M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13K sorts)
    [!!] Joins performed without indexes: 324
    [!!] Temporary tables created on disk: 27% (2K on disk / 9K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 15K opened)
    [OK] Open file limit used: 5% (128/2K)
    [OK] Table locks acquired immediately: 99% (227K immediate / 227K locks)
    [!!] Connections aborted: 68%
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        key_buffer_size (> 347.7M)
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)


    Thanks in advance for your help.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    Please ensure you let MySQL run for at least 24 hours to get the most accurate tuner results. Or, if the load is too much, try troubleshooting the load further to see if you can find out additional information:

    Troubleshooting High Loads On Linux Systems

    Thank you.
     
  3. oswgarcia

    oswgarcia Member

    Joined:
    Oct 1, 2014
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Thanks for your response Michael, but I have not applied any tune yet, that is what I am trying to confirm. I know there are a lot of posts about this issue on this forum, I went through many of them, but as you know every environment is different.

    Basically, I would like to know if all recommendations suggested by mysqltuner will apply to my environment or just some of them.

    Thanks,
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  5. oswgarcia

    oswgarcia Member

    Joined:
    Oct 1, 2014
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Thanks Michael.
     
  6. 24x7ss

    24x7ss Well-Known Member

    Joined:
    Sep 30, 2014
    Messages:
    271
    Likes Received:
    16
    Trophy Points:
    18
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Twitter:
    First of all I will suggest you to run "watch mysqladmin proc" command It will show you current db which is eating resources. If you find any db then enable slow queries so that you can check which query is taking time to execute. Also, include below variables in my.cnf. Check the correct syntax of that.

    ===========================
    key_buffer_size (> 347.7M)
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    ==========================
     
  7. oswgarcia

    oswgarcia Member

    Joined:
    Oct 1, 2014
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    Thanks 24x7 for your reply, I will follow your recommendations and will monitor the server for 24hrs.
    After that, I will post the results here.


    Thanks,
     
Loading...

Share This Page