PRAGMA в SQLite: настройка соединения для продакшена

PRAGMA — это способ поговорить с движком

PRAGMA — это специальная инструкция SQLite, которая читает или меняет поведение самого движка. Запускается она как обычный SQL-запрос, но трогает не ваши данные, а настройки базы.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

PRAGMA — это запрос, который возвращает текущее значение, а в форме присваивания меняет его:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

Главное, что нужно понять: большинство PRAGMA действуют в рамках одного соединения. Открыли новое соединение — и настройки снова по умолчанию. Именно поэтому в продакшене обычно держат небольшой блок PRAGMA-команд, который выполняется сразу после установки каждого соединения.

Базовый набор для продакшена

Если из всех sqlite pragma запоминать только пять — запоминайте эти:

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

SELECT 'настроено' AS status;

Это разумные настройки по умолчанию практически для любого приложения, где SQLite — основное хранилище. Каждую из них стоит разобрать отдельно, чем мы и займёмся ниже.

journal_mode = WAL

Режим журналирования определяет, как SQLite обеспечивает надёжность записей. По умолчанию используется DELETE — откатный журнал, при котором писатели блокируют читателей, а читатели блокируют писателей. Для консольной утилиты это нормально, а вот для веб-приложения — настоящая боль.

Режим WAL (Write-Ahead Logging) меняет картину: читатели и писатели больше не мешают друг другу. Пока идёт коммит, читатели видят согласованный снимок данных. Писатель по-прежнему может быть только один в момент времени, но чтение под нагрузкой остаётся быстрым.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

Что важно знать:

• journal_mode — постоянный параметр: один раз выставили, и режим сохраняется для файла БД. Заново на каждом подключении прописывать не нужно, но и хуже от этого не будет.
• В режиме WAL рядом с вашим .db появляются ещё два файла: -wal и -shm. Пока база открыта — не удаляйте их.
• WAL плохо дружит с сетевыми файловыми системами (NFS, SMB). Держите базу на локальном диске.

Про WAL и конкурентный доступ есть отдельная статья, где всё разобрано глубже. Сейчас же — просто включаем.

synchronous = NORMAL

synchronous отвечает за то, насколько настойчиво SQLite сбрасывает данные на диск. Здесь классический компромисс: надёжность против скорости.

• FULL (по умолчанию) — сброс после каждого коммита. Максимальная надёжность, но медленнее.
• NORMAL — сброс на безопасных контрольных точках. В связке с WAL — вполне безопасно и заметно быстрее.
• OFF — на усмотрение ОС. Быстро, но при отключении питания есть риск повредить базу.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

Целое число в результате (1) соответствует режиму NORMAL. В связке с WAL именно NORMAL — рекомендуемый вариант: подтверждённые транзакции при падении процесса не теряются, под угрозой только самые последние записи при внезапном отключении питания. Для большинства приложений это разумный компромисс.

Не используйте OFF, если только не наполняете одноразовую базу, которую не жалко пересоздать с нуля.

foreign_keys = ON

Вот тут многие спотыкаются. SQLite умеет работать с внешними ключами, но их проверка по умолчанию выключена, и включается она отдельно для каждого соединения:

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 ('Призраки', 999);

С foreign_keys = ON последний INSERT упадёт — автора с id 999 попросту нет. А без этой PRAGMA SQLite спокойно запишет осиротевшую строку, и через пару месяцев вы обнаружите этот бардак.

Выполняйте PRAGMA foreign_keys = ON; самым первым запросом на каждом новом соединении. Большинство ORM делают это автоматически, но если вы работаете с драйвером напрямую — забота о включении лежит на вас.

busy_timeout = 5000

SQLite допускает только одного писателя одновременно. Если второе соединение попробует что-то записать, пока первое находится в середине транзакции, оно получит SQLITE_BUSY и сразу же вылетит — таково поведение по умолчанию.

PRAGMA busy_timeout говорит SQLite подождать и повторить попытку:

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

Значение задаётся в миллисекундах. 5000 означает «ждать блокировку до 5 секунд, потом сдаваться». В связке с WAL это убирает почти все случайные ошибки database is locked в многопоточных приложениях.

Если вам хочется поднять таймаут выше 30 секунд — скорее всего, проблема не в таймауте, а в слишком долгих транзакциях. Чините их.

cache_size

cache_size определяет, сколько страниц БД SQLite держит в памяти. Чем больше кэш, тем меньше обращений к диску — а значит, быстрее запросы по «горячим» данным.

Значение можно задать в двух формах:

• Положительное число — количество страниц. При стандартном размере страницы 4 КБ 2000 соответствует 8 МБ.
• Отрицательное число — размер в кибибайтах. -20000 — это 20 МБ независимо от размера страницы.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

Отрицательное значение удобнее тем, что не приходится считать в уме: ты просто говоришь «дай мне 20 МБ под кэш», а не умножаешь на размер страницы. Для небольшого приложения 20–50 МБ — за глаза. Если база большая и нагрузка в основном на чтение — смело поднимай выше. Как и synchronous, параметр cache_size задаётся отдельно для каждого соединения.

mmap_size

Memory-mapped I/O позволяет SQLite читать куски файла базы напрямую из страничного кэша ОС, минуя лишнее копирование. На больших базах это заметно ускоряет чтение:

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

Это 256 МБ. SQLite смапит в память столько данных, сколько влезет, но не больше указанного лимита. Подкачкой страниц занимается ОС, так что заранее эти 256 МБ не выделяются — вы лишь разрешаете использовать до этого объёма.

mmap_size особенно хорош на нагрузках с преобладанием чтения. На небольших БД он точно не повредит. Значения по умолчанию довольно скромные, так что увеличить их — почти всегда выигрыш.

PRAGMA optimize

Планировщик запросов выбирает индексы на основе статистики. Устаревшая статистика — кривые планы. PRAGMA optimize обновляет её дёшево и быстро:

PRAGMA optimize;

Рекомендуемый сценарий — выполнять её прямо перед закрытием долгоживущих соединений: при остановке приложения или в конце обработчика запроса, который держит соединение открытым какое-то время. Работает быстро (обычно миллисекунды) и реально что-то делает только тогда, когда есть что обновлять.

Это не то же самое, что ANALYZE — полный пересбор статистики. optimize — её лёгкая версия, которую не страшно запускать часто.

Как посмотреть текущие настройки

Чтобы узнать, как сейчас сконфигурировано соединение, достаточно вызвать PRAGMA без присваивания значения:

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

Полезно при отладке: подключаешься из другого драйвера, поведение внезапно меняется — почти всегда дело в разнице PRAGMA.

Ещё есть PRAGMA pragma_list; — она выводит все PRAGMA, которые поддерживает текущая сборка:

PRAGMA pragma_list;

Не то чтобы это надо заучивать наизусть, но иметь под рукой стоит.

Настройки, которые задаются при создании БД, а не на лету

Часть PRAGMA настраивает сам файл базы данных и применяется только до того, как создана хотя бы одна таблица:

• PRAGMA page_size = 8192; — размер страницы на диске. По умолчанию 4096, и для большинства задач этого хватает. Страницы побольше пригодятся, если у вас крупные строки.
• PRAGMA encoding = 'UTF-8'; — кодировка текста.

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

Если вы меняете page_size на уже существующей базе, изменения вступят в силу только после VACUUM. Поэтому такие параметры лучше задать один раз при создании БД — и забыть про них.

Шаблон инициализации соединения

В реальном коде эти настройки обычно прописывают там же, где открывается соединение с базой. Если описать это на уровне идеи:

-- Запускать один раз на каждом новом соединении:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- Запускать периодически или перед закрытием:
PRAGMA optimize;

temp_store = MEMORY держит временные таблицы и индексы в оперативке — это ускоряет запросы, которым нужна сортировка или агрегация без индекса.

Вот и весь продакшен-чеклист. Полдюжины строк — и SQLite превращается из «сойдёт для разработки» в «годится для реальной нагрузки».

Дальше: типичные ошибки

Даже с правильно выставленными PRAGMA вы рано или поздно столкнётесь со стандартным набором ошибок SQLite — database is locked, disk I/O error, constraint failed. На следующей странице разберём, что каждая из них на самом деле означает и как её лечить.