Help optimizing MySQL for a 32GB system

johnburk

• January 22, 2015 03:57

I am going to upgrade from 16GB ram to 32GB and I looks I am not using the full potential of it. Can you please look at my config and see how you would optimize it? MySQL Settings
[mysqld] innodb_buffer_pool_size=1342177280 max_allowed_packet=268435456 key_buffer_size = 512M local-infile=0 max_allowed_packet = 32M max_connections = 300 max_heap_table_size = 512MB open_files_limit=32000 query_cache_limit = 64M query_cache_size = 256M query_cache_type = 1 table_definition_cache = 8000 table_open_cache = 16000 thread_cache_size = 128 thread_cache_size=100 tmp_table_size = 512MB connect_timeout=40 interactive_timeout=120 myisam_sort_buffer_size=64M ##### SLOW QUERY ###### slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.9 log-queries-not-using-indexes ##### INNODB ###### innodb_file_per_table=1 innodb_log_buffer_size=8M innodb_flush_method=O_DIRECT innodb_open_files=2400 #### Per connection #### join_buffer_size=1M read_buffer_size=2M read_rnd_buffer_size=4M sort_buffer_size=256K [myisamchk] key_buffer = 32M sort_buffer = 32M read_buffer = 16M write_buffer = 16M
Mysqltuner.pl output
-------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.6.22-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 450M (Tables: 2655) [--] Data in InnoDB tables: 652M (Tables: 1398) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [--] Data in MEMORY tables: 0B (Tables: 68) [!!] Total fragmented tables: 18 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 2d 0h 21m 14s (74M q [428.119 qps], 939K conn, TX: 702B, RX: 9B) [--] Reads / Writes: 90% / 10% [--] Total buffers: 2.5G global + 7.5M per thread (300 max threads) [OK] Maximum possible memory usage: 4.7G (30% of installed RAM) [OK] Slow queries: 0% (717K/74M) [OK] Highest usage of available connections: 60% (182/300) [OK] Key buffer size / total MyISAM indexes: 512.0M/240.1M [OK] Key buffer hit rate: 99.9% (221M cached / 315K reads) [OK] Query cache efficiency: 88.1% (59M cached / 67M selects) [!!] Query cache prunes per day: 984286 [OK] Sorts requiring temporary tables: 0% (207 temp sorts / 1M sorts) [!!] Joins performed without indexes: 646 [OK] Temporary tables created on disk: 20% (469K on disk / 2M total) [OK] Thread cache hit rate: 99% (369 created / 939K connections) [!!] Table cache hit rate: 0% (2K open / 2M opened) [OK] Open file limit used: 8% (2K/32K) [OK] Table locks acquired immediately: 99% (7M immediate / 7M locks) [OK] InnoDB data size / buffer pool: 652.9M/1.2G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Increasing the query_cache size over 128M may reduce performance Adjust your join queries to always utilize indexes Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 256M) [see warning above] join_buffer_size (> 1.0M, or always use indexes with joins) table_cache (> 16000)

• 

  cPanelMichael

  • January 22, 2015 13:16

  Hello :) You could modify the variables based on the "Variables to adjust" results under "Recommendations", however note that you may want to consult with a qualified system administrator for assistance with tuning your MySQL configuration if you do not receive sufficient user-feedback on this thread. Thank you.

  0

Please sign in to leave a comment.