Skip to main content

High Load Issues

gch5185
My Server Specs: [LIST]
  • 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:
    [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"

    • Comments

    2 comments

    Date Votes
    • gch5185
      mysql tuner report
      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 >> 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)
      0
    • cPanelMichael
      Hello :) The following thread is useful if your server is under high loads: Troubleshooting High Loads On Linux Systems Thank you.
      0

    Please sign in to leave a comment.

    Didn't find what you were looking for?

    New post