> boun...@sqlite.org] On Behalf Of Richard Hipp > (2) WAL really does give better concurrency since it allows other processes > to continue reading while a single process is writing to the database. In > rollback mode you can have one writer *or* multiple readers. In WAL mode > you can have one writer *and* multiple readers. On a busy system, or in a > system with long-running transactions, the difference can be significant. > > (3) Transactions commit a lot faster with WAL. Depending on your app, you > might see a measurable performance improvement just by switching to WAL. > > (4) WAL mode is much more resistant to corruption following a power failure > that occurs soon after the disk drive lied and said that content was > committed to oxide when in fact it was still in a volatile track buffer. > And pretty much all consumer-grade disk drives tell such lies these days.
I just recently started to use WAL and had the following experience with above: I work with large databases (e.g. 40 Gb) and when loading data to these, I will usually get my program to make copy of the database through the OS and then during heavy data loads run the database with: PRAGMA journal_mode = OFF; PRAGMA locking_mode = EXCLUSIVE; PRAGMA synchronous = 0; PRAGMA cache_size = 400000; This increases the load spead tremendously. However, as the database increases the time spend copying the database increases with it, although it is still much faster than using the default journal_mode (DELETE). Also, the database is locked for even read access during the loads. Reading about WAL (http://sqlite.org/wal.html) I decided to try it out during data loads with: PRAGMA journal_mode = WAL; PRAGMA locking_mode = NORMAL; PRAGMA synchronous = 1; PRAGMA cache_size = 400000; (Thus using the same size cache and now just the default settings for locking_mode and synchronous). The result was that the data loads were running at virtually the same speed as when journal_mode was set to OFF! Also, the database can now be opened for reading by other processes and I no longer have the hassle of waiting for OS copies to be done for every load, so having had only positive results and no adverse effects I can really recommend the write ahead logging. Best regards, Frank _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
