MySQL to Redis - Import and Model

Question

I'm thinking to use Redis to cache some user data snapshot(s) in order to speed up the access to that data (one of the reasons is because my MySQL table(s) suffer of lock contention) and I'm looking for the best way to import in one step a table like this(which may contain from a few record to millions of records):

mysql> select * from mytable where snapshot = 1133;
+------+--------------------------+----------------+-------------------+-----------+-----------+
| id   | email                    | name           | surname           | operation | snapshot  |
+------+--------------------------+----------------+-------------------+-----------+-----------+
| 2989 | [email protected] | fake-name-2989 | fake-surname-2989 |         2 |      1133 |
| 2990 | [email protected] | fake-name-2990 | fake-surname-2990 |        10 |      1133 |
| 2992 | [email protected] | fake-name-2992 | fake-surname-2992 |         5 |      1133 |
| 2993 | [email protected] | fake-name-2993 | fake-surname-2993 |         5 |      1133 |
| 2994 | [email protected] | fake-name-2994 | fake-surname-2994 |         9 |      1133 |
| 2995 | [email protected] | fake-name-2995 | fake-surname-2995 |         7 |      1133 |
| 2996 | [email protected] | fake-name-2996 | fake-surname-2996 |         1 |      1133 |
+------+--------------------------+----------------+-------------------+-----------+-----------+

into the Redis key-value store.

I can have many "snapshots" to load into Redis, and the basic access pattern is (SQL like syntax)

• select * from mytable where snapshot = ? and id = ?

these snapshots can also coming from others table, so the "global unique ID per snapshot" is the column snapshot, ex:

mysql> select * from my_other_table where snapshot = 1134;
+------+--------------------------+----------------+-------------------+-----------+-----------+
| id   | email                    | name           | surname           | operation | snapshot  |
+------+--------------------------+----------------+-------------------+-----------+-----------+
| 2989 | [email protected] | fake-name-2989 | fake-surname-2989 |         1 |      1134 |
| 2990 | [email protected] | fake-name-2990 | fake-surname-2990 |         8 |      1134 |
| 2552 | [email protected] | fake-name-2552 | fake-surname-2552 |         5 |      1134 |
+------+--------------------------+----------------+-------------------+-----------+-----------+

The loaded snapshot into redis never change, they are available only for a week via TTL

• There is a way to load in one step this kind of data(rows and columns) into redis combining redis-cli --pipe and HMSET?

• What is the best model to use in redis in order to store/get this data (thinking at the access pattern)?

I have found the redis-cli --pipe Redis Mass Insertion (and also MySQL to Redis in One Step) but I can't figure out the best way to achieve my requirements (load from mysql in one step all rows/colums, best redis model for this) using HMSET

Thanks in advance

Cristian.

Answer 1

Model

To be able to query your data from Redis the same way as:

select * from mytable where snapshot = ?
select * from mytable where id = ?

You'll need the model below.

Note: select * from mytable where snapshot = ? and id = ? does not make a lot of sense here, since it's the same as select * from mytable where id = ?.

Key type and naming

[Key Type] [Key name pattern]
HASH       d:{id}
ZSET       d:ByInsertionDate
SET        d:BySnapshot:{id}

Note: I used d: as a namespace but you may want to rename it with the name of your domain model.

Data insertion

Insert a new line from Mysql into Redis:

hmset d:2989 id 2989 email [email protected] name fake-name-2989 ... snapshot 1134
zadd d:ByInsertionDate {current_timestamp} d:2989
sadd d:BySnapshot:1134 d:2989

Another example:

hmset d:2990 id 2990 email [email protected] name fake-name-2990 ... snapshot 1134
zadd d:ByInsertionDate {current_timestamp} d:2990
sadd d:BySnapshot:1134 d:2990

Cron

Here is the algorithm that must be run each day or week depending on your requirements:

for key_name in redis(ZREVRANGEBYSCORE d:ByInsertionDate -inf {timestamp_one_week_ago})

 // retrieve the snapshot id from d:{id}
 val snapshot_id = redis(hget {key_name} snapshot)

 // remove the hash (d:{id})
 redis(del key_name)

 // remove the hash entry from the set
 redis(srem d:BySnapshot:{snapshot_id} {key_name})

// clean the zset from expired keys
redis(zremrangebyscore d:ByInsertionDate -inf {timestamp_one_week_ago})

Usage

select * from my_other_table where snapshot = 1134; will be either:

{snapshot_id} = 1134
for key_name in redis(smembers d:BySnapshot:{snapshot_id})
  print(redis(hgetall {keyname}))

or write a lua script to do this directly on redis side. Finally:

select * from my_other_table where id = 2989; will be:

{id} = 2989
print(redis(hgetall d:{id}))

Import

This part is quite easy, just read the table and follow the above model. Depending on your requirements you may want to import all (or a part of) your data with an hourly/daily/weekly cron.