From 5f6a0d2342323541e44ff2f1e7329053d0263800 Mon Sep 17 00:00:00 2001 From: Eric Wong Date: Tue, 25 Aug 2020 03:02:47 +0000 Subject: 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: 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. --- lib/PublicInbox/Over.pm | 23 ++++++++++++++++++++++- 1 file changed, 22 insertions(+), 1 deletion(-) (limited to 'lib/PublicInbox/Over.pm') diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm index a2f04117..3e74b7a6 100644 --- 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; } -- cgit v1.2.3-24-ge0c7