测试 MySQL 延迟的工具

Question 1

您希望测量哪种类型的延迟？

无论如何，这对于自制来说相当简单 - 选择一个你喜欢的脚本库（python、perl、php 等等），然后加载一个计时库。在伪代码中，它看起来像这样：

timer.start()
query.execute()
timer.stop()

针对各种服务器执行该操作并检查延迟。

Answer

您希望测量哪种类型的延迟？

无论如何，这对于自制来说相当简单 - 选择一个你喜欢的脚本库（python、perl、php 等等），然后加载一个计时库。在伪代码中，它看起来像这样：

timer.start()
query.execute()
timer.stop()

针对各种服务器执行该操作并检查延迟。

Question 2

创建两个存储过程

一个运行您选择的查询
对第一个存储过程进行基准测试

安装此数据库和存储过程

drop database if exists mybmark;
create database mybmark;
use mybmark
DELIMITER $$
DROP PROCEDURE IF EXISTS `mybmark`.`testproc` $$
CREATE PROCEDURE `mybmark`.`testproc` ()
BEGIN
    DECLARE answer INT;
    SELECT 1+2 INTO answer;
END $$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `mybmark`.`mybenchmark` $$
CREATE PROCEDURE `mybmark`.`mybenchmark` (loop_count INT,expr varchar(128))
BEGIN
    DECLARE dt1,dt2,dtdiff,ndx INT;

    SET dt1 = UNIX_TIMESTAMP();
    SET ndx = loop_count;
    SET @sql = expr;
    PREPARE stmt FROM @sql;
    WHILE ndx > 0 DO
        EXECUTE stmt;
        SET ndx = ndx - 1;
    END WHILE;
    DEALLOCATE PREPARE stmt;
    SET dt2 = UNIX_TIMESTAMP();
    SET dtdiff = dt2 - dt1;
    SELECT dt1,dt2,dtdiff;

END $$
DELIMITER ;

存储过程mybmark.testproc刚刚执行了表达式的 SELECT。您可以通过拥有一个包含 131,072 行的表来进一步测试此基准测试。然后，您可以故意进行缓慢计数的查询。

首先创建表并填充 131,072 行

USE mybmark;
CREATE TABLE mytable (A int) ENGINE=MyISAM;
INSERT INTO mytable VALUES (1);
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;
INSERT INTO mytable SELECT A FROM mytable;

接下来将的缓慢计数添加mytable到中mybmark。testproc

DELIMITER $$
DROP PROCEDURE IF EXISTS `mybmark`.`testproc` $$
CREATE PROCEDURE `mybmark`.`testproc` ()
BEGIN
    DECLARE answer INT;
    SELECT COUNT(1) INTO answer FROM mybmark.mytable WHERE 1=1;
END $$
DELIMITER ;

现在，拨打mybmark. mybenchmark10 次

call mybmark.mybenchmark(100,'CALL mybmark.testproc()');
call mybmark.mybenchmark(1000,'CALL mybmark.testproc()');
call mybmark.mybenchmark(10000,'CALL mybmark.testproc()');
call mybmark.mybenchmark(100000,'CALL mybmark.testproc()');

在 MySQL 本地计算机和 Amazon RDS 中执行所有这些步骤

我于2012年4月3日写了这个算法：https://dba.stackexchange.com/a/16029/877

Answer