lua resty module to use a in memory database

gg.lafon · 2014-11-13T02:23:58+00:00

you, sir won 1000 kudos und internets, great work! i will put this into a production later next week and report back, if i find issues. cheers, mex 201...

lua resty module to use a in memory database

gg.lafon

Hello,

I currently use Redis to implement an autocomplete feature and it works well (thanks to zrangebylex command)

The autocomplete feature is for proposing the cities and the list of cities proposed is filtered according the country, area and sub area

So in redis I needed to duplicate the data in order to be able to query according to the whole country or to the country and area or to the country, area and sub area.

Finally each city is inserted 3 times in Redis in 3 sorted set.

it would have been easier to use an in momory database like sqlite with only one table to implement this feature without duplicating the data.

Do you have some ideas to improve the way to implement an efficient autocomplete feature without duplicating the data like I did it? (some redis tips or an efficient in memory database usable with a lua-resty module?)

Thank you

Jerome

agentzh

Hello!

On Wed, Nov 12, 2014 at 10:23 AM, Jerome Lafon wrote:
> Finally each city is inserted 3 times in Redis in 3 sorted set.
> it would have been easier to use an in momory database like sqlite with only
> one table to implement this feature without duplicating the data.
>

Well, this is how redis works: building indexes for queries yourself.
Maybe you can use IDs to save some storage. For a real in-memory data
bases, the information has to be duplicated somehow as well because
they also need to build indexes to speedup things (though the indexes
are usually built and maintained automatically).

> Do you have some ideas to improve the way to implement an efficient
> autocomplete feature without duplicating the data like I did it? (some redis
> tips or an efficient in memory database usable with a lua-resty module?)
>

To embed an in-memory database directly into the nginx server, you
have to make it reside in the shm and protect it with locks so that
all the workers can access it safely. Similar to ngx_lua's
lua_shared_dict facility :)

Regards,
-agentzh

gg.lafon

Thank you for your reply

My concern is that I have around 100 000 records to store 3 times and each record contains between 20 and 100 characters.

I though that with a real database the total weight could be really less than with Redis. And the index building is automatic, it can make saving some time.

I was thinking using an in memory database app like sqlite but it seems not easy to interact with it by TCP for the non blocking behavior.

Do you any experience with voltdb database or a similar in memory database accessible via TCP?

Jerome

Le mercredi 12 novembre 2014 21:27:20 UTC+1, agentzh a écrit :

Hello!

On Wed, Nov 12, 2014 at 10:23 AM, Jerome Lafon wrote:
> Finally each city is inserted 3 times in Redis in 3 sorted set.
> it would have been easier to use an in momory database like sqlite with only
> one table to implement this feature without duplicating the data.
>

Well, this is how redis works: building indexes for queries yourself.
Maybe you can use IDs to save some storage. For a real in-memory data
bases, the information has to be duplicated somehow as well because
they also need to build indexes to speedup things (though the indexes
are usually built and maintained automatically).

> Do you have some ideas to improve the way to implement an efficient
> autocomplete feature without duplicating the data like I did it? (some redis
> tips or an efficient in memory database usable with a lua-resty module?)
>

To embed an in-memory database directly into the nginx server, you
have to make it reside in the shm and protect it with locks so that
all the workers can access it safely. Similar to ngx_lua's
lua_shared_dict facility :)

Regards,
-agentzh

manchev

Redis is an in-memory database already. Key-value store technically with tons of other useful data structures and features. Saving to disk is just a feature.

It seems you're just looking for a traditional relational database. Postgres or MySQL can work entirely from memory too - put them on a ram disk for example. They are supported by OpenResty pretty well I think.

Then, there's the problem of data durability, of course, so you may want to spin up a replication server on the same machine to replicate contents from ram disk to hard disk.

Redis is not a bad idea after all if you can model your data properly and fit it into the data structures provided. Maybe this is your best bet and deserves a second thought.

BTW, you don't want TCP if you want low latency/overhead -- just use unix sockets instead.

Best,
Vladislav

On Thu, Nov 13, 2014 at 1:05 AM, Jerome Lafon <gg....@gmail.com> wrote:

Thank you for your reply
My concern is that I have around 100 000 records to store 3 times and each record contains between 20 and 100 characters.
I though that with a real database the total weight could be really less than with Redis. And the index building is automatic, it can make saving some time.
I was thinking using an in memory database app like sqlite but it seems not easy to interact with it by TCP for the non blocking behavior.
Do you any experience with voltdb database or a similar in memory database accessible via TCP?

Jerome

Le mercredi 12 novembre 2014 21:27:20 UTC+1, agentzh a écrit :
Hello!

On Wed, Nov 12, 2014 at 10:23 AM, Jerome Lafon wrote:
> Finally each city is inserted 3 times in Redis in 3 sorted set.
> it would have been easier to use an in momory database like sqlite with only
> one table to implement this feature without duplicating the data.
>

Well, this is how redis works: building indexes for queries yourself.
Maybe you can use IDs to save some storage. For a real in-memory data
bases, the information has to be duplicated somehow as well because
they also need to build indexes to speedup things (though the indexes
are usually built and maintained automatically).

> Do you have some ideas to improve the way to implement an efficient
> autocomplete feature without duplicating the data like I did it? (some redis
> tips or an efficient in memory database usable with a lua-resty module?)
>

To embed an in-memory database directly into the nginx server, you
have to make it reside in the shm and protect it with locks so that
all the workers can access it safely. Similar to ngx_lua's
lua_shared_dict facility :)

Regards,
-agentzh

.

akunz


Hello Jerome,

what about lua and a lua_shared_dict, warming up from redis? I am notsure how much data a lua_shared_dict can handle, but you can find someinformation about such a idea here:


http://devblog.mixlr.com/2012/09/01/nginx-lua/

Alexander



Am 13.11.2014 03:25, schrieb Vladislav Manchev:

Redis is an in-memory database already. Key-value store technically with
tons of other useful data structures and features. Saving to disk is
just a feature.

It seems you're just looking for a traditional relational database.
Postgres or MySQL can work entirely from memory too - put them on a ram
disk for example. They are supported by OpenResty pretty well I think.

Then, there's the problem of data durability, of course, so you may want
to spin up a replication server on the same machine to replicate
contents from ram disk to hard disk.

Redis is not a bad idea after all if you can model your data properly
and fit it into the data structures provided. Maybe this is your best
bet and deserves a second thought.

BTW, you don't want TCP if you want low latency/overhead -- just use
unix sockets instead.

Best,
Vladislav

On Thu, Nov 13, 2014 at 1:05 AM, Jerome Lafon <gg....@gmail.com
<mailto:gg....@gmail.com>> wrote:

    Thank you for your reply
    My concern is that I have around 100 000 records to store 3 times
    and each record contains between 20 and 100 characters.
    I though that with a real database the total weight could be really
    less than with Redis. And the index building is automatic, it can
    make saving some time.
    I was thinking using an in memory database app like sqlite but it
    seems not easy to interact with it by TCP for the non blocking behavior.
    Do you any experience with voltdb database or a similar in memory
    database accessible via TCP?

    Jerome

    Le mercredi 12 novembre 2014 21:27:20 UTC+1, agentzh a écrit :

        Hello!

        On Wed, Nov 12, 2014 at 10:23 AM, Jerome Lafon wrote:
         > Finally each city is inserted 3 times in Redis in 3 sorted set.
         > it would have been easier to use an in momory database like
        sqlite with only
         > one table to implement this feature without duplicating the
        data.
         >

        Well, this is how redis works: building indexes for queries
        yourself.
        Maybe you can use IDs to save some storage. For a real in-memory
        data
        bases, the information has to be duplicated somehow as well because
        they also need to build indexes to speedup things (though the
        indexes
        are usually built and maintained automatically).

         > Do you have some ideas to improve the way to implement an
        efficient
         > autocomplete feature without duplicating the data like I did
        it? (some redis
         > tips or an efficient in memory database usable with a
        lua-resty module?)
         >

        To embed an in-memory database directly into the nginx server, you
        have to make it reside in the shm and protect it with locks so that
        all the workers can access it safely. Similar to ngx_lua's
        lua_shared_dict facility :)

        Regards,
        -agentzh.




--

Mit freundlichen Grüßen
Alexander Kunz

WISHMEDIA GmbH
Kopernikusstr. 4
D-65232 Taunusstein

http://www.wishmedia.de

Tel: +49-6128-973797
Fax: +49-6128-973798

E-Mail: ak...@wishmedia.de

Geschäftsführer: Alexander Kunz
Handelsregister: Amtsgericht Wiesbaden, HRB 24125
USt-IdNr.: DE265062989

gg.lafon

Thanks everybody for your replies.

I'm gonna try to benchmark Postgresql with a temporary table using exactly the same data used with Redis.

I did not want to involve postgresql with all the autocomplete SELECT but if the benchmark is good I will think about that.

Jérôme

Le mercredi 12 novembre 2014 19:23:57 UTC+1, Jerome Lafon a écrit :

Hello,
I currently use Redis to implement an autocomplete feature and it works well (thanks to zrangebylex command)
The autocomplete feature is for proposing the cities and the list of cities proposed is filtered according the country, area and sub area
So in redis I needed to duplicate the data in order to be able to query according to the whole country or to the country and area or to the country, area and sub area.
Finally each city is inserted 3 times in Redis in 3 sorted set.
it would have been easier to use an in momory database like sqlite with only one table to implement this feature without duplicating the data.

Do you have some ideas to improve the way to implement an efficient autocomplete feature without duplicating the data like I did it? (some redis tips or an efficient in memory database usable with a lua-resty module?)

Thank you
Jerome