Tool to test MySQL latency

Question

I want to compare the latency of my web application against two different MySQL servers: one a local machine and one hosted via Amazon RDS.

What would be a good, free, and (preferably) easy tool to use to test MySQL latency? I have both Windows and Linux to work with, so the tools should run on at least one of those.

Just remember, if you're running a RDS and your servers aren't in EC2, you're missing out on one of the big benefits - having everything in the same internal network. — ceejayoz, Oct 16 '12 at 21:52
Product recommendations are off topic. Please see the FAQ for more details. — John Gardeniers, Oct 17 '12 at 00:55

score 1 · Answer 1 · answered Jun 07 '12 at 16:37

What type of latency are you hoping to measure?

Regardless, this is quite simple to homebrew - pick a scripting library of your choice (python, perl, php, whatever), then load up a timing library. In pseudocode, it would look something like:

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

Execute that against the various servers and examine latency.

score 0 · Answer 2 · edited Apr 13 '17 at 12:43

Create Two Stored Procedures

One to Run a Query of Your Choice
One to Benchmark the First Stored Procedure

Install this database and the Stored Procedures

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 ;

The Stored Procedure mybmark.testproc just executed a SELECT of an expression. You could further test this benchmarking against tables by having a table with 131,072 rows. You can then make a query that counts slow on purpose.

First create the table and fill it will a 131,072 rows

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;

Next Add the Sluggish Count of mytable into 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 ;

Now, call mybmark.mybenchmark 10 times

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()');

Perform all these steps in MySQL Local Machine and Amazon RDS

I wrote this algorithm back on April 3, 2012 : https://dba.stackexchange.com/a/16029/877

score 0 · Answer 3 · answered Oct 16 '12 at 21:47

To test MySQL latency for your comparison reasons, I would take some the most frequent queries you usually use and run them on both your local and RDS databases and then you’ll have an indication of latency differences between the two. This is because latency is very much depended on the type of queries and the amount of data you would retrieve.

BTW, here is an interesting post on database latencies, and, since you’re using RDS, in this cloud database site you can find a latency comparison between RDS and another cloud db. Cheers :)

Tool to test MySQL latency

3 Answers3