Re: [sqlite] PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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)? 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 + ";") It's close, but not quite equivalent. The checkpoint operation does not consider the journal_size_limit setting in wal mode. Instead, it is applied when a writer writes a transaction into the very start of the wal file. So, say you manage to checkpoint the entire wal file (because there are no old readers preventing this). The next connection to write to the database will write its transaction into the start of the wal file, overwriting old content. When the writer commits, it checks if the wal file on disk is larger than the configured "PRAGMA journal_size_limit". If it is, the writer truncates it - either to the configured limit or to the smallest possible size without truncating away part of the transaction that was just written. In other words - "PRAGMA journal_size_limit" is applied by the next COMMIT after a successful checkpoint, not by the checkpoint itself. Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?
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:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Howard Kapustein
Sent: Tuesday, September 05, 2017 1:07 PM
To: sqlite-users@mailinglists.sqlite.org
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
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] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
