SQLite в проде: WAL, busy_timeout и ошибка, которую таймаут не лечит

· 3 мин чтения · sqliteбазы данных

У меня есть небольшой сервис — API и пара фоновых воркеров, — который уже несколько месяцев живёт на SQLite. Нагрузка скромная: десятки запросов в секунду в пике, база на пару гигабайт. Postgres тут был бы перебором, а SQLite с правильными настройками справляется спокойно. Ниже — то, что я включаю всегда, и одна ошибка, на которую я потратил вечер.

Базовые настройки

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

PRAGMA journal_mode = WAL;     -- сохраняется в файле базы
PRAGMA synchronous = NORMAL;   -- в WAL это не ломает целостность
PRAGMA busy_timeout = 5000;    -- ждать блокировку до 5 секунд
PRAGMA foreign_keys = ON;

journal_mode достаточно выставить один раз — он запоминается в самом файле. А вот busy_timeout, synchronous и foreign_keys действуют только на текущее соединение, поэтому я выполняю их при открытии каждого соединения из пула.

synchronous = NORMAL в режиме WAL не приводит к повреждению базы: при внезапном отключении питания можно потерять последние транзакции, но файл останется целым. Для моего сервиса это приемлемо.

«database is locked», хотя busy_timeout стоит

Через пару месяцев в логах начали изредка появляться SQLITE_BUSY. Странно: таймаут пять секунд, а запросы короткие. Оказалось, ошибка возникала мгновенно, без всякого ожидания.

Причина — в отложенных транзакциях. BEGIN по умолчанию означает BEGIN DEFERRED: транзакция начинается как читающая и пытается получить блокировку на запись только при первом INSERT или UPDATE. Если к этому моменту другое соединение уже успело что-то записать, снимок данных у нашей транзакции устарел, и SQLite сразу возвращает SQLITE_BUSY (точнее, SQLITE_BUSY_SNAPSHOT). Ждать тут бессмысленно, поэтому busy_timeout даже не вызывается.

Лечится это явным указанием намерения: транзакции, которые будут писать, я начинаю с BEGIN IMMEDIATE.

BEGIN IMMEDIATE;
SELECT balance FROM accounts WHERE id = ?;
UPDATE accounts SET balance = ? WHERE id = ?;
COMMIT;

Теперь блокировка на запись берётся в самом начале, а если она занята — срабатывает обычное ожидание по busy_timeout. Ошибки пропали. В Go-драйверах это обычно настраивается параметром в строке подключения, в Python — через isolation_level="IMMEDIATE".

Чекпоинты и разрастание WAL

Изменения сначала пишутся в файл -wal, а потом переносятся в основную базу при чекпоинте. По умолчанию он запускается автоматически, когда WAL дорастает примерно до 1000 страниц. Но чекпоинт не может продвинуться дальше самого старого активного читателя. Если какой-то процесс держит транзакцию чтения открытой часами, WAL будет только расти.

У меня так делал фоновый экспорт, который читал всю таблицу одной транзакцией. Разбил его на пачки по несколько тысяч строк — файл WAL снова стал стабильно небольшим.

Бэкапы

Копировать файл базы через cp на работающем сервисе нельзя: можно получить несогласованную копию, да и файл -wal легко забыть. Безопасные варианты:

sqlite3 app.db ".backup '/backup/app-$(date +%F).db'"
# или, начиная с SQLite 3.27:
sqlite3 app.db "VACUUM INTO '/backup/app-$(date +%F).db'"

VACUUM INTO заодно сжимает копию. После бэкапа я запускаю PRAGMA integrity_check; на копии — это быстро и спокойнее для нервов. Дальше файл забирает restic, об этом — в заметке про бэкапы.

Итого