> 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
