We're seeing a weird statistic on the number of table-opens. I've appended the mysqltuner dump below.
It says we've open 94,000 tables in the past 24 hours. But there's only 15,000 tables total and we know that on this particular read-slave replicant only about 100 tables are being accessed.
Furthermore it says we're caching only 2k of those 94k.
How do I make sense of these numbers? And how to fix the caching issue -- sure we could "increase the table cache" but that doesn't sound like the root issue.
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.9-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 14960)
[--] Data in InnoDB tables: 225M (Tables: 570)
[!!] Total fragmented tables: 2115
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 13m 36s (647K q [80.798 qps], 28K conn, TX: 247B, RX: 542M)
[--] Reads / Writes: 63% / 37%
[--] Total buffers: 2.5G global + 2.7M per thread (1000 max threads)
[OK] Maximum possible memory usage: 5.2G (66% of installed RAM)
[OK] Slow queries: 0% (783/647K)
[OK] Highest usage of available connections: 5% (57/1000)
[OK] Key buffer size / total MyISAM indexes: 1.0G/1.4G
[OK] Key buffer hit rate: 100.0% (763M cached / 74K reads)
[OK] Query cache efficiency: 55.5% (335K cached / 604K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (312 temp sorts / 113K sorts)
[!!] Temporary tables created on disk: 47% (59K on disk / 124K total)
[OK] Thread cache hit rate: 99% (63 created / 28K connections)
[!!] Table cache hit rate: 2% (2K open / 94K opened)
[OK] Open file limit used: 75% (3K/5K)
[OK] Table locks acquired immediately: 99% (577K immediate / 583K locks)
[!!] Connections aborted: 45%
[OK] InnoDB data size / buffer pool: 225.3M/256.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
table_cache (> 2048)