I've recently been having problems with thrashing as a result of running out of memory. (My VPS has 256M total)
I'm trying to tune MySQL using mysqltuner.pl, and get the following results:
-------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.51a-3ubuntu5.4-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 114M (Tables: 454) [!!] Total fragmented tables: 34 -------- Performance Metrics ------------------------------------------------- [--] Up for: 40s (570 q [14.250 qps], 23 conn, TX: 154K, RX: 23K) [--] Reads / Writes: 100% / 0% [--] Total buffers: 338.0M global + 2.7M per thread (20 max threads) [!!] Maximum possible memory usage: 392.9M (153% of installed RAM) [OK] Slow queries: 0% (5/570) [OK] Highest usage of available connections: 15% (3/20) [!!] Key buffer size / total MyISAM indexes: 8.0M/9.4M [!!] Key buffer hit rate: 57.1% (7 cached / 3 reads) [OK] Query cache efficiency: 21.9% (7 cached / 32 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts) [OK] Temporary tables created on disk: 0% (0 on disk / 32 total) [OK] Thread cache hit rate: 86% (3 created / 23 connections) [OK] Table cache hit rate: 26% (128 open / 484 opened) [OK] Open file limit used: 25% (259/1K) [OK] Table locks acquired immediately: 100% (492 immediate / 492 locks) -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** key_buffer_size (> 9.4M)
But I'm a bit confused on how to get the maximum memory usage down? It seems to be based on key_buffer and max_connections, but there must be something else involved too?
my.cnf:
key_buffer = 8M max_allowed_packet = 12M thread_stack = 128K thread_cache_size = 8 max_connections = 20 table_cache = 128 tmp_table_size = 256M max_heap_table_size = 256M join_buffer_size = 256K query_cache_limit = 8M query_cache_size = 64M
I've been trying to read through MySQL tuning articles, but they seem geared toward people who already know what they're doing! Any help would be appreciated. Thanks!