Tối ưu hiệu năng SQLite: Hướng dẫn Production về WAL Mode, tinh chỉnh PRAGMA và xử lý Concurrency

Table of Contents

Bối cảnh & Lý do

Sáu tháng trước, đội ngũ của chúng tôi triển khai một dịch vụ tiếp nhận telemetry gọn nhẹ, xử lý khoảng 450 request mỗi giây. Chúng tôi chọn SQLite để giữ cho khâu vận hành đơn giản tối đa: không cần container database độc lập, không độ trễ mạng (zero network hops) và chi phí vận hành gần như bằng không. Tuy nhiên, chỉ trong vòng 48 giờ, log hệ thống bắt đầu ngập tràn một lỗi quen thuộc: sqlite3.OperationalError: database is locked.

Các kỹ sư thường xem nhẹ SQLite, coi nó như một món đồ chơi hoặc công cụ mock chỉ dùng để test in-memory. Định kiến đó hoàn toàn sai lầm. SQLite đang vận hành các hệ điều hành di động, bảng điều khiển ô tô, trình duyệt web và các edge proxy có throughput cực cao. Nút thắt cổ chai hiếm khi nằm ở chính storage engine. Vấn đề là SQLite mặc định sử dụng các cấu hình cực kỳ thận trọng, vốn được thiết kế cho các vi điều khiển nhúng cấu hình thấp từ năm 2004, chứ không phải cho các máy chủ đa nhân hiện đại chạy ổ cứng NVMe SSD.

Theo mặc định, SQLite sử dụng chế độ rollback journal (DELETE hoặc PERSIST). Mỗi thao tác ghi (write) đều khóa độc quyền toàn bộ file database. Reader không thể đọc khi writer đang commit, và writer không thể ghi khi có bất kỳ reader nào đang giữ một transaction mở. Khi ứng dụng của bạn nhận 30 request đồng thời qua các worker, các query sẽ bị dồn hàng đợi, chạm ngưỡng timeout mặc định 0 mili-giây của SQLite và văng lỗi.

Để khắc phục vấn đề này, bạn chỉ cần thực hiện 3 điều chỉnh thực tế: chuyển sang chế độ Write-Ahead Logging (WAL) để tách biệt reader khỏi writer, tinh chỉnh các cờ PRAGMA bộ nhớ và thiết lập connection timeout lớn hơn 0 trong mã nguồn ứng dụng.

Cài đặt

Các image Linux tiêu chuẩn và base container tối giản thường đi kèm các gói SQLite đã lỗi thời. Nếu muốn có query plan chính xác, các hàm JSON hiện đại và công cụ checkpoint đáng tin cậy, bạn cần đảm bảo bản build của mình tương đối mới.

Trên các hệ thống Debian hoặc Ubuntu LTS, cài đặt CLI và header files:

sudo apt update
sudo apt install -y sqlite3 libsqlite3-dev

Trên các container Alpine Linux:

apk add --no-cache sqlite sqlite-dev

Tiếp theo, hãy kiểm tra xem bản binary của bạn có hỗ trợ multi-threading hay không:

sqlite3 --version
sqlite3 :memory: "PRAGMA compile_options;" | grep -E "THREADSAFE|ENABLE_JSON1|ENABLE_RTREE"

Hãy tìm dòng THREADSAFE=1 (serialized) hoặc THREADSAFE=2 (multi-threaded). Cả hai thiết lập này đều cho phép các thread chạy đồng thời thao tác an toàn trên các connection handle khác nhau mà không gây xung đột dữ liệu.

Cấu hình

Việc tinh chỉnh SQLite bao gồm hai nhóm thiết lập: các cờ ở cấp độ database được lưu trực tiếp vào file header, và các PRAGMA ở cấp độ kết nối cần được thực thi mỗi khi mở một connection mới.

1. Bật Write-Ahead Logging (WAL Mode)

Thay đổi hiệu quả nhất mà bạn có thể áp dụng là chuyển đổi journal mode:

PRAGMA journal_mode = WAL;

Thiết lập này có tính bền vững (persistent). SQLite sẽ ghi trực tiếp vào header của database, do đó các kết nối trong tương lai sẽ tự động kế thừa. Thay vì ghi đè trực tiếp lên các page của database chính, WAL mode sẽ append các thay đổi vào một file đồng hành có đuôi .db-wal. Reader vẫn tiếp tục đọc các page nguyên vẹn từ file chính trong khi writer ghi nối tiếp vào log. Writer không bao giờ block các reader đang hoạt động, và reader cũng không bao giờ block writer.

2. Tinh chỉnh hiệu năng bằng PRAGMA

Các PRAGMA liên quan đến bộ nhớ và đồng bộ hóa chỉ có hiệu lực trong phạm vi của từng kết nối (connection-scoped). Hãy chạy các lệnh sau mỗi khi ứng dụng khởi tạo một connection mới:

-- Giảm số lần gọi fsync nhưng vẫn đảm bảo tính bền vững ACID trong WAL mode
PRAGMA synchronous = NORMAL;

-- Cấp phát 64MB RAM cho page cache (giá trị âm = KiB)
PRAGMA cache_size = -64000;

-- Lưu trữ bảng tạm, index và sort buffer trực tiếp trên RAM
PRAGMA temp_store = MEMORY;

-- Map tối đa 256MB database vào không gian địa chỉ bộ nhớ ảo
PRAGMA mmap_size = 268435456;

-- Chờ tối đa 5000ms để giải phóng lock trước khi trả về lỗi
PRAGMA busy_timeout = 5000;

-- Bắt buộc kiểm tra tính toàn vẹn tham chiếu (foreign key) khi insert và delete
PRAGMA foreign_keys = ON;

Dưới đây là tác dụng thực tế của các cờ này khi hệ thống chịu tải cao:

synchronous = NORMAL: Ở chế độ rollback journal cổ điển, NORMAL có nguy cơ gây hỏng dữ liệu khi mất điện đột ngột. Nhưng trong WAL mode, nó vẫn đảm bảo tính toàn vẹn dữ liệu trong khi chỉ thực hiện sync xuống ổ đĩa vật lý trong các đợt checkpoint. Trong các bài benchmark của chúng tôi trên instance AWS c6i, throughput ghi đã tăng khoảng 4,2 lần sau khi chuyển từ FULL sang NORMAL.
cache_size = -64000: Bộ nhớ đệm mặc định rất nhỏ: chỉ 2MB (2.000 page, mỗi page 4KB). Cấp phát 64MB cho mỗi connection sẽ giúp giữ các page của table và index luôn sẵn sàng trong RAM, loại bỏ các thao tác đọc đĩa lặp đi lặp lại.
mmap_size: Memory-mapped I/O cho phép Linux kernel map trực tiếp các page vào bộ nhớ userspace. Query sẽ bỏ qua được bước copy dữ liệu trung gian giữa kernel space và application space, tiết kiệm lượng CPU đáng kể với các tác vụ thiên về đọc (read-heavy).
busy_timeout: Khi lượng write tăng đột biến trong chốc lát, các transaction gửi đến sẽ lùi lại và thử lại trong tối đa 5 giây thay vì báo lỗi ngay lập tức.

3. Tích hợp vào ứng dụng (Mô hình kết nối trong Python)

Hãy đóng gói các cấu hình trên vào một connection factory tập trung. Dưới đây là đoạn code mẫu đã được kiểm chứng trên production bằng thư viện chuẩn sqlite3 của Python:

import sqlite3
import contextlib
from pathlib import Path

DB_PATH = Path("/var/lib/app/production.db")

def get_connection(db_file: Path = DB_PATH) -> sqlite3.Connection:
    conn = sqlite3.connect(
        db_file,
        timeout=5.0,
        isolation_level=None,  # Chế độ Autocommit: chúng ta kiểm soát transaction tường minh
        check_same_thread=False
    )
    
    # Áp dụng các PRAGMA ở cấp độ kết nối
    conn.execute("PRAGMA journal_mode = WAL;")
    conn.execute("PRAGMA synchronous = NORMAL;")
    conn.execute("PRAGMA cache_size = -64000;")
    conn.execute("PRAGMA temp_store = MEMORY;")
    conn.execute("PRAGMA mmap_size = 268435456;")
    conn.execute("PRAGMA busy_timeout = 5000;")
    conn.execute("PRAGMA foreign_keys = ON;")
    
    return conn

@contextlib.contextmanager
def transaction(conn: sqlite3.Connection):
    """Chiếm giữ write lock ngay lập tức để ngăn ngừa deadlock."""
    conn.execute("BEGIN IMMEDIATE;")
    try:
        yield conn
        conn.execute("COMMIT;")
    except Exception:
        conn.execute("ROLLBACK;")
        raise

Hãy lưu ý lệnh BEGIN IMMEDIATE. Theo mặc định, SQLite trì hoãn việc lock ghi cho đến câu lệnh INSERT hoặc UPDATE đầu tiên. Nếu hai connection cùng bắt đầu một transaction đọc ở chế độ deferred và sau đó cả hai đồng thời cố nâng cấp lên quyền ghi, chúng sẽ rơi vào trạng thái deadlock. Khởi tạo write transaction với BEGIN IMMEDIATE sẽ loại bỏ hoàn toàn race condition này.

Kiểm chứng & Giám sát

Sau khi triển khai các thay đổi này, hãy kiểm tra lại các giá trị runtime trực tiếp trên file database production của bạn.

1. Kiểm tra PRAGMA đang hoạt động

Truy vấn các cấu hình đang kích hoạt thông qua CLI:

sqlite3 /var/lib/app/production.db << 'EOF'
PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA cache_size;
PRAGMA mmap_size;
PRAGMA busy_timeout;
EOF

Các giá trị kỳ vọng trả về là wal, 1 (tương ứng với NORMAL), -64000, 268435456, và 5000.

2. Giám sát dung lượng file WAL và trạng thái Checkpoint

Quan sát thư mục chứa dữ liệu trong quá trình hệ thống đang hoạt động. Bạn sẽ thấy ba file:

ls -lh /var/lib/app/production.db*

Bên cạnh file production.db, bạn sẽ thấy file production.db-wal và file shared-memory index production.db-shm. Mặc định, SQLite sẽ tự động kích hoạt checkpoint khi file WAL đạt 1.000 page (~4MB). Tuy nhiên, nếu có một read query chưa được đóng và vẫn giữ một snapshot transaction cũ, SQLite sẽ không thể truncate file log. File WAL khi đó có thể phình to lên tới vài gigabyte, buộc các reader phải quét qua lượng log khổng lồ và làm giảm độ trễ đọc từ 0.4ms xuống hơn 25ms.

Kiểm tra tiến trình checkpoint bằng một câu query nhanh:

-- Trả về (busy_flag, total_log_pages, checkpointed_pages)
PRAGMA wal_checkpoint(PASSIVE);

Nếu các tác vụ batch khối lượng lớn đẩy dung lượng WAL vượt quá 50MB, bạn có thể chủ động kích hoạt truncate checkpoint trong các khoảng thời gian hệ thống rảnh:

sqlite3 /var/lib/app/production.db "PRAGMA wal_checkpoint(TRUNCATE);"

3. Benchmark Concurrency: Trước và Sau khi tối ưu

Để đo lường sự khác biệt về throughput, hãy chạy 10 thread đồng thời thực hiện kết hợp cả transaction đọc và ghi:

import time
import threading
from pathlib import Path
from get_db import get_connection, transaction

def test_worker(worker_id: int, total_ops: int):
    conn = get_connection(Path("/tmp/benchmark.db"))
    for i in range(total_ops):
        if i % 3 == 0:
            with transaction(conn):
                conn.execute("INSERT INTO metrics (val) VALUES (?);", (f"val-{worker_id}-{i}",))
        else:
            cursor = conn.execute("SELECT COUNT(*) FROM metrics;")
            cursor.fetchone()
    conn.close()

# Khởi tạo schema
init_conn = get_connection(Path("/tmp/benchmark.db"))
init_conn.execute("CREATE TABLE IF NOT EXISTS metrics (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT);")
init_conn.close()

start = time.perf_counter()
threads = [threading.Thread(target=test_worker, args=(w, 200)) for w in range(10)]
for t in threads: t.start()
for t in threads: t.join()
elapsed = time.perf_counter() - start

print(f"Hoàn thành 2.000 tác vụ đồng thời trên 10 thread trong {elapsed:.2f}s mà không gặp lỗi.")

Với cấu hình rollback journal mặc định, bài test này thất bại chỉ sau 80 lần lặp với lỗi database is locked. Sau khi kích hoạt WAL mode, synchronous = NORMAL và cấu hình busy timeout 5.000ms, toàn bộ 2.000 tác vụ đã hoàn thành không một lỗi trong 0,38 giây, duy trì mức xử lý trên 5.200 transaction mỗi giây.