在MySql中计算密钥效率,密钥缓冲区和查询缓存命中率的公式是什么？

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了在MySql中计算密钥效率,密钥缓冲区和查询缓存命中率的公式是什么？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2296字，纯文字阅读大概需要4分钟。

内容图文

现在,我已经通过名为information_schema的MySQL DB从’GLOBAL_STATUS’和’GLOBAL_VARIABLES’表中检索了一些日期.

我可以找回

> key_buffer_size
> key_cache_block_size
> query_cache_limit
> Query_cache_size …依此类推……

但是,我不知道如何计算Key hitrate的速率,使用的Key缓冲区和查询缓存命中率

所以,有人可以得到关于那些的公式,例如
      ？ /？ * 100％=？

解决方法:

供您参考,我也试图解释一些概念.

关键命中率

基本命中率基本上有两种形式

1.Key Read Efficiency

Key Reads: The number of physical reads of a key block from disk.

Key Read Request: The number of requests to read a key block from the
cache.

Key Read Efficiency = [1 - (Key_reads/Key_read_requests)]*100

Key Read Efficiency: The ratio of the number of physical reads of a
key block from the cache to the number of requests to read a key block
from the cache in percentage. The MySQL performance is good if the
value of Key Read Efficiency is 90 percent and above. Increasing the
size of the cache improves the value of Key Read Efficiency and hence
an improved the performance.

2.Key写效率

Key Writes: The number of physical writes of a key block to disk.

Key Write Request: The number of requests to write a key block to the
cache.

Key Write Efficiency=(Key_write/Key_write_requests)*100

Key Write Efficiency: The ratio of the number of physical writes of a
key block to the cache to the number of requests to write a key block
to the cache in percentage. For a good performance of the MySQL
server, the value of Key Write Efficiency must be 90 percent and
above. If it is found less, then you can increase the size of the
cache to improve the performance.

使用密钥缓冲区

查找key_buffer_size的值作为显示变量,如’key_buffer_size’;

将其转换为MB.

找到所有MyISAM索引大小：

SELECT SUM(INDEX_LENGTH)/(1024*1024) ‘Index Size’ FROM
information_schema.TABLES where ENGINE=’MyISAM’ AND TABLE_SCHEMA NOT
IN(‘mysql’,’information_schema’);

找

(Index Size)/key_buffer_size(in MB) * 100

if result is (<=100 ) then your all indexes are cached into key_buffer

if result is (>100) then your all indexes are not cached into
key_buffer you may gain performance boost by increasing
key_buffer_size.

查询缓存命中率

Hit rate = Qcache_hits / (Qcache_hits + Com_select) * 100

Insert rate = Qcache_inserts / (Qcache_hits + Com_select) * 100

Prune rate = (Qcache_lowmem_prunes / Qcache_inserts) * 100

内容总结

以上是互联网集市为您收集整理的在MySql中计算密钥效率,密钥缓冲区和查询缓存命中率的公式是什么？全部内容，希望文章能够帮你解决在MySql中计算密钥效率,密钥缓冲区和查询缓存命中率的公式是什么？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---