howto shrink files in wal mode

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

howto shrink files in wal mode

rene
Hi,

After running the following script in sqlite 3.8.7.1:

set dbfile {c:/temp/db.sqlite3}
file delete -force $dbfile
sqlite3 ::db $dbfile
::db eval {
  PRAGMA journal_mode = WAL;
  create table test2 (id, txt)
}
for {set i 0} {$i < 10000} {incr i} {
  set t txt$i
  ::db eval {insert into test2 values($i,$t)}
}
::db eval {PRAGMA wal_checkpoint(FULL)}
puts a=[file size $dbfile]
::db eval {
  delete from test2;
  PRAGMA wal_checkpoint(FULL);
  vacuum;
}
puts b=[file size $dbfile]
::db eval {vacuum}
puts c=[file size $dbfile]
::db close
puts d=[file size $dbfile]


I got as result:

a=177152
b=177152
c=177152
d=2048

Is there a way to shrink the file when it is still open in WAL mode?


Thank you

René


Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
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: howto shrink files in wal mode

Richard Hipp-3
On 5/5/15, Zaumseil René <[hidden email]> wrote:
>
> Is there a way to shrink the file when it is still open in WAL mode?
>

Run "PRAGMA wal_checkpoint=TRUNCATE;" in SQLite 3.8.8.2 or later.
--
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
|

Re: howto shrink files in wal mode

rene
In reply to this post by rene
> On 5/5/15, Zaumseil Ren? <[hidden email]<mailto:[hidden email]>> wrote:

> >

> > Is there a way to shrink the file when it is still open in WAL mode?

> >

>

> Run "PRAGMA wal_checkpoint=TRUNCATE;" in SQLite 3.8.8.2 or later.

> --

> D. Richard Hipp

> [hidden email]<mailto:[hidden email]>

Changing all wal_checkpoint(FULL) to wal_checkpoint(TRUNCATE) and using qlite3 version 3.8.8.3 still gives the same result:


a=177152

b=177152

c=177152

d=2048

To clarify, I need to shrink the dbfile, not the *wal file.

Any help appreciated.


René



Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
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: howto shrink files in wal mode

Simon Slavin-3

On 7 May 2015, at 9:53am, Zaumseil René <[hidden email]> wrote:

> To clarify, I need to shrink the dbfile, not the *wal file.

The only thing that will do that under your conditions is VACUUM.

Simon.
_______________________________________________
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: howto shrink files in wal mode

rene
In reply to this post by rene
> > To clarify, I need to shrink the dbfile, not the *wal file.
> The only thing that will do that under your conditions is VACUUM.
>
> Simon.

I have done "vacuum" right before the b= output.

But the dbfile will only shrink "after" I have closed the db.
When I try the same in "DELETE" mode everything is fine.
But I still need the WAL mode for performance reasons.


Rene


Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
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: howto shrink files in wal mode

Simon Slavin-3

On 7 May 2015, at 10:12am, Zaumseil René <[hidden email]> wrote:

> I have done "vacuum" right before the b= output.
>
> But the dbfile will only shrink "after" I have closed the db.

How are you measuring the size of the database file ?

Simon.
_______________________________________________
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: howto shrink files in wal mode

rene
In reply to this post by rene

>How are you measuring the size of the database file ?
>
>Simon.
Using the tcl "file" command.

puts b=[file size $dbfile]

Please see in the script in the original post.


Rene


Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
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: howto shrink files in wal mode

Simon Slavin-3

> On 7 May 2015, at 10:46am, Zaumseil René <[hidden email]> wrote:
>
>> How are you measuring the size of the database file ?
>
> Using the tcl "file" command.
>
> puts b=[file size $dbfile]

I think tcl gets its file size from the operating system and the operating system doesn't update the size of flies with open handles.  But you are going to need someone who knows tcl to check that.

> Please see in the script in the original post.

I'm sorry.  You did show it and I forgot it was in that post.

Simon.
_______________________________________________
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: howto shrink files in wal mode

Dan Kennedy-4
In reply to this post by rene
On 05/07/2015 04:46 PM, Zaumseil René wrote:
>> How are you measuring the size of the database file ?
>>
>> Simon.
> Using the tcl "file" command.
>
> puts b=[file size $dbfile]
>
> Please see in the script in the original post.

If you do the VACUUM before the "PRAGMA wal_checkpoint" instead of
after, does that help?

Dan.

_______________________________________________
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: howto shrink files in wal mode

rene
In reply to this post by rene

> I think tcl gets its file size from the operating system and the operating system doesn't update the size of flies with open
> handles.  But you are going to need someone who knows tcl to check that.

I think that would be me :)

> > Please see in the script in the original post.
>
> I'm sorry.  You did show it and I forgot it was in that post.

No problem so far.

But my problem remains :( If I do the script step by step I also do not see the file shrink in the file explorer.
The dbfile will only shrink "after" I close the db connection. Even with an explicit "vacuum" command.

So the question remain: How to shrink an used dbfile in wal mode?

So far the only working solution I found is to disconnect and connect again.


Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
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: howto shrink files in wal mode

Simon Slavin-3

On 7 May 2015, at 11:25am, Zaumseil René <[hidden email]> wrote:

> But my problem remains :( If I do the script step by step I also do not see the file shrink in the file explorer.
> The dbfile will only shrink "after" I close the db connection. Even with an explicit "vacuum" command.
>
> So the question remain: How to shrink an used dbfile in wal mode?

I think the file actually has shrunk.  I just think that the size reported is not updated while the file is open.  This is definitely true in File Explorer and may be true for tcl too.  This is a choice made by your operating system and nothing can be done inside SQLite to override it.

Simon.
_______________________________________________
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: howto shrink files in wal mode

rene
In reply to this post by rene

>If you do the VACUUM before the "PRAGMA wal_checkpoint" instead of after, does that help?
>
>Dan.

You won the jackpot! Thank you for the tip.
May be this should be mentioned in the official documentation.

>I think the file actually has shrunk.  I just think that the size reported is not updated while the file is open.  This is definitely
>true in File Explorer and may be true for tcl too.  This is a choice made by your operating system and nothing can be done
>inside SQLite to override it.
>
>Simon.

No, because if I copy the still open file it has the same size as reported.


Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users