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 Usage

Discussion in 'Workarounds and Optimization' started by serverhaa, Feb 7, 2016.

  1. serverhaa

    serverhaa Registered

    Joined:
    Feb 7, 2016
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    london
    cPanel Access Level:
    Root Administrator
    Dear all ;

    hi ;


    my server has high usage on CPU and the most process are because of MYSQL ;

    could you please help me to modify my.cnf in the best as possible ?

    My Server Detailes :
    Code:
    Total processors: 8
    Intel(R) Xeon(R) CPU E5-1630 v3 @ 3.70GHz
    Speed : 3700.051 MHz
    Cache :10240 KB
    ---------------------------------
    Memory : 64GB DDR4
    --------------------------------
    SSD Drives
    -------------------------------
    CloudLinux
    --------------------------------
    
    My Current my.cnf :
    Code:
    [mysql]
    #port                           = 3306
    #socket                         = /var/lib/mysql/mysql.sock
    [mysqld]
    #user                           = mysql
    innodb_lock_wait_timeout=2000
    #local-infile=0
    default-storage-engine=InnoDB
    #socket                         = /var/lib/mysql/mysql.sock
    key-buffer-size=32M
    myisam-recover="FORCE,BACKUP"
    thread_concurrency=16
    max-allowed-packet=16M
    max-connect-errors=1000000
    datadir="/var/lib/mysql/"
    expire-logs-days=14
    sync-binlog=1
    tmp-table-size=32M
    max-heap-table-size=32M
    query-cache-type=1
    query-cache-size=128M
    max-connections=2000
    thread-cache-size=150
    open-files-limit=65535
    table-definition-cache=4096
    table-open-cache=1000
    innodb-flush-method=O_DIRECT
    innodb-log-files-in-group=2
    innodb-flush-log-at-trx-commit=1
    innodb-file-per-table=1
    innodb-buffer-pool-size=16G
    innodb_buffer_pool_instances=16G
    log-queries-not-using-indexes=1
    innodb_file_per_table=1
    max_allowed_packet=536870912
    open_files_limit=50000
    
    MYSQL Tune Result :
    Code:
    # perl mysqltuner.pl
    >>  MySQLTuner 1.6.4 - 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.6.28
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 13G (Tables: 30915)
    [--] Data in InnoDB tables: 10G (Tables: 26599)
    [--] Data in MEMORY tables: 8M (Tables: 793)
    Argument "" isn't numeric in numeric gt (>) at mysqltuner.pl line 1253 (#1)
        (W numeric) The indicated string was fed as an argument to an operator
        that expected a numeric value instead.  If you're fortunate the message
        will identify which operator was so unfortunate.
    
    [OK] Total fragmented tables:
    
    -------- 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
    Argument "" isn't numeric in numeric eq (==) at mysqltuner.pl line 1492 (#1)
    Argument "" isn't numeric in numeric eq (==) at mysqltuner.pl line 1500 (#1)
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4m 0s (131K q [548.046 qps], 2K conn, TX: 2B, RX: 18M)
    [--] Reads / Writes: 76% / 24%
    [--] Binary logging is disabled
    [--] Total buffers: 17.2G global + 64.5M per thread (2000 max threads)
    [OK] Maximum reached memory usage: 18.6G (29.54% of installed RAM)
    [!!] Maximum possible memory usage: 143.1G (227.89% of installed RAM)
    [!!] Slow queries: 9% (12K/131K)
    [OK] Highest usage of available connections: 1% (22/2000)
    [OK] Aborted connections: 0.04%  (1/2512)
    [OK] Query cache efficiency: 67.9% (73K cached / 108K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (22 temp sorts / 5K sorts)
    [!!] Joins performed without indexes: 328
    [!!] Temporary tables created on disk: 74% (4K on disk / 5K total)
    [OK] Thread cache hit rate: 99% (22 created / 2K connections)
    [OK] Table cache hit rate: 99% (1K open / 1K opened)
    [OK] Open file limit used: 3% (1K/50K)
    [OK] Table locks acquired immediately: 99% (51K immediate / 51K locks)
    
    -------- MyISAM Metrics ------------------------------------------------------
    [!!] Key buffer used: 19.5% (209M used / 1B cache)
    [!!] Cannot calculate MyISAM index size - re-run script as root user
    
    -------- InnoDB Metrics ------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 16.0G/10.8G
    [!!] InnoDB buffer pool instances: 64
    [!!] InnoDB Used buffer: 14.61% (153174 used/ 1048576 total)
    [OK] InnoDB Read buffer efficiency: 99.95% (121791677 hits/ 121849663 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 3215 writes)
    
    -------- ThreadPool Metrics --------------------------------------------------
    [--] ThreadPool stat is disabled.
    
    -------- AriaDB Metrics ------------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- TokuDB Metrics ------------------------------------------------------
    [--] TokuDB is disabled.
    
    -------- Galera Metrics ------------------------------------------------------
    [--] Galera is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] 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
        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 ***
        join_buffer_size (> 256.0K, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
        innodb_buffer_pool_instances(=16)
     
  2. vanessa

    vanessa Well-Known Member
    PartnerNOC

    Joined:
    Sep 26, 2006
    Messages:
    817
    Likes Received:
    22
    Trophy Points:
    18
    Location:
    Virginia Beach, VA
    cPanel Access Level:
    DataCenter Provider
    If MySQL is using a lot of resources, you need to start my looking at what's going on in there. A lot of people think there's some magic switch in my.cnf that can fix this, but that's simply not how it works, and no one here can give you anything but a guess on what settings they thing are best. Download mysqltuner.pl, run it, and learn to troubleshoot or hire something that can.
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    It's also important to let MySQL run as long as possible (preferably 24 hours) before running the tuner to get the most accurate results.

    Thank you.
     
  4. kdean

    kdean Well-Known Member

    Joined:
    Oct 19, 2012
    Messages:
    262
    Likes Received:
    12
    Trophy Points:
    18
    Location:
    Orlando, FL
    cPanel Access Level:
    Root Administrator
    You do seem to have a misconfiguration of your innodb_buffer_pool_instances which is looking for a value of 1 to 64, but you have it set to 16G which is causing it to set to it's highest value of 64 which is dividing your buffer pool into 64 256 MB max memory instances. The default is 8 which would divide the buffer pool into 8 2G slices. I wouldn't go higher than 16 since MySQL recommends at least 1 GB per instance. You can also try lower values all the way down to 1. You just need to gadge the performance differences on your system.

    MySQL :: MySQL 5.6 Reference Manual :: 14.11 InnoDB Startup Options and System Variables
     
Loading...

Share This Page