Joe Wilson <[EMAIL PROTECTED]> wrote: > What operations/work patterns will benefit most from > PRAGMA locking_mode = EXCLUSIVE? > > Can you roughly quantify the speedups in such cases? >
When you do "PRAGMA locking_mode=EXCLUSIVE", it means that SQLite will not release its EXCLUSIVE lock after its does its first write until you either close the connection or you do "PRAGMA locking_mode=NORMAL". There are various optimizations that can occur when this is the case. There is a plot of runtime of CVSHEAD versus version 3.3.13 at http://www.sqlite.org/relspeed-20070326-ephemeral.gif (As the name implies, this GIF will ephemeral. Do not expect it to be there for more than a few days.) Any value less than 1.0 means that performance has improved. The test script is http://www.sqlite.org/cvstrac/fileview?f=sqlite/test/speed2.test The orange bars show the relative performance of CVSHEAD with no special build options. 1.0 is the performance of version 3.3.13 so anything less than 1.0 is an improvement. The red bars show CVSHEAD with all the source files concatenated into a single big file and compiled as a single translation unit. Compiling this way allows the compiler to do additional optimizations that result in improved performance. The blue bars are the same sqlite-as-one- great-big-source-file optimization but with -DSQLITE_OMIT_XFER_OPT=1 defined. The tests are cumulative. The database that results from one test feeds into the next. The two "insert" tests at the top both consists of a large number (50000) of distinct INSERT statements. These tests are dominated by the parser and code generator and all of our recent work has been directed toward optimizing the backend so these tests do not show any improvement. You can see that the new "XFER Optimizer" actually results in a slight performance drop since for each INSERT statement, the parser has to check to see if the XFER optimization applies. (It never does in the first two tests.) The interface between the B-Tree layer and the Pager layer has been refactored. This refactoring prevented the compiler from doing certain function inlinings that it was able to do before, resulting in a performance drop for the orange bar. But when the pager and b-tree modules were combined into a single source file, the function inlining was able to occur again and so performance improved. Tests select4 through select6 are about twice as fast. We believe this is because the pager cache is no longer flushed unless the database is changed by another process. VACUUM is much faster due to the XFER optimization. Furthermore, VACUUM with the XFER optimization greatly reduces th database fragmentation. This is (we believe) why performance is so much better in the later tests for the orange and red bars. Operations on a defragmented database file go much faster. Changes have been made to the b-tree and pager layers that omit certain unnecessary writes to the database file and to the rollback journal. This reduction in disk I/O results in the 20x performance improvements seen for tests delete1 and drop1. CVSHEAD passes the "quick" regression tests. But we know there are still issues with the code. CVSHEAD is not currently recommended for use in shipping products, but it is adequate for development work, we believe. In past releases of SQLite, we have made available a ZIP archive with preprocessed source files. In the future, we may change this so that instead of a ZIP archive full of individual files, we ship a single "sqlite3.c" source file which contains all of the source file in a single translation unit. By this mode of delivery, we hope to enable users to see the performance improvements we are seeing in our red bars. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
