主页 / dba / 问题 / 65341
Accepted
Michał Herman
Asked: 2014-05-19 07:31:02 +0800 CST

InnoDB 缓冲池命中率

  • 772

如在http://cherry.world.edoors.com/COBFKUqnUdBY给出的，可以通过以下 sql 查询获得缓冲区命中率：

SELECT round ((P2.variable_value / P1.variable_value),4), 
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

问题

  • 该查询给出的缓冲区命中率是从什么时期开始的？从数据库引擎启动到现在？
  • 是否有可能从给定的时间段获得缓冲区命中率？（例如：最后 10 分钟）
mysql innodb

1 个回答

  1. Best Answer

    这是自正常运行时间以来的命中率（上次 MySQL 启动）

    你可以做两件事来获得最后 10 分钟

    方法#1

    刷新所有状态值，休眠 10 分钟，运行查询

    FLUSH STATUS;
SELECT SLEEP(600) INTO @x;
SELECT round ((P2.variable_value / P1.variable_value),4), 
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

    方法#2

    捕获innodb_buffer_pool_read_requests，innodb_buffer_pool_reads，睡眠 10 分钟，运行查询 innodb_buffer_pool_read_requests 和 innodb_buffer_pool_reads 的差异

    SELECT
    P1.variable_value,P2.variable_value
INTO
    @rqs,@rds
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_read_requests'
AND P2.variable_name = 'innodb_buffer_pool_reads'; 
SELECT SLEEP(600) INTO @x;
SELECT round (((P2.variable_value - @rds) / (P1.variable_value - @rqs)),4), 
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_read_requests'
AND P2.variable_name = 'innodb_buffer_pool_reads';

    试试看 ！！！

    • 2

相关问题