PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

Howard Kapustein
The docs are a little unclear => https://sqlite.org/pragma.html#pragma_journal_size_limit

I need to disable autocheckpoint@close (for other reasons) so I'm looking for ways to fence the -wal file. If I want to bound a database's -wal file to <=1MB when I'm not in a transaction is it just PRAGMA journal_size_limit=1048576;? Does this affect -wal growth *during* a transaction, i.e. if I BEGIN; ...INSERT lots...; COMMIT will I successfully reach COMMIT? The journal_size_limit doesn't affect *growth* of the -wal, right? Just 'at rest' size  (i.e. outside any transaction)?

As for why... I'm good with -wal growth while I do work but in some cases I've got too much work coming and going w/o any idle period for autocheckpoint@close to kick in. In a few cases I see huge (700MB+) -wal files when I've got no transaction in play. I've set SQLITE_FCNTL_PERSIST_WAL=1 and want to explicitly checkpoint similar to how sqlite does on close i.e. Passive + truncate

SQLITE_PRIVATE int sqlite3WalClose(...){
...
      rc = sqlite3WalCheckpoint(pWal, db,
          SQLITE_CHECKPOINT_PASSIVE, 0, 0, sync_flags, nBuf, zBuf, 0, 0
      );
...
        }else if( pWal->mxWalSize>=0 ){
          /* Try to truncate the WAL file to zero bytes if the checkpoint
          ** completed and fsynced (rc==SQLITE_OK) and we are in persistent
          ** WAL mode (bPersist) and if the PRAGMA journal_size_limit is a
          ** non-negative value (pWal->mxWalSize>=0).  Note that we truncate
          ** to zero bytes as truncating to the journal_size_limit might
          ** leave a corrupt WAL file on disk. */
          walLimitSize(pWal, 0);
        }
      }
    }

Is this the functional equivalent?
int limit = ...get current value via sqlite3_exec(db, "PRAGMA journal_size_limit;")...
sqlite3_exec(db, "PRAGMA journal_size_limit=0;")
sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_PASSIVE, &size, &chkpt);
sqlite3_exec(db, "PRAGMA journal_size_limit=" + limit + ";")

Thanks,


  *   Howard
_______________________________________________
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: PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

David Raymond
The journal_size_limit in WAL mode is for when the WAL file resets (everything checkpointed successfully). It doesn't limit transaction size in any way. It's simply "when everything has checkpointed: cut the file back to at most this size" rather than the normal mode of: "wal file size will never shrink, just be recycled", (similar to how the database file size doesn't shrink when you delete records).

Can't help you on the C code part though, sorry.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Howard Kapustein
Sent: Tuesday, September 05, 2017 1:07 PM
To: [hidden email]
Subject: [sqlite] PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

The docs are a little unclear => https://sqlite.org/pragma.html#pragma_journal_size_limit

I need to disable autocheckpoint@close (for other reasons) so I'm looking for ways to fence the -wal file. If I want to bound a database's -wal file to <=1MB when I'm not in a transaction is it just PRAGMA journal_size_limit=1048576;? Does this affect -wal growth *during* a transaction, i.e. if I BEGIN; ...INSERT lots...; COMMIT will I successfully reach COMMIT? The journal_size_limit doesn't affect *growth* of the -wal, right? Just 'at rest' size  (i.e. outside any transaction)?

As for why... I'm good with -wal growth while I do work but in some cases I've got too much work coming and going w/o any idle period for autocheckpoint@close to kick in. In a few cases I see huge (700MB+) -wal files when I've got no transaction in play. I've set SQLITE_FCNTL_PERSIST_WAL=1 and want to explicitly checkpoint similar to how sqlite does on close i.e. Passive + truncate

SQLITE_PRIVATE int sqlite3WalClose(...){
...
      rc = sqlite3WalCheckpoint(pWal, db,
          SQLITE_CHECKPOINT_PASSIVE, 0, 0, sync_flags, nBuf, zBuf, 0, 0
      );
...
        }else if( pWal->mxWalSize>=0 ){
          /* Try to truncate the WAL file to zero bytes if the checkpoint
          ** completed and fsynced (rc==SQLITE_OK) and we are in persistent
          ** WAL mode (bPersist) and if the PRAGMA journal_size_limit is a
          ** non-negative value (pWal->mxWalSize>=0).  Note that we truncate
          ** to zero bytes as truncating to the journal_size_limit might
          ** leave a corrupt WAL file on disk. */
          walLimitSize(pWal, 0);
        }
      }
    }

Is this the functional equivalent?
int limit = ...get current value via sqlite3_exec(db, "PRAGMA journal_size_limit;")...
sqlite3_exec(db, "PRAGMA journal_size_limit=0;")
sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_PASSIVE, &size, &chkpt);
sqlite3_exec(db, "PRAGMA journal_size_limit=" + limit + ";")

Thanks,


  *   Howard
_______________________________________________
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: PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

Dan Kennedy-4
In reply to this post by Howard Kapustein
> I use journal_mode=WAL and have periods of checkpoint starvation (complicated
> reasons) so I'm looking to prune the -wal file but in less blunt way than
> SQLITE_CHECKPOINT_TRUNCATE.
> Behaviorally I want SQLITE_CHECKPOINT_PASSIVE *and then* if required -wal
> content < journal_size_limit, to do the truncation a la
> SQLITE_CHECKPOINT_TRUNCATE
>
> SQLITE_CHECKPOINT_PASSIVE gives me the best-effort checkpointing, but in the
> best case I don't get -wal shrinkage. SQLITE_CHECKPOINT_TRUNCATE is more of an
> aggressive do-it-now-wait-if-necessary which gets me the shrinkage behavior,
> but with *required*-effort rather than best-effort. I'd really like both --
> best-effort checkpoint AND best-effort truncate.
>
> sqlite3WalClose does exactly what I want (the key bits)
>
>      sqlite3OsLock(pWal->pDbFd, SQLITE_LOCK_EXCLUSIVE)
>      sqlite3WalCheckpoint(pWal, db, SQLITE_CHECKPOINT_PASSIVE,...)
>      sqlite3OsFileControlHint(pWal->pDbFd, SQLITE_FCNTL_PERSIST_WAL, &bPersist)
>      if (bPersist) { if (pWal->mxWalSize>=0) { walLimitSize(pWal, 0) } }
>
> But AFAICT this is only called when PRAGMA journal_mode changes to not WAL or
> the pager cache is closed when via sqlite3_close(). I'm a long running process
> with connection caching and associated prepared statements so torching the
> connection to trigger this isn't optimal.
>
> Can I indirectly get this behavior if I open then immediately close a
> connection?

I think so. If there are no other connections to the same database. If
there are any other connections, the checkpoint-on-close won't happen of
course.

> I think I can sorta approximate this it if I disable the busy-handler, do
> SQLITE_CHECKPOINT_TRUNCATE, ignore Busy/Locked errors and restore the
> busy-handler before returning, but that's merely 'sorta' -- ugly on several
> levels.

Which part of the sorta is the problem? If you run an
SQLITE_CHECKPOINT_TRUNCATE checkpoint without a busy-handler, or with
the busy-handler rigged to return 0 immediately, then it will:

   * attempt a best-effort checkpoint (same as PASSIVE),
   * if the entire wal file was checkpointed, check if any readers are
still using it,
   * if no readers are still using it, truncate the wal file to zero
bytes in size.

> I don't see any way to directly try to best-effort truncate the -wal file e.g.
>
>      sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_PASSIVE, NULL, NULL))
>      sqlite3_file_control(db, NULL, SQLITE_FCNTL_TRUNCATE_WAL, NULL)

Is the idea that this file-control would truncate the wal file to zero
bytes in size iff it could safely do so without blocking on any other
clients?

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: PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

Howard Kapustein
In reply to this post by Howard Kapustein
Looking through the source a bit I see sqlite3_busy_timeout is a no-fail operation (aside from misuse) so sqlite3_busy_timeout(0); SQLITE_CHECKPOINT_TRUNCATEsqlite3_busy_timeout(n); does what I need. Thanks all.

        - Howard

P.S. sqlite3_close intentionally doesn't respect journal_size_limit. From sqlite3WalClose

        }else if( pWal->mxWalSize>=0 ){
          /* Try to truncate the WAL file to zero bytes if the checkpoint
          ** completed and fsynced (rc==SQLITE_OK) and we are in persistent
          ** WAL mode (bPersist) and if the PRAGMA journal_size_limit is a
          ** non-negative value (pWal->mxWalSize>=0).  Note that we truncate
          ** to zero bytes as truncating to the journal_size_limit might
          ** leave a corrupt WAL file on disk. */
          walLimitSize(pWal, 0);
        }

That last comment "Note that we truncate..." is interesting. When would this corrupt WAL rear its head?
_______________________________________________
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: PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

Clemens Ladisch
Howard Kapustein wrote:

>           /* Try to truncate the WAL file to zero bytes if the checkpoint
>           ** completed and fsynced (rc==SQLITE_OK) and we are in persistent
>           ** WAL mode (bPersist) and if the PRAGMA journal_size_limit is a
>           ** non-negative value (pWal->mxWalSize>=0).  Note that we truncate
>           ** to zero bytes as truncating to the journal_size_limit might
>           ** leave a corrupt WAL file on disk. */
>           walLimitSize(pWal, 0);
>
> That last comment "Note that we truncate..." is interesting. When
> would this corrupt WAL rear its head?

A 'normal' checkpoint would adjust the WAL file header to record that
the data has been checkpointed and is no longer valid.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users