From d15053fff323d88a487a66a235e036f9e3687d1d Mon Sep 17 00:00:00 2001 From: Eric Wong Date: Fri, 15 Aug 2025 13:41:35 +0000 Subject: [PATCH] init|*index|convert: --wal enables Write-Ahead Log in SQLite SQLite WAL ought to improve concurrency for readers when writers are active. While users always had the option of enabling it with the sqlite3(1) tool, having a `--wal' switch may encourage its use. It's already on by default for lei/store and mail_sync.sqlite3 where readers and writers are the same user, but enabling it by default for public-facing daemons would cause permissions problems so it remains optional. The key downside of WAL is readers (-(netd|httpd|imapd|nntpd|pop3d) require write access to the directories containing over.sqlite3 and msgmap.sqlite3). In the past, public-facing read-only daemons were not expected to ever have write permissions to inbox and extindex directories, but WAL requires writability since SQLite||DBD::SQLite currently doesn't provide a way to guarantee the persistence of -wal and -shm files, especially when accessed by sqlite3(1) or 3rd-party scripts. The main advantage of supporting WAL is so the writers won't block readers, avoiding busy timeouts on the read side and reducing the need for writers to commit transactions periodically to keep readers happy (currently a ridiculously short 5s). We can investigate higher commit intervals (or eliminating them entirely) for users of WAL. WAL should also reduce fragmentation in btrfs and similar CoW filesystems by reducing fsync(2) calls, this remains to be proven as measuring such changes takes a while and I don't want to put more wear on my SSD. --- Documentation/public-inbox-convert.pod | 2 ++ Documentation/public-inbox-extindex.pod | 2 ++ Documentation/public-inbox-index.pod | 8 ++++++++ lib/PublicInbox/ExtSearchIdx.pm | 1 + lib/PublicInbox/InboxWritable.pm | 9 ++++++--- lib/PublicInbox/LeiStore.pm | 2 +- lib/PublicInbox/Msgmap.pm | 3 ++- lib/PublicInbox/OverIdx.pm | 2 +- lib/PublicInbox/SearchIdx.pm | 3 ++- lib/PublicInbox/TestCommon.pm | 1 + lib/PublicInbox/V2Writable.pm | 4 ++-- script/public-inbox-convert | 11 +++++++++-- script/public-inbox-extindex | 2 +- script/public-inbox-index | 2 +- script/public-inbox-init | 1 + t/convert-compact.t | 12 ++++++++++++ t/extsearch.t | 4 +++- t/indexlevels-mirror.t | 7 ++++++- t/init.t | 26 +++++++++++++++++++++---- 19 files changed, 83 insertions(+), 19 deletions(-) diff --git a/Documentation/public-inbox-convert.pod b/Documentation/public-inbox-convert.pod index a2f8caf59..86e8265b3 100644 --- a/Documentation/public-inbox-convert.pod +++ b/Documentation/public-inbox-convert.pod @@ -49,6 +49,8 @@ process which distributes work to the Xapian shards. =item --max-size=BYTES +=item --wal + These options affect indexing. They have no effect if L is specified diff --git a/Documentation/public-inbox-extindex.pod b/Documentation/public-inbox-extindex.pod index 2db7d7e90..d9d4d9011 100644 --- a/Documentation/public-inbox-extindex.pod +++ b/Documentation/public-inbox-extindex.pod @@ -34,6 +34,8 @@ along with L and L Perl modules. =item --batch-size SIZE +=item --wal + These switches behave as they do for L =item --all diff --git a/Documentation/public-inbox-index.pod b/Documentation/public-inbox-index.pod index f1a2180ad..c839f74ec 100644 --- a/Documentation/public-inbox-index.pod +++ b/Documentation/public-inbox-index.pod @@ -209,6 +209,14 @@ Available in public-inbox 2.0.0+ Passed directly to L to limit changes for C<--reindex> +=item --wal + +Enable WAL (Write-Ahead-Log) on SQLite files. This may reduce +fragmentation and improve performance; but requires read-only +processes (e.g. public-inbox-netd) to have write access +to all directories containing *.sqlite3 files. +See L for more info. + =back =head1 FILES diff --git a/lib/PublicInbox/ExtSearchIdx.pm b/lib/PublicInbox/ExtSearchIdx.pm index 33c25134d..bc1e68d77 100644 --- a/lib/PublicInbox/ExtSearchIdx.pm +++ b/lib/PublicInbox/ExtSearchIdx.pm @@ -68,6 +68,7 @@ sub new { $self->{shards} = $self->count_shards || nproc_shards { nproc => $opt->{jobs} }; my $oidx = PublicInbox::OverIdx->new("$self->{xpfx}/over.sqlite3"); + $oidx->{journal_mode} = 'wal' if $opt->{wal}; $self->{-no_fsync} = $oidx->{-no_fsync} = 1 if !$opt->{fsync}; $self->{-dangerous} = 1 if $opt->{dangerous}; $self->{oidx} = $oidx; diff --git a/lib/PublicInbox/InboxWritable.pm b/lib/PublicInbox/InboxWritable.pm index f69aec754..a4e63a5c4 100644 --- a/lib/PublicInbox/InboxWritable.pm +++ b/lib/PublicInbox/InboxWritable.pm @@ -30,13 +30,16 @@ sub assert_usable_dir { sub _init_v1 { my ($self) = @_; - my $skip_artnum = ($self->{-creat_opt} // {})->{'skip-artnum'}; - if (defined($self->{indexlevel}) || defined($skip_artnum)) { + my $opt = $self->{-creat_opt} // {}; + my $skip_artnum = $opt->{'skip-artnum'}; + if (defined($self->{indexlevel}) || defined($skip_artnum) || + $opt->{wal}) { require PublicInbox::SearchIdx; require PublicInbox::Msgmap; my $sidx = PublicInbox::SearchIdx->new($self, 1); # just create + $sidx->{oidx}->{journal_mode} = 'wal' if $opt->{wal}; $sidx->begin_txn_lazy; - my $mm = PublicInbox::Msgmap->new_file($self, 1); + my $mm = PublicInbox::Msgmap->new_file($self, 1, $opt); if (defined $skip_artnum) { $mm->{dbh}->begin_work; $mm->skip_artnum($skip_artnum); diff --git a/lib/PublicInbox/LeiStore.pm b/lib/PublicInbox/LeiStore.pm index 46460c569..9d97ef58a 100644 --- a/lib/PublicInbox/LeiStore.pm +++ b/lib/PublicInbox/LeiStore.pm @@ -139,7 +139,7 @@ sub eidx_init { my $tl = wantarray && $self->{-err_wr} ? on_destroy(\&_tail_err, $self) : undef; - $eidx->idx_init({-private => 1}); # acquires lock + $eidx->idx_init({wal => 1, -private => 1}); # acquires lock wantarray ? ($eidx, $tl) : $eidx; } diff --git a/lib/PublicInbox/Msgmap.pm b/lib/PublicInbox/Msgmap.pm index 71415febc..0dacdb848 100644 --- a/lib/PublicInbox/Msgmap.pm +++ b/lib/PublicInbox/Msgmap.pm @@ -16,7 +16,7 @@ use PublicInbox::Over; use Scalar::Util qw(blessed); sub new_file { - my ($class, $ibx, $rw) = @_; + my ($class, $ibx, $rw, $opt) = @_; my $f; if (blessed($ibx)) { $f = $ibx->mm_file; @@ -27,6 +27,7 @@ sub new_file { return if !$rw && !-r $f; my $self = bless { filename => $f }, $class; + $self->{journal_mode} = 'wal' if $opt->{wal}; my $dbh = $self->{dbh} = PublicInbox::Over::dbh_new($self, $rw); if ($rw) { $dbh->begin_work; diff --git a/lib/PublicInbox/OverIdx.pm b/lib/PublicInbox/OverIdx.pm index b7bebf585..93f2f11b2 100644 --- a/lib/PublicInbox/OverIdx.pm +++ b/lib/PublicInbox/OverIdx.pm @@ -473,7 +473,7 @@ sub create { my ($dir) = ($fn =~ m!(.*?/)[^/]+\z!); File::Path::mkpath($dir); } - $self->{journal_mode} = 'WAL' if $opt->{-private}; + $self->{journal_mode} = 'wal' if $opt->{wal}; # create the DB: PublicInbox::Over::dbh($self); $self->dbh_close; diff --git a/lib/PublicInbox/SearchIdx.pm b/lib/PublicInbox/SearchIdx.pm index 125f43f79..8ddbc07a0 100644 --- a/lib/PublicInbox/SearchIdx.pm +++ b/lib/PublicInbox/SearchIdx.pm @@ -540,7 +540,7 @@ sub v1_mm_init ($) { die "BUG: v1_mm_init is only for v1\n" if $self->{ibx}->version != 1; $self->{mm} //= do { require PublicInbox::Msgmap; - PublicInbox::Msgmap->new_file($self->{ibx}, 1); + PublicInbox::Msgmap->new_file($self->{ibx}, 1, $self->{-opt}); }; } @@ -1108,6 +1108,7 @@ sub _index_sync { local $SIG{QUIT} = $quit; local $SIG{INT} = $quit; local $SIG{TERM} = $quit; + $self->{oidx}->{journal_mode} = 'wal' if $opt->{wal}; my $xdb = $self->begin_txn_lazy; $self->{oidx}->rethread_prepare($opt); my $mm = v1_mm_init $self; diff --git a/lib/PublicInbox/TestCommon.pm b/lib/PublicInbox/TestCommon.pm index ab988de40..ac8028788 100644 --- a/lib/PublicInbox/TestCommon.pm +++ b/lib/PublicInbox/TestCommon.pm @@ -949,6 +949,7 @@ sub create_inbox ($;@) { $opt{-primary_address} //= $addr->[0] // "$ident\@example.com"; my $parallel = delete($opt{importer_parallel}) // 0; my $creat_opt = { nproc => delete($opt{nproc}) // 1 }; + $creat_opt->{wal} = 1 if $opt{wal}; my $ibx = PublicInbox::InboxWritable->new({ %opt }, $creat_opt); if (!-f "$dir/creat.stamp") { my $im = $ibx->importer($parallel); diff --git a/lib/PublicInbox/V2Writable.pm b/lib/PublicInbox/V2Writable.pm index 3ae6c4d08..7186b2cce 100644 --- a/lib/PublicInbox/V2Writable.pm +++ b/lib/PublicInbox/V2Writable.pm @@ -85,7 +85,7 @@ sub init_inbox { my $skip_epoch = ($self->{ibx}->{-creat_opt} // {})->{'skip-epoch'}; $max = $skip_epoch if (defined($skip_epoch) && !defined($max)); $self->{mg}->add_epoch($max // 0); - $self->idx_init; + $self->idx_init($self->{ibx}->{-creat_opt}); my $skip_artnum = ($self->{ibx}->{-creat_opt} // {})->{'skip-artnum'}; $self->{mm}->skip_artnum($skip_artnum) if defined $skip_artnum; $self->done; @@ -243,7 +243,7 @@ sub _idx_init { # with_umask callback # Now that all subprocesses are up, we can open the FDs # for SQLite: - my $mm = $self->{mm} = PublicInbox::Msgmap->new_file($ibx, 1); + my $mm = $self->{mm} = PublicInbox::Msgmap->new_file($ibx, 1, $opt); $mm->{dbh}->begin_work; } diff --git a/script/public-inbox-convert b/script/public-inbox-convert index 713c28815..cf87b4ebb 100755 --- a/script/public-inbox-convert +++ b/script/public-inbox-convert @@ -37,7 +37,7 @@ GetOptions($opt, qw(jobs|j=i index! help|h C=s@), # index options qw(verbose|v+ rethread compact|c+ fsync|sync! indexlevel|index-level|L=s max_size|max-size=s - batch_size|batch-size=s + batch_size|batch-size=s wal sequential-shard|seq-shard )) or die $help; if ($opt->{help}) { print $help; exit 0 }; @@ -72,12 +72,19 @@ if ($opt->{'index'}) { PublicInbox::Admin::require_or_die(keys %$mods); PublicInbox::Admin::progress_prepare($opt); $env = PublicInbox::Admin::index_prepare($opt, $cfg); + if (!$opt->{wal}) { + $opt->{wal} = 1 if $old->over->dbh->selectrow_array( + 'PRAGMA journal_mode') eq 'wal'; + $old->cleanup; + } } local %ENV = (%$env, %ENV) if $env; my $new = { %$old }; $new->{inboxdir} = PublicInbox::Config::rel2abs_collapsed($new_dir); $new->{version} = 2; -$new = PublicInbox::InboxWritable->new($new, { nproc => $opt->{jobs} }); +my $creat_opt = { nproc => $opt->{jobs} }; +$creat_opt->{wal} = 1 if $opt->{wal}; +$new = PublicInbox::InboxWritable->new($new, $creat_opt); $new->{-no_fsync} = 1 if !$opt->{fsync}; my $v2w; diff --git a/script/public-inbox-extindex b/script/public-inbox-extindex index 2e5a5d2cd..6b1b06c72 100755 --- a/script/public-inbox-extindex +++ b/script/public-inbox-extindex @@ -28,7 +28,7 @@ See public-inbox-extindex(1) man page for full documentation. EOF my $opt = { quiet => -1, compact => 0, fsync => 1, scan => 1 }; GetOptions($opt, qw(verbose|v+ reindex rethread compact|c+ jobs|j=i - fsync|sync! fast dangerous + fsync|sync! fast dangerous wal indexlevel|index-level|L=s max_size|max-size=s batch_size|batch-size=s dedupe:s@ gc commit-interval=i watch scan! dry-run|n diff --git a/script/public-inbox-index b/script/public-inbox-index index 82baa7b48..72f561381 100755 --- a/script/public-inbox-index +++ b/script/public-inbox-index @@ -36,7 +36,7 @@ my $opt = { 'update-extindex' => [], # ":s@" optional arg sets '' if no arg given }; GetOptions($opt, qw(verbose|v+ reindex rethread compact|c+ jobs|j=i prune - fsync|sync! xapian_only|xapian-only dangerous + fsync|sync! xapian_only|xapian-only dangerous wal indexlevel|index-level|L=s max_size|max-size=s batch_size|batch-size=s since|after=s until|before=s diff --git a/script/public-inbox-init b/script/public-inbox-init index b959fd917..7497d058b 100755 --- a/script/public-inbox-init +++ b/script/public-inbox-init @@ -41,6 +41,7 @@ my (@c_extra, @chdir); my $creat_opt = {}; my %opts = ( 'V|version=i' => \$version, + wal => \($creat_opt->{wal}), 'L|index-level|indexlevel=s' => \$indexlevel, 'S|skip|skip-epoch=i' => \($creat_opt->{'skip-epoch'}), 'skip-artnum=i' => \($creat_opt->{'skip-artnum'}), diff --git a/t/convert-compact.t b/t/convert-compact.t index b123f17b2..533dd67d5 100644 --- a/t/convert-compact.t +++ b/t/convert-compact.t @@ -11,6 +11,7 @@ require_mods(qw(DBD::SQLite Xapian)); have_xapian_compact; my ($tmpdir, $for_destroy) = tmpdir(); my $ibx = create_inbox 'v1', indexlevel => 'medium', tmpdir => "$tmpdir/v1", + wal => 1, pre_cb => sub { my ($inboxdir) = @_; PublicInbox::Import::init_bare($inboxdir); @@ -46,6 +47,12 @@ foreach (@xdir) { 'sharedRepository respected on file after convert'); } +is $ibx->mm->{dbh}->selectrow_array('PRAGMA journal_mode'), 'wal', + '-compact preserves msgmap.sqlite3 wal'; +is $ibx->over->dbh->selectrow_array('PRAGMA journal_mode'), 'wal', + '-compact preserves over.sqlite3 wal'; +$ibx->cleanup; + local $ENV{PI_CONFIG} = '/dev/null'; my ($out, $err) = ('', ''); my $rdr = { 1 => \$out, 2 => \$err }; @@ -83,6 +90,11 @@ ok(run_script($cmd, $env, $rdr), 'v2 compact works'); $ibx->{inboxdir} = "$tmpdir/x/v2"; $ibx->{version} = 2; is($ibx->mm->num_highwater, $hwm, 'highwater mark unchanged in v2 inbox'); +is $ibx->mm->{dbh}->selectrow_array('PRAGMA journal_mode'), 'wal', + '-convert preserves msgmap.sqlite3 wal'; +is $ibx->over->dbh->selectrow_array('PRAGMA journal_mode'), 'wal', + '-convert preserves over.sqlite3 wal'; +$ibx->cleanup; @xdir = glob("$tmpdir/x/v2/xap*/*"); foreach (@xdir) { diff --git a/t/extsearch.t b/t/extsearch.t index 698421235..bb1fcfc50 100644 --- a/t/extsearch.t +++ b/t/extsearch.t @@ -44,11 +44,13 @@ run_script(['-mda', '--no-precheck'], $env, { 0 => $in }) or BAIL_OUT '-mda'; run_script([qw(-index -Lbasic), "$home/v1test"]) or BAIL_OUT "index $?"; -ok(run_script([qw(-extindex --dangerous --all), "$home/extindex"]), +ok(run_script([qw(-extindex --dangerous --all --wal), "$home/extindex"]), 'extindex init'); { my $es = PublicInbox::ExtSearch->new("$home/extindex"); ok($es->has_threadid, '->has_threadid'); + my $jm = $es->over->dbh->selectrow_array('PRAGMA journal_mode'); + is $jm, 'wal', "--wal enables `journal_mode = wal' in over.sqlite3"; } if ('with boost') { diff --git a/t/indexlevels-mirror.t b/t/indexlevels-mirror.t index c852f72cb..cf813d088 100644 --- a/t/indexlevels-mirror.t +++ b/t/indexlevels-mirror.t @@ -34,7 +34,8 @@ my $import_index_incremental = sub { local $ENV{PI_CONFIG} = "$tmpdir/config"; # index master (required for v1) - my @cmd = (qw(-index -j0 --dangerous), $ibx->{inboxdir}, "-L$level"); + my @cmd = (qw(-index --wal -j0 --dangerous), + $ibx->{inboxdir}, "-L$level"); push @cmd, '-c' if have_xapian_compact; ok(run_script(\@cmd, undef, { 2 => \$err }), 'index master'); my $ro_master = PublicInbox::Inbox->new({ @@ -44,6 +45,10 @@ my $import_index_incremental = sub { my $msgs = $ro_master->over->recent; is(scalar(@$msgs), 1, 'only one message in master, so far'); is($msgs->[0]->{mid}, 'm@1', 'first message in master indexed'); + my $jm = $ro_master->over->dbh->selectrow_array('PRAGMA journal_mode'); + is $jm, 'wal', 'over.sqlite3 respects --wal'; + $jm = $ro_master->mm->{dbh}->selectrow_array('PRAGMA journal_mode'); + is $jm, 'wal', 'msgmap.sqlite3 respects --wal'; # clone @cmd = (qw(git clone --mirror -q)); diff --git a/t/init.t b/t/init.t index 275192cfe..05cbc9421 100644 --- a/t/init.t +++ b/t/init.t @@ -14,10 +14,19 @@ sub quiet_fail { ok(!run_script($cmd, undef, { 2 => \$err, 1 => \$err }), $msg); } +my $check_wal = sub { + my ($bn) = ($_[0] =~ m!/([^/]+)/*\z!); + my $ibx = PublicInbox::Inbox->new({inboxdir => $_[0]}); + my $jm = $ibx->over->dbh->selectrow_array('PRAGMA journal_mode'); + is $jm, 'wal', "--wal works with $bn (over.sqlite3)"; + $jm = $ibx->mm->{dbh}->selectrow_array('PRAGMA journal_mode'); + is $jm, 'wal', "--wal works with $bn (msgmap.sqlite3)"; +}; + { local $ENV{PI_DIR} = "$tmpdir/.public-inbox/"; my $cfgfile = "$ENV{PI_DIR}/config"; - my $cmd = [ '-init', 'blist', "$tmpdir/blist", + my $cmd = [ qw(-init blist), "$tmpdir/blist", qw(http://example.com/blist blist@example.com) ]; my $umask = umask(070) // xbail "umask: $!"; ok(run_script($cmd), 'public-inbox-init OK'); @@ -127,12 +136,13 @@ SKIP: { local $ENV{PI_DIR} = "$tmpdir/.public-inbox/"; local $ENV{PI_EMERGENCY} = "$tmpdir/.public-inbox/emergency"; my $cfgfile = "$ENV{PI_DIR}/config"; - my $cmd = [ '-init', '-V2', 'v2list', "$tmpdir/v2list", + my $cmd = [ qw(-init -V2 --wal v2list), "$tmpdir/v2list", qw(http://example.com/v2list v2list@example.com) ]; ok(run_script($cmd), 'public-inbox-init -V2 OK'); ok(-d "$tmpdir/v2list", 'v2list directory exists'); ok(-f "$tmpdir/v2list/msgmap.sqlite3", 'msgmap exists'); ok(-d "$tmpdir/v2list/all.git", 'catch-all.git directory exists'); + $check_wal->("$tmpdir/v2list"); $cmd = [ '-init', 'v2list', "$tmpdir/v2list", qw(http://example.com/v2list v2list@example.com) ]; ok(run_script($cmd), 'public-inbox-init is idempotent'); @@ -204,8 +214,8 @@ SKIP: { $addr = 'skip4@example.com'; $env = { ORIGINAL_RECIPIENT => $addr }; - $cmd = [ qw(-init -V1 --skip-artnum 12 -Lmedium skip4), "$tmpdir/skip4", - qw(http://example.com/skip4), $addr ]; + $cmd = [ qw(-init -V1 --wal --skip-artnum 12 -Lmedium skip4), + "$tmpdir/skip4", qw(http://example.com/skip4), $addr ]; ok(run_script($cmd), '--skip-artnum -V1'); $err = ''; ok(run_script([qw(-mda --no-precheck)], $env, $rdr), 'deliver V1'); @@ -214,6 +224,14 @@ SKIP: { "$tmpdir/skip4/public-inbox/msgmap.sqlite3"); $n = $mm->num_for($mid); is($n, 13, 'V1 NNTP article numbers skipped via --skip-artnum'); + $check_wal->("$tmpdir/skip4"); + + # n.b. for now, indexlevel defaults to `full' if unspecified w/ + # --wal or --skip-artnum + $cmd = [ qw(-init -V1 --wal wal-only-v1), "$tmpdir/wal-1", + qw(http://example.com/wal-1 wal@example.com) ]; + ok run_script($cmd), '--wal with -V1'; + $check_wal->("$tmpdir/wal-1"); } { -- 2.47.3