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 Optimization

Discussion in 'Workarounds and Optimization' started by Kurogane, Oct 10, 2016.

Tags:
  1. Kurogane

    Kurogane Member

    Joined:
    Dec 2, 2007
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    I've a problem with mysql i'm getting very but very high CPU usage and i not know how i can solved it

    Code:
    top - 22:50:48 up 7 days, 15:34,  4 users,  load average: 16.64, 14.86, 14.75
    Tasks: 211 total,   1 running, 210 sleeping,   0 stopped,   0 zombie
    Cpu(s): 96.2%us,  1.3%sy,  0.0%ni,  2.1%id,  0.1%wa,  0.0%hi,  0.3%si,  0.0%st
    Mem:  32900252k total, 32637120k used,   263132k free,   984688k buffers
    Swap:  1569780k total,    73260k used,  1496520k free, 11498392k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    30053 mysql     20   0 12.8g 2.9g 7224 S 614.7  9.4 149:18.22 mysqld
    
    show engine innodb status

    paste.ee/p/7N2Zk

    MySQLTuner

    Code:
     >>  MySQLTuner 1.6.18 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.52-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics ---------------------------------------------                                                                                                                                                                                               --------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My                                                                                                                                                                                               ISAM +PERFORMANCE_SCHEMA
    [--] Data in MyISAM tables: 179M (Tables: 133)
    [--] Data in InnoDB tables: 2G (Tables: 74)
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations ----------------------------------------------                                                                                                                                                                                               --------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [!!] There is no basic password file list!
    
    -------- CVE Security Recommendations ------------------------------------------                                                                                                                                                                                               --------------------
    [--] Skipped due to --cvefile option undefined
    
    -------- Performance Metrics ---------------------------------------------------                                                                                                                                                                                               --------------------
    [--] Up for: 3h 0m 37s (1M q [115.484 qps], 55K conn, TX: 85G, RX: 161M)
    [--] Reads / Writes: 98% / 2%
    [--] Binary logging is enabled (GTID MODE: OFF)
    [--] Physical Memory     : 31.4G
    [--] Max MySQL memory    : 9.5G
    [--] Other process memory: 5.4G
    [--] Total buffers: 8.1G global + 2.8M per thread (500 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 9.2G (29.29% of installed RAM)
    [OK] Maximum possible memory usage: 9.5G (30.13% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory                                                                                                                                                                                                available
    [!!] Slow queries: 8% (112K/1M)
    [OK] Highest usage of available connections: 80% (402/500)
    [!!] Aborted connections: 20.79%  (11447/55056)
    [OK] Query cache is disabled by default due to mutex contention on multiprocesso                                                                                                                                                                                               r machines.
    [OK] Sorts requiring temporary tables: 0% (12 temp sorts / 271K sorts)
    [OK] No joins without indexes
    [!!] Temporary tables created on disk: 50% (89K on disk / 178K total)
    [OK] Thread cache hit rate: 98% (896 created / 55K connections)
    [OK] Table cache hit rate: 88% (1K open / 1K opened)
    [OK] Open file limit used: 0% (234/65K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] Binlog cache memory access: 99.98% ( 17098 Memory / 17101 Total)
    
    -------- Performance schema ----------------------------------------------------                                                                                                                                                                                               --------------------
    [--] Performance schema is disabled.
    
    -------- ThreadPool Metrics ----------------------------------------------------                                                                                                                                                                                               --------------------
    [--] ThreadPool stat is disabled.
    
    -------- MyISAM Metrics --------------------------------------------------------                                                                                                                                                                                               --------------------
    [!!] Key buffer used: 20.5% (13M used / 67M cache)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/55.8M
    [OK] Read Key buffer hit rate: 97.4% (618K cached / 16K reads)
    [!!] Write Key buffer hit rate: 14.0% (1K cached / 1K writes)
    
    -------- AriaDB Metrics --------------------------------------------------------                                                                                                                                                                                               --------------------
    [--] AriaDB is disabled.
    
    -------- InnoDB Metrics --------------------------------------------------------                                                                                                                                                                                               --------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 8.0G/2.9G
    [OK] InnoDB buffer pool instances: 8
    [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
    [OK] InnoDB Read buffer efficiency: 100.00% (80960321191 hits/ 80960472731 total                                                                                                                                                                                               )
    [!!] InnoDB Write Log efficiency: 17.77% (7154 hits/ 40248 total)
    [!!] InnoDB log waits: 0.00% (1 waits / 33094 writes)
    
    -------- TokuDB Metrics --------------------------------------------------------                                                                                                                                                                                               --------------------
    [--] TokuDB is disabled.
    
    -------- Galera Metrics --------------------------------------------------------                                                                                                                                                                                               --------------------
    [--] Galera is disabled.
    
    -------- Replication Metrics ---------------------------------------------------                                                                                                                                                                                               --------------------
    [--] Galera Synchronous replication: NO
    [--] No replication slave(s) for this server.
    [--] This is a standalone server.
    
    -------- Recommendations -------------------------------------------------------                                                                                                                                                                                               --------------------
    General recommendations:
        Restrict Host for user@% to user@SpecificDNSorIp
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce or eliminate unclosed connections and network issues
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Variables to adjust:
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
        innodb_log_buffer_size (>= 8M)
    
    my.cnf

    Code:
    
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default-storage-engine         = InnoDB
    socket                         = /var/lib/mysql/mysql.sock
    pid-file                       = /var/lib/mysql/mysql.pid
    skip-external-locking
    skip-name-resolve
    
    # MyISAM #
    key-buffer-size                = 64M
    myisam-recover                 = FORCE,BACKUP
    
    # SAFETY #
    max-allowed-packet             = 16M
    max-connect-errors             = 1000000
    
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    
    # BINARY LOGGING #
    log-bin                        = /var/lib/mysql/mysql-bin
    expire-logs-days               = 14
    sync-binlog                    = 1
    
    # CACHES AND LIMITS #
    tmp-table-size                 = 32M
    max-heap-table-size            = 32M
    query-cache-type               = 0
    query-cache-size               = 0
    max-connections                = 500
    thread-cache-size              = 50
    open-files-limit               = 65535
    table-definition-cache         = 1024
    table-open-cache               = 1536
    
    # INNODB #
    innodb-flush-method            = O_DIRECT
    innodb-log-files-in-group      = 2
    innodb-log-file-size           = 256M
    innodb-log-buffer-size           = 8M
    innodb-flush-log-at-trx-commit = 1
    innodb-file-per-table          = 1
    innodb-buffer-pool-size        = 8G
    
    # LOGGING #
    log-error                      = /var/lib/mysql/mysql-error.log
    log-queries-not-using-indexes  = 1
    slow-query-log                 = 1
    slow-query-log-file            = /var/lib/mysql/mysql-slow.log
    general_log                       = 1
    general_log_file               = /var/lib/mysql/general.log
    
     
    #1 Kurogane, Oct 10, 2016
    Last edited: Oct 11, 2016
  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,

    You could try implementing some of the advice suggested by the tuner:

    Then, let MySQL run for at least 24 hours, and run the tuner again to see if there's new advice.

    Thank you.
     
  3. Kurogane

    Kurogane Member

    Joined:
    Dec 2, 2007
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    That is not the issue i can't wait 24h because we a getting the same issue.

    My website can't not load because is to slow because MySQL when restating in a few minutes my website is not available or very slow.
     
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,482
    Likes Received:
    203
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    It sounds like there's something wrong with at least one of your accounts websites. Optimizing MySQL is not a silver bullet.
    You need to find out which site is crippling your server like that.
     
  5. Kurogane

    Kurogane Member

    Joined:
    Dec 2, 2007
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    I know what account/site is because is only one site is hosted in the server.

    So you think is script issue?
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

Share This Page