Re: [sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?
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?
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?
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?
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?
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 , 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?
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?
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 , 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?
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
[sqlite] SQLite is perfect for FILE based MESSAGE QUEUE?
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