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. > There should be little to no disk I/O during a ROLLBACK in WAL mode. WAL is a "write-ahead log". That means that to rollback a change, you just ignore the parts of the log that comprise the transaction that is being rolled back. Hence ROLLBACK in WAL should be instantaneous. > > 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 > I'm thinking you are checkpointing way to often.... How many transactions do you do per second? > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
