Search code examples
mysqlredisbenchmarking

MySQL Outperforming Redis

I'm trying to make a script of benchmarks for a mysql operation vs a redis operation.

Here is what I've tried:

1./ List of comment ids with a separate hash of comment JSON Data mapped to comment id

2./ List of comments json data

3./ Sorted set of comments with ranking and json data as mapped value

For some reason, mysql keeps out performing redis and I dont understand why. Im querying 100 records.

Here are my operations (separated by attempts):

1./ $client->rpush($commentId); $client->hmset($commentId,$data);

2./ $client->rpush($jsonData);

3./ $client->zadd("comments",$i,$jsonData);

Here's my benchmark script:

$client = new Predis\Client($conf);
$st=microtime(true);
// sorted set solution
$dat=$client->zrange("comments",0,100);
// list solution
//$dat=$client->lrange("comments",0,100);
$ft=microtime(true);
$overall=$ft-$st;
echo "REDIS=>".$overall."\n";

$sta=microtime(true);
$st=mysqli_query($dbh,"select SQL_NO_CACHE * from comments where status>0 order by createdate desc limit 0,100");
while($r=mysqli_fetch_assoc($st)){
  $dd=$r;
}
$fta=microtime(true);
$overall=$fta-$sta;
echo "MYSQL=>".$overall."\n";

Here is my redis store script for sorted sets:

$st=mysqli_query($dbh,"select SQL_NO_CACHE * from comments where status>0 order by createdate desc LIMIT 100");
$i=1;
while($r=mysqli_fetch_assoc($st)){
  $client->zadd("comments",$i,json_encode($r));
  $i++;
}

Here is my redis store script for list:

$st=mysqli_query($dbh,"select SQL_NO_CACHE * from comments where status>0 order by createdate desc LIMIT 100");
$i=1;
while($r=mysqli_fetch_assoc($st)){
  $key="comment:$id";
  $client->rpush("comments",$key);
  foreach($r as $k=>$v){
   $client->hset($key,$k,$v);
  }
  $i++;
}

Here is my redis store script for list without pointing hash:

$st=mysqli_query($dbh,"select SQL_NO_CACHE * from comments where status>0 order by createdate desc LIMIT 100");
$i=1;
while($r=mysqli_fetch_assoc($st)){
  $key="comment:$id";
  $client->rpush("comments",json_encode($r));
}

Here is the DB Schema:

CREATE TABLE `comments` (
  `commentid` int(11) NOT NULL AUTO_INCREMENT,
  `parentid` int(11) DEFAULT '0',
  `refno` int(11) DEFAULT '0',
  `createdate` int(11) DEFAULT '0',
  `remoteip` varchar(80) DEFAULT '',
  `fingerprint` varchar(50) DEFAULT '',
  `locid` int(11) DEFAULT '0',
  `clubid` int(11) DEFAULT '0',
  `profileid` int(11) DEFAULT '0',
  `userid` int(11) DEFAULT '0',
  `global` int(11) DEFAULT '0',
  `official` int(11) DEFAULT '0',
  `legacyuser` int(11) DEFAULT '0',
  `mediaid` int(11) DEFAULT '0',
  `status` int(11) DEFAULT '1',
  `comment` varchar(4000) DEFAULT '',
  `likes` int(11) DEFAULT '0',
  `dislikes` int(11) DEFAULT '0',
  `import` int(11) DEFAULT '0',
  `author` varchar(50) DEFAULT '',
  PRIMARY KEY (`commentid`),
  KEY `comments_locid` (`locid`),
  KEY `comments_userid` (`userid`),
  KEY `idx_legacyusers` (`legacyuser`),
  KEY `profile_index` (`profileid`),
  KEY `comments_createdate` (`createdate`),
  KEY `compound_for_comments` (`locid`,`global`,`status`),
  KEY `global` (`global`),
  KEY `status` (`status`),
  KEY `locid_status` (`locid`,`status`),
  KEY `global_status` (`global`,`status`)
) ENGINE=InnoDB

We are using Redislabs as our redis server.

If I'm missing anything to make this a valid question please let me know.

Solution

  • Based on above comments, the network latency of the remote Redis instance hosted in AWS was greater than the latency of a local MySQL instance.

    Naturally, when you add 40-100ms of latency to every Redis request (depending on how far across the internet your app is from the hosted Redis), it makes Redis appear slower in terms of total request time.