MySQL for Redis - Import and Model

Question

I am thinking of using Redis to cache snapshots (s) of user data in order to speed up access to this data (one of the reasons is because my MySQL table suffers a lock conflict), and I'm looking for a better way to import a table like this in one step (which can contain from several records to millions of records):

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

in the Redis key store.

I can have many “snapshots” to upload to Redis, and the main access pattern (SQL syntax)

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

these snapshots can also come from other tables , so the “ global unique identifier for the snapshot ” is the snapshot , ex column:

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

The uploaded snapshot in redis never changes, they are available only after a week via TTL

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

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

I found redis-cli --pipe Redis Mass Insertion (as well as MySQL for Redis in one step ), but I cannot find a better way to fulfill my requirements (loading from mysql in one step all rows / columns, the best redis model for this ) using HMSET

Thank you in advance

Cristian.

Answer 1

Model

To request data from Redis in the same way as:

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

You will need the model below.

Note: select * from mytable where snapshot = ? and id = ? select * from mytable where snapshot = ? and id = ? doesn't make much sense here, as it matches select * from mytable where id = ? .

Type and purpose of names

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

Note. I used d: as a namespace, but you can 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 [e-mail] 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 [e-mail] name fake-name-2990 ... snapshot 1134 zadd d:ByInsertionDate {current_timestamp} d:2990 sadd d:BySnapshot:1134 d:2990 

Cron

Here is an algorithm that should run every 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}) 

Using

select * from my_other_table where snapshot = 1134; will 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 it right on the redis side. Finally:

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

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

Import

This part is pretty simple, just read the table and follow the model above. Depending on your requirements, you can import all (or part) of your data using the hourly / daily / weekly cron.