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.

Optitmize MySQL Configuration to reduce CPU usage

Discussion in 'Workarounds and Optimization' started by Herchel, Sep 14, 2016.

Tags:
  1. Herchel

    Herchel Registered

    Joined:
    Sep 14, 2016
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    France
    cPanel Access Level:
    Website Owner
    Hello,
    I'm using a Kimsufi server with this configuration: Core i5 4 cores/4 threads and with 16Gb of RAM and HDD 1To. With Ubuntu 14.04.4 LTS (GNU/Linux 3.14.32-xxxx-grs-ipv6-64 x86_64).
    After launching a htop command my CPU is used 100% and mysql processs used approx. > 200% of CPU and website is very slow.
    This is my /etc/mysql/my.cnf config:
    Code:
    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    #
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    # This will be passed to all mysql clients
    # It has been reported that passwords should be enclosed with ticks/quotes
    # escpecially if they contain "#" chars...
    # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
    [client]
    port            = 3306
    socket          = /var/run/mysqld/mysqld.sock
    
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    
    # This was formally known as [safe_mysqld]. Both versions are currently parsed.
    [mysqld_safe]
    socket          = /var/run/mysqld/mysqld.sock
    nice            = 0
    
    [mysqld]
    #
    # * Basic Settings
    #
    user            = mysql
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    basedir         = /usr
    datadir         = /var/lib/mysql
    tmpdir          = /tmp
    lc-messages-dir = /usr/share/mysql
    skip-external-locking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address            = 127.0.0.1
    #
    # * Fine Tuning
    #
    key_buffer              = 6144M
    key_buffer_size         = 6144M
    max_allowed_packet      = 512M
    thread_stack            = 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    #max_connections        = 500
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit       = 1024M
    query_cache_size        = 10240M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    #
    # Error log - should be very few entries.
    #
    log_error = /var/log/mysql/error.log
    #
    # Here you can see queries with especially long duration
    #log_slow_queries       = /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id              = 1
    #log_bin                        = /var/log/mysql/mysql-bin.log
    expire_logs_days        = 10
    max_binlog_size         = 100M
    #binlog_do_db           = include_database_name
    #binlog_ignore_db       = include_database_name
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    #default-storage_engine=InnoDB
    #innodb_buffer_pool_size = 256M
    #innodb_log_file_size = 256M
    #innodb_log_buffer_size = 4M
    #innodb_flush_log_at_trx_commit = 2
    #innodb_thread_concurrency = 8
    #innodb_flush_method = O_DIRECT
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    #
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem
    secure-file-priv = /var/tmp
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet      = 16M
    
    [mysql]
    #no-auto-rehash # faster start of mysql but no tab completition
    
    [isamchk]
    key_buffer = 16M
    
    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    includedir /etc/mysql/conf.d/
    and MySQLTuner results:
    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
    error: Found option without preceding group in config file: /etc/mysql/conf.d/local.cnf at line: 1
    [OK] Logged in using credentials passed on the command line
    [OK] Currently running supported MySQL version 5.5.49-0ubuntu0.14.04.1
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics ---------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My                    ISAM +PERFORMANCE_SCHEMA
    [--] Data in InnoDB tables: 3G (Tables: 429)
    [--] Data in MyISAM tables: 18M (Tables: 38)
    [--] Data in MEMORY tables: 0B (Tables: 17)
    [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: 21m 39s (1M q [1K qps], 258 conn, TX: 751M, RX: 604M)
    [--] Reads / Writes: 57% / 43%
    [--] Binary logging is disabled
    [--] Physical Memory     : 15.6G
    [--] Max MySQL memory    : 16.6G
    [--] Other process memory: 117.9M
    [--] Total buffers: 16.2G global + 2.7M per thread (151 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [!!] Maximum reached memory usage: 16.2G (104.03% of installed RAM)
    [!!] Maximum possible memory usage: 16.6G (106.29% of installed RAM)
    [!!] Overall possible memory usage with other process exceeded memory
    [OK] Slow queries: 0% (0/1M)
    [OK] Highest usage of available connections: 11% (17/151)
    [!!] Aborted connections: 6.20%  (16/258)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [!!] Query cache may be disabled by default due to mutex contention.
    [OK] Sorts requiring temporary tables: 0% (118 temp sorts / 56K sorts)
    [!!] Joins performed without indexes: 94
    [!!] Temporary tables created on disk: 33% (383 on disk / 1K total)
    [OK] Thread cache hit rate: 93% (17 created / 258 connections)
    [OK] Table cache hit rate: 79% (197 open / 247 opened)
    [OK] Open file limit used: 4% (42/1K)
    [OK] Table locks acquired immediately: 100% (137K immediate / 137K locks)
    
    -------- Performance schema ----------------------------------------------------
    [--] Performance schema is disabled.
    
    -------- ThreadPool Metrics ----------------------------------------------------
    [--] ThreadPool stat is disabled.
    
    -------- MyISAM Metrics --------------------------------------------------------
    [!!] Key buffer used: 18.8% (1B used / 6B cache)
    [OK] Key buffer size / total MyISAM indexes: 6.0G/11.3M
    [OK] Read Key buffer hit rate: 100.0% (14M cached / 3K reads)
    [OK] Write Key buffer hit rate: 100.0% (2M cached / 0 writes)
    
    -------- AriaDB Metrics --------------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- InnoDB Metrics --------------------------------------------------------
    [--] InnoDB is enabled.
    [!!] InnoDB buffer pool / data size: 128.0M/3.0G
    [OK] InnoDB buffer pool instances: 1
    [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
    [OK] InnoDB Read buffer efficiency: 100.00% (778690869 hits/ 778696521 total)
    [!!] InnoDB Write Log efficiency: 37.2% (10851 hits/ 29171 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 40022 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:
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Dedicate this server to your database for highest performance.
        Enable the slow query log to troubleshoot bad queries
        Reduce or eliminate unclosed connections and network issues
        Configure your accounts with ip or subnets only, then update your configurat                    ion with skip-name-resolve=1
        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 which have no LIMIT clause
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_type (=0)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        innodb_buffer_pool_size (>= 3G) if possible.
    
    Can anyone help me to optimize that. My concern is that if this case is not resolved; may be my server will be hanged.
    Note that i'm a very newbbie in database administration so, please explain with more details!!! Please!
    Regards
     
  2. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    865
    Likes Received:
    9
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Infopro likes this.
  3. Herchel

    Herchel Registered

    Joined:
    Sep 14, 2016
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    France
    cPanel Access Level:
    Website Owner
    Hi Kernow,
    Thank you for you assistance. I've solved my problem with this tools.
    Regards
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

Share This Page