Re: [sqlite] WAL pragma question

[Dan Kennedy]

On 10/28/2016 09:53 PM, David Raymond wrote:

So my new understanding: This happens at the end of the write to the WAL file, 
but before the actual checkpoint. And then any checkpoint just works normally. 
So basically, even with journal_size_limit = 0, the WAL will always be at least 
as large as the last write, even if checkpointed successfully and completely.


Right. The truncation is the last step in a database write, not part of 
a checkpoint operation.



-So I insert 10 GB of data, the WAL grows to 10GB.
-At the end of writing to the WAL it says "yup, you don't have extra, so I'm leaving 
you alone."
-The auto checkpoint runs, (let's says it completes everything), and rewinds 
the WAL (if possible) without truncating it, because auto checkpoints are 
passive.
-Now the main DB file is all synched, but I have a 10GB WAL file, which stays 
that way until the next write.
-I insert 1 byte of data
-If the WAL was rewound successfully it writes the new data at the start of the 
WAL.
-At the end of writing that to the WAL it says "wow you're bloated, I'm truncating 
you." and cuts it back to 1 page in size.
-Checkpoint runs on the 1 byte insert. WAL stays at 1 page.

Starting to make sense now, thanks. It was that "hey, I'm all checkpointed and 
synched up but the file's still huge" bit there throwing me off. Of course I have to 
ask again here, am I understanding it correctly now?


Yep, that's it.

Dan.








-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Dan Kennedy
Sent: Friday, October 28, 2016 5:21 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] WAL pragma question

On 10/28/2016 03:16 AM, David Raymond wrote:

I'm playing around with WAL mode here for the first time, along with some of 
the pragmas, and I'm getting some weird results. I was hoping someone could let 
me know if I'm missing something, or if yes, it is indeed weird.

For starters, I'm looking at the journal_size_limit pragma.
http://www.sqlite.org/pragma.html#pragma_journal_size_limit
In its description it does say that it works for WAL mode. "To always truncate rollback journals and WAL 
files to their minimum size, set the journal_size_limit to zero." So I create a new database, turn on 
WAL mode, set that pragma, create a table, and insert some stuff into it. Then I check the file sizes, and 
the -wal file hasn't shrunk at all. I made sure it was large enough to go over the wal_autocheckpoint 
threshold and it didn't shrink. I ran a manual "pragma wal_checkpoint;" and it didn't shrink. I 
tried with specifying passive, full, and restart and it didn't shrink. It seems that I can only get it to 
shrink by doing an explicit "pragma wal_checkpoint(truncate);" But if that's the only way to shrink 
the file down, then what's the point of the pragma here?

Or, as is more likely, what obvious thing is my brain missing at the end of the 
day?

In wal mode, the wal file is truncated according to "PRAGMA
journal_size_limit" after the first transaction is written following a
checkpoint. Or, technically, after a writer writes a transaction into
the beginning of the physical file. So:

sqlite> PRAGMA journal_mode = wal;
wal
sqlite> PRAGMA journal_size_limit = 0;
0
sqlite> CREATE TABLE t1(a, b);
sqlite> INSERT INTO t1 VALUES(randomblob(10), randomblob(10));
/* WAL file is now roughly 200KiB */
sqlite> PRAGMA wal_checkpoint;
0|52|52
/* Still roughly 200KiB */
sqlite> INSERT INTO t1 VALUES(1, 1);
/* Now truncated to 4KiB */

This is because the locking scheme only allows writers to modify the wal
file, not checkpointers.

Dan.

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



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

Re: [sqlite] WAL pragma question

[David Raymond]

So my new understanding: This happens at the end of the write to the WAL file, 
but before the actual checkpoint. And then any checkpoint just works normally. 
So basically, even with journal_size_limit = 0, the WAL will always be at least 
as large as the last write, even if checkpointed successfully and completely.

-So I insert 10 GB of data, the WAL grows to 10GB.
-At the end of writing to the WAL it says "yup, you don't have extra, so I'm 
leaving you alone."
-The auto checkpoint runs, (let's says it completes everything), and rewinds 
the WAL (if possible) without truncating it, because auto checkpoints are 
passive.
-Now the main DB file is all synched, but I have a 10GB WAL file, which stays 
that way until the next write.
-I insert 1 byte of data
-If the WAL was rewound successfully it writes the new data at the start of the 
WAL.
-At the end of writing that to the WAL it says "wow you're bloated, I'm 
truncating you." and cuts it back to 1 page in size.
-Checkpoint runs on the 1 byte insert. WAL stays at 1 page.

Starting to make sense now, thanks. It was that "hey, I'm all checkpointed and 
synched up but the file's still huge" bit there throwing me off. Of course I 
have to ask again here, am I understanding it correctly now?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Dan Kennedy
Sent: Friday, October 28, 2016 5:21 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] WAL pragma question

On 10/28/2016 03:16 AM, David Raymond wrote:
> I'm playing around with WAL mode here for the first time, along with some of 
> the pragmas, and I'm getting some weird results. I was hoping someone could 
> let me know if I'm missing something, or if yes, it is indeed weird.
>
> For starters, I'm looking at the journal_size_limit pragma.
> http://www.sqlite.org/pragma.html#pragma_journal_size_limit
> In its description it does say that it works for WAL mode. "To always 
> truncate rollback journals and WAL files to their minimum size, set the 
> journal_size_limit to zero." So I create a new database, turn on WAL mode, 
> set that pragma, create a table, and insert some stuff into it. Then I check 
> the file sizes, and the -wal file hasn't shrunk at all. I made sure it was 
> large enough to go over the wal_autocheckpoint threshold and it didn't 
> shrink. I ran a manual "pragma wal_checkpoint;" and it didn't shrink. I tried 
> with specifying passive, full, and restart and it didn't shrink. It seems 
> that I can only get it to shrink by doing an explicit "pragma 
> wal_checkpoint(truncate);" But if that's the only way to shrink the file 
> down, then what's the point of the pragma here?
>
> Or, as is more likely, what obvious thing is my brain missing at the end of 
> the day?

In wal mode, the wal file is truncated according to "PRAGMA 
journal_size_limit" after the first transaction is written following a 
checkpoint. Or, technically, after a writer writes a transaction into 
the beginning of the physical file. So:

   sqlite> PRAGMA journal_mode = wal;
   wal
   sqlite> PRAGMA journal_size_limit = 0;
   0
   sqlite> CREATE TABLE t1(a, b);
   sqlite> INSERT INTO t1 VALUES(randomblob(10), randomblob(10));
   /* WAL file is now roughly 200KiB */
   sqlite> PRAGMA wal_checkpoint;
   0|52|52
   /* Still roughly 200KiB */
   sqlite> INSERT INTO t1 VALUES(1, 1);
   /* Now truncated to 4KiB */

This is because the locking scheme only allows writers to modify the wal 
file, not checkpointers.

Dan.

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

Re: [sqlite] WAL pragma question

[J Decker]

Also if you have any connections open, the journal may exist, so it doesn't
have to constantly open and close it.


On Fri, Oct 28, 2016 at 2:21 AM, Dan Kennedy  wrote:

> On 10/28/2016 03:16 AM, David Raymond wrote:
>
>> I'm playing around with WAL mode here for the first time, along with some
>> of the pragmas, and I'm getting some weird results. I was hoping someone
>> could let me know if I'm missing something, or if yes, it is indeed weird.
>>
>> For starters, I'm looking at the journal_size_limit pragma.
>> http://www.sqlite.org/pragma.html#pragma_journal_size_limit
>> In its description it does say that it works for WAL mode. "To always
>> truncate rollback journals and WAL files to their minimum size, set the
>> journal_size_limit to zero." So I create a new database, turn on WAL mode,
>> set that pragma, create a table, and insert some stuff into it. Then I
>> check the file sizes, and the -wal file hasn't shrunk at all. I made sure
>> it was large enough to go over the wal_autocheckpoint threshold and it
>> didn't shrink. I ran a manual "pragma wal_checkpoint;" and it didn't
>> shrink. I tried with specifying passive, full, and restart and it didn't
>> shrink. It seems that I can only get it to shrink by doing an explicit
>> "pragma wal_checkpoint(truncate);" But if that's the only way to shrink the
>> file down, then what's the point of the pragma here?
>>
>> Or, as is more likely, what obvious thing is my brain missing at the end
>> of the day?
>>
>
> In wal mode, the wal file is truncated according to "PRAGMA
> journal_size_limit" after the first transaction is written following a
> checkpoint. Or, technically, after a writer writes a transaction into the
> beginning of the physical file. So:
>
>   sqlite> PRAGMA journal_mode = wal;
>   wal
>   sqlite> PRAGMA journal_size_limit = 0;
>   0
>   sqlite> CREATE TABLE t1(a, b);
>   sqlite> INSERT INTO t1 VALUES(randomblob(10), randomblob(10));
>   /* WAL file is now roughly 200KiB */
>   sqlite> PRAGMA wal_checkpoint;
>   0|52|52
>   /* Still roughly 200KiB */
>   sqlite> INSERT INTO t1 VALUES(1, 1);
>   /* Now truncated to 4KiB */
>
> This is because the locking scheme only allows writers to modify the wal
> file, not checkpointers.
>
> Dan.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL pragma question

[Dan Kennedy]

On 10/28/2016 03:16 AM, David Raymond wrote:

I'm playing around with WAL mode here for the first time, along with some of 
the pragmas, and I'm getting some weird results. I was hoping someone could let 
me know if I'm missing something, or if yes, it is indeed weird.

For starters, I'm looking at the journal_size_limit pragma.
http://www.sqlite.org/pragma.html#pragma_journal_size_limit
In its description it does say that it works for WAL mode. "To always truncate rollback journals and WAL 
files to their minimum size, set the journal_size_limit to zero." So I create a new database, turn on 
WAL mode, set that pragma, create a table, and insert some stuff into it. Then I check the file sizes, and 
the -wal file hasn't shrunk at all. I made sure it was large enough to go over the wal_autocheckpoint 
threshold and it didn't shrink. I ran a manual "pragma wal_checkpoint;" and it didn't shrink. I 
tried with specifying passive, full, and restart and it didn't shrink. It seems that I can only get it to 
shrink by doing an explicit "pragma wal_checkpoint(truncate);" But if that's the only way to shrink 
the file down, then what's the point of the pragma here?

Or, as is more likely, what obvious thing is my brain missing at the end of the 
day?


In wal mode, the wal file is truncated according to "PRAGMA 
journal_size_limit" after the first transaction is written following a 
checkpoint. Or, technically, after a writer writes a transaction into 
the beginning of the physical file. So:


  sqlite> PRAGMA journal_mode = wal;
  wal
  sqlite> PRAGMA journal_size_limit = 0;
  0
  sqlite> CREATE TABLE t1(a, b);
  sqlite> INSERT INTO t1 VALUES(randomblob(10), randomblob(10));
  /* WAL file is now roughly 200KiB */
  sqlite> PRAGMA wal_checkpoint;
  0|52|52
  /* Still roughly 200KiB */
  sqlite> INSERT INTO t1 VALUES(1, 1);
  /* Now truncated to 4KiB */

This is because the locking scheme only allows writers to modify the wal 
file, not checkpointers.


Dan.

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

[sqlite] WAL pragma question

[David Raymond]

I'm playing around with WAL mode here for the first time, along with some of 
the pragmas, and I'm getting some weird results. I was hoping someone could let 
me know if I'm missing something, or if yes, it is indeed weird.

For starters, I'm looking at the journal_size_limit pragma.
http://www.sqlite.org/pragma.html#pragma_journal_size_limit
In its description it does say that it works for WAL mode. "To always truncate 
rollback journals and WAL files to their minimum size, set the 
journal_size_limit to zero." So I create a new database, turn on WAL mode, set 
that pragma, create a table, and insert some stuff into it. Then I check the 
file sizes, and the -wal file hasn't shrunk at all. I made sure it was large 
enough to go over the wal_autocheckpoint threshold and it didn't shrink. I ran 
a manual "pragma wal_checkpoint;" and it didn't shrink. I tried with specifying 
passive, full, and restart and it didn't shrink. It seems that I can only get 
it to shrink by doing an explicit "pragma wal_checkpoint(truncate);" But if 
that's the only way to shrink the file down, then what's the point of the 
pragma here?

Or, as is more likely, what obvious thing is my brain missing at the end of the 
day?

(Tested with pre-compiled Windows CLI 3.15.0 in Windows 7. I did refresh the 
folder in a Windows Explorer window to make sure it wasn't just stale directory 
info for the file sizes)

Thanks for any help,
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users