On Tue, Jun 7, 2011 at 10:03 PM, Eric Sigler <esig...@expensify.com> wrote:
> 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.
>
If you get a copy of the latest 3.7.7 beta code from the website and run it,
and if you set
PRAGMA journal_size_limit=2000000;
That will cause the WAL file size to fall back to 2MB after a successful
checkpoint.
In most applications, the WAL file should never grow beyond 1 or 2
megabytes. Do you have really long-running transactions? Do you have a
read transaction open for a long time together with concurrent write
transactions?
>
> (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
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
