Re: [sqlite] Checkpoint time

[Richard Hipp]

On Fri, Dec 2, 2011 at 2:15 AM, Sreekumar TP  wrote:

> There is another reader but sharing the same connection. So checkpoint
> process cannot be blocked by another reader.
>

The other reader doesn't have to be active - it merely needs to be holding
open a read transaction.  Perhaps you have failed to run sqlite3_reset() or
sqlite3_finalize() on one or more of the statements from the reader.

If you run "PRAGMA wal_checkpoint(RESTART)" then the SQLite connection
running that pragma will block until all readers and writers clear and the
checkpoint can run to completion - to the point of resetting the WAL file.
If that pragma then blocks forever, then you know you have a stuck
transaction somewhere.




>
>
> On Thu, Dec 1, 2011 at 8:54 PM, Yves Goergen  >wrote:
>
> >  On 01.12.2011 18:58 CE(S)T, Sreekumar TP wrote:
> > > During the measurement of manual checkpoint times, I see the following
> > > behaviour -
> > >
> > > # of WAL frames checkpointed is over 1000
> > > size of DB has not changed after checkpoint.(measured using fstat)
> > > Time for the checkpoint if around 500 ms.
> > >
> > > Why is it that eventhough 1000+ frames are checkpointed, the DB size
> has
> > > not increased ?
> > > All records inserted are new and unique records and insertions start
> from
> > > empty database.
> >
> > Could it be that the pages were not actually written to the database
> > because there was a reader lock on them? Did any other process access
> > the database (and thus hold any locks) while you did the checkpointing?
> >
> > (Wild guess. I've only learned about WAL and started using it yesterday.
> > I've read the whole WAL documentation page [1] though.)
> >
> > [1] http://sqlite.org/wal.html
> >
> > --
> > Yves Goergen "LonelyPixel" 
> > Visit my web laboratory at http://beta.unclassified.de
> > ___
> > 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] Checkpoint time

[Sreekumar TP]

There is another reader but sharing the same connection. So checkpoint
process cannot be blocked by another reader.


On Thu, Dec 1, 2011 at 8:54 PM, Yves Goergen wrote:

>  On 01.12.2011 18:58 CE(S)T, Sreekumar TP wrote:
> > During the measurement of manual checkpoint times, I see the following
> > behaviour -
> >
> > # of WAL frames checkpointed is over 1000
> > size of DB has not changed after checkpoint.(measured using fstat)
> > Time for the checkpoint if around 500 ms.
> >
> > Why is it that eventhough 1000+ frames are checkpointed, the DB size has
> > not increased ?
> > All records inserted are new and unique records and insertions start from
> > empty database.
>
> Could it be that the pages were not actually written to the database
> because there was a reader lock on them? Did any other process access
> the database (and thus hold any locks) while you did the checkpointing?
>
> (Wild guess. I've only learned about WAL and started using it yesterday.
> I've read the whole WAL documentation page [1] though.)
>
> [1] http://sqlite.org/wal.html
>
> --
> Yves Goergen "LonelyPixel" 
> Visit my web laboratory at http://beta.unclassified.de
> ___
> 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] Checkpoint time

[Yves Goergen]

On 01.12.2011 18:58 CE(S)T, Sreekumar TP wrote:
> During the measurement of manual checkpoint times, I see the following
> behaviour -
> 
> # of WAL frames checkpointed is over 1000
> size of DB has not changed after checkpoint.(measured using fstat)
> Time for the checkpoint if around 500 ms.
> 
> Why is it that eventhough 1000+ frames are checkpointed, the DB size has
> not increased ?
> All records inserted are new and unique records and insertions start from
> empty database.

Could it be that the pages were not actually written to the database
because there was a reader lock on them? Did any other process access
the database (and thus hold any locks) while you did the checkpointing?

(Wild guess. I've only learned about WAL and started using it yesterday.
I've read the whole WAL documentation page [1] though.)

[1] http://sqlite.org/wal.html

-- 
Yves Goergen "LonelyPixel" 
Visit my web laboratory at http://beta.unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Checkpoint time

[Sreekumar TP]

Hello,

During the measurement of manual checkpoint times, I see the following
behaviour -

# of WAL frames checkpointed is over 1000
size of DB has not changed after checkpoint.(measured using fstat)
Time for the checkpoint if around 500 ms.

Why is it that eventhough 1000+ frames are checkpointed, the DB size has
not increased ?
All records inserted are new and unique records and insertions start from
empty database.

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