over+msgmap: respect WAL journal_mode if set

WAL actually seems to have ideal locking characteristics given
concurrency problems I'm experiencing with --reindex running
in parallel with expensive read-only SQLite queries:
<https://public-inbox.org/meta/20200825001204.GA840@dcvr/>

Unfortunately, we cannot blindly use WAL while preserving
compatibility with existing setups nor our guarantees that
read-only daemons are indeed "read-only".

However, respect an user's the choice to set WAL on their
own if they're comfortable with giving -nntpd/-httpd/-imapd
processes write permission to the directory storing SQLite DBs.

diff --git a/lib/PublicInbox/Msgmap.pm b/lib/PublicInbox/Msgmap.pm
index 5b4cebc105bd21c659b1182721323cbf881d381a..d28e96c8588b002e62a09b64dc6eaaad51346309 100644 (file)
--- a/lib/PublicInbox/Msgmap.pm
+++ b/lib/PublicInbox/Msgmap.pm
@@ -31,9 +31,6 @@ sub new_file {
        my $self = bless { filename => $f }, $class;
        my $dbh = $self->{dbh} = PublicInbox::Over::dbh_new($self, $rw);
        if ($rw) {
-               # TRUNCATE reduces I/O compared to the default (DELETE)
-               $dbh->do('PRAGMA journal_mode = TRUNCATE');
-
                $dbh->begin_work;
                create_tables($dbh);
                $self->created_at(time) unless $self->created_at;

diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm
index a2f0411776ac9f2b413aedbeb6daea72aa3273fc..3e74b7a6f3ccd9372d67078fab01a38ebc92aa05 100644 (file)
--- a/lib/PublicInbox/Over.pm
+++ b/lib/PublicInbox/Over.pm
@@ -42,7 +42,28 @@ sub dbh_new {
                $st = pack('dd', $st[0], $st[1]);
        } while ($st ne $self->{st} && $tries++ < 3);
        warn "W: $f: .st_dev, .st_ino unstable\n" if $st ne $self->{st};
-       $dbh->do('PRAGMA synchronous = OFF') if ($rw // 0) > 1;
+
+       if ($rw) {
+               # TRUNCATE reduces I/O compared to the default (DELETE).
+               #
+               # Do not use WAL by default since we expect the case
+               # where any users may read via read-only daemons
+               # (-httpd/-imapd/-nntpd); but only a single user has
+               # write permissions for -watch/-mda.
+               #
+               # Read-only WAL support in SQLite 3.22.0 (2018-01-22)
+               # doesn't do what we need: it is only intended for
+               # immutable read-only media (e.g. CD-ROM) and not
+               # usable for our use case described above.
+               #
+               # If an admin is willing to give read-only daemons R/W
+               # permissions; they can enable WAL manually and we will
+               # respect that by not clobbering it.
+               my $jm = $dbh->selectrow_array('PRAGMA journal_mode');
+               $dbh->do('PRAGMA journal_mode = TRUNCATE') if $jm ne 'wal';
+
+               $dbh->do('PRAGMA synchronous = OFF') if $rw > 1;
+       }
        $dbh;
 }
 

diff --git a/lib/PublicInbox/OverIdx.pm b/lib/PublicInbox/OverIdx.pm
index d42d6fe76a273f5ed5f839a68a5388efc05eda1f..9f4a56fbd4861b9978a3a3146317a42d597a95f1 100644 (file)
--- a/lib/PublicInbox/OverIdx.pm
+++ b/lib/PublicInbox/OverIdx.pm
@@ -23,11 +23,6 @@ sub dbh_new {
        my ($self) = @_;
        my $dbh = $self->SUPER::dbh_new($self->{-no_fsync} ? 2 : 1);
 
-       # TRUNCATE reduces I/O compared to the default (DELETE)
-       # We do not use WAL since we're optimized for read-only ops,
-       # (and read-only requires SQLite 3.22.0 (2018-01-22)).
-       $dbh->do('PRAGMA journal_mode = TRUNCATE');
-
        # 80000 pages (80MiB on SQLite <3.12.0, 320MiB on 3.12.0+)
        # was found to be good in 2018 during the large LKML import
        # at the time.  This ought to be configurable based on HW

diff --git a/t/over.t b/t/over.t
index 07672aa70a2e56c34ba3e83390f2899b7b3b6da8..8bf64ecb30aa1e73e1ccf4a2716d920aa4277d1f 100644 (file)
--- a/t/over.t
+++ b/t/over.t
@@ -65,4 +65,14 @@ isnt($over->max, 0, 'max is non-zero');
 
 $over->rollback_lazy;
 
+# L<perldata/"Version Strings">
+my $v = eval 'v'.$over->{dbh}->{sqlite_version};
+SKIP: {
+       skip("no WAL in SQLite version $v < 3.7.0", 1) if $v lt v3.7.0;
+       $over->{dbh}->do('PRAGMA journal_mode = WAL');
+       $over = PublicInbox::OverIdx->new("$tmpdir/over.sqlite3");
+       is($over->connect->selectrow_array('PRAGMA journal_mode'), 'wal',
+               'WAL journal_mode not clobbered if manually set');
+}
+
 done_testing();