Cache SQL result into ElastiCache

This post is English translation of “ElastiCacheにRDSへのクエリ結果をキャッシュさせる” written by @smokeymonkey.

Introduction

Amazon ElastiCache has several use cases, one of which is query caching. When application accesses Amazon RDS or other RDBMS, query result set is cached in ElastiCache. When application tries to read data, firstly queries it to ElastiCache, then queries to RDS only when the aimed object does not exist in ElastiCache.

I implemented a simple Ruby code to test this behavior.

Preparation

Today I used ElastiCache for Memcached, and RDS for MySQL, and Dalli, Memcached client written in Ruby.

Dalli’s usage is very easy. just Dalli.new to create connection definition and then get/set the target value.

$ irb
irb(main):001:0> require 'dalli'
=> true
irb(main):002:0> dc = Dalli::Client.new('smokeycache.xxxxx.cfg.apne1.cache.amazonaws.com:11211')
=> #<Dalli::Client:0x0000000115fb48 @servers=["smokeycache.xxxxx.cfg.apne1.cache.amazonaws.com:11211"], @options={}, @ring=nil>
irb(main):003:0> dc.set('foo','bar')
=> 144115188075855872

Let's check if the value is set to ElastiCache correctly by opening another terminal and query to Memcached.

$ irb
irb(main):001:0> require 'dalli'
=> true
irb(main):002:0> dc = Dalli::Client.new('smokeycache.xxxxx.cfg.apne1.cache.amazonaws.com:11211')
=> #<Dalli::Client:0x00000002073a08 @servers=["smokeycache.xxxxx.cfg.apne1.cache.amazonaws.com:11211"], @options={}, @ring=nil>
irb(main):003:0> puts dc.get('foo')
bar
=> nil

That’s all.

Implementation

The Ruby code below is a sample code to cache SQL Result into ElastiCache for Memcached. SQL string is passed by command line arguments. The code removes spaces from SQL string, serializes using Marshal, encodes the result into Base64. This result is to be used as the key of Memcached record. If cache hits, the result is fetched from Memcached. If not, query is issued toward MySQL, and the result of it is recorded into Memcached.

require ‘mysql’ 
require ‘dalli’ 
require ‘base64’ 
 
dc = Dalli::Client.new(‘smokeycache.xxxxx.cfg.apne1.cache.amazonaws.com:11211’) 
value = Array.new 
 
sqlstr = ARGV[0] 
key = Base64.encode64(Marshal.dump(sqlstr.strip)) 
 
if dc.get(key) == nil then 
  puts “*** no cache ***” 
  mysqlcli = Mysql.connect(‘dbs.xxxxx.ap-northeast-1.rds.amazonaws.com’,’user’,’password’,’shop’) 
  mysqlcli.query(ARGV[0]).each_hash do |row| 
    puts row 
    value.push(row) 
  end 
  dc.set(key, value) 
else 
  puts “*** cache hit ***” 
  value = dc.get(key) 
  value.each do |row| 
    puts row 
  end 
end

Run the code.

The table “customer” is like below:

mysql> select * from customer;
+------+----------+
| id   | name     |
+------+----------+
|    1 | alice    |
|    2 | bob      |
|    3 | charles  |
|    4 | donny    |
|    5 | elie     |
|    6 | fabian   |
|    7 | gabriel  |
|    8 | harold   |
|    9 | Ignatius |
|   10 | jonny    |
+------+----------+
10 rows in set (0.00 sec)

At first, get all rows of customer table.

$ ruby ./cache.rb “select * from customer” 
*** no cache ***
{“id”=>”1”, “name”=>”alice”} 
{“id”=>”2”, “name”=>”bob”} 
{“id”=>”3”, “name”=>”charles”} 
{“id”=>”4”, “name”=>”donny”} 
{“id”=>”5”, “name”=>”elie”} 
{“id”=>”6”, “name”=>”fabian”} 
{“id”=>”7”, “name”=>”gabriel”} 
{“id”=>”8”, “name”=>”harold”} 
{“id”=>”9”, “name”=>”Ignatius”} 
{“id”=>”10”, “name”=>”jonny”}

As you can see, “no cache” is printed as the Memcached currently have no cache at all. Try once more time.

$ ruby ./cache.rb “select * from customer” 
*** cache hit ***
{“id”=>”1”, “name”=>”alice”} 
{“id”=>”2”, “name”=>”bob”} 
{“id”=>”3”, “name”=>”charles”} 
{“id”=>”4”, “name”=>”donny”} 
{“id”=>”5”, “name”=>”elie”} 
{“id”=>”6”, “name”=>”fabian”} 
{“id”=>”7”, “name”=>”gabriel”} 
{“id”=>”8”, “name”=>”harold”} 
{“id”=>”9”, “name”=>”Ignatius”} 
{“id”=>”10”, “name”=>”jonny”}

“cache hit” is printed, which mean Memcached returned the query cache record.

Next, try another SQL statement.

$ ruby ./cache.rb “select * from customer where id = 3” 
*** no cache ***
{“id”=>”3”, “name”=>”charles”}

“no cache” is printed because this SQL is not recorded as cache.

$ ruby ./cache.rb “select * from customer where id = 3” 
*** cache hit ***
{“id”=>”3”, “name”=>”charles”}

you can see “cache hit” again!

Summary

In this case, I guess the benefit of cache is limited as I tried with very small amount of data. However, as the amount of data grows, ElastiCache for Memcached works effectively as cache I feel like getting well with ElastiCache :)