Controlling of check-pointing of WAL Dbs

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Controlling of check-pointing of WAL Dbs

Albert Banaszkiewicz
Hello.

I am new here, thus I would like to apologize in advance if the question I am going to rise was already asked in the past.

I am using SQLite 3.11.1 (actually it is a NDS consortium customized version based on it).

According to documentation, in case of WAL mode enabled databases, final check-pointing is being done when the last connection to DB is about to be closed.

We are running into the scenario, where we would like to have a complete control over the moment when check-pointing happens. This is easy in case of auto-check-pointing, where it can be completely disabled or customized (via hooks).
However, it is possible that during the component life-time there is going to be no 'appropriate' moment to schedule it and if the user powers device down, we still want to avoid it since modern OSes typically expect running applications to terminate within the specified (several seconds usually) time intervals. Of course, we don't want to loose any data contained in the WAL journal as well and ideally, we would like to be still able to schedule check-pointing in the 'appropriate' time after reboot.

So finally, the questions:

1. Is there a way to control (i.e. disable) check-pointing happening at DB closure ? (personally, I could not find anything like this in the code but perhaps I missed something)
2. If not, are there any plans to introduce it ? Or is it not feasible at all ?

Thank you in advance,
Albert
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Controlling of check-pointing of WAL Dbs

Clemens Ladisch
Albert Banaszkiewicz wrote:
> 1. Is there a way to control (i.e. disable) check-pointing happening at DB closure ?

The internal sqlite3WalClose() function does a checkpoint if it is able
to get an exclusive lock (which implies that there are no other readers/
writers).  So to prevent this checkpoint, you would have to keep some
other connection with an active transaction (read-only suffices) while
you're closing the first connection.  And then never close that other
connection ...


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Controlling of check-pointing of WAL Dbs

Paul Sanderson
In reply to this post by Albert Banaszkiewicz
Can you kill the process rather than shut down your application? If
the last connection doesn't get shut then the WAL will remain and will
be in opened (but not played) when the DB is re-opened.
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 27 April 2016 at 12:38, Albert Banaszkiewicz
<[hidden email]> wrote:

> Hello.
>
> I am new here, thus I would like to apologize in advance if the question I am going to rise was already asked in the past.
>
> I am using SQLite 3.11.1 (actually it is a NDS consortium customized version based on it).
>
> According to documentation, in case of WAL mode enabled databases, final check-pointing is being done when the last connection to DB is about to be closed.
>
> We are running into the scenario, where we would like to have a complete control over the moment when check-pointing happens. This is easy in case of auto-check-pointing, where it can be completely disabled or customized (via hooks).
> However, it is possible that during the component life-time there is going to be no 'appropriate' moment to schedule it and if the user powers device down, we still want to avoid it since modern OSes typically expect running applications to terminate within the specified (several seconds usually) time intervals. Of course, we don't want to loose any data contained in the WAL journal as well and ideally, we would like to be still able to schedule check-pointing in the 'appropriate' time after reboot.
>
> So finally, the questions:
>
> 1. Is there a way to control (i.e. disable) check-pointing happening at DB closure ? (personally, I could not find anything like this in the code but perhaps I missed something)
> 2. If not, are there any plans to introduce it ? Or is it not feasible at all ?
>
> Thank you in advance,
> Albert
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

ODP: Controlling of check-pointing of WAL Dbs

Albert Banaszkiewicz
In reply to this post by Clemens Ladisch

Hi Clemens,

Indeed, that would do the trick, however, during the device shutdown there is no way to keep the connection open. And that is really an inconvenient time to perform the operation due its duration.

Cheers,
Albert

________________________________________
Od: [hidden email] <[hidden email]> w imieniu użytkownika Clemens Ladisch <[hidden email]>
Wysłane: 27 kwietnia 2016 13:51
Do: [hidden email]
Temat: Re: [sqlite] Controlling of check-pointing of WAL Dbs

Albert Banaszkiewicz wrote:
> 1. Is there a way to control (i.e. disable) check-pointing happening at DB closure ?

The internal sqlite3WalClose() function does a checkpoint if it is able
to get an exclusive lock (which implies that there are no other readers/
writers).  So to prevent this checkpoint, you would have to keep some
other connection with an active transaction (read-only suffices) while
you're closing the first connection.  And then never close that other
connection ...


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

ODP: Controlling of check-pointing of WAL Dbs

Albert Banaszkiewicz
In reply to this post by Paul Sanderson
Hi Paul,

Interesting idea :) A bit hacky but that's something to be considered perhaps.
However, killing the application usually implies action being taken from outside of the app. I don't believe such things are possible on i.e. iOS (one of the platform supported by our app), though I might be wrong.

Cheers,
Albert  
________________________________________
Od: [hidden email] <[hidden email]> w imieniu użytkownika Paul Sanderson <[hidden email]>
Wysłane: 27 kwietnia 2016 13:54
Do: SQLite mailing list
Temat: Re: [sqlite] Controlling of check-pointing of WAL Dbs

Can you kill the process rather than shut down your application? If
the last connection doesn't get shut then the WAL will remain and will
be in opened (but not played) when the DB is re-opened.
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 27 April 2016 at 12:38, Albert Banaszkiewicz
<[hidden email]> wrote:

> Hello.
>
> I am new here, thus I would like to apologize in advance if the question I am going to rise was already asked in the past.
>
> I am using SQLite 3.11.1 (actually it is a NDS consortium customized version based on it).
>
> According to documentation, in case of WAL mode enabled databases, final check-pointing is being done when the last connection to DB is about to be closed.
>
> We are running into the scenario, where we would like to have a complete control over the moment when check-pointing happens. This is easy in case of auto-check-pointing, where it can be completely disabled or customized (via hooks).
> However, it is possible that during the component life-time there is going to be no 'appropriate' moment to schedule it and if the user powers device down, we still want to avoid it since modern OSes typically expect running applications to terminate within the specified (several seconds usually) time intervals. Of course, we don't want to loose any data contained in the WAL journal as well and ideally, we would like to be still able to schedule check-pointing in the 'appropriate' time after reboot.
>
> So finally, the questions:
>
> 1. Is there a way to control (i.e. disable) check-pointing happening at DB closure ? (personally, I could not find anything like this in the code but perhaps I missed something)
> 2. If not, are there any plans to introduce it ? Or is it not feasible at all ?
>
> Thank you in advance,
> Albert
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Controlling of check-pointing of WAL Dbs

Richard Hipp-3
In reply to this post by Albert Banaszkiewicz
On 4/27/16, Albert Banaszkiewicz <[hidden email]> wrote:

> Hello.
>
> I am new here, thus I would like to apologize in advance if the question I
> am going to rise was already asked in the past.
>
> I am using SQLite 3.11.1 (actually it is a NDS consortium customized version
> based on it).
>
> According to documentation, in case of WAL mode enabled databases, final
> check-pointing is being done when the last connection to DB is about to be
> closed.
>
> We are running into the scenario, where we would like to have a complete
> control over the moment when check-pointing happens. This is easy in case of
> auto-check-pointing, where it can be completely disabled or customized (via
> hooks).
> However, it is possible that during the component life-time there is going
> to be no 'appropriate' moment to schedule it and if the user powers device
> down, we still want to avoid it since modern OSes typically expect running
> applications to terminate within the specified (several seconds usually)
> time intervals. Of course, we don't want to loose any data contained in the
> WAL journal as well and ideally, we would like to be still able to schedule
> check-pointing in the 'appropriate' time after reboot.
>
> So finally, the questions:
>
> 1. Is there a way to control (i.e. disable) check-pointing happening at DB
> closure ? (personally, I could not find anything like this in the code but
> perhaps I missed something)
> 2. If not, are there any plans to introduce it ? Or is it not feasible at
> all ?

There is no documented and supported way of preventing a checkpoint
when the last DB connection closes.  But you can work around that
simply by killing the process without ever calling sqlite3_close().

When you do that, it leaves the -wal and -shm files on disk.  The next
time any process connects to the database, it will read the entire
-wal file from beginning to end in order to reconstruct the -shm file.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

ODP: Controlling of check-pointing of WAL Dbs

Albert Banaszkiewicz

Good morning Richard,

Thank you for the tip.
I assume (I will test it later), that by killing the process you mean simply shutting it down (regularly) without ever calling sqlite3_close, am I right ?
If so, that is quite feasible in our case I think.

Regards,
Albert

________________________________________
Od: [hidden email] <[hidden email]> w imieniu użytkownika Richard Hipp <[hidden email]>
Wysłane: 27 kwietnia 2016 15:23
Do: SQLite mailing list
Temat: Re: [sqlite] Controlling of check-pointing of WAL Dbs

On 4/27/16, Albert Banaszkiewicz <[hidden email]> wrote:

> Hello.
>
> I am new here, thus I would like to apologize in advance if the question I
> am going to rise was already asked in the past.
>
> I am using SQLite 3.11.1 (actually it is a NDS consortium customized version
> based on it).
>
> According to documentation, in case of WAL mode enabled databases, final
> check-pointing is being done when the last connection to DB is about to be
> closed.
>
> We are running into the scenario, where we would like to have a complete
> control over the moment when check-pointing happens. This is easy in case of
> auto-check-pointing, where it can be completely disabled or customized (via
> hooks).
> However, it is possible that during the component life-time there is going
> to be no 'appropriate' moment to schedule it and if the user powers device
> down, we still want to avoid it since modern OSes typically expect running
> applications to terminate within the specified (several seconds usually)
> time intervals. Of course, we don't want to loose any data contained in the
> WAL journal as well and ideally, we would like to be still able to schedule
> check-pointing in the 'appropriate' time after reboot.
>
> So finally, the questions:
>
> 1. Is there a way to control (i.e. disable) check-pointing happening at DB
> closure ? (personally, I could not find anything like this in the code but
> perhaps I missed something)
> 2. If not, are there any plans to introduce it ? Or is it not feasible at
> all ?

There is no documented and supported way of preventing a checkpoint
when the last DB connection closes.  But you can work around that
simply by killing the process without ever calling sqlite3_close().

When you do that, it leaves the -wal and -shm files on disk.  The next
time any process connects to the database, it will read the entire
-wal file from beginning to end in order to reconstruct the -shm file.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ODP: Controlling of check-pointing of WAL Dbs

Richard Hipp-3
On 4/28/16, Albert Banaszkiewicz <[hidden email]> wrote:
> I assume (I will test it later), that by killing the process you mean simply
> shutting it down (regularly) without ever calling sqlite3_close, am I right
> ?

Yes
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

ODP: ODP: Controlling of check-pointing of WAL Dbs

Albert Banaszkiewicz

Thank you for all your help!

Cheers,
Albert

________________________________________
Od: [hidden email] <[hidden email]> w imieniu użytkownika Richard Hipp <[hidden email]>
Wysłane: 28 kwietnia 2016 15:16
Do: SQLite mailing list
Temat: Re: [sqlite] ODP: Controlling of check-pointing of WAL Dbs

On 4/28/16, Albert Banaszkiewicz <[hidden email]> wrote:
> I assume (I will test it later), that by killing the process you mean simply
> shutting it down (regularly) without ever calling sqlite3_close, am I right
> ?

Yes
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users