PRAGMA w SQLite: konfiguracja połączeń na produkcję

PRAGMA to sposób rozmowy z silnikiem

PRAGMA to specyficzna dla SQLite instrukcja, która odczytuje lub zmienia zachowanie silnika. Uruchamiasz ją jak każde inne polecenie SQL, ale zamiast twoich danych dotyka konfiguracji bazy.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

Uruchomiona jako zapytanie PRAGMA zwraca bieżącą wartość. Uruchomiona jako przypisanie zmienia tę wartość:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

Model myślowy: większość PRAGMA działa w obrębie połączenia. Otwórz nowe połączenie, a wartości domyślne wracają. Dlatego kod produkcyjny zwykle ma krótki blok instrukcji PRAGMA uruchamianych zaraz po nawiązaniu każdego połączenia.

Podstawowy zestaw na produkcję

Jeśli masz zapamiętać tylko pięć PRAGMA, zapamiętaj te:

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

SELECT 'skonfigurowano' AS status;

To rozsądna wartość domyślna dla prawie każdej aplikacji, która używa SQLite jako głównego magazynu danych. Każde z tych ustawień warto zrozumieć osobno i właśnie o tym jest reszta strony.

journal_mode = WAL

Tryb dziennika decyduje, jak SQLite zapewnia trwałość zapisów. Domyślny DELETE używa dziennika wycofywania: piszący blokują czytających, a czytający blokują piszących. Dla narzędzia wiersza poleceń to w porządku, dla aplikacji webowej bolesne.

WAL (Write-Ahead Logging) odwraca tę sytuację. Czytający i piszący nie blokują się nawzajem: czytający widzą spójny obraz danych, gdy piszący zatwierdza zmiany. Nadal zapisuje tylko jeden piszący naraz, ale odczyty pozostają szybkie pod obciążeniem.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

Kilka rzeczy, które warto wiedzieć:

• journal_mode jest trwałe: raz ustawione zostaje takie dla pliku bazy. Nie musisz ustawiać go w każdym połączeniu, ale to nie szkodzi.
• WAL tworzy dwa dodatkowe pliki obok twojego .db: -wal i -shm. Nie usuwaj ich, gdy baza jest otwarta.
• WAL słabo działa na sieciowych systemach plików (NFS, SMB). Trzymaj bazę na dysku lokalnym.

Jest osobna strona o trybie WAL i współbieżności, która wchodzi głębiej. Na razie: włącz go.

synchronous = NORMAL

synchronous decyduje, jak agresywnie SQLite zrzuca dane na dysk. To kompromis między trwałością a szybkością.

• FULL (domyślnie): zrzut po każdym zatwierdzeniu. Maksymalna trwałość. Wolniej.
• NORMAL: zrzut w bezpiecznych punktach kontrolnych. Bezpieczne z WAL. Szybciej.
• OFF: decyzję zostawia się systemowi. Szybko, ale grozi uszkodzeniem bazy przy utracie zasilania.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

Liczba w wyniku (1) odpowiada NORMAL. W trybie WAL NORMAL to zalecane ustawienie: przy awarii nie tracisz zatwierdzonych transakcji, a przy zaniku zasilania ryzykujesz tylko utratę tych najnowszych. Dla większości aplikacji to właściwa równowaga.

Nie używaj OFF, chyba że zapełniasz jednorazową bazę i możesz ją odtworzyć od zera.

foreign_keys = ON

Na tym wielu się potyka. SQLite obsługuje klucze obce, ale ich wymuszanie jest domyślnie wyłączone i jest to ustawienie połączenia:

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 ('Duchy', 999);

Z foreign_keys = ON ostatnie wstawienie się nie udaje, bo nie istnieje autor o id 999. Bez tej PRAGMA SQLite chętnie zapisze osierocony wiersz, a bałagan odkryjesz po miesiącach.

Uruchamiaj PRAGMA foreign_keys = ON; jako pierwszą instrukcję w każdym nowym połączeniu. Większość ORM robi to automatycznie, ale jeśli używasz surowego sterownika, to twoje zadanie.

busy_timeout = 5000

SQLite pozwala na jednego piszącego naraz. Jeśli drugie połączenie próbuje pisać, gdy pierwsze jest w trakcie transakcji, domyślnie dostaje SQLITE_BUSY i od razu się poddaje.

busy_timeout każe SQLite zamiast tego czekać i próbować ponownie:

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

Wartość jest w milisekundach. 5000 oznacza "czekaj na blokadę do 5 sekund, zanim się poddasz". W połączeniu z WAL eliminuje to większość przypadkowych błędów database is locked w aplikacjach współbieżnych.

Jeśli zaczynasz podnosić tę wartość powyżej 30 sekund, prawdziwym rozwiązaniem są raczej krótsze transakcje, a nie dłuższy limit czasu.

cache_size

cache_size ustala, ile stron bazy danych SQLite trzyma w pamięci. Większa pamięć podręczna to mniej odczytów z dysku, a więc szybsze zapytania na często używanych danych.

Wartość ma dwie formy:

• Liczba dodatnia: strony. Przy domyślnym rozmiarze strony 4 KB 2000 to 8 MB.
• Liczba ujemna: kibibajty. -20000 to 20 MB niezależnie od rozmiaru strony.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

Forma ujemna jest łatwiejsza do ogarnięcia: mówisz "daj mi 20 MB pamięci podręcznej" zamiast liczyć z rozmiarem strony. Dla małej aplikacji 20-50 MB w zupełności wystarczy. Przy obciążeniu z przewagą odczytów na większej bazie ustaw więcej. Podobnie jak synchronous, cache_size działa w obrębie połączenia.

mmap_size

I/O mapowane w pamięci pozwala SQLite czytać fragmenty pliku bazy prosto z pamięci podręcznej stron systemu operacyjnego, bez dodatkowego kopiowania. Może to przyspieszyć odczyty w dużych bazach:

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

To 256 MB. SQLite zmapuje do tylu danych z bazy, jeśli jest miejsce. Stronicowaniem zajmuje się system operacyjny, więc nie alokujesz od razu 256 MB, tylko pozwalasz na zmapowanie do tej wielkości.

mmap_size błyszczy przy obciążeniach z przewagą odczytów. W małych bazach też nie szkodzi. Wartości domyślne są zachowawcze, więc podniesienie ich zwykle się opłaca.

PRAGMA optimize

Planer zapytań korzysta ze statystyk przy wyborze indeksów. Nieaktualne statystyki oznaczają złe plany. PRAGMA optimize tanio je aktualizuje:

PRAGMA optimize;

Zalecany wzorzec to uruchamianie go tuż przed zamknięciem długo działających połączeń: przy wyłączaniu aplikacji albo na końcu obsługi żądania, która trzyma połączenie przez dłuższy czas. Działa szybko (zwykle milisekundy) i wykonuje pracę tylko wtedy, gdy coś naprawdę wymaga aktualizacji.

To nie to samo co ANALYZE, które przebudowuje statystyki w całości. optimize to lekki kuzyn, którego można uruchamiać często.

Odczyt wszystkich ustawień

Jeśli chcesz zobaczyć, jak jest skonfigurowane bieżące połączenie, odpytaj PRAGMA bez przypisania:

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

Przydaje się przy debugowaniu: gdy łączysz się innym sterownikiem i zastanawiasz się, czemu zachowanie się zmieniło, prawie zawsze chodzi o różnicę w PRAGMA.

Jest też PRAGMA pragma_list;, które wypisuje każdą PRAGMA obsługiwaną przez daną kompilację:

PRAGMA pragma_list;

Nie ma sensu tego zapamiętywać, ale przydaje się, gdy trzeba.

Ustawienia na etap tworzenia, nie na czas działania

Kilka PRAGMA konfiguruje sam plik bazy i działa tylko przed utworzeniem jakichkolwiek tabel:

• PRAGMA page_size = 8192;: rozmiar strony na dysku. Domyślnie 4096, co wystarcza przy większości obciążeń. Większe strony pomagają przy dużych wierszach.
• PRAGMA encoding = 'UTF-8';: kodowanie tekstu.

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

Jeśli zmieniasz page_size w istniejącej bazie, musisz wykonać VACUUM, by zmiana zadziałała. Ustaw te wartości raz, przy tworzeniu bazy, i zapomnij o nich.

Prawdziwy fragment konfiguracji połączenia

W kodzie aplikacji zwykle znajduje się to w miejscu, które otwiera połączenie. Koncepcyjnie:

-- Uruchom raz w każdym nowym połączeniu:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- Uruchamiaj okresowo albo przed zamknięciem:
PRAGMA optimize;

temp_store = MEMORY trzyma tymczasowe tabele i indeksy w RAM, co przyspiesza zapytania, które muszą sortować lub agregować bez indeksu.

To cała lista kontrolna na produkcję. Kilka linijek i SQLite przechodzi od "wystarczy do developmentu" do "nadaje się do prawdziwego obciążenia".

Dalej: typowe błędy

Nawet z dobrymi PRAGMA trafisz na zwykły zestaw błędów SQLite: database is locked, disk I/O error, constraint failed. Następna strona wyjaśnia, co każdy z nich naprawdę oznacza i jak go naprawić.