How should i adjust the variable i get from mysqltuner?

Posted December 29, 2014 5.2k views


I have run mysqltuner on my host and i end up whit this information:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.40-0ubuntu0.14.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MyISAM tables: 36M (Tables: 178)
[--] Data in InnoDB tables: 31M (Tables: 101)
[!!] Total fragmented tables: 115

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 17h 45m 51s (8M q [35.883 qps], 51K conn, TX: 21B, RX: 2B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 236.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 641.8M (32% of installed RAM)
[OK] Slow queries: 0% (0/8M)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/10.0M
[OK] Key buffer hit rate: 99.9% (10M cached / 10K reads)
[OK] Query cache efficiency: 76.1% (5M cached / 7M selects)
[!!] Query cache prunes per day: 30940
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 350K sorts)
[!!] Temporary tables created on disk: 33% (18K on disk / 55K total)
[OK] Thread cache hit rate: 99% (20 created / 51K connections)
[!!] Table cache hit rate: 14% (390 open / 2K opened)
[OK] Open file limit used: 42% (434/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 31.2M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    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
Variables to adjust:
    query_cache_size (> 60M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 400)

Any recommendation how i can adjust this variable?

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Submit an Answer
1 answer

These variables can be set by changing the default values in your /etc/mysql/my.cnf file and then restarting your MySQL service with

service mysql restart