SQLite в проде: WAL, busy_timeout и ошибка, которую таймаут не лечит
У меня есть небольшой сервис — 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, об этом — в заметке про бэкапы.
Итого
- WAL,
synchronous = NORMALиbusy_timeoutна каждом соединении. - Пишущие транзакции — только через
BEGIN IMMEDIATE. - Не держать длинные читающие транзакции.
- Бэкапить через
.backupилиVACUUM INTO, а не черезcp.