Re: [sqlite] Slow rollback - possible causes?

[Richard Hipp]

On Tue, Jun 7, 2011 at 10:03 PM, Eric Sigler  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=200;

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  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 
> 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 = 300
> >>
> >> 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

Re: [sqlite] Slow rollback - possible causes?

[Richard Hipp]

On Tue, Jun 7, 2011 at 9:02 PM, Eric Sigler  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 = 300
>
> 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

Re: [sqlite] Slow rollback - possible causes?

[Pavel Ivanov]

> We're issuing "PRAGMA
> wal_checkpoint" to the open DB handle.

If you want guaranteed finish of the checkpoint (and thus not growing
WAL-file) you need to issue "PRAGMA wal_checkpoint(RESTART)".


Pavel


On Tue, Jun 7, 2011 at 10:36 PM, Eric Sigler  wrote:
> So, should the WAL file shrink back to 0 then?  We're issuing "PRAGMA
> wal_checkpoint" to the open DB handle.
>
> -Eric
>
> On Tue, Jun 7, 2011 at 7:06 PM, Pavel Ivanov  wrote:
>>> (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?)
>>
>> It shrinks, when the full checkpoint is completed successfully. Until
>> then it grows.
>>
>>
>> Pavel
> ___
> 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

Re: [sqlite] Slow rollback - possible causes?

[Eric Sigler]

So, should the WAL file shrink back to 0 then?  We're issuing "PRAGMA
wal_checkpoint" to the open DB handle.

-Eric

On Tue, Jun 7, 2011 at 7:06 PM, Pavel Ivanov  wrote:
>> (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?)
>
> It shrinks, when the full checkpoint is completed successfully. Until
> then it grows.
>
>
> Pavel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow rollback - possible causes?

[Pavel Ivanov]

> (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?)

It shrinks, when the full checkpoint is completed successfully. Until
then it grows.


Pavel


On Tue, Jun 7, 2011 at 10:03 PM, Eric Sigler  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.
>
> (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  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  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 = 300
>>>
>>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow rollback - possible causes?

[Eric Sigler]

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  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  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 = 300
>>
>> 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

Re: [sqlite] Slow rollback - possible causes?

[Pavel Ivanov]

> 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  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 = 300
>
> 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

Re: [sqlite] Slow rollback - possible causes?

[Simon Slavin]

On 8 Jun 2011, at 2:02am, Eric Sigler wrote:

> Does anyone know of a reason why we might be seeing SQLite transaction
> rollbacks that take between 60 and 240 seconds?

My initial thought was a faulty hard disk: bad sectors or a duff controller.  
Given that you're running inside a VM, it might also be faulty RAM.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Slow rollback - possible causes?

[Eric Sigler]

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 = 300

Any thoughts or ideas?

-Eric
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users