need help with mysql optimization please

mark135uk

• February 06, 2015 19:13

hi im having some small trouble with my server load going over, my server details are below
CPU Intel(R) Xeon(R) CPU E5-1650 v2 @ 3.50GHz Cores : 12 Cache : 12288KB RAM 8x 16384MB Disks 2 x 2000 GB Motherboard X9SRE/X9SRE-3F/X9SRi/X9SRi-3F
my .conf below
root@ns426755 [~]# cat /etc/my.cnf [mysqld] innodb_file_per_table=1 bind-address=127.0.0.1 #### ADDDED ALL BEWLO LINES ON JUNE,06,2014 # Memory usage skip-external-locking max_connections = 3056 max_user_connections = 3056 max_connect_errors = 999999 innodb_file_per_table = ON innodb_buffer_pool_size = 8G key_buffer = 1024M key_buffer_size = 512M max_allowed_packet=268435456 group_concat_max_len = 12192 table_cache = 1024 tmp_table_size = 256M max_heap_table_size = 128M sort_buffer_size = 3M read_buffer_size = 3M read_rnd_buffer_size = 10M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 64M query_prealloc_size = 262144 query_alloc_block_size = 65535 transaction_alloc_block_size = 8192 transaction_prealloc_size = 4096 max_write_lock_count = 8 query_cache_limit = 5M thread_cache_size = 382 query_cache_size= 128M # Try number of CPU's*2 for thread_concurrency (eHound has 4 CPU's) thread_concurrency = 24 # Disable Federated by default skip-federated open_files_limit=10000 [mysqld_safe] log-error="/var/log/mysqld.log" pid-file="/var/run/mysqld/mysqld.pid" [mysqldump] quick max_allowed_packet = 64M [mysql] no-auto-rehash [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 256M write_buffer = 256M [myisamchk] key_buffer = 512M sort_buffer = 512M max_allowed_packet = 14M read_buffer = 8256M write_buffer = 256M sort_buffer_size = 1M join_buffer_size = 1M thread_stack = 192K [mysqlhotcopy] interactive-timeout
i have quite a bit of ram but dont know if im making use of it all, if you need any more info let me know thanks in advance :)

• 

  mark135uk

  • February 07, 2015 10:33

  here are my sqltuner results
  >> MySQLTuner 1.4.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.5.40-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 2G (Tables: 123) [--] Data in InnoDB tables: 7M (Tables: 77) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 11 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 2d 0h 10m 52s (165M q [956.931 qps], 2M conn, TX: 499B, RX: 24B) [--] Reads / Writes: 34% / 66% [--] Total buffers: 8.8G global + 16.4M per thread (3056 max threads) [OK] Maximum possible memory usage: 57.6G (45% of installed RAM) [OK] Slow queries: 0% (40/165M) [OK] Highest usage of available connections: 5% (153/3056) [OK] Key buffer size / total MyISAM indexes: 512.0M/1.3G [OK] Key buffer hit rate: 100.0% (35B cached / 6M reads) [OK] Query cache efficiency: 48.6% (38M cached / 79M selects) [!!] Query cache prunes per day: 62165 [OK] Sorts requiring temporary tables: 1% (228K temp sorts / 17M sorts) [!!] Joins performed without indexes: 4074 [!!] Temporary tables created on disk: 37% (65K on disk / 176K total) [OK] Thread cache hit rate: 99% (153 created / 2M connections) [!!] Table cache hit rate: 14% (992 open / 6K opened) [OK] Open file limit used: 6% (1K/15K) [OK] Table locks acquired immediately: 95% (138M immediate / 145M locks) [OK] InnoDB buffer pool / data size: 8.0G/7.7M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries 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 without LIMIT clauses Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: query_cache_size (> 128M) join_buffer_size (> 128.0K, or always use indexes with joins) tmp_table_size (> 256M) max_heap_table_size (> 128M) table_open_cache (> 1024)
  thanks in advance

  0
• 

  cPanelMichael

  • February 10, 2015 12:47

  Hello :) Aside from the MySQL optimization request, you also mentioned: hi im having some small trouble with my server load going over, my server details are below
  You may find this thread helpful: Troubleshooting High Loads On Linux Systems Thank you.

  0

Please sign in to leave a comment.