☰

为什么我们的 MySQL 表打开率这么大而缓存命中率这么低？

tag-icon • tag-icon mysql cache mysql-replication
为什么我们的 MySQL 表打开率这么大而缓存命中率这么低？

我们看到表打开次数的统计数据有些奇怪。我已将 mysqltuner 转储附加到下面。

它说我们在过去 24 小时内打开了 94,000 张表。但总共只有 15,000 张表，而且我们知道在这个特定的读取从属复制器上只有大约 100 张表被访问。

此外，它还说我们只缓存了这 94k 中的 2k。

我该如何理解这些数字？以及如何修复缓存问题——当然我们可以“增加表缓存”，但这听起来不像是根本问题。

-------- 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)

答案1

看MySQL 如何打开/关闭表在官方手册中，对表缓存及其使用方法进行了很好的描述，并提供了有用的注释。查询中使用的每个表可能对每个连接都有多个句柄，这意味着如果您的请求并发性很高并且使用包含许多表的查询，则每个表可能需要数十个句柄。

您可以做的一件有用的事情是使用某种形式的监控，每隔一分钟左右检查一次 open_table/opened_tables 值，看看它们的行为如何。就我而言，很容易看出流量突然激增可能是 open_tables 大幅增加的原因。

还有这个问题这和你的非常相似。

答案2

lwdba@localhost (DB information_schema) :: 显示类似“query%”的变量；
+------------------------------+----------+
| 变量名称 | 值 |
+---------------------------+----------+
| 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 |
+---------------------------+----------+

在此显示中，查询缓存大小（全局变量）为 64M。查询缓存限制是1M。

这意味着我可以缓存最多 64 个查询，其最大数据集为 1M 或更少，或者缓存 128 个查询，其数据集为 500K，或者任何其他组合，总计可达 64M。

您可能需要使用这些设置来玩一些游戏（查询缓存大小和查询缓存限制)，以便正确缓存较大的查询数据集。否则，不满足这两个变量所施加的限制的查询将不会被缓存。

相关内容