PRAGMA trong SQLite: cấu hình kết nối cho production

PRAGMA là cách bạn nói chuyện với công cụ cơ sở dữ liệu

PRAGMA là một câu lệnh riêng của SQLite dùng để đọc hoặc thay đổi cách công cụ cơ sở dữ liệu hoạt động. Bạn chạy chúng như mọi câu SQL khác, nhưng thay vì chạm vào dữ liệu, chúng chạm vào cấu hình của cơ sở dữ liệu.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

Chạy như một truy vấn, PRAGMA trả về giá trị hiện tại. Chạy như một phép gán, nó thay đổi giá trị:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

Cách hình dung: phần lớn PRAGMA áp dụng theo từng kết nối. Mở một kết nối mới là các giá trị mặc định quay trở lại. Đó là lý do code production thường có một khối nhỏ các câu lệnh PRAGMA chạy ngay sau khi mỗi kết nối được thiết lập.

Cấu hình nền cho production

Nếu chỉ nhớ năm PRAGMA, hãy nhớ những cái này:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;

SELECT 'configured' AS status;

Đó là cấu hình mặc định hợp lý cho gần như mọi ứng dụng dùng SQLite làm kho dữ liệu chính. Mỗi thiết lập đều đáng hiểu riêng, và phần còn lại của trang này đi qua từng cái.

journal_mode = WAL

Chế độ journal quyết định cách SQLite làm cho thao tác ghi trở nên bền vững. Mặc định là DELETE, dùng rollback journal: tiến trình ghi chặn tiến trình đọc và ngược lại. Ổn với một công cụ dòng lệnh, nhưng rất khổ với một ứng dụng web.

WAL (Write-Ahead Logging) đảo ngược điều đó. Tiến trình đọc và ghi không chặn nhau: tiến trình đọc thấy một bản chụp nhất quán trong khi tiến trình ghi đang commit. Bạn vẫn chỉ có một tiến trình ghi tại một thời điểm, nhưng thao tác đọc vẫn nhanh khi tải cao.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

Vài điều cần biết:

• journal_mode là thiết lập lâu dài: một khi đã đặt, nó giữ nguyên cho tệp cơ sở dữ liệu đó. Bạn không cần đặt lại trên mỗi kết nối, nhưng đặt lại cũng không sao.
• WAL tạo thêm hai tệp cạnh tệp .db: một tệp -wal và một tệp -shm. Đừng xóa chúng khi cơ sở dữ liệu đang mở.
• WAL không hoạt động tốt trên hệ thống tệp mạng (NFS, SMB). Hãy để cơ sở dữ liệu trên ổ đĩa cục bộ.

Có một trang tài liệu riêng về chế độ WAL và xử lý đồng thời đi sâu hơn. Còn bây giờ: hãy bật nó lên.

synchronous = NORMAL

synchronous quyết định SQLite ghi dữ liệu xuống đĩa quyết liệt đến mức nào. Đây là sự đánh đổi giữa độ bền và tốc độ.

• FULL (mặc định): flush sau mỗi lần commit. Bền vững tối đa. Chậm hơn.
• NORMAL: flush tại các checkpoint an toàn. An toàn khi dùng WAL. Nhanh hơn.
• OFF: để hệ điều hành tự quyết. Nhanh nhưng có nguy cơ hỏng dữ liệu khi mất điện.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

Số nguyên trong kết quả (1) tương ứng với NORMAL. Với chế độ WAL, NORMAL là thiết lập được khuyến nghị: bạn không mất các giao dịch đã commit khi chương trình bị crash, chỉ có nguy cơ mất những giao dịch gần nhất khi mất điện. Với hầu hết ứng dụng, đó là sự cân bằng hợp lý.

Đừng dùng OFF trừ khi bạn đang nạp dữ liệu vào một cơ sở dữ liệu dùng một lần và có thể tạo lại từ đầu.

foreign_keys = ON

Thiết lập này hay làm người ta vấp. SQLite hỗ trợ khóa ngoại (foreign key), nhưng việc kiểm tra mặc định bị tắt, và đây là thiết lập theo từng kết nối:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO books (title, author_id) VALUES ('Ghosts', 999);

Với foreign_keys = ON, lệnh insert cuối cùng thất bại vì không có tác giả nào có id 999. Không có PRAGMA này, SQLite vui vẻ ghi hàng mồ côi đó và vài tháng sau bạn mới phát hiện ra mớ hỗn độn.

Chạy PRAGMA foreign_keys = ON; làm câu lệnh đầu tiên trên mỗi kết nối mới. Hầu hết ORM tự làm việc này; nếu bạn dùng driver thô, đó là việc của bạn.

busy_timeout = 5000

SQLite chỉ cho phép một tiến trình ghi tại một thời điểm. Nếu kết nối thứ hai cố ghi trong khi kết nối thứ nhất đang giữa giao dịch, mặc định nó nhận SQLITE_BUSY và dừng ngay lập tức.

busy_timeout bảo SQLite chờ và thử lại:

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

Giá trị tính bằng mili giây. 5000 nghĩa là "chờ khóa tối đa 5 giây trước khi bỏ cuộc". Kết hợp với WAL, thiết lập này loại bỏ phần lớn các lỗi database is locked vô cớ trong ứng dụng chạy đồng thời.

Nếu bạn thấy mình phải tăng giá trị này lên quá 30 giây, cách sửa thật sự có lẽ là rút ngắn giao dịch, không phải kéo dài thời gian chờ.

cache_size

cache_size đặt số trang cơ sở dữ liệu mà SQLite giữ trong bộ nhớ. Cache càng lớn thì càng ít lần đọc đĩa, nghĩa là truy vấn trên dữ liệu hay dùng chạy nhanh hơn.

Giá trị có hai dạng:

• Số dương: tính theo trang. Với kích thước trang mặc định 4 KB, 2000 là 8 MB.
• Số âm: tính theo kibibyte. -20000 là 20 MB bất kể kích thước trang.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

Dạng số âm dễ hình dung hơn: bạn đang nói "cho tôi 20 MB cache" thay vì phải tính toán với kích thước trang. Với một ứng dụng nhỏ, từ 20 đến 50 MB là đủ. Với khối lượng đọc nhiều trên cơ sở dữ liệu lớn hơn, hãy tăng cao hơn. Giống synchronous, cache_size áp dụng theo từng kết nối.

mmap_size

I/O ánh xạ bộ nhớ (memory-mapped I/O) cho phép SQLite đọc các phần của tệp cơ sở dữ liệu trực tiếp từ page cache của hệ điều hành, bỏ qua một lần sao chép. Nó có thể tăng tốc đọc trên cơ sở dữ liệu lớn:

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

Đó là 256 MB. SQLite sẽ ánh xạ tối đa chừng đó dung lượng của cơ sở dữ liệu vào bộ nhớ nếu còn chỗ. Hệ điều hành lo việc phân trang, nên bạn không thật sự cấp phát trước 256 MB, mà chỉ cho phép ánh xạ tối đa đến mức đó.

mmap_size phát huy tác dụng với khối lượng đọc nhiều. Nó cũng vô hại với cơ sở dữ liệu nhỏ. Giá trị mặc định khá dè dặt, nên tăng nó lên thường có lợi.

PRAGMA optimize

Trình lập kế hoạch truy vấn dùng thống kê để chọn chỉ mục. Thống kê cũ nghĩa là kế hoạch tệ. PRAGMA optimize cập nhật các thống kê đó với chi phí thấp:

PRAGMA optimize;

Cách được khuyến nghị là chạy nó ngay trước khi đóng các kết nối tồn tại lâu: khi ứng dụng tắt, hoặc ở cuối một request handler giữ kết nối khá lâu. Lệnh này nhanh (thường chỉ vài mili giây) và chỉ làm việc khi thật sự có gì cần cập nhật.

Nó không giống ANALYZE, lệnh dựng lại toàn bộ thống kê. optimize là người anh em nhẹ nhàng, có thể chạy thường xuyên.

Đọc tất cả thiết lập

Nếu muốn xem một kết nối hiện đang được cấu hình thế nào, hãy truy vấn các PRAGMA mà không gán giá trị:

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;
PRAGMA busy_timeout;
PRAGMA cache_size;
PRAGMA page_size;
PRAGMA temp_store;

Rất hữu ích khi gỡ lỗi: khi kết nối từ một driver khác và thắc mắc vì sao hành vi thay đổi, nguyên nhân gần như luôn là khác biệt về PRAGMA.

Còn có PRAGMA pragma_list;, lệnh liệt kê mọi PRAGMA mà bản build hỗ trợ:

PRAGMA pragma_list;

Không phải thứ bạn cần học thuộc, nhưng tiện khi cần đến.

Thiết lập thuộc về lúc tạo, không phải lúc chạy

Một vài PRAGMA cấu hình chính tệp cơ sở dữ liệu và chỉ có hiệu lực trước khi tạo bất kỳ bảng nào:

• PRAGMA page_size = 8192;: kích thước trang trên đĩa. Mặc định là 4096, đủ tốt cho hầu hết khối lượng công việc. Trang lớn hơn có lợi với các hàng lớn.
• PRAGMA encoding = 'UTF-8';: bảng mã văn bản.

PRAGMA page_size = 8192;
PRAGMA encoding = 'UTF-8';
CREATE TABLE ...

Nếu đổi page_size trên một cơ sở dữ liệu đã có, bạn cần chạy VACUUM để thay đổi có hiệu lực. Hãy đặt những thiết lập này một lần lúc tạo rồi quên chúng đi.

Một đoạn thiết lập kết nối thực tế

Trong code ứng dụng, phần này thường nằm ở nơi mở kết nối. Về mặt ý tưởng:

-- Run once on every new connection:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- Run periodically, or before close:
PRAGMA optimize;

temp_store = MEMORY giữ các bảng và chỉ mục tạm trong RAM, giúp tăng tốc các truy vấn cần sắp xếp hoặc tổng hợp mà không có chỉ mục.

Đó là toàn bộ danh sách kiểm tra cho production. Chừng nửa tá dòng, và SQLite chuyển từ "ổn cho môi trường phát triển" thành "phù hợp với khối lượng công việc thật".

Tiếp theo: lỗi thường gặp

Ngay cả với PRAGMA tốt, bạn vẫn sẽ gặp những lỗi SQLite quen thuộc: database is locked, disk I/O error, constraint failed. Trang tiếp theo giải thích từng lỗi thật sự có nghĩa là gì và cách sửa.