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.

High Load Issues

Discussion in 'Workarounds and Optimization' started by gch5185, Aug 26, 2015.

  1. gch5185

    gch5185 Registered

    Joined:
    Aug 26, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Malaysia
    cPanel Access Level:
    Root Administrator
    My Server Specs:
    • Intel Xeon Quad Core 3.3GHz (8 threads)
    • 1 Gbps Uplink
    • 16 GB RAM
    • 1,000 GB RAID-1 Drives
    • 25 TB Bandwidth
    • 5 Dedicated IPs

    My my.cnf content:

    Code:
    [mysqld]
    
    #innodb_force_recovery = 3
    
    
    #slow-query-log=1
    
    long_query_time=1
    
    log-slow-queries        = /var/log/mysql/mysql-slow.log
    
    log-queries-not-using-indexes=1
    
    # Settings user and group are ignored when systemd is used.
    
    # If you need to run mysqld under different user or group,
    
    # customize your systemd unit file for mysqld according to the
    
    # instructions in http://fedoraproject.org/wiki/Systemd
    
    datadir="/var/lib/mysql"
    
    socket="/var/lib/mysql/mysql.sock"
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    
    symbolic-links=0
    
    
    back_log=50
    
    open_files_limit=50000
    
    query_cache_size=256M
    
    query_cache_limit=8M
    
    query_cache_min_res_unit=2K
    
    max_connections=20000
    
    max_user_connections=10000
    
    max_connect_errors=9999999
    
    thread_stack=192K
    
    transaction_isolation=REPEATABLE-READ
    
    wait_timeout=180
    
    interactive_timeout = 180
    
    tmp_table_size=512M
    
    binlog_cache_size=1M
    
    max_heap_table_size=512M
    
    sort_buffer_size=2M
    
    join_buffer_size=256M
    
    thread_cache_size=100
    
    thread_concurrency=16
    
    key_buffer_size=1000M
    
    max_allowed_packet=268435456
    
    table_cache=5000
    
    table_definition_cache=3072
    
    
    #delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
    
    #delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts
    
    
    myisam_sort_buffer_size=2M # can be increased per sessions if needed for alter tables (indexes, repair)
    
    
    #query_cache_limit=2M # leave at default unless there is a good reason
    
    #join_buffer=2M # leave at default unless there is a good reason
    
    #sort_buffer_size=2M # leave at default unless there is a good reason
    
    #read_rnd_buffer_size=256K # leave at default unless there is a good reason
    
    #read_buffer_size=2M # leave at default unless there is a good reason
    
    
    collation_server=utf8_unicode_ci
    
    character_set_server=utf8
    
    
    #general_log=1
    
    #log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;
    
    log-output=FILE
    
    
    innodb_file_per_table=1
    
    #innodb_flush_method=O_DIRECT
    
    innodb_buffer_pool_size=134217728
    
    innodb_additional_mem_pool_size=29M
    
    innodb_log_buffer_size=256M
    
    innodb_thread_concurrency=16 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
    
    innodb_log_file_size = 512M
    
    innodb_read_io_threads=64
    
    innodb_write_io_threads=64
    
    innodb_fast_shutdown=0
    
    innodb_flush_method=O_DIRECT
    
    
    default-storage-engine=MyISAM
    
    [mysqld_safe]
    
    log-error="/var/log/mysqld.log"
    
    pid-file="/var/run/mysqld/mysqld.pid"
    
    
     
  2. gch5185

    gch5185 Registered

    Joined:
    Aug 26, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Malaysia
    cPanel Access Level:
    Root Administrator
    mysql tuner report

    Code:
    perl: warning: Setting locale failed.
    
    perl: warning: Please check that your locale settings:
    
    LANGUAGE = (unset),
    
    LC_ALL = (unset),
    
    LC_CTYPE = "UTF-8",
    
    LANG = "en_US.UTF-8"
    
        are supported and installed on your system.
    
    perl: warning: Falling back to the standard locale ("C").
    
    
    >>  MySQLTuner 1.2.0 - 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.5.42-cll
    
    [OK] Operating on 64-bit architecture
    
    
    -------- Storage Engine Statistics -------------------------------------------
    
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    
    [--] Data in MyISAM tables: 2G (Tables: 1632)
    
    [--] Data in InnoDB tables: 24M (Tables: 853)
    
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    
    [--] Data in MEMORY tables: 13M (Tables: 26)
    
    [!!] Total fragmented tables: 14
    
    
    -------- Security Recommendations  -------------------------------------------
    
    [OK] All database users have passwords assigned
    
    
    -------- Performance Metrics -------------------------------------------------
    
    [--] Up for: 4d 18h 35m 27s (1B q [3K qps], 7M conn, TX: 1904B, RX: 193B)
    
    [--] Reads / Writes: 61% / 39%
    
    [--] Total buffers: 2.0G global + 258.6M per thread (20000 max threads)
    
    [!!] Maximum possible memory usage: 5052.1G (324368% of installed RAM)
    
    [OK] Slow queries: 0% (2M/1B)
    
    [OK] Highest usage of available connections: 2% (479/20000)
    
    [OK] Key buffer size / total MyISAM indexes: 1000.0M/1.1G
    
    [OK] Key buffer hit rate: 100.0% (27B cached / 359K reads)
    
    [OK] Query cache efficiency: 97.6% (1B cached / 1B selects)
    
    [!!] Query cache prunes per day: 3103850
    
    [OK] Sorts requiring temporary tables: 0% (332 temp sorts / 5M sorts)
    
    [!!] Joins performed without indexes: 1916
    
    [!!] Temporary tables created on disk: 41% (2M on disk / 6M total)
    
    [OK] Thread cache hit rate: 99% (1K created / 7M connections)
    
    [OK] Table cache hit rate: 98% (3K open / 3K opened)
    
    [OK] Open file limit used: 4% (4K/100K)
    
    [OK] Table locks acquired immediately: 99% (66M immediate / 67M locks)
    
    [OK] InnoDB data size / buffer pool: 24.1M/128.0M
    
    
    -------- Recommendations -----------------------------------------------------
    
    General recommendations:
    
        Run OPTIMIZE TABLE to defragment tables for better performance
    
        Reduce your overall MySQL memory footprint for system stability
    
        Adjust your join queries to always utilize indexes
    
        Temporary table size is already large - reduce result set size
    
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    
    Variables to adjust:
    
      *** MySQL's maximum memory usage is dangerously high ***
    
      *** Add RAM before increasing MySQL buffer variables ***
    
        query_cache_size (> 128M)
    
        join_buffer_size (> 256.0M, or always use indexes with joins)
    
    
     
  3. 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