Server is a 8-core Xeon with 16GB of ram running multiple wordpress blogs.
Highest usage of available connections based on the last 6 weeks has been 382.
Any suggestions on how I can improve and optimize my.cnf?
Mysqltuner output
Fragmentation of 60 to 100 happens within 2 to 4 hours of optimizing the database.
My.cnf
Highest usage of available connections based on the last 6 weeks has been 382.
Any suggestions on how I can improve and optimize my.cnf?
Mysqltuner output
Fragmentation of 60 to 100 happens within 2 to 4 hours of optimizing the database.
Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 2018)
[--] Data in InnoDB tables: 262M (Tables: 720)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 3M (Tables: 34)
[!!] Total fragmented tables: 91
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 17h 26m 49s (35M q [109.964 qps], 474K conn, TX: 889B, RX: 8B)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 2.6G global + 14.2M per thread (400 max threads)
[OK] Maximum possible memory usage: 8.1G (51% of installed RAM)
[OK] Slow queries: 0% (2K/35M)
[OK] Highest usage of available connections: 10% (43/400)
[OK] Key buffer size / total MyISAM indexes: 1.2G/1.2G
[OK] Key buffer hit rate: 99.9% (540M cached / 344K reads)
[OK] Query cache efficiency: 60.5% (17M cached / 29M selects)
[!!] Query cache prunes per day: 240109
[OK] Sorts requiring temporary tables: 0% (73 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 2285
[OK] Temporary tables created on disk: 23% (369K on disk / 1M total)
[OK] Thread cache hit rate: 99% (43 created / 474K connections)
[!!] Table cache hit rate: 0% (5K open / 3M opened)
[OK] Open file limit used: 4% (6K/128K)
[OK] Table locks acquired immediately: 99% (14M immediate / 14M locks)
[OK] InnoDB data size / buffer pool: 262.7M/512.0M
-------- 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 (> 512M) [see warning above]
join_buffer_size (> 2.0M, or always use indexes with joins)
table_cache (> 64000)
Code:
[mysqld]
connect_timeout=360
default-storage-engine=MyISAM
innodb_additional_mem_pool_size=40M
innodb_buffer_pool_size=512M
innodb_commit_concurrency=16
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=12M
innodb_max_dirty_pages_pct=90
innodb_thread_concurrency=16
interactive_timeout=2400
join_buffer_size=2M
key_buffer_size=1280M
local-infile=0
log-slow-queries
long_query_time=1
max_allowed_packet=32M
max_connections=400
max_heap_table_size=256M
open_files_limit=100000
query_cache_limit=32M
query_cache_min_res_unit=512
query_cache_size=512M
read_buffer_size=4M
read_rnd_buffer_size=2M
sort_buffer_size=6M
table_cache=64K
table_definition_cache=8K
table_open_cache=64000
thread_cache_size=4M
tmp_table_size=256M
wait_timeout=2400
Last edited: