Hi, I want to optimize a MySQL server with 2CPUs and 4Gb of RAM, shared with a Apache service. The MySQL have abut 1500 tables (10% InnoDB). The my.cnf settings: Code: innodb_buffer_pool_size = 64M join_buffer_size = 512K key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP #max_connections = 100 table_cache = 200 #thread_concurrency = 10 query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 The mysqltuner.pl script output this: Code: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.49-1ubuntu8.1-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 200M (Tables: 1294) [--] Data in InnoDB tables: 35M (Tables: 194) [--] Data in MEMORY tables: 0B (Tables: 2) [!!] Total fragmented tables: 207 -------- Performance Metrics ------------------------------------------------- [--] Up for: 4m 27s (30K q [114.536 qps], 304 conn, TX: 232M, RX: 5M) [--] Reads / Writes: 76% / 24% [--] Total buffers: 130.0M global + 3.1M per thread (151 max threads) [OK] Maximum possible memory usage: 592.4M (14% of installed RAM) [OK] Slow queries: 0% (169/30K) [OK] Highest usage of available connections: 7% (12/151) [OK] Key buffer size / total MyISAM indexes: 16.0M/62.4M [OK] Key buffer hit rate: 98.7% (765K cached / 9K reads) [OK] Query cache efficiency: 93.6% (26K cached / 28K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 214 sorts) [!!] Joins performed without indexes: 2 [OK] Temporary tables created on disk: 23% (67 on disk / 283 total) [OK] Thread cache hit rate: 96% (12 created / 304 connections) [!!] Table cache hit rate: 2% (200 open / 9K opened) [OK] Open file limit used: 37% (385/1K) [OK] Table locks acquired immediately: 100% (2K immediate / 2K locks) [OK] InnoDB data size / buffer pool: 35.5M/64.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Adjust your join queries to always utilize indexes Increase table_cache gradually to avoid file descriptor limits Variables to adjust: join_buffer_size (> 512.0K, or always use indexes with joins) table_cache (> 200) I've incremented join_buffer_size and table_cache but keep throwing these advices. I don't like the "Table cache hit rate: 2% (200 open / 9K opened)". Any advice to optimize the MySQl config? Thx
