sqlite-wal-mode testing

00-sqlite-wal-mode-testing.md

Environment Setup

1. Python Version Check

   python -V

   Python 3.12.1

2. FastAPI and Uvicorn Installation

   poetry add fastapi
   poetry add uvicorn

3. Run FastAPI Application

   poetry run uvicorn main:app --workers 4

Load Testing

1. Locust Installation

   poetry add locust

2. Locust Execution

   poetry run locust -f locustfile.py --headless -u 10 -r 1 --run-time 1m --host http://localhost:8000

3. Benchmark Script Execution

   poetry run python run_benchmark.py 10

WAL Mode Enablement

1. WAL Mode Enablement

   sqlite3 test.db

   PRAGMA journal_mode=WAL;

Load Test Results

1. Without WAL Mode

   sqlite3 test.db
   PRAGMA journal_mode=DELETE;

   poetry run python run_benchmark.py 5

   Initial DB count: 690249
   running: locust -f locustfile.py --headless -u 1000 -r 500 --run-time 5s 
   num_requests: 3135
   num_reqs_per_sec: 627.0
   num_failures: 0
   AssertionError: NG: actual_request_count=3135 but got 3154

2. With WAL Mode

   sqlite3 test.db
   PRAGMA journal_mode=WAL;

   locust -f locustfile.py --headless -u 8000 -r 2000 --run-time 5s 

Litestream Introduction

1. Litestream Configuration

   dbs:
     - path: test.db
       replicas:
         - disable: true
           url: file:///tmp/test.db

2. Litestream Startup

   litestream replicate -config litestream.yml

litestream.yml

dbs:
  - path: test.db
    replicas:
      - disable: true
        url: file:///tmp/test.db

locustfile.py

from locust import HttpUser, TaskSet, task, between

class UserBehavior(TaskSet):
    @task
    def write_data(self):
        self.client.post("/write", json={"data": "test_data"})

class WebsiteUser(HttpUser):
    tasks = [UserBehavior]
    wait_time = between(1, 2)

main.py

from pydantic import BaseModel
from contextlib import asynccontextmanager
import logging
from fastapi import FastAPI
import sqlite3

app = FastAPI()
logger = logging.getLogger("uvicorn")


def get_db():
    conn = sqlite3.connect("test.db")
    return conn


def init_db():
    conn = get_db()
    conn.execute("CREATE TABLE IF NOT EXISTS test (data TEXT)")
    conn.execute("CREATE TABLE IF NOT EXISTS request_count (count INTEGER)")
    conn.commit()
    conn.close()


@asynccontextmanager
async def lifespan(app: FastAPI):
    init_db()
    yield


class DataModel(BaseModel):
    data: str


@app.post("/write")
async def write_data(data: DataModel):
    conn = get_db()
    try:
        cursor = conn.cursor()
        cursor.execute("INSERT INTO test (data) VALUES (?)", (data.data,))
        conn.commit()
        logger.info(f"Data written to the database: {data.data}")
    except Exception as e:
        conn.rollback()
        logger.error(f"Error writing data to the database: {e}")
    finally:
        conn.close()
    return {"message": "Data written"}


@app.get("/count")
async def count_data():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM test")
    count = cursor.fetchone()[0]
    conn.close()
    return {"count": count}

run_benchmark.py

import subprocess
import sqlite3
import json
import sys


def get_db_count():
    conn = sqlite3.connect("test.db")
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM test")
    count = cursor.fetchone()[0]
    conn.close()
    return count


def get_actual_request_count_from_json(json_output, run_time):
    data = json.loads(json_output)
    for entry in data:
        if entry["name"] == "/write":
            print(f"num_requests: {entry['num_requests']}")
            print(f"num_reqs_per_sec: {entry['num_requests']/run_time}")
            print(f"num_failures: {entry['num_failures']}")
            assert int(entry['num_failures']) == 0, "num_failures is not 0"
            
            return entry["num_requests"]
    raise ValueError("Request Count not found in the JSON output")


def run_benchmark(user_count, spawn_rate, run_time):
    print(
        f"running: locust -f locustfile.py --headless -u {user_count} -r {spawn_rate} --run-time {run_time} --host http://localhost:8000"
    )
    result = subprocess.run(
        [
            "poetry",
            "run",
            "locust",
            "-f",
            "locustfile.py",
            "--headless",
            "-u",
            str(user_count),
            "-r",
            str(spawn_rate),
            "--run-time",
            run_time,
            "--host",
            "http://localhost:8000",
            "--json",
            "--csv", "locust_result",
        ],
        capture_output=True,
        text=True,
    )
    return result.stdout


def main():
    initial_count = get_db_count()
    print(f"Initial DB count: {initial_count}")
    run_time = int(sys.argv[1])
    json_output = run_benchmark(user_count=200, spawn_rate=100, run_time=str(run_time)+'s')

    final_count = get_db_count()
    actual_request_count = get_actual_request_count_from_json(json_output, run_time)

    assert (
        initial_count + actual_request_count == final_count
    ), f"NG: actual_request_count={actual_request_count} but got {final_count - initial_count}"
    print("OK")


if __name__ == "__main__":
    main()