Will
Active member
Just looking for some tips on improving my mysql settings.
my.cnf
mysqltuner
Running a VPS with 2.5GB RAM. Any suggestions would be appreciated.
my.cnf
Code:
[mysqld]
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=800M
innodb_file_per_table=1
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
myisam_sort_buffer_size=16M
expire_logs_days=7
query_cache_size=32M
thread_cache_size=16K
max_allowed_packet=16M
local-infile=0
table_open_cache=2K
table_definition_cache=3K
open_files_limit=2K
max_connections=100
wait_timeout=30
interactive_timeout=60
connect_timeout=10
read_buffer_size=2M
slow_query_log=1
slow_query_log_file="/var/log/slow_queries.log"
sort_buffer_size=2M
join_buffer_size=4M
key_buffer_size=128M
query_cache_limit=4M
query_cache_type=1
long_query_time=5
tmp_table_size=128M
max_heap_table_size=128M
[myisamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
[mysqldump]
max_allowed_packet=16M
quick
[mysql]
no-auto-rehash
[mysqlhotcopy]
interactive-timeout
mysqltuner
Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.24-1~dotdeb.1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 37M (Tables: 93)
[--] Data in InnoDB tables: 300M (Tables: 321)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 5M (Tables: 8)
[!!] Total fragmented tables: 183
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 9d 3h 16m 24s (59M q [75.936 qps], 4M conn, TX: 578B, RX: 8B)
[--] Reads / Writes: 84% / 16%
[--] Total buffers: 1.1G global + 8.5M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.9G (76% of installed RAM)
[OK] Slow queries: 0% (142/59M)
[OK] Highest usage of available connections: 10% (10/100)
[OK] Key buffer size / total MyISAM indexes: 128.0M/22.4M
[OK] Key buffer hit rate: 100.0% (43M cached / 12K reads)
[OK] Query cache efficiency: 54.6% (19M cached / 34M selects)
[!!] Query cache prunes per day: 53192
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 430K sorts)
[!!] Joins performed without indexes: 19730
[!!] Temporary tables created on disk: 49% (309K on disk / 622K total)
[OK] Thread cache hit rate: 99% (10 created / 4M connections)
[!!] Table cache hit rate: 14% (762 open / 5K opened)
[OK] Open file limit used: 6% (275/4K)
[OK] Table locks acquired immediately: 99% (20M immediate / 20M locks)
[!!] Connections aborted: 8%
[OK] InnoDB data size / buffer pool: 300.7M/800.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
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_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 4.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 128M)
table_cache (> 2048)
Running a VPS with 2.5GB RAM. Any suggestions would be appreciated.