mysql performance mysqltuner guidance

sahostking

• June 01, 2015 14:51

Using the following, need some help optimizing please: MySQL version 10.0.15-MariaDB-cll-lve
[mysqld] slow-query-log=1 long-query-time=1 slow-query-log-file="/var/log/mysql-slow.log" datadir="/data/mysql" local-infile=0 max_connections=500 max_user_connections=30 key_buffer_size=512M myisam_sort_buffer_size=64M read_buffer_size=1M table_open_cache=6000 thread_cache_size=384 wait_timeout=6000 connect_timeout=20 tmp_table_size=256M max_heap_table_size=256M max_allowed_packet=268435456 net_buffer_length=16384 max_connect_errors=10 concurrent_insert=2 read_rnd_buffer_size=786432 bulk_insert_buffer_size=8M query_cache_limit=5M query_cache_size=256M query_cache_type=1 query_prealloc_size=262144 query_alloc_block_size=65535 transaction_alloc_block_size=8192 transaction_prealloc_size=4096 max_write_lock_count=8 log-error="/var/log/mysqld.log" #log-queries-not-using-indexes external-locking=FALSE open_files_limit=50000 innodb_stats_on_metadata=0 low_priority_updates=1 innodb_file_per_table innodb_buffer_pool_size=134217728 [mysqld_safe] [mysqldump] quick max_allowed_packet=16M [isamchk] key_buffer=384M sort_buffer=384M read_buffer=256M write_buffer=256M [myisamchk] key_buffer=384M sort_buffer=384M read_buffer=256M write_buffer=256M #### Per connection configuration #### sort_buffer_size=1M join_buffer_size=1M thread_stack=192K MSQL Tuner results: -------- Performance Metrics ------------------------------------------------- [--] Up for: 2d 8h 5m 20s (21M q [104.976 qps], 237K conn, TX: 125B, RX: 4B) [--] Reads / Writes: 95% / 5% [--] Total buffers: 1.1G global + 4.2M per thread (500 max threads) [OK] Maximum possible memory usage: 3.2G (41% of installed RAM) [OK] Slow queries: 0% (12K/21M) [OK] Highest usage of available connections: 38% (193/500) [OK] Key buffer size / total MyISAM indexes: 512.0M/340.7M [OK] Key buffer hit rate: 99.2% (97M cached / 814K reads) [OK] Query cache efficiency: 42.4% (13M cached / 32M selects) [!!] Query cache prunes per day: 104111 [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 769K sorts) [!!] Joins performed without indexes: 18152 [!!] Temporary tables created on disk: 36% (248K on disk / 681K total) [OK] Thread cache hit rate: 99% (193 created / 237K connections) [!!] Table cache hit rate: 1% (6K open / 425K opened) [OK] Open file limit used: 8% (4K/50K) [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks) [!!] InnoDB buffer pool / data size: 128.0M/2.9G [OK] InnoDB log waits: 0 -------- 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 Temporary table size is already large - reduce result set size 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: bit.ly/1mi7c4C Variables to adjust: query_cache_size (> 256M) [see warning above] join_buffer_size (> 128.0K, or always use indexes with joins) table_open_cache (> 6000) innodb_buffer_pool_size (>= 2G)

• 

  cPanelMichael

  • June 02, 2015 14:42

  Hello, Note that you can always implement some of the recommendations provided by the MySQL tuner, let MySQL run for another 24 hours, and post the updated results for user-feedback. Thank you.

  0

Please sign in to leave a comment.