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 CPU usage above 95%

Discussion in 'Workarounds and Optimization' started by Dimopanagos, Nov 1, 2014.

  1. Dimopanagos

    Dimopanagos Registered

    Joined:
    Nov 1, 2014
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Ubuntu 14.04.1 LTS, 4GB Ram

    Hi There i am attaching output of lscpu:
    root@mysql:~# lscpu
    Architecture: x86_64
    CPU op-mode(s): 32-bit, 64-bit
    Byte Order: Little Endian
    CPU(s): 4
    On-line CPU(s) list: 0-3
    Thread(s) per core: 1
    Core(s) per socket: 1
    Socket(s): 4
    NUMA node(s): 1
    Vendor ID: GenuineIntel
    CPU family: 6
    Model: 45
    Stepping: 7
    CPU MHz: 2593.840
    BogoMIPS: 5257.44
    Hypervisor vendor: Xen
    Virtualization type: full
    L1d cache: 32K
    L1i cache: 32K
    L2 cache: 256K
    L3 cache: 20480K
    NUMA node0 CPU(s): 0-3

    Output of free -m :
    root@mysql:~# free -m
    total used free shared buffers cached
    Mem: 3947 3650 297 0 135 472
    -/+ buffers/cache: 3042 905

    And here is my config:
    Code:
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /var/run/mysqld/mysqld.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default-storage-engine         = InnoDB
    socket                         = /var/run/mysqld/mysqld.sock
    pid-file                       = /var/run/mysqld/mysqld.pid
    
    # MyISAM #
    key-buffer-size                = 1024M
    myisam-recover                 = FORCE,BACKUP
    
    # SAFETY #
    max-allowed-packet             = 16M
    max-connect-errors             = 1000000
    skip-name-resolve
    sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE
    sysdate-is-now                 = 1
    innodb                         = FORCE
    innodb-strict-mode             = 1
    
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    
    # BINARY LOGGING #
    #log-bin                        = /var/lib/mysql/mysql-bin
    #expire-logs-days               = 1
    #sync-binlog                    = 1
    #binlog_format                  = mixed
    
    # CACHES AND LIMITS #
    tmp-table-size                 = 32M
    max-heap-table-size            = 32M
    query-cache-type               = 1
    query-cache-size               = 2M
    query-cache-limit              = 3M
    max-connections                = 10
    thread-cache-size              = 35
    open-files-limit               = 65535
    table-definition-cache         = 4096
    table-open-cache               = 4096
    
    # INNODB #
    innodb-flush-method            = O_DIRECT
    innodb-log-files-in-group      = 2
    innodb-log-file-size           = 128M
    innodb-flush-log-at-trx-commit = 1
    #innodb-flush-log-at-trx-commit = 2
    innodb-file-per-table          = 1
    innodb-buffer-pool-size        = 2G
    
    
    # LOGGING #
    log-error                      = /var/lib/mysql/mysql-error.log
    long_query_time                = 1
    log-queries-not-using-indexes  = 0
    slow-query-log                 = 1
    slow-query-log-file            = /var/lib/mysql/mysql-slow.log
    
    Code:
     >>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.6.19-0ubuntu0.14.04.1-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in InnoDB tables: 8G (Tables: 190)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in CSV tables: 0B (Tables: 4)
    [!!] Total fragmented tables: 14
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 19h 53m 32s (7M q [49.914 qps], 448 conn, TX: 4B, RX: 1B)
    [--] Reads / Writes: 50% / 50%
    [--] Total buffers: 3.0G global + 1.1M per thread (10 max threads)
    [OK] Maximum possible memory usage: 3.1G (79% of installed RAM)
    [OK] Slow queries: 0% (10/7M)
    [!!] Highest connection usage: 100%  (11/10)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/97.0K
    [OK] Key buffer hit rate: 100.0% (214K cached / 1 reads)
    [!!] Query cache efficiency: 1.8% (71K cached / 4M selects)
    [OK] Query cache prunes per day: 0
    [!!] Sorts requiring temporary tables: 13% (27 temp sorts / 198 sorts)
    [OK] Temporary tables created on disk: 20% (417 on disk / 2K total)
    [OK] Thread cache hit rate: 97% (11 created / 448 connections)
    [!!] Table cache hit rate: 19% (381 open / 1K opened)
    [OK] Open file limit used: 0% (57/65K)
    [OK] Table locks acquired immediately: 100% (7M immediate / 7M locks)
    [!!] InnoDB data size / buffer pool: 8.4G/2.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce or eliminate persistent connections to reduce connection usage
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        max_connections (> 10)
        wait_timeout (< 28800)
        interactive_timeout (< 28800)
        query_cache_limit (> 3M, or use smaller result sets)
        sort_buffer_size (> 256K)
        read_rnd_buffer_size (> 256K)
        table_cache (> 4096)
        innodb_buffer_pool_size (>= 8G)
    
    Any suggests what i can do so my server never hung or overload?
    It serve a big (Dating) site that has only on messages table over 19million records.
     
  2. smoge

    smoge Well-Known Member

    Joined:
    Jul 2, 2004
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    156
    Add ram. Increase max connections as 10 is very low. All that stuff in MySQL tuner suggestions.
     
  3. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,809
    Likes Received:
    1,898
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello :)

    I just wanted to make note of the fact that you mentioned using Ubuntu, which is not a supported OS at this time:

    cPanel Installation - Choose an OS

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
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