The Fastest Query Is the One You Don't Run

This is the third post in the Campfire series. The last one took a proxy out of the path. This one is about a check that was cheap on its own and expensive because of how often it ran and where.

The numbers were measured during development, one fix at a time, on an Intel i9-11900K running Linux with the app on four CPUs. They aren’t the final benchmark.

The cache and its question

Campfire caches whole responses, and smaller pieces of data called record snapshots, in memory. A cached entry is good until anything is committed to the database. To know when that happens, the Rails app runs PRAGMA data_version on a connection it keeps open for the purpose. SQLite returns a number that changes whenever another connection has committed since this connection last looked. If the number moved, the cache drops its entries.

That’s a sensible design, and the C# port copied it. The trouble was the count. The room page asked the question about five times per request: once to capture the version, once for each of three record snapshots, and once more when reading the cached response. At tens of thousands of requests a second, that’s a lot of queries for one number.

Step 1: a lock held across a query

The first profile showed the process using only two of its four cores. Lock contention explained why. The cache’s mutex saw 32,700 contentions in 10 seconds, with 4.5 seconds of total waiting.

Every check took the mutex, and inside it, prepared PRAGMA data_version from scratch, ran it, and returned. So every request queued several times on one lock while SQLite parsed a statement. The first fix was the smallest one: prepare the command once, when the observer connection opens, and reuse it. Same lock, same answer, less time holding it.

That change landed together with the allocation fix for empty request bodies, and the two together added 7 to 12% on the read pages.

Step 2: move the query out of the lock

Preparing once made the query cheaper, but every request still went through one lock and one connection. The C port of Campfire showed a better shape: each caller runs the check on its own pooled read-only connection, outside the lock, and only touches the shared state when the answer says something changed. If an observer sees its data_version move, it bumps a process-wide generation number and the cache drops its entries, as Rails does. The mutex now guards only the entries and that number.

This added about 6 to 10% on the messages, sidebar and search pages. The room page didn’t move on the harness, and a separate run with a lighter load generator measured it at +6.5%.

Step 3: read what SQLite reads

After the first two fixes, the check was still the largest single cost in a request. At 33,600 requests a second, ResponseCache.Current() was 9,856 of about 28,000 CPU samples on the request path, about 35%. Most of it was SQLite stepping and resetting the statement. PRAGMA data_version opens and closes a read transaction every time, and that’s real work even when the answer is “nothing changed”.

So the question became: where does SQLite get the answer? In WAL mode, every connection shares a small file next to the database, the -shm file, and its first bytes hold the WAL-index header. Every commit rewrites that 48-byte header. When a connection starts a read transaction, it compares the header with the copy it saw last. If they differ, something was committed, and that’s exactly when data_version moves.

The cache now maps the -shm file read-only with MemoryMappedFile and compares those 48 bytes itself, under the mutex. It uses the same check SQLite’s own readers use against a torn read. A commit writes the header twice, so the reader copies both and treats copies that don’t match, or a header that isn’t initialized yet, as “changed”. A false “changed” only costs a cache miss, so that’s the safe direction to be wrong in. The layout is SQLite’s documented file format, not an internal detail.

A few things made this safe to ship:

  • It still sees commits from other processes, as the pragma does. The C port uses an in-process counter instead, which is faster but misses writes from outside the process, and Rails doesn’t miss those.
  • The read-only connection behind the mapping stays open. It holds the lock that keeps the database in WAL mode and the -shm file in place.
  • A database that isn’t in WAL mode, or a -shm that can’t be mapped, falls back to the pooled pragma from step 2.
  • Tests assert that the header path is the one in use, and that 50 consecutive commits from another connection each move the version.

After the change, Current() dropped from 9,856 samples to 1,666, and almost all of those were waiting to take the mutex, not reading anything.

The result

Measured during development, before and after step 3 alone:

Route Quick A/B Benchmark harness
Room page +16.2% +24.4%
Messages page +17.4% +15.3%
Sidebar +14.5% +20.1%
Search +15.8% +14.3%
Post a message −0.5% +3.9%

The quick A/B used a lighter load generator in three alternating rounds; the harness ran three rounds of 8 seconds per route. Posting is limited by the single writer, so it shouldn’t move, and it didn’t beyond noise.

The lesson

A query that takes a few microseconds is cheap until you multiply it. Here it was several queries per request, at tens of thousands of requests a second, inside a lock every request needed. Each fix removed a different multiplier: the statement parse, the lock held during I/O, and finally the query itself.

When you have a check like this, ask what the database itself looks at to answer it. Often it’s a counter or a header you can read directly, and reading it costs a memory access instead of a round trip.

Check your own app

  • Count how many times one request runs the same “has anything changed?” check. A cache that validates on every read can easily do it several times.
  • Look for I/O inside a lock: a query, a file read, a network call. Move it out, or make what’s inside the lock a memory read.
  • Prepare statements you run on every request once, and reuse them.
  • In a contention profile, compare the time spent waiting with the time spent working. 4.5 seconds of waiting in 10 seconds is a lock that’s serializing your app.

Try it in LINQPad

This script asks SQLite “has anything changed?” both ways: with the prepared pragma under a lock, and by reading the 48-byte header from the mapped -shm file under the same lock. It also checks that a commit moves the header. Add the Microsoft.Data.Sqlite NuGet package (F4).

var path = Path.Combine(Path.GetTempPath(), "version-demo.sqlite3");
SqliteConnection.ClearAllPools();
foreach (var file in new[] { path, path + "-wal", path + "-shm" }) File.Delete(file);

using var writer = Open();
Exec(writer, "PRAGMA journal_mode = WAL");
Exec(writer, "CREATE TABLE messages (id INTEGER PRIMARY KEY, body TEXT)");

// The question every cached request asks: has anything been committed since I last looked?
using var observer = Open();
using var pragma = observer.CreateCommand();
pragma.CommandText = "PRAGMA data_version";
pragma.Prepare();
var gate = new object();

// The same answer without SQL: the 48-byte WAL-index header at the start of the -shm file.
using var shm = MemoryMappedFile.CreateFromFile(
    new FileStream(path + "-shm", FileMode.Open, FileAccess.Read, FileShare.ReadWrite | FileShare.Delete),
    null, 0, MemoryMappedFileAccess.Read, HandleInheritability.None, leaveOpen: false);
using var view = shm.CreateViewAccessor(0, 96, MemoryMappedFileAccess.Read);
var header = new byte[48];

var before = Header();
Exec(writer, "INSERT INTO messages (body) VALUES ('hello')");
new { HeaderMovedAfterCommit = !before.SequenceEqual(Header()) }.Dump();

new
{
    PragmaUnderLock = Time(() => { lock (gate) pragma.ExecuteScalar(); }),
    HeaderUnderLock = Time(() => { lock (gate) view.ReadArray(0, header, 0, 48); }),
}.Dump("Nanoseconds per check, 4 threads at once");

SqliteConnection Open()
{
    var connection = new SqliteConnection($"Data Source={path};Pooling=False");
    connection.Open();
    return connection;
}

void Exec(SqliteConnection connection, string sql)
{
    using var command = connection.CreateCommand();
    command.CommandText = sql;
    command.ExecuteNonQuery();
}

byte[] Header()
{
    var copy = new byte[48];
    view.ReadArray(0, copy, 0, 48);
    return copy;
}

double Time(Action check, int threads = 4, int perThread = 100_000)
{
    check();
    var clock = Stopwatch.StartNew();
    Parallel.For(0, threads, new ParallelOptions { MaxDegreeOfParallelism = threads }, _ =>
    {
        for (var i = 0; i < perThread; i++) check();
    });
    return Math.Round(clock.Elapsed.TotalNanoseconds / (threads * perThread));
}

You’ll need System.IO.MemoryMappedFiles and System.Threading.Tasks in the query’s namespaces. On my Windows machine the pragma under the lock took about 3,500 ns per check and the header read about 70 ns. This demo skips the torn-read check and the fallbacks, so treat it as a measurement, not as code to copy. The real version is in ResponseCache.cs.

The next post is about two log lines per request that nobody was reading.

csharp dotnet performance sqlite caching locks memory mapped files linqpad