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 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:
    6
    Add ram. Increase max connections as 10 is very low. All that stuff in MySQL tuner suggestions.
     
  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
Loading...

Share This Page