Re: [sqlite] Using sqlite's WAL with a hash table store
Check out: http://jaksprats.wordpress.com/2010/09/28/introducing-redisql-the-lightning-fast-polyglot/ On Nov 3, 2010, at 10:51 AM, Jay A. Kreibich wrote: > On Wed, Nov 03, 2010 at 05:10:22PM +0300, Alexey Pechnikov scratched > on the wall: >> 2010/11/3 Jay A. Kreibich >>> >>> Why not just use an off-the-self hash cache, like memcached, or an >>> off-the-self hash database, like Redis? Redis even supports an >>> Append-Only write mode (e.g. WAL-ish journal mode). >> >> How about power fail or OS crash? As I know, Redis does not >> garantees the >> data safety. > > This is getting a bit off topic, but quickly... > > No, by default Redis does not provide the level of protection a > default file-backed SQLite database provides. Redis's append-only > mode does a pretty good job, however. Redis will update journal > with each command, and a sync is performed every second. In theory, > in the case of a power or OS crash, maximum data loss is right around > one second worth of transactions. Application crash will not result > in data loss, since the OS still has valid file buffers that will > eventually be flushed. You can also configure things so that the > journal syncs after each command, providing similar protection to > SQLite. That is, as you might expect, somewhat slow, however. > >> And I think SQLite in-memory database is faster. > > Without testing, I'd guess Redis is faster for basic read/write > operations. Redis is also a proper server and allows multiple client > connections, even for a fully memory based data set. > > By default Redis will hold all data in RAM, and is highly optimized > for one-- and only one-- basic operation, while SQLite is supporting > a much richer and more expressive data processing environment. > > Each tool has its place, and they're not really trying to solve > the same problem. > > The whole reason I'm looking to merge the two has to do with SQLite's > expressive environment. In designing a very high-performance app, > the highly-optimized common-case queries can talk to Redis directly. > This is fast, but requires jumping through a lot of hoops in the > application code. Conversely, the less frequent queries (including > many of the management tasks) can talk to SQLite, which can then talk > to Redis. Use of the SQL language makes development MUCH faster for > those operations that are not as performance-critical. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > ___ > 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] Using sqlite's WAL with a hash table store
On Wed, Nov 03, 2010 at 05:10:22PM +0300, Alexey Pechnikov scratched on the wall: > 2010/11/3 Jay A. Kreibich > > > > Why not just use an off-the-self hash cache, like memcached, or an > > off-the-self hash database, like Redis? Redis even supports an > > Append-Only write mode (e.g. WAL-ish journal mode). > > How about power fail or OS crash? As I know, Redis does not garantees the > data safety. This is getting a bit off topic, but quickly... No, by default Redis does not provide the level of protection a default file-backed SQLite database provides. Redis's append-only mode does a pretty good job, however. Redis will update journal with each command, and a sync is performed every second. In theory, in the case of a power or OS crash, maximum data loss is right around one second worth of transactions. Application crash will not result in data loss, since the OS still has valid file buffers that will eventually be flushed. You can also configure things so that the journal syncs after each command, providing similar protection to SQLite. That is, as you might expect, somewhat slow, however. > And I think SQLite in-memory database is faster. Without testing, I'd guess Redis is faster for basic read/write operations. Redis is also a proper server and allows multiple client connections, even for a fully memory based data set. By default Redis will hold all data in RAM, and is highly optimized for one-- and only one-- basic operation, while SQLite is supporting a much richer and more expressive data processing environment. Each tool has its place, and they're not really trying to solve the same problem. The whole reason I'm looking to merge the two has to do with SQLite's expressive environment. In designing a very high-performance app, the highly-optimized common-case queries can talk to Redis directly. This is fast, but requires jumping through a lot of hoops in the application code. Conversely, the less frequent queries (including many of the management tasks) can talk to SQLite, which can then talk to Redis. Use of the SQL language makes development MUCH faster for those operations that are not as performance-critical. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite's WAL with a hash table store
2010/11/3 Jay A. Kreibich > > Why not just use an off-the-self hash cache, like memcached, or an > off-the-self hash database, like Redis? Redis even supports an > Append-Only write mode (e.g. WAL-ish journal mode). > How about power fail or OS crash? As I know, Redis does not garantees the data safety. And I think SQLite in-memory database is faster. I use in-memory SQLite DB and dump (full or incrementally) periodically the DB on disk and restore from disk on startup. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite's WAL with a hash table store
Interesting.. thanks. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent: 03 November 2010 03:44 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Using sqlite's WAL with a hash table store On Wed, Nov 03, 2010 at 12:31:35PM +0200, Ben Harper scratched on the wall: > I guess I could actually dump the hash table into a blob. > I'm also doing something like a bloom filter, and I guess that can just > as well go into a blob too.. Basically the system is a big cache, > and it must quickly answer the question "Do you have this item in > your cache?". The cache is going to receive a lot of queries for > which the answer is "NO", and I need the determination of that > answer to be fast. Why not just use an off-the-self hash cache, like memcached, or an off-the-self hash database, like Redis? Redis even supports an Append-Only write mode (e.g. WAL-ish journal mode). If you really want to access it from inside SQLite, use a virtual table to wrap a Redis database. I've been toying with this idea for a personal project. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ 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] Using sqlite's WAL with a hash table store
On Wed, Nov 03, 2010 at 12:31:35PM +0200, Ben Harper scratched on the wall: > I guess I could actually dump the hash table into a blob. > I'm also doing something like a bloom filter, and I guess that can just > as well go into a blob too.. Basically the system is a big cache, > and it must quickly answer the question "Do you have this item in > your cache?". The cache is going to receive a lot of queries for > which the answer is "NO", and I need the determination of that > answer to be fast. Why not just use an off-the-self hash cache, like memcached, or an off-the-self hash database, like Redis? Redis even supports an Append-Only write mode (e.g. WAL-ish journal mode). If you really want to access it from inside SQLite, use a virtual table to wrap a Redis database. I've been toying with this idea for a personal project. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite's WAL with a hash table store
Thanks - I didn't think of using that. Maybe it's a good fit. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Alexey Pechnikov Sent: 03 November 2010 11:51 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Using sqlite's WAL with a hash table store FTS3 extension is very fast and scalable hash engine. I did test FTS3 up to 400+ millions of record and it's nice. 2010/11/3 Ben Harper > Hi, > I know the answer to this question is really "Just try it and see", but I > want to gauge whether the idea is sane or not before I spend/waste time on > the effort: > > I want to build a custom hash table DB, and to solve the > concurrency+durability I need something akin to a WAL, and SQLite's WAL > seems like a perfect fit. I've looked into the wal.c/wal.h a bit and from my > brief perusal it looks like I could quite easily strap the SQLite WAL onto > my custom hash table DB. > > Does this sound like a reasonable thing to do? > Am I going to have to do a lot of work to spoof the WAL logic, or is it > made to run pretty much ignorant of the file that it is WAL'ing against? > > Thanks, > Ben > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ 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] Using sqlite's WAL with a hash table store
I guess I could actually dump the hash table into a blob. I'm also doing something like a bloom filter, and I guess that can just as well go into a blob too.. Basically the system is a big cache, and it must quickly answer the question "Do you have this item in your cache?". The cache is going to receive a lot of queries for which the answer is "NO", and I need the determination of that answer to be fast. That's why I've got a bloom filter-ish thing going which performs that task. Even for a large cache (500k entries), one can keep the entire filter in about 2MB of memory and have less than 1% false positive rate. Unfortunately I can't think of an equivalent data structure that would use B+Tree linear indices to achieve that. I've done a naive implementation of what I want using straight sqlite, but the performance is not really adequate. It's hard to quantify exactly where the bottlenecks lie though, which is what motivates me to try something else and see what kind of performance delta I get. Thanks. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: 03 November 2010 11:17 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Using sqlite's WAL with a hash table store On 3 Nov 2010, at 8:30am, Ben Harper wrote: > I know the answer to this question is really "Just try it and see", but I > want to gauge whether the idea is sane or not before I spend/waste time on > the effort: > > I want to build a custom hash table DB, and to solve the > concurrency+durability I need something akin to a WAL, and SQLite's WAL seems > like a perfect fit. I've looked into the wal.c/wal.h a bit and from my brief > perusal it looks like I could quite easily strap the SQLite WAL onto my > custom hash table DB. Modifying SQL, and taking SQL source code and putting it into your own project, are difficult and time-consuming. As a prototype why not /use/ SQL, storing your hash codes in a column ? Use that as a prototype and see if it's fast enough. If it is, stop there. If you find calculating your hashes externally proves too clunky, you could write a custom function to calculate your hash codes http://www.sqlite.org/c3ref/create_function.html , or you could remove the extra column but implement your hash codes as a collating sequence: http://www.sqlite.org/c3ref/create_collation.html Any of the three above ways to do it gets you all the advantages of the WAL code /and/ a SQL engine. 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
Re: [sqlite] Using sqlite's WAL with a hash table store
FTS3 extension is very fast and scalable hash engine. I did test FTS3 up to 400+ millions of record and it's nice. 2010/11/3 Ben Harper > Hi, > I know the answer to this question is really "Just try it and see", but I > want to gauge whether the idea is sane or not before I spend/waste time on > the effort: > > I want to build a custom hash table DB, and to solve the > concurrency+durability I need something akin to a WAL, and SQLite's WAL > seems like a perfect fit. I've looked into the wal.c/wal.h a bit and from my > brief perusal it looks like I could quite easily strap the SQLite WAL onto > my custom hash table DB. > > Does this sound like a reasonable thing to do? > Am I going to have to do a lot of work to spoof the WAL logic, or is it > made to run pretty much ignorant of the file that it is WAL'ing against? > > Thanks, > Ben > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite's WAL with a hash table store
On 3 Nov 2010, at 8:30am, Ben Harper wrote: > I know the answer to this question is really "Just try it and see", but I > want to gauge whether the idea is sane or not before I spend/waste time on > the effort: > > I want to build a custom hash table DB, and to solve the > concurrency+durability I need something akin to a WAL, and SQLite's WAL seems > like a perfect fit. I've looked into the wal.c/wal.h a bit and from my brief > perusal it looks like I could quite easily strap the SQLite WAL onto my > custom hash table DB. Modifying SQL, and taking SQL source code and putting it into your own project, are difficult and time-consuming. As a prototype why not /use/ SQL, storing your hash codes in a column ? Use that as a prototype and see if it's fast enough. If it is, stop there. If you find calculating your hashes externally proves too clunky, you could write a custom function to calculate your hash codes http://www.sqlite.org/c3ref/create_function.html , or you could remove the extra column but implement your hash codes as a collating sequence: http://www.sqlite.org/c3ref/create_collation.html Any of the three above ways to do it gets you all the advantages of the WAL code /and/ a SQL engine. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
