Experimenting with combining SQLite and Redis

[Grisha Trubetskoy]

Happy New Year everyone,

I though I'd share my experimental Redis mod in this group, hopefully this is sufficiently on-topic. 

I've successfully embedded SQLite within Redis server, and have written code that lets one submit SQL statements, as well as a SQL function that allows Redis commands from within SQL.

At this point there exists only a one-page simple introduction on http://thredis.org/ which IMHO explains what it is done pretty well. (I originally named it Thredis though it seems to have far diverged from simply a multi-threaded version of Redis.)

I am very curious whether people think that this may be practically useful, or is this more like combining a toaster with a dishwasher :). My original use case was data-analytics on large datasets which required some elaborate zset operations and I wanted to minimize network back-and-forth by keeping as much processing as possible on the server side. Introducing SQL into the mix access allows for some interesting possibilities, for example something as trivial as substring search of lists/hashes/zsets, ability to join them, etc.

I've observed some interesting things, for example a SELECT with a LIKE clause on a Redis list object of a million elements runs actually faster than over a SQLite table. (Which is sort of obvious - iterating over a list, which is a linked-list is much cheaper than the SQLite B-Tree).

Another thought is that once SQLite4 matures, there may be a more elegant alternative to this whereby Redis Cluster could serve as the backend replacing the LSM layer, providing an in-memory SQL database with infinite linear scalability. (The only problem is that Redis currently does not provide a Sorted Map structure or an Iterator interface, but that could be solved).

Anyway - I would appreciate any feedback/thoughts on this idea!

Cheers,

Grisha

[M. Edward (Ed) Borasky]

Well, there's this:

http://blog.rgbdaily.com/2011/02/03/redis-and-sqlite-why-wait-for-good-enough/

I'm not a big user of SQLite on its own; if it's embedded in something
that's great, but I tend to fall back on PostgreSQL for an RDBMS
simply because I have much more experience with it than the others,
and because I know a few of the PostgreSQL hackers. But the idea of a
three-part embedded tool set - Redis, SQLite and Lua - is very
appealing to me. I'm guessing Redis Sentinel and Redis Cluster are
"enough" for most use cases without adding an RDBMS, though.

--
Twitter: http://twitter.com/znmeb; Computational Journalism Publishers
Workbench: http://znmeb.github.com/Computational-Journalism-Publishers-Workbench/

How the Hell can the lion sleep with all those people singing "A weem
oh way!" at the top of their lungs?

[pedigree]

Oh, this looks great.  I've got a 24 thread box here with 80gb of ram and I'm using it right now for testing hash collisions on email addresses vs memory usage offset.  I'm going to compile this right now!

[pedigree]

Oh well, not a great start for me :(

Temporary breakpoint 1 at 0x41af90: file redis.c, line 2861.
Starting program: /home/master/thredis/src/thredis-server
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".

Temporary breakpoint 1, main (argc=1, argv=0x7fffffffe6b8) at redis.c:2861
2861    int main(int argc, char **argv) {
(gdb) continue
Continuing.
[27096] 01 Jan 12:55:30.945 # Warning: no config file specified, using the default config. In order to specify a config file use /home/master/thredis/src/thredis-server /path/to/redis.conf
[27096] 01 Jan 12:55:30.947 * Max number of open files set to 10032
[27096] 01 Jan 12:55:30.947 * Starting 16 worker threads with a threadpool queue of size 1024.
[New Thread 0x7ffff67ff700 (LWP 27099)]
[New Thread 0x7ffff5ffe700 (LWP 27100)]
[New Thread 0x7ffff57fd700 (LWP 27101)]
[New Thread 0x7ffff4ffc700 (LWP 27102)]
[New Thread 0x7ffff47fb700 (LWP 27103)]
[New Thread 0x7ffff3ffa700 (LWP 27104)]
[New Thread 0x7ffff37f9700 (LWP 27105)]
[New Thread 0x7ffff2ff8700 (LWP 27106)]
[New Thread 0x7ffff27f7700 (LWP 27107)]
[New Thread 0x7ffff1ff6700 (LWP 27108)]
[New Thread 0x7ffff17f5700 (LWP 27109)]
[New Thread 0x7ffff0ff4700 (LWP 27110)]
[New Thread 0x7ffff07f3700 (LWP 27111)]
[New Thread 0x7fffefff2700 (LWP 27112)]
[New Thread 0x7fffef7f1700 (LWP 27113)]
[New Thread 0x7fffeeff0700 (LWP 27114)]
[New Thread 0x7fffee7ef700 (LWP 27115)]
[New Thread 0x7fffedfee700 (LWP 27116)]

Program received signal SIGSEGV, Segmentation fault.
0x000000000043a7b9 in sqlite3MemMalloc (nByte=40) at sqlite3.c:15438
15438       p[0] = nByte;
(gdb) bt
#0  0x000000000043a7b9 in sqlite3MemMalloc (nByte=40) at sqlite3.c:15438
#1  0x000000000041b839 in mallocWithAlarm (n=40, pp=0x7fffffffe488) at sqlite3.c:18734
#2  0x00000000004210ce in sqlite3Malloc (n=40) at sqlite3.c:18767
#3  0x0000000000421115 in sqlite3MallocZero (n=40) at sqlite3.c:19035
#4  0x000000000042c1d8 in pthreadMutexAlloc (iType=<optimised out>) at sqlite3.c:17901
#5  pthreadMutexAlloc (iType=<optimised out>) at sqlite3.c:17889
#6  0x000000000042e37e in sqlite3MutexAlloc (id=1) at sqlite3.c:17491
#7  sqlite3_initialize () at sqlite3.c:46236
#8  sqlite3_initialize () at sqlite3.c:46191
#9  0x00000000004600cd in openDatabase (zFilename=0x4f7b51 ":memory:", ppDb=0x73e248, flags=65542, zVfs=0x0) at sqlite3.c:113810
#10 0x000000000047e96a in sqlInit () at sql.c:464
#11 0x000000000041b1cf in main (argc=<optimised out>, argv=<optimised out>) at redis.c:2929
(gdb) info regs
Undefined info command: "regs".  Try "help info".
(gdb) info reg
rax            0x7ffff6811610   140737329042960
rbx            0xfffffffff6811610       -159312368
rcx            0x9      9
rdx            0x1      1
rsi            0x7fffffffe488   140737488348296
rdi            0xb      11
rbp            0x28     0x28
rsp            0x7fffffffe440   0x7fffffffe440
r8             0x80     128
r9             0x5      5
r10            0x7ffff6807768   140737329002344
r11            0x1      1
r12            0x7fffffffe488   140737488348296
r13            0x0      0
r14            0x0      0
r15            0x0      0
rip            0x43a7b9 0x43a7b9 <sqlite3MemMalloc+41>
eflags         0x10282  [ SF IF RF ]
cs             0x33     51
ss             0x2b     43
ds             0x0      0
es             0x0      0
fs             0x0      0
gs             0x0      0

[pedigree]

Seems to be working when compiled for 32bit.

[Grisha Trubetskoy]

Sorry about that, I think this ought to fix the 64bit problem. For whatever reason I've been using the 32bit version when hacking this together.

https://github.com/grisha/thredis/commit/eeb7ead533613a2d4b0695c0b6267120e9f10d5c

[pedigree]

Yes, that fixes it :-)  Now off to see how it handles of lot of threads.

[Josiah Carlson]

Also, if you're looking for SQL inside Redis (without the
persistence), another Redis user by the name of Russel Sullivan built
a SQL layer inside Redis:

https://github.com/JakSprats/Alchemy-Database

... Though he's since moved onto another company (which doesn't do
SQL, though does do high transactional volume with automated
failover).

- Josiah

> --
> You received this message because you are subscribed to the Google Groups
> "Redis DB" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/redis-db/-/aUvBFUh6DPAJ.
>
> 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.

[Grisha Trubetskoy]

Yes, I saw that, and found it inspiring/validating that someone else has thought of something very similar, it's too bad that it is sort of unfinished/commercialized. I think Russel implemented his own version of SQL parsing, which I found could be avoided by wonders of SQLite and another difference is that I am not one to swear by single-thread execution model because I'm not convinced that sharding and multi-process is simpler than threads and a bunch of locks (I don't have a strong opinion on this, I'd rather try both and see which is better). Another diff is the license, I'm not a fan of restrictive licenses.

Grisha

[Josiah Carlson]

The AGPL is only restrictive if you don't plan on giving back to the
community. If you make no alterations to the software, you can point
people to the original sources, and that should be sufficient.

- Josiah