Stress-Testing SQLite Concurrency in PHP 8: The `checkv0.1` Beta Experiment

SQLite has a reputation for being lightweight, serverless, and lightning-fast for single-user applications or small-scale web apps. But what happens when you throw high concurrency at it—say, 50 simultaneous processes hammering a single database with readers, writers, updaters, and deleters? And more importantly, how does turning on Write-Ahead Logging (WAL) change the game under heavy contention?

To find out, I built checkv0.1, a robust concurrency and benchmarking script written in PHP 8. It uses pcntl_fork() to simulate a chaotic multi-process environment, tracking everything from execution time and memory delta to CPU cycles and system RAM.

Here is a breakdown of how the test script works, what it measures, and why it is an essential diagnostic tool for modern PHP applications.

---

What Does checkv0.1 Do?

The script simulates a heavy-load production environment right from your CLI. Instead of relying on sequential requests, it forks 50 concurrent child processes divided evenly across four distinct operational workloads:

• Writers (25%): Insert new records into the test table inside a transaction.
• Readers (25%): Query row counts to measure read performance under concurrent write pressure.
• Updaters (25%): Randomly select and update existing records using subquery locking.
• Deleters (25%): Randomly purge records from the table concurrently.

It runs this gauntlet twice: first under the default journal mode, and then under WAL (Write-Ahead Logging) mode, finishing up with a detailed performance diff comparing both runs.

---

Key Engineering Highlights in the Code

1. Robust Process Forking with pcntl

Using PHP's Process Control (pcntl) extension, the script spawns 50 independent child processes simultaneously. Each process executes its assigned role (reader, writer, updater, or deleter), measures its own performance metrics, and exits cleanly while the parent process waits via pcntl_waitpid().

2. Sane PDO Defaults & Busy Timeouts

One of the quickest ways to crash a concurrent SQLite script is immediate failure on lock contention. The script configures PDO with a custom busy timeout:

$pdo->setAttribute(PDO::ATTR_TIMEOUT, (int) (BUSY_TIMEOUT_MS / 1000));

Instead of throwing a fatal database is locked error instantly, worker processes will wait up to 5 seconds for active locks to clear, mimicking real-world connection queuing.

3. Comprehensive Telemetry (measure())

Every single operation is wrapped in a high-precision telemetry function that records:

• Execution Duration: Precise timing down to microseconds using microtime(true).
• Memory Footprint: Tracks memory consumption deltas before and after execution.
• CPU Cycle Estimation: Automatically parses /proc/cpuinfo to estimate clock frequencies and calculate CPU cycles utilized per core.
• System RAM Check: Monitors available host memory via /proc/meminfo to ensure the test machine isn't running out of resources.

4. Safe Aggregation with File Locking

Because 50 child processes write their metrics to shared log files concurrently, data corruption is a real risk. The script prevents race conditions using exclusive file locks:

file_put_contents(strtolower($category) . "_times.log", $logLine, FILE_APPEND | LOCK_EX);

---

Code

<?php
/**
 * SQLite Concurrency Test in PHP 8
 * Run: php sqlite_concurrency_test.php
 *
 * Fixed version — see accompanying notes for the list of bugs corrected.
 */
declare(strict_types=1);

const DB_FILE = __DIR__ . '/test_concurrency.db';
const NUM_PROCESSES = 50;   // Total concurrent processes
const WRITE_RATIO  = 0.25;  // 25% writers
const READ_RATIO   = 0.25;  // 25% readers
const UPDATE_RATIO = 0.25;  // 25% updaters
const DELETE_RATIO = 0.25;  // 25% deleters
const BUSY_TIMEOUT_MS = 5000; // wait up to 5s for locks instead of failing immediately

/** Ensure required extensions exist before we do anything else */
function check_requirements(): void {
    if (!extension_loaded('pcntl')) {
        die("Fatal: the 'pcntl' extension is required (and is not available on Windows or most non-CLI SAPIs).\n");
    }
    if (!extension_loaded('pdo_sqlite')) {
        die("Fatal: the 'pdo_sqlite' extension is required.\n");
    }
}

/** Get real-time CPU info */
function getCpuInfo(): array {
    $cpuinfo = file('/proc/cpuinfo');
    $cores = 0;
    $mhzSum = 0.0;
    $mhzCount = 0;
    foreach ($cpuinfo as $line) {
        if (strpos($line, 'processor') === 0) {
            $cores++;
        }
        if (strpos($line, 'cpu MHz') === 0) {
            $parts = explode(':', $line);
            $mhzSum += floatval(trim($parts[1]));
            $mhzCount++;
        }
    }
    // Average across all cores instead of just keeping the last one seen,
    // since per-core clock speed can vary with frequency scaling.
    $mhz = $mhzCount > 0 ? $mhzSum / $mhzCount : 0.0;
    $hz = $mhz * 1e6;
    return ['cores' => max($cores, 1), 'clock_hz' => $hz];
}

/** Get real-time RAM info */
function getRamInfo(): array {
    $meminfo = file('/proc/meminfo');
    $total = 0;
    $avail = 0;
    foreach ($meminfo as $line) {
        if (strpos($line, 'MemTotal:') === 0) {
            $parts = preg_split('/\s+/', $line);
            $total = intval($parts[1]) * 1024;
        }
        if (strpos($line, 'MemAvailable:') === 0) {
            $parts = preg_split('/\s+/', $line);
            $avail = intval($parts[1]) * 1024;
        }
    }
    return ['total_bytes' => $total, 'available_bytes' => $avail];
}

/** Open a PDO connection with sane defaults for a concurrency test */
function open_db(): PDO {
    $pdo = new PDO('sqlite:' . DB_FILE);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // Without this, SQLite fails instantly with "database is locked" under
    // any real contention instead of waiting briefly for the lock to clear.
    $pdo->setAttribute(PDO::ATTR_TIMEOUT, (int) (BUSY_TIMEOUT_MS / 1000));
    return $pdo;
}

/** Initialize the database */
function init_db(): void {
    // Remove the main db file AND any leftover WAL sidecar files from a
    // previous run; otherwise a stale -wal/-shm file can leave the DB in an
    // inconsistent state for the next test.
    foreach ([DB_FILE, DB_FILE . '-wal', DB_FILE . '-shm'] as $f) {
        if (file_exists($f)) {
            unlink($f);
        }
    }
    $pdo = new PDO('sqlite:' . DB_FILE);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)");
    // seed a few rows so updaters/deleters have something to act on immediately
    $stmt = $pdo->prepare("INSERT INTO test (value) VALUES (:val)");
    for ($i = 0; $i < 10; $i++) {
        $stmt->execute([':val' => "seed-$i"]);
    }
    $pdo = null;
}

/**
 * Measure wrapper: time + memory + system RAM.
 * $category is the aggregation bucket used for the log filename
 * (e.g. "reader"); $label is only used for the per-process console line.
 */
function measure(string $category, string $label, callable $fn, array $cpu): void {
    $start = microtime(true);
    $memStart = memory_get_usage(true);
    $failed = false;
    $error = '';

    try {
        $fn();
    } catch (Throwable $e) {
        $failed = true;
        $error = $e->getMessage();
    }

    $elapsed = microtime(true) - $start;
    $memEnd = memory_get_usage(true);

    $ms = $elapsed * 1000;
    $cycles_per_core = $elapsed * $cpu['clock_hz'];
    $cycles_total = $cycles_per_core * $cpu['cores'];

    $ram = getRamInfo();

    $status = $failed ? "FAILED ($error)" : "OK";
    echo "[$label] Status: $status | Duration: " . number_format($ms, 3) . " ms | "
        . number_format($elapsed, 6) . " s | "
        . number_format($cycles_per_core) . " cycles/core | "
        . number_format($cycles_total) . " cycles total | "
        . "Mem Start: $memStart | Mem End: $memEnd | Mem Used: " . ($memEnd - $memStart) . " bytes | "
        . "System RAM Avail: " . number_format($ram['available_bytes'] / (1024 * 1024), 2) . " MB\n";

    // Bucketed by category (not by per-process label) so print_summary can
    // actually find and aggregate these files. LOCK_EX prevents interleaved
    // writes from the many concurrent child processes sharing this file.
    $logLine = $elapsed . "," . ($memEnd - $memStart) . "," . ($failed ? 1 : 0) . PHP_EOL;
    file_put_contents(strtolower($category) . "_times.log", $logLine, FILE_APPEND | LOCK_EX);
}

/** Reader */
function reader(int $id, array $cpu): void {
    measure("reader", "Reader-$id", function() {
        $pdo = open_db();
        $pdo->query("SELECT COUNT(*) FROM test")->fetchColumn();
    }, $cpu);
}

/** Writer */
function writer(int $id, array $cpu): void {
    measure("writer", "Writer-$id", function() use ($id) {
        $pdo = open_db();
        $pdo->beginTransaction();
        $stmt = $pdo->prepare("INSERT INTO test (value) VALUES (:val)");
        $stmt->execute([':val' => "Process-$id"]);
        $pdo->commit();
    }, $cpu);
}

/** Updater */
function updater(int $id, array $cpu): void {
    measure("updater", "Updater-$id", function() use ($id) {
        $pdo = open_db();
        $pdo->beginTransaction();
        $stmt = $pdo->prepare("UPDATE test SET value = :val WHERE id = (SELECT id FROM test ORDER BY RANDOM() LIMIT 1)");
        $stmt->execute([':val' => "Updated-$id"]);
        $pdo->commit();
    }, $cpu);
}

/** Deleter */
function deleter(int $id, array $cpu): void {
    measure("deleter", "Deleter-$id", function() {
        $pdo = open_db();
        $pdo->beginTransaction();
        $pdo->exec("DELETE FROM test WHERE id = (SELECT id FROM test ORDER BY RANDOM() LIMIT 1)");
        $pdo->commit();
    }, $cpu);
}

/**
 * Print summary and return the raw stats (keyed by category, plus "total")
 * so the caller can diff this run against another one later.
 */
function print_summary(float $totalElapsed, array $cpu): array {
    $categories = ["reader", "writer", "updater", "deleter"];
    $stats = [];

    echo "\n=== Summary ===\n";
    echo str_pad("Metric", 20) . str_pad("Milliseconds", 15) . str_pad("Seconds", 15)
        . str_pad("Cycles/Core", 20) . str_pad("Cycles Total", 20)
        . str_pad("Avg Mem Used (B)", 20) . "Failures\n";
    echo str_repeat("-", 120) . "\n";

    foreach ($categories as $cat) {
        $file = "{$cat}_times.log";
        if (!file_exists($file)) {
            continue;
        }

        $lines = array_filter(array_map('trim', file($file)), fn($l) => $l !== '');
        $times = [];
        $mems = [];
        $failures = 0;
        foreach ($lines as $line) {
            $parts = explode(",", $line);
            if (count($parts) < 2) {
                continue; // skip malformed rows defensively
            }
            $times[] = (float) $parts[0];
            $mems[] = (int) $parts[1];
            if (isset($parts[2]) && (int) $parts[2] === 1) {
                $failures++;
            }
        }

        if (count($times) === 0) {
            @unlink($file);
            continue; // avoid division by zero if the file was empty
        }

        $avgTime = array_sum($times) / count($times);
        $avgMem = array_sum($mems) / count($mems);

        echo str_pad("Avg " . ucfirst($cat), 20)
            . str_pad(number_format($avgTime * 1000, 3), 15)
            . str_pad(number_format($avgTime, 6), 15)
            . str_pad(number_format($avgTime * $cpu['clock_hz']), 20)
            . str_pad(number_format($avgTime * $cpu['clock_hz'] * $cpu['cores']), 20)
            . str_pad(number_format($avgMem), 20)
            . $failures . "\n";

        $stats[$cat] = [
            'avg_time_s'   => $avgTime,
            'avg_mem_b'    => $avgMem,
            'failures'     => $failures,
            'sample_count' => count($times),
        ];

        @unlink($file);
    }

    echo str_pad("Total Test", 20)
        . str_pad(number_format($totalElapsed * 1000, 3), 15)
        . str_pad(number_format($totalElapsed, 6), 15)
        . str_pad(number_format($totalElapsed * $cpu['clock_hz']), 20)
        . str_pad(number_format($totalElapsed * $cpu['clock_hz'] * $cpu['cores']), 20)
        . str_pad("-", 20)
        . "-" . "\n";

    $stats['total'] = ['avg_time_s' => $totalElapsed];

    return $stats;
}

/**
 * Print "WAL enabled - WAL disabled" diff for Milliseconds and Seconds only.
 * Positive Diff means WAL enabled was slower than WAL disabled; negative
 * means WAL enabled was faster.
 */
function print_comparison(array $statsDisabled, array $statsEnabled): void {
    echo "\n=== Diff: WAL Enabled - WAL Disabled ===\n";
    echo str_pad("Metric", 20)
        . str_pad("WAL Disabled (ms)", 20) . str_pad("WAL Enabled (ms)", 20) . str_pad("Diff (ms)", 16)
        . str_pad("WAL Disabled (s)", 18) . str_pad("WAL Enabled (s)", 18) . str_pad("Diff (s)", 16) . "Diff (%)\n";
    echo str_repeat("-", 148) . "\n";

    $keys = array_unique(array_merge(array_keys($statsDisabled), array_keys($statsEnabled)));
    usort($keys, function ($a, $b) {
        if ($a === 'total') return 1;
        if ($b === 'total') return -1;
        return strcmp($a, $b);
    });

    foreach ($keys as $key) {
        $disabled = $statsDisabled[$key]['avg_time_s'] ?? null;
        $enabled = $statsEnabled[$key]['avg_time_s'] ?? null;
        if ($disabled === null || $enabled === null) {
            continue; // category missing in one run — skip rather than compare against nothing
        }

        $disabledMs = $disabled * 1000;
        $enabledMs = $enabled * 1000;
        $diffMs = $enabledMs - $disabledMs;
        $diffS = $enabled - $disabled;
        $diffPct = $disabled != 0.0 ? ($diffS / $disabled) * 100 : 0.0;

        $label = $key === 'total' ? 'Total Test' : 'Avg ' . ucfirst($key);

        echo str_pad($label, 20)
            . str_pad(number_format($disabledMs, 3), 20)
            . str_pad(number_format($enabledMs, 3), 20)
            . str_pad(($diffMs >= 0 ? '+' : '') . number_format($diffMs, 3), 16)
            . str_pad(number_format($disabled, 6), 18)
            . str_pad(number_format($enabled, 6), 18)
            . str_pad(($diffS >= 0 ? '+' : '') . number_format($diffS, 6), 16)
            . ($diffPct >= 0 ? '+' : '') . number_format($diffPct, 2) . "%\n";
    }
}

/** Run test. Returns the stats from print_summary() for later comparison. */
function run_test(bool $useWal = false): array {
    $cpu = getCpuInfo();
    echo "\n=== Running Test ===\n";

    init_db();

    if ($useWal) {
        $pdo = new PDO('sqlite:' . DB_FILE);
        $pdo->exec("PRAGMA journal_mode = WAL;");
        $pdo = null;
        echo "WAL mode enabled.\n";
    }

    $pids = [];
    $startTime = microtime(true);

    for ($i = 0; $i < NUM_PROCESSES; $i++) {
        $pid = pcntl_fork();
        if ($pid === -1) {
            die("Could not fork process\n");
        } elseif ($pid === 0) {
            $ratio = $i / NUM_PROCESSES;
            if ($ratio < WRITE_RATIO) {
                writer($i, $cpu);
            } elseif ($ratio < WRITE_RATIO + READ_RATIO) {
                reader($i, $cpu);
            } elseif ($ratio < WRITE_RATIO + READ_RATIO + UPDATE_RATIO) {
                updater($i, $cpu);
            } else {
                deleter($i, $cpu);
            }
            exit(0);
        } else {
            $pids[] = $pid;
        }
    }

    foreach ($pids as $pid) {
        pcntl_waitpid($pid, $status);
    }

    $elapsed = microtime(true) - $startTime;
    $stats = print_summary($elapsed, $cpu);

    $ram = getRamInfo();
    echo "\nDetected CPU cores: {$cpu['cores']}, Clock: " . number_format($cpu['clock_hz']) . " Hz\n";
    echo "System RAM total: " . number_format($ram['total_bytes'] / (1024 * 1024), 2)
        . " MB, Available: " . number_format($ram['available_bytes'] / (1024 * 1024), 2) . " MB\n";

    return $stats;
}

// Run tests
check_requirements();

echo "Test 1: Default mode (DELETE journal / WAL disabled)\n";
$statsWalDisabled = run_test(false);

echo "\nTest 2: WAL mode (WAL enabled)\n";
$statsWalEnabled = run_test(true);

// Diff: WAL enabled - WAL disabled, for Milliseconds and Seconds.
print_comparison($statsWalDisabled, $statsWalEnabled);

How to Run It

To run checkv0.1 on your development machine, keep the following prerequisites in mind:

1. CLI Environment: You must run this via the PHP CLI (it will not run on Windows or non-CLI SAPIs because it depends on the pcntl extension).
2. Extensions: Ensure pcntl and pdo_sqlite are enabled in your php.ini.

Save the code as sqlite_concurrency_test.php and execute it in your terminal:

php sqlite_concurrency_test.php

---

What to Look For in the Results

When you execute the script, it outputs a per-process log, followed by an aggregated Summary Table for default mode, another for WAL mode, and finally a Diff Table comparing the two.

• The Default Run: Expect higher contention bottlenecks. Without WAL, readers block writers and vice-versa because SQLite uses a rollback journal, locking the entire database file during writes.
• The WAL Run: Write-Ahead Logging allows multiple readers to operate concurrently while a write is underway (since writers append to a separate -wal log file). You should see significantly fewer lock timeouts and improved throughput, especially for read-heavy or mixed workloads.

Have you experimented with SQLite concurrency in your PHP applications, or do you prefer switching to MySQL/PostgreSQL once user traffic scales up? Let me know your thoughts or optimizations in the comments below!