[sqlite] SQLite Database in Shared Memory

[Manuj Bhatia]

Hi,

I am trying to implement a shared queue (to asynchronously exchange messages
between processes) using SQLite.
Since I do not need my queues to be persistent (at least for now), I do not
want to use disk based SQLite database (for better performance).

I see there is an option to create purely in-memory DBs, but I don't see
anything for the shared memory.
Any ideas about how to implement a DB in shared memory?

Thanks,
Manuj
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Pavel Ivanov]

> Any ideas about how to implement a DB in shared memory?

It's impossible with current SQLite code base. Even if you try to
implement your own database cache and will allocate it in some shared
memory it won't work because along with cache pages SQLite also stores
some internal information which should be bound to one process.

So your best shot is to re-write SQLite's part related to shared cache
and use some inter-process locks there instead of mutexes (they are
there used now). Probably this approach will work.

But the best solution will be to use standard IPC mechanisms and to
not make things over-complicated. Generally databases should be used
if you need at least some kind of persistence between process
restarts.


Pavel

[Alexey Pechnikov]

TCP-socket listening daemon + SQLite in-memory database may be helpful.

--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/

[Jay A. Kreibich]

On Mon, May 10, 2010 at 11:15:59AM -0400, Pavel Ivanov scratched on the wall:

> > Any ideas about how to implement a DB in shared memory?
>
> It's impossible with current SQLite code base. Even if you try to
> implement your own database cache and will allocate it in some shared
> memory it won't work because along with cache pages SQLite also stores
> some internal information which should be bound to one process.

Yes... in-memory DBs are strongly tied to the database connection
that creates them. They cannot be accessed by multiple processes.

The only option for a true multi-access in-memory DB would be to
write a VFS module. That's a somewhat non-trivial piece of code.

The other option would be to use a standard file-based DB and just
turn all the syncs off. Most of the DB would live in the OS disk
cache, and you'd get nearly the same performance.

> But the best solution will be to use standard IPC mechanisms and to
> not make things over-complicated. Generally databases should be used
> if you need at least some kind of persistence between process
> restarts.

Agreed. There are other, likely better, ways of solving this problem.

Protected queues for IPC message passing is an old, old problem.
Many advanced threading libs have the tools for this. If not,
there are many different message-passing libraries out there
like MPI.

-j

--
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson

[Simon Slavin]

On 10 May 2010, at 4:47pm, Alexey Pechnikov wrote:

> TCP-socket listening daemon + SQLite in-memory database may be helpful.

Yes. You can make one process, which handles all your SQLite transactions, and receives its orders from other processes via inter-process calls or TCP/IP. I've seen a few solutions which do this and they work fine. But that process will itself become some sort of bottleneck if you have many processes calling it. And I think that the original post in this thread described a situation where that was not a good solution.

Simon.

[Manuj Bhatia]

Pavel,

I do not have a requirement of persistence in my current design, but I
expect that we might extend this shared-queue solution to more areas of the
server and will require some sort of persistence then.
That is one of the main reasons I do not want to use IPC queues (there are
other reasons like fixed message sizes, minimal support for queue/message
level metadata).

One of the main attractions of SQLite-based solution is to be able to
perform all kind of queries on the queue itself (from the point of view of
maintenance scripts/production support).
In my experience, if there are lots of services sharing different types of
messages over an IPC shared queue, sometimes you run into a situation where
the queue starts backing up and there is no way for production support folks
to determine which particular service is causing the backup (by sending
messages too fast, or consuming them really slow). And, in the end the only
solution is to bounce all the services (instead of just bouncing the
culprit) and we never discover the root cause of the backup.

If I use a SQLite-backed queue, I can simply use the command line shell and
run queries like:

select sender, receiver, count(*)
from queue
group by sender, receiver;

Or any combination of message metadata to analyze the current state of the
queue.

Also, I can easily modify my queue APIs to just update a used flag, instead
of deleting the message from the db. This way, I can analyze all the
messages at the end of day and determine all kinds of statistics (like how
long does a particular type of message sits in the queue).

In short, using a SQLite-backed queue solution gives me a lot of options
that a simple IPC based (and, for that matter, even a professional Messaging
Product) does not give.

Jay,
I did think of implementing a VFS for the shared-memory, but as you
mentioned a file-based DB with all syncs off might be a simpler trade-off.

Alexey,
As Simon said, having a socket based daemon solution is something I want to
avoid because it adds another layer to the architecture.

Thanks,
Manuj

[Black, Michael (IS)]

Just 'cuz you don't need persitence now of course doesn't mean you can't use it.

That solves your "shared memory" problem even though it's not as elegant.

You can even access via file shares that way too which sounds a bit like what you may want do anyways.

Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems


________________________________

[Pavel Ivanov]

> In short, using a SQLite-backed queue solution gives me a lot of options
> that a simple IPC based (and, for that matter, even a professional Messaging
> Product) does not give.

Also SQLite-backed solution gives you a big restriction that IPC
doesn't: you have to poll the queue instead of pushing to it. I.e. the
process reading the queue will have to execute some query periodically
to see if there's anything in the queue. You don't want to execute
this query without delay because it will eat 100% of you CPU at any
time even when there's nothing in the queue. Besides it can introduce
writer starvation. But when you execute query with any delay you lose
immediate reaction of the queue. It's your choice of course.

BTW, look closely at your requirements - you have some contradiction
in them. You don't want to mess with file system because *you think*
it will have performance penalty (as was already said it's not always
true because OS cache your file in memory anyway). You don't want to
use IPC because it's "bad". You want SQLite to work completely in
memory and you want it to work inside several processes with the same
memory. But how do you think SQLite should interact with itself to
avoid reader in one process reading corrupted data while writer in
another process is writing something new? The only way to do it is to
use IPC. And SQLite does use one (probably the easiest) method of IPC
- file systems locks. No other IPC mechanism is implemented in SQLite.
So you have to allow SQLite to do its job - you need to have your
database in the file system even if you won't ever read it once your
application is closed.


Pavel

[Eric Smith]

Manuj Bhatia wrote:

> I do not have a requirement of persistence in my current design, but I
> expect that we might extend this shared-queue solution to more areas of
> the server and will require some sort of persistence then.
> That is one of the main reasons I do not want to use IPC queues (there are
> other reasons like fixed message sizes, minimal support for queue/message
> level metadata).

OP might consider creating a database file on a tmpfs filesystem.
The OS tricks SQLite (and everything in user space) into thinking the
file is a normal file with all the usual properties thereof -- but
it's backed by RAM and not any persistent medium. You'll get the perf
benefits you wanted, along with the relatively easy ability to make the
DB persistent later.

Fedora 12 has one of these mounted at /dev/shm by default, though I
presume any modern Linux will support this.

Caveat (1). I ran 'make test' on SQLite 3.6.23.1 on my box (Linux ___
2.6.32.11-99.fc12.i686 #1 SMP Mon Apr 5 16:32:08 EDT 2010 i686 athlon
i386 GNU/Linux) from within a tmpfs filesystem and 23 tests failed:

shared-1.1.1 shared-1.2.1 shared-1.4.1.1 shared-1.4.1.2 shared-1.4.1.3
shared-2.1.1 shared-2.2.1 shared-2.4.1.1 shared-2.4.1.2 shared-2.4.1.3
stmt-1.2 stmt-1.3 stmt-1.4 stmt-1.5 stmt-1.6 stmt-1.7 stmt-2.1 stmt-2.2
stmt-2.3 stmt-2.5 tempdb-2.2 tempdb-2.3 tkt2565-1.X

I wanted to investigate to see why but haven't had the time -- it has
to do with the global Tcl variable sqlite_open_file_count. Running the
fixture on just those test files yields passes (every time), but running
the whole 'veryquick' suite yields failures (every time). I see there's
machinery to try to clear all state between test runs -- obviously this
is not successful in my test.

The testfixture is dynamically linked against these libraries:
linux-gate.so.1 => (0x00511000)
libtcl8.5.so => /usr/lib/libtcl8.5.so (0x005cb000)
libdl.so.2 => /lib/libdl.so.2 (0x00d1f000)
libm.so.6 => /lib/libm.so.6 (0x00d42000)
libpthread.so.0 => /lib/libpthread.so.0 (0x00d26000)
libc.so.6 => /lib/libc.so.6 (0x00ba9000)
/lib/ld-linux.so.2 (0x00b87000)

Caveat (2). I don't claim this is the best solution for the OP -- just
a possibility.

Eric

--
Eric A. Smith

I think there's a world market for about five computers.
-- attr. Thomas J. Watson (Chairman of the Board, IBM), 1943

[Manuj Bhatia]

I am sorry... when I said IPC-based I really meant an IPC-Queue-based (the
standard msgget()/mq_open() stuff).
I have nothing against IPC :). It's just that an IPC-queue solution will not
satisfy all my requirements.
I will definitely be using IPC semaphore/mutex facility to avoid having to
poll the database every time (and of course for SQLite to perform the
serialization).

Here is a high-level flow of what I have in mind:
1. Writer writes a message to the db.
2. Then, it increments a semaphore (indicating a new message is available).
3. All the readers are waiting to lock the semaphore (so there will be no
sleep-polling pattern).
4. One of the readers obtains a lock on the semaphore.
5. At that time, it queries the database and retrieves a message to be
processed.
6. Then it again goes into a wait state to lock the semaphore.

Considering that there is no straight-forward solution to get SQLite into
the shared memory, I'll probably go with a disk-based database and use other
optimization methods (like no syncing, caching etc.) to help with the
performance.

Thanks!
~Manuj

[Manuj Bhatia]

I am developing this solution for an AIX machine. I am not sure if it does
any such optimization for the temp file system.
As someone recommended, I can probably implement a VFS for Shared-memory,
but that seems to be too much work :)

I am inclining towards a file-based DB with syncs turned off. If the
performance is not satisfactory, then I might look at implementing a VFS.

Thanks,
~Manuj

[Black, Michael (IS)]

I think you may be worrying too much about file speed as it's already pretty fast.

But if you want AIX ramdisk check here:
http://www.ee.pw.edu.pl/~pileckip/aix/mkramdisk.htm

Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems


________________________________

From: sqlite-use...@sqlite.org on behalf of Manuj Bhatia
Sent: Tue 5/11/2010 11:15 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite Database in Shared Memory

[Alexey Pechnikov]

Hm... You can use the dedicated thread in your application for SQLite
in-memory database. Why you want to build external application for
this? And SQL for you task is not needed I think - you can use the
simple hash table or any other simple structure. If you have same
additional needs or ideas - speak it!

--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/

[Manuj Bhatia]

I need to exchange messages across processes, not threads. And one of the
reasons that I am inclined towards SQLite is that I do not want a separate
Queue-manager process.
I'll just write wrapper APIs around SQLite and embed them into each
application, so I have a manager-less implementation.

I found a Queue implementation using SQLite at XMLBlaster:
http://www.xmlblaster.org/xmlBlaster/doc/requirements/client.c.queue.html

I'll see how they have implemented it and might adapt it to my needs.

Michael,
Thanks for the link! I didn't know about this feature of AIX. I'll see if I
can get my Unix Admins to create a Ram Disk for me to play around with.

Thanks,
Manuj

On Tue, May 11, 2010 at 2:29 PM, Alexey Pechnikov <pech...@mobigroup.ru>wrote:

> Hm... You can use the dedicated thread in your application for SQLite
> in-memory database. Why you want to build external application for
> this? And SQL for you task is not needed I think - you can use the
> simple hash table or any other simple structure. If you have same
> additional needs or ideas - speak it!
>

[Jeremy Hinegardner]

Another library option would be JLog

https://labs.omniti.com/trac/jlog

enjoy,

-jeremy

--
========================================================================
Jeremy Hinegardner jer...@hinegardner.org