I don’t have dedicated mysql server - its LAMPP with php app running on the same machine.
Below is mysqltuner result.
-------- General Statistics --------------------------------------------------
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.27-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[–] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 19G (Tables: 491)
[–] Data in ARCHIVE tables: 28B (Tables: 1)
[!!] Total fragmented tables: 18
-------- Performance Metrics -------------------------------------------------
[–] Up for: 37d 1h 9m 12s (1B q [481.357 qps], 1M conn, TX: 2B, RX: 2B)
[–] Reads / Writes: 36% / 64%
[–] Total buffers: 1.1G global + 13.2M per thread (100 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.4G (60% of installed RAM)
[OK] Slow queries: 0% (25K/1B)
[OK] Highest usage of available connections: 79% (79/100)
[OK] Key buffer size / total MyISAM indexes: 1.0G/2.2G
[OK] Key buffer hit rate: 99.7% (26B cached / 67M reads)
[OK] Query cache efficiency: 57.9% (5M cached / 9M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (35 temp sorts / 227M sorts)
[!!] Joins performed without indexes: 14973
[OK] Temporary tables created on disk: 11% (134K on disk / 1M total)
[OK] Thread cache hit rate: 99% (462 created / 1M connections)
[!!] Table cache hit rate: 0% (500 open / 94K opened)
[OK] Open file limit used: 82% (849/1K)
[OK] Table locks acquired immediately: 99% (123M immediate / 123M locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
join_buffer_size (> 1.0M, or always use indexes with joins)
table_cache (> 500)
I see a biggest problem here is Table cache hit rate: 0% !
I have open files limit of 1024 on ubuntu.
I couldn’t set table_cache >650 as trying above that creates problems as others are there to use the system !!
How can i consider increasing open files limit?
What is required to check before doing that ?