Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

closed as off topic by John Gardeniers, Magellan, sysadmin1138 Oct 19 '12 at 19:55

Questions on Server Fault are expected to relate to professional server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
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 0:55
add comment

3 Answers

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.

share|improve this answer
add comment

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 : http://dba.stackexchange.com/a/16029/877

share|improve this answer
add comment

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

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.