We haven't watched the WAL continuously, but we have noticed that the WAL file grows slowly in size over time between application restarts (around every 2 weeks). Currently, the WAL file for one of our DBs is around 40MB, we've seen it grow up to 130MB or so. I'll try to catch the WAL size and see if it changes dramatically.
(Actually, that was another general question we had, should that WAL file ever shrink during use? Why would it grow to that size at all?) -Eric On Tue, Jun 7, 2011 at 6:44 PM, Pavel Ivanov <paiva...@gmail.com> wrote: >> DB file in WAL mode, checkpointing done every 5 seconds by separate >> thread in program > > Depending on the mode of checkpointing you use it can fail if there > are some other reading or writing transactions in progress. And at the > time you observe very long rollback actual checkpointing happens > because no other transactions are active. Did you monitor the size of > WAL file? > > > Pavel > > > On Tue, Jun 7, 2011 at 9:02 PM, Eric Sigler <esig...@expensify.com> wrote: >> Hello! >> >> Does anyone know of a reason why we might be seeing SQLite transaction >> rollbacks that take between 60 and 240 seconds? (One particularly odd >> occurrence was almost 20 minutes long!) This doesn't seem to happen >> often, but when it does it's painful. During the rollback, the disk >> is definitely seeing a large amount of IO activity. >> >> The transactions being rolled back don't appear to be specific to any >> one table (some of the tables have ~200k rows, one table has ~17M >> rows), similarly we've seen transactions rolled back for different >> UPDATE and INSERT operations. (Overall, the workload is for a >> high-ish traffic web application. Lots of reads, far fewer writes). >> >> DB file in WAL mode, checkpointing done every 5 seconds by separate >> thread in program >> SQLite version: 3.7.2 >> DB filesize: approximately 15GB >> Transaction size: sometimes a few KB, up to ~2MB >> OS: Ubuntu Linux 10.04 >> >> Hardware-wise, the SQLite instance is running in a VM with 4GB of RAM, >> 2 virtual CPUs (early 2010 Xeons), IO for this VM runs on a single >> 750GB SATA disk (Barracuda ES.2) with minimal to moderate other IO >> going to it (we'll be separating more of the workload out soon). >> >> Other pragmas that may or may not be relevant: >> count_changes = OFF >> synchronous = OFF >> temp_store = MEMORY >> wal_autocheckpoint = 0 >> cache_size = 3000000 >> >> Any thoughts or ideas? >> >> -Eric >> _______________________________________________ >> 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
