In Berkeley DB SQL interface, the pragma
wal_autocheckpoint can be used to control
the total number of log files that get accumulated
before an automatic purge happens. For example,
if we set pragma wal_checkpoint=N, and
when the current amount of data written to logs since the
last checkpoint equals or exceeds "N" pages, a checkpoint
happens. If the log file auto-removing is enabled
(which is enabled by default), the accumulated log
files are auto-purged.
In Berkeley DB SQL interface, the default value of
wal_autocheckpoint is 512 pages.
With the default page size as 4096 bytes, it means when
the current amount of data written to logs since
the last checkpoint equals or exceeds 2 MB, a
checkpoint happens and the accumulated log files
purge. As in Berkeley DB SQL interface, by default
a single log file has the maximium size as 2 MB,
so we will at most have two 2 MB log files in a
Bekeley DB environment.
You can make changes to the default values. For
example, if you change wal_autocheckpoint
to 256 pages with the page size as 4096 bytes, it means
when the current amount of data written to logs since
the last checkpoint equals or exceeds 1 MB, a checkpoint
happens and the accumulated log files get purged. You
can also change the single log file size to 256 KB
(check the usage of PRAGMA journal_size_limit).
With this setting, you will have maximum five 256 KB log
files in the Berkeley DB environment.
If there are huge transactions, the Berkeley DB accumulated log files will be as many as needed.
User can turn off the auto-checkpointing (for example,
by PRAGMA wal_autocheckpoint = -1).
In this case, users must have their own management
on the accumulated log files.
Pragma wal_autocheckpoint is not
persistent and will reset to default after we reset
the database connection.