Transfering large redis data to MYSQL

[Mr_Google]

Hi here's my problem. I need to transfer a large amount of data (page
hits) from Redis to Mysql everyday. Reason I'm doing this is because
redis sort (hits descending) can be slow, so I'm going to have to use
mysql to store and sort hits. Data in in the range of 50 million keys.

I can't use daily cron, because it'll timeout due to the large amount
of data transferred at one go. Anyone got any idea / solution they
implemented to this problem?

[Josiah Carlson]

I'm sure there are a few people who would be happy to answer your
question, but first it would be great if you could describe what you
are inserting into redis, how you are storing it in redis, and what
you want out at the end.

Thanks,
- Josiah

> --
> You received this message because you are subscribed to the Google Groups "Redis DB" group.
> To post to this group, send email to redi...@googlegroups.com.
> To unsubscribe from this group, send email to redis-db+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/redis-db?hl=en.
>
>

[Jeremy Zawodny]

Your cron comment is confusing.  Why will it not work?

Jeremy

On Fri, Jul 2, 2010 at 8:52 PM, Mr_Google <kah...@gmail.com> wrote:

[Mason Jones]

As Jeremy asked, I'm also curious why a cron wouldn't work and why you
say it would time out...

One idea, though I'm not sure how difficult it might be (partially
depending on your specific data) would be to set up Redis to use an
append-only file, and write a script to read that file, parse it, and
use it to write to MySQL. In theory I think that should work, though
in practice...who knows.

[Tobias Petry]

Do you have tried to optimize your Redis datastructure?
Are you using zsets? They should be incredibly fast.
The most everything MySQL does you can rebuild in redis by creating
custom datastructures with lists, (z)sets and hashmaps.
When you describe in detail what data you store and how you want to
access them, we can help you making it efficient ;)

I don't know if there's really a fast way dumping the data, maybe the
best is a script running on a redis dump, exporting the relevant
information to a csv-file and then mysql's "load data infile"-command

[Terry Martin]

Your situation is interesting to me.  I'm not sure, but my initial thought is to leverage the pubsub messaging paradigm to push data to MySQL, etc.  Perhaps a daemon that brokers transactional requests.  Again, just my initial thought.  I haven't tested this, but it seems like an interesting angle to explore.  

Looking forward to watching this thread.

- Terry

 @tzmartin

[Oscar]

The situation is interesting to me too.
We have an analyse system to query/analyze the operation data.
Say, we want to know how many registered accounts between the
specified ages.
The only way I could considered is to scan all the accounts, it's a
low-performance way.

So, I need migrate the redis data to mysql, and make the analyse
system access mysql.
Back to this thread, is there any easy or high-performance way to
migrate the redis data to mysql?

On Jul 3, 12:12 pm, Terry Martin <terryzmar...@gmail.com> wrote:
> Your situation is interesting to me.  I'm not sure, but my initial thought
> is to leverage the pubsub messaging paradigm to push data to MySQL, etc.
>  Perhaps a daemon that brokers transactional requests.  Again, just my
> initial thought.  I haven't tested this, but it seems like an interesting
> angle to explore.
>
> Looking forward to watching this thread.
>
> - Terry

>  @tzmartin <http://twitter.com/tzmartin>

>
>
>
> On Fri, Jul 2, 2010 at 11:52 PM, Mr_Google <kahc...@gmail.com> wrote:
> > Hi here's my problem. I need to transfer a large amount of data (page
> > hits) from Redis to Mysql everyday. Reason I'm doing this is because
> > redis sort (hits descending) can be slow, so I'm going to have to use
> > mysql to store and sort hits. Data in in the range of 50 million keys.
>
> > I can't use daily cron, because it'll timeout due to the large amount
> > of data transferred at one go. Anyone got any idea / solution they
> > implemented to this problem?
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "Redis DB" group.
> > To post to this group, send email to redi...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > redis-db+u...@googlegroups.com<redis-db%2Bunsubscribe@googlegroups.c om>
> > .

[Pieter Noordhuis]

There is no out-of-the-box solution. Because everyone uses Redis differently, every migration script would be different. Everybody migrating data from Redis to something else should wrap a few client libraries and roll their own solution, tailored exactly to their needs.

Cheers,

Pieter

To unsubscribe from this group, send email to redis-db+u...@googlegroups.com.

[Jak Sprats]

Redis data is only one dimensional(STRING,LIST,SET) or two
dimensional(ZSET,HASH), so transforming redis commands into Mysql
tables is actually fairly easy.

The idea of having a script that listens to the append-only file (from
Marion) DOES work, it requires some transformations, but is fairly
quick, the key is your data is organized in a way that can easily
NORMALISE.

Building transformations is not that hard: (quick & dirty writeup)
A.) STRING (created by "SET" cmd) can simple be INSERTed into a table
w/ a pk that auto-increments and two varchar(256)s named key and value
1.) "SET x 10000"-> "INSERT into STRINGS values ("x","10000"); {pk
will be 1}
2.) "SET y 20000"-> "INSERT into STRINGS values ("y","20000"); {pk
will be 2}
B.) LIST is pretty much the same except the table needs a timestamp
and LPOPs sort to last timestamp and delete.
1.) "LADD L 10" -> INSERT into LISTS ("L","10") {timestamp would be
1280349447}
2.) "LADD L 20" -> INSERT into LISTS ("L","20") {timestamp would be
1280349448}
3.) "LPOP L" -> DELETE from LISTS where name = "L" and timestamp
= (SELECT max(timestamp) from LISTS where name ="L");
C.) HASH needs two fields (key varchar(32) and value FLOAT), HDEL
needs to create a SQL DELETE with pk=x and key=k.
1.) "HADD H key1 val1" -> INSERT INTO HASHES values
("H","key1",val1");
2.) "HDEL H keys1" -> DELETE FROM HASHES where pk="H" and
key="key1"

Further complicated denormalised key-value sets can also be normalised
back into SQL
Here is an example bash script:
CLI="./redis-cli"
$CLI set user:1:name bill
$CLI set user:1:age 33
$CLI set user:1:status member
$CLI set user:2:name jane
$CLI set user:2:age 22
$CLI set user:2:status premium

Its pretty easy to dump this data, use the unix "sort" command and
insert the following rows into the following table:
#CREATE table users (id int primary key, name varchar(32), age int,
status varchar(32));
#insert into users values(1,"bill",33,"member");
#insert into users values(2,"jane",22,"premium");

So if your data is organised properly, having a script do a "tail -f
appendonly-file | ./script | mysql" will work.

The Key is to make sure your data can be NORMALISED trivially and
systematically.

Hope my explanation was not so quick and dirty that it makes sense :)

On Jul 2, 11:52 pm, Mason Jones <masonjo...@charnel.com> wrote:
> As Jeremy asked, I'm also curious why a cron wouldn't work and why you
> say it would time out...
>
> One idea, though I'm not sure how difficult it might be (partially
> depending on your specific data) would be to set up Redis to use an
> append-only file, and write a script to read that file, parse it, and
> use it to write to MySQL. In theory I think that should work, though
> in practice...who knows.
>