Very new to MySQL optimization techniques.
Please find the below report of mysqltuner.pl and suggest what variables in my.cnf should I change for performance optimization.
One more question -
I'm not able to find few variables like query_cache_size, thread_cache_size in my my.cnf. Can I explicitly add them to my.cnf ?
Report :
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.8
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 10K (Tables: 10)
[--] Data in InnoDB tables: 176M (Tables: 60)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 61
-------- Performance Metrics -------------------------------------------------
[--] Up for: 21h 15m 41s (237K q [3.099 qps], 42 conn, TX: 29M, RX: 52M)
[--] Reads / Writes: 68% / 32%
[--] Total buffers: 184.0M global + 1.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 419.9M (20% of installed RAM)
[OK] Slow queries: 0% (0/237K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.3M
[OK] Key buffer hit rate: 100.0% (610K cached / 7 reads)
[!!] Query cache is disabled
[!!] Sorts requiring temporary tables: 38% (5 temp sorts / 13 sorts)
[OK] Temporary tables created on disk: 0% (6 on disk / 67K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 83% (36 open / 43 opened)
[OK] Open file limit used: 0% (18/65K)
[OK] Table locks acquired immediately: 100% (257K immediate / 257K locks)
[!!] InnoDB data size / buffer pool: 176.3M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (>= 8M)
sort_buffer_size (> 512K)
read_rnd_buffer_size (> 512K)
thread_cache_size (start at 4)
innodb_buffer_pool_size (>= 176M)
Yes. You can add variables to my.cnf And, depending on the version of MySQL, change them dynamically - in the mysql console.