Re: [sqlite] SQLite Database in Shared Memory

[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 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!
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Database in Shared Memory

[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


On Tue, May 11, 2010 at 8:39 AM, Eric Smith  wrote:

> 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
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Database in Shared Memory

[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


On Tue, May 11, 2010 at 7:34 AM, Pavel Ivanov  wrote:

> > 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
>
> On Mon, May 10, 2010 at 3:59 PM, Manuj Bhatia 
> wrote:
> > 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 st

Re: [sqlite] SQLite Database in Shared Memory

[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



On Mon, May 10, 2010 at 10:56 AM, Simon Slavin  wrote:

>
> 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.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[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-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users