Home / server / Questions / 250877
In Process
Jason Cohen
Asked: 2011-03-24 07:24:11 +0800 CST

Why is our MySQL table-opens so large and cache-hit so low?

  • 772

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)
mysql cache mysql-replication

2 Answers

  1. See How MySQL Opens/Closes Tables in the official manual for a pretty good description of the table cache and how it is used along with some useful comments. Each table used in a query may have multiple handles for each connection which means if you have a high request concurrency and use queries containing many tables each table may require dozens of handles.

    A useful thing you can do is use some form of monitoring to check the open_table/opened_tables values every minute or so and see how they behave. In my case it is easy to see that sudden peaks in traffic are likely causes of large increases in opened_tables.

    There is also this SO question which is very similar to yours.

    • 2

  2. lwdba@localhost (DB information_schema) :: show variables like 'query%';
    +------------------------------+----------+
    | Variable_name | Value |
    +------------------------------+----------+
    | query_alloc_block_size | 8192 |
    | query_cache_limit | 1048576 |
    | query_cache_min_res_unit | 4096 |
    | query_cache_size | 67108864 |
    | query_cache_type | ON |
    | query_cache_wlock_invalidate | OFF |
    | query_prealloc_size | 8192 |
    +------------------------------+----------+

    In this display, query_cache_size (global variable) is 64M. The query_cache_limit is 1M.

    This means I could have up to 64 queries cached whose maximum data set is 1M or less, or 128 queries cached with 500K data sets, or any other combination that can total up to 64M.

    You may need to play some games with these settings (query_cache_size and query_cache_limit) so that larger sets of queried data is cached properly. Otherwise, queries not meeting the limits imposed by those two variables will not get cached.

    • 1