Cpu bound operations / in-process data access

derek

I just posted, but it didn't show up so forgive me if this is a duplicate...

I am trying to do a multi dimensional filter:

SELECT id FROM X WHERE a in (1,2) AND b in(3,4) AND c in (4,6) AND d in (3,5) .....

from < 100,000 items

The approaches I am looking at are:

1. embedding SQLite, using it as an in-memory only store, and prepared statements. this dissuaded me (https://groups.google.com/forum/#!msg/openresty-en/lUH10YE_6Z8/yinTC855qfYJ)

2. using this data structure https://github.com/chriso/bitset from lua, and doing unions / intersections

3. building a data structure in lua and traversing it in pure lua

4. calling a service with the cosocket API

the data structure can be built at startup, and is static (unchanged during runtime) so the data structure can be read only.

performance / low latency is the #1 concern (under high concurrency)

Besides the cosocket API, I am assuming all of my other solutions are blocking and will kill performance? is there a better way to do this in process? using co-routines?

thanks!

agentzh

Hello!

On Fri, Oct 11, 2013 at 3:53 PM,  <de...@eclipse.io> wrote:
> I just posted, but it didn't show up so forgive me if this is a duplicate...
>

Your previous post requires moderation for some reason (maybe you
posted that before joining the group?)

> The approaches I am looking at are:
> 1. embedding SQLite, using it as an in-memory only store, and prepared
> statements. this dissuaded me
> (https://groups.google.com/forum/#!msg/openresty-en/lUH10YE_6Z8/yinTC855qfYJ)

Embedding things like SQLite into Nginx has one big issue: the data is
not shared across all the Nginx workers. You need shared memory based
solutions like ngx_lua's shared dictionary API:

    http://wiki.nginx.org/HttpLuaModule#ngx.shared.DICT

It is fine if

1. you just have identical read-only data set,
2. your SQLite queries take a very short time to complete.

> 2. using this data structure https://github.com/chriso/bitset from lua, and
> doing unions / intersections

The same caveat (above) also applies here :)

> 3. building a data structure in lua and traversing it in pure lua

Still the same thing as above.

> 4. calling a service with the cosocket API
>

This option has fewer limitations but adds more complexity (and
probably more overhead at the same time) :)

Regards,
-agentzh

derek

great, thanks for the response. So if I am ok with loading the data structure in each worker, and it's pretty high performance (CPU work that can't really be avoided) sounds like it makes sense to run the query in process.

On Saturday, October 12, 2013 12:15:37 AM UTC-7, agentzh wrote:

Hello!

On Fri, Oct 11, 2013 at 3:53 PM, <de...@eclipse.io> wrote:
> I just posted, but it didn't show up so forgive me if this is a duplicate...
>

Your previous post requires moderation for some reason (maybe you
posted that before joining the group?)

> The approaches I am looking at are:
> 1. embedding SQLite, using it as an in-memory only store, and prepared
> statements. this dissuaded me
> (https://groups.google.com/forum/#!msg/openresty-en/lUH10YE_6Z8/yinTC855qfYJ)

Embedding things like SQLite into Nginx has one big issue: the data is
not shared across all the Nginx workers. You need shared memory based
solutions like ngx_lua's shared dictionary API:

http://wiki.nginx.org/HttpLuaModule#ngx.shared.DICT

It is fine if

1. you just have identical read-only data set,
2. your SQLite queries take a very short time to complete.

> 2. using this data structure https://github.com/chriso/bitset from lua, and
> doing unions / intersections

The same caveat (above) also applies here :)

> 3. building a data structure in lua and traversing it in pure lua

Still the same thing as above.

> 4. calling a service with the cosocket API
>

This option has fewer limitations but adds more complexity (and
probably more overhead at the same time) :)

Regards,
-agentzh