Re: [sqlite] Any way to disable journaling & rollback?
Thank you for pointing out the omitJournal argument to sqlite3BtreeFactory! That's just what I was looking for. By forcing omitJournal to 1 in all cases (thereby always turning off journaling), I was able to increase the total speed of my application by a factor of 2.5 over the speed I got by just removing the call to FlushFileBuffers (which by itself provided a speed-up factor of 20). So by removing journaling, my application runs 50 times as fast as it does with journaling enabled! I don't know how much write performance matters to others, but increasing the speed of an application by a factor of 50 is worth looking into. Aladdin Lamp wrote: In order to disable totally journaling, I think (to be confirmed by real sqlite experts though) that you could patch each 4 calls to the function: int sqlite3BtreeFactory(const sqlite3 *db, const char *zFilename, int omitJournal, int nCache, int flags, Btree **ppBtree); using always 1 as the third parameter (omitJournal) instead of 0 in the following files: - attach.c (line 136) - build.c (line 3212) - main.c (line 1025) (- and in vdbe.c (line 2611) but omitJournal argument already equals 1 in this file) Keep us informed if this works. Aladdin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any way to disable journaling & rollback?
Holding commits with a timeout is a feasible solution. However, in my application it is somewhat complex to implement. Multiple threads are accessing the database, and read requests usually run in a different thread than writes. I don't want reads to be blocked while a commit timeout waits, so a read would have to force a commit. I don't think one thread can commit transactions for another thread, so I would have to set up an inter-thread queueing system to allow readers to notify writers in different threads that commits need to be done. Since commits are done in multiple places, this approach will get messy fast. I think removing the FlushFileBuffers call (which is normally done on every commit) is the best solution. It is not as fast as totally turning off journaling, but it allows my program to run 20 times faster without the complexity of trying to hold commitments. Also, journaling _is_ being done (just not forced to the disk). So if my application crashes but Windows continues to run, the journaling will eventually get flushed from Windows cache to disk, and it should be available for a rollback. If I can figure out how to totally turn off journaling, I will do some timing tests to see how much that speeds things up. It certainly won't be a factor of 20, but a factor of 2 is possible. A new pragma "journaling=[off|on]" would be nice. Key wrote: Removing the journalling will certainly cause you lots of grief in the event of a "crash"... You could do the following, The write code (inserts) will queue incoming data into an "array/storage in memory etc..." When the first row is captured set a timer. When either the timer expires or you reach a row limit threshold, write the data to sqlite in a batched transaction. This way you get good performance and reliablity! But if you code crashes and you don't keep persistent what was in memory you'll loose that data. You'll probably need to do some tuning of the timer/row limits to get a balance between performance and potential data loss. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any way to disable journaling & rollback?
> Do you know that the performance without doing anything special is unacceptable? If I do insertions of a test set of 2000 records using a BEGIN TRANSACTION/COMMIT around each one, the speed is 20 times slower than doing additions in a single transaction. I hacked the winSync routine and removed the call to FlushFileBuffers which forces Windows to write data from its cache to disk; this immediately sped up the run by a factor of 20 without making any other changes. In other words, removing the call of FlushFileBuffers for each transaction made my application run 20 times faster. A speed factor of 20 is significant. One solution is to batch up many insertions within the same transaction. The problem is that in my application insertions come in sporadically along with read accesses to the data that must be serviced quickly. So it is tricky to try to batch up insertions within transactions when I don't know how long it will be before another insertion arrives, and I must deal with a continuous flow of read accesses interleaved with the insertions. While transactions and rollback are a wonderful facility, I would like to see some option to turn them off when performance is the primary consideration. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Any way to disable journaling & rollback?
I have an application that requires creating a database with about 50 million records. Is there any way to turn off journaling and rollback during the creation? I am willing to sacrifice reliability for speed in this situation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Direct access to Btree routines in SQLite
); status = sqlite3BtreeData(BtCursor,0,DataSize,buf); ASSERT(strcmp(buf,Data1) == 0); /* Check if we can read the second record */ status = sqlite3BtreeMoveto(BtCursor,Key2,1,0,); status = sqlite3BtreeDataSize(BtCursor,); status = sqlite3BtreeData(BtCursor,0,DataSize,buf); ASSERT(strcmp(buf,Data2) == 0); /* Close the cursor */ status = sqlite3BtreeCloseCursor(BtCursor); /* * Close the database. */ status = sqlite3_close(db); /* * Finished */ return; } I would like to see the Btree routines documented and supported. Phil Sherrod ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
