Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Jeremy Hinegardner]

You may also want to look at libjlog https://labs.omniti.com/labs/jlog

enjoy,

-jeremy

On Thu, Sep 30, 2010 at 10:11:54AM +0200, Lynton Grice wrote:
> Hi there,
> 
>  
> 
> I am a HUGE SQLite fan and have an interesting question I have been
> scratching my head about for a couple days now.
> 
>  
> 
> First off my daily job is very much around "messaging" and I am very
> familiar with message queue products like Websphere MQ and Fiorano MQ.
> 
>  
> 
> When you install Websphere MQ or Fiorano MQ they have a FILE based queue
> underneath by default (typically one file per queue with the messages etc
> stored at different offsets).
> 
>  
> 
> There messaging systems will have "file writer locks" that in essence will
> only allow ONE WRITER per queue at any one time. So any clients sending
> messages will have "to wait in line" until the message can be physically
> written to file.
> 
>  
> 
> Bottomline: We have one writer and multiple readers per queuefine.
> 
>  
> 
> Then I scratch my head and wonder why SQLite is not the PERFECT persistence
> layer for building an awesome "file based queue" on? It is lightening fast
> by default, and also has the same "locking issues" described above. We are
> talking milliseconds anywaybetween writes
> 
>  
> 
> Bottomline: Is there any reason why anyone would think someone like me
> coding a solid message queue using SQLite is a bad idea? Am I missing
> something? I personally think it is a wonderful idea and would be hugely
> useful to my daily job
> 
>  
> 
> Any help or advise in this regard would be hugely appreciated ;-)
> 
>  
> 
> Thanks
> 
>  
> 
> Lynton
> 
>  
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 

 Jeremy Hinegardner  jer...@hinegardner.org 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Andrew Davison]

I've used SQLite for exactly this. I have no gripes with it at all. I 
ended up using just one table in one DB as indexing makes things simple 
and fast. WAL i've been trying these last few days and seems trouble-free.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Lynton Grice]

Hi Andrew,

Yup, I think my mind is made up...I will use SQLite with WAL with "fairly
frequent" commits to force the update of the database from the WAL file.

How often do you run the commits between the two files? Or do you just use
the default?

Chat later

Lynton

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andrew Davison
Sent: 01 October 2010 12:43 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

I've used SQLite for exactly this. I have no gripes with it at all. I 
ended up using just one table in one DB as indexing makes things simple 
and fast and I support arbitrary queues with multiple readers. WAL i've 
been trying these last few days and seems trouble-free.

On 30/09/2010 6:11 PM, Lynton Grice wrote:
> Hi there,
>
>
>
> I am a HUGE SQLite fan and have an interesting question I have been
> scratching my head about for a couple days now.
>
>
>
> First off my daily job is very much around "messaging" and I am very
> familiar with message queue products like Websphere MQ and Fiorano MQ.
>
>
>
> When you install Websphere MQ or Fiorano MQ they have a FILE based queue
> underneath by default (typically one file per queue with the messages etc
> stored at different offsets).
>
>
>
> There messaging systems will have "file writer locks" that in essence will
> only allow ONE WRITER per queue at any one time. So any clients sending
> messages will have "to wait in line" until the message can be physically
> written to file.
>
>
>
> Bottomline: We have one writer and multiple readers per queuefine.
>
>
>
> Then I scratch my head and wonder why SQLite is not the PERFECT
persistence
> layer for building an awesome "file based queue" on? It is lightening fast
> by default, and also has the same "locking issues" described above. We are
> talking milliseconds anywaybetween writes
>
>
>
> Bottomline: Is there any reason why anyone would think someone like me
> coding a solid message queue using SQLite is a bad idea? Am I missing
> something? I personally think it is a wonderful idea and would be hugely
> useful to my daily job
>
>
>
> Any help or advise in this regard would be hugely appreciated ;-)
>
>
>
> Thanks
>
>
>
> Lynton
>
>
>
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Andrew Davison]

I've used SQLite for exactly this. I have no gripes with it at all. I 
ended up using just one table in one DB as indexing makes things simple 
and fast and I support arbitrary queues with multiple readers. WAL i've 
been trying these last few days and seems trouble-free.

On 30/09/2010 6:11 PM, Lynton Grice wrote:
> Hi there,
>
>
>
> I am a HUGE SQLite fan and have an interesting question I have been
> scratching my head about for a couple days now.
>
>
>
> First off my daily job is very much around "messaging" and I am very
> familiar with message queue products like Websphere MQ and Fiorano MQ.
>
>
>
> When you install Websphere MQ or Fiorano MQ they have a FILE based queue
> underneath by default (typically one file per queue with the messages etc
> stored at different offsets).
>
>
>
> There messaging systems will have "file writer locks" that in essence will
> only allow ONE WRITER per queue at any one time. So any clients sending
> messages will have "to wait in line" until the message can be physically
> written to file.
>
>
>
> Bottomline: We have one writer and multiple readers per queuefine.
>
>
>
> Then I scratch my head and wonder why SQLite is not the PERFECT persistence
> layer for building an awesome "file based queue" on? It is lightening fast
> by default, and also has the same "locking issues" described above. We are
> talking milliseconds anywaybetween writes
>
>
>
> Bottomline: Is there any reason why anyone would think someone like me
> coding a solid message queue using SQLite is a bad idea? Am I missing
> something? I personally think it is a wonderful idea and would be hugely
> useful to my daily job
>
>
>
> Any help or advise in this regard would be hugely appreciated ;-)
>
>
>
> Thanks
>
>
>
> Lynton
>
>
>
> ___
> 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 is perfect for FILE based MESSAGE QUEUE?

[Lynton Grice]

Hi Drake,

Thanks for your comments below, I really appreciate your insight on this ;-)

Lynton

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Drake Wilson
Sent: 30 September 2010 12:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

Quoth Lynton Grice <lynton.gr...@logosworld.com>, on 2010-09-30 12:05:18
+0200:
> BTW: What is WAL 

WAL mode uses a write-ahead log instead of a rollback journal, which
can help reduce write activity but requires the use of shared memory
to keep things consistent between database handles.  If I were doing
queues in SQLite I would use WAL mode if possible, based on what I
read at http://sqlite.org/wal.html.

> Also what is the "worst that can happen" with regard
> to:
> 
> "...rollback journal activity multiplying the number of synchronized
writes.
> "

The rollback journal essentially implies that each transaction gets
synced twice: once to keep the old data in the rollback journal, and
once to write the new data.  o/` Double the fsyncs, double your dues;
it's a statement per transaction with SQLite queues. o/`

Problems I can imagine with a plainer ring-buffer-like approach would
be along the lines of unclear semantics on the platform side regarding
which writes are stable with regard to others, such as whether a crash
in the middle of an append is able to corrupt data from beforehand.
ISTR SQLite doing fairly extensive work to overcome these kinds of
limitations in some cases.

> Thanks again
> 
> Lynton

   ---> Drake Wilson
___
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 is perfect for FILE based MESSAGE QUEUE?

[Drake Wilson]

Quoth Lynton Grice , on 2010-09-30 12:05:18 +0200:
> BTW: What is WAL 

WAL mode uses a write-ahead log instead of a rollback journal, which
can help reduce write activity but requires the use of shared memory
to keep things consistent between database handles.  If I were doing
queues in SQLite I would use WAL mode if possible, based on what I
read at http://sqlite.org/wal.html.

> Also what is the "worst that can happen" with regard
> to:
> 
> "...rollback journal activity multiplying the number of synchronized writes.
> "

The rollback journal essentially implies that each transaction gets
synced twice: once to keep the old data in the rollback journal, and
once to write the new data.  o/` Double the fsyncs, double your dues;
it's a statement per transaction with SQLite queues. o/`

Problems I can imagine with a plainer ring-buffer-like approach would
be along the lines of unclear semantics on the platform side regarding
which writes are stable with regard to others, such as whether a crash
in the middle of an append is able to corrupt data from beforehand.
ISTR SQLite doing fairly extensive work to overcome these kinds of
limitations in some cases.

> Thanks again
> 
> Lynton

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

[Lynton Grice]

Hi Drake,

Thanks for your comments, much appreciated.

I will look into your concerns below, thanks. To me it seems like a perfect
match, one could even break it up into "one .db file per message
queue"...very nice and easy to use.

I already have a message queue implementation in Python that uses SQLite but
now I am wanting to re-write the "python API" in pure C. 

BTW: What is WAL mode? Also what is the "worst that can happen" with regard
to:

"...rollback journal activity multiplying the number of synchronized writes.
"

Thanks again

Lynton


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Drake Wilson
Sent: 30 September 2010 11:51 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?

Quoth Lynton Grice <lynton.gr...@logosworld.com>, on 2010-09-30 10:11:54
+0200:
> Bottomline: We have one writer and multiple readers per queuefine.
> 
> Then I scratch my head and wonder why SQLite is not the PERFECT
persistence
> layer for building an awesome "file based queue" on? It is lightening fast
> by default, and also has the same "locking issues" described above. We are
> talking milliseconds anywaybetween writes
>
> Bottomline: Is there any reason why anyone would think someone like me
> coding a solid message queue using SQLite is a bad idea?

I don't think it would be a bad idea per se, but I'm not sure it would
gain you much over other approaches.  It would certainly be simple!

If you're going for high performance, I would be a little wary of
rollback journal activity multiplying the number of synchronized
writes.  WAL mode would help with that, but it would increase the
underlying complexity WRT filesystem and shared memory accesses.

   ---> Drake Wilson
___
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 is perfect for FILE based MESSAGE QUEUE?

[Drake Wilson]

Quoth Lynton Grice , on 2010-09-30 10:11:54 +0200:
> Bottomline: We have one writer and multiple readers per queuefine.
> 
> Then I scratch my head and wonder why SQLite is not the PERFECT persistence
> layer for building an awesome "file based queue" on? It is lightening fast
> by default, and also has the same "locking issues" described above. We are
> talking milliseconds anywaybetween writes
>
> Bottomline: Is there any reason why anyone would think someone like me
> coding a solid message queue using SQLite is a bad idea?

I don't think it would be a bad idea per se, but I'm not sure it would
gain you much over other approaches.  It would certainly be simple!

If you're going for high performance, I would be a little wary of
rollback journal activity multiplying the number of synchronized
writes.  WAL mode would help with that, but it would increase the
underlying complexity WRT filesystem and shared memory accesses.

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users