Сколько миллисекунд без читателей нужно SQLite, чтобы файл -wal перестал расти

В режиме WAL база SQLite пишет изменения сначала в файл -wal, а потом переносит их в основной файл и начинает -wal с начала. Документация предупреждает: если читатели перекрываются и хотя бы один активен всегда, -wal растёт без предела, и нужны промежутки без читателей. Какой длины, там не сказано. В обсуждениях это лечат sleep() на 16 мс и больше. Я замерил в контейнере на SQLite 3.46.1. На моём стенде хватало общей паузы в единицы миллисекунд: 2 мс при 50 коммитах/с, 5 мс при 200 и 1000. Неожиданно для меня при быстрой записи паузы понадобилось больше, чем при медленной. Без неё три читателя по 50 мс, сменяющие друг друга, раздули -wal так же, как один на 20 секунд: 19,7 МиБ, и ни разу SQLite не начал файл заново. Случайные запросы по 5 мс почти не мешали, пока хотя бы 13% времени не было ни одного читателя. Ниже замеры, догадка о том, откуда берутся эти миллисекунды, и признак, по которому проблему видно раньше, чем кончится диск.

Что уже известно

Документация SQLite описывает механизм в разделе про слишком большие WAL-файлы. Если читателей много, их транзакции перекрываются и хотя бы один читатель активен всегда, checkpoint не завершится и WAL «будет расти без предела». Лечение там названо так: нужны «reader gaps», промежутки без читателей, или ручной checkpoint в режиме RESTART/TRUNCATE. Сколько должен длиться промежуток, там не сказано.

Почти тот же эксперимент этой осенью опубликовал автор поста «Why your SQLite WAL file never shrinks» на dev.to. На macOS с писателем 2000 коммитов/с три читателя по секунде со сдвигом дали рост 11,9 МиБ/с. TRUNCATE раз в 5 с в двух прогонах из трёх удержал файл до 60 МиБ ценой остановки писателя до 1,8 с. Его вывод для практики: держать чтения короткими и следить за разницей log − checkpointed. Промежуток без читателей он сравнивал в двух положениях: «около 20% времени» и «никогда». Его самые короткие чтения длились 0,4 с.

На форуме SQLite (2020) WAL в 2 ГБ при базе в 6 МБ перестал расти после «occasional sleep» в потоках, на Hacker News (2024) спасались sleep() от 16 мс на всех запросах. Нужную паузу там не измеряли.

Стенд

Все проверки идут в одноразовом контейнере python:3.15-slim с ограничением в 2 CPU: Python 3.15.0, SQLite 3.46.1, страница 4096 байт, автоматический checkpoint по умолчанию (1000 страниц), synchronous=NORMAL, если не сказано иное. В пробе (первая таблица) писатель вставляет в таблицу на 2000 строк по одной строке с 500 байтами данных за коммит. Во всех остальных проверках таблица на 20 000 строк, а писатель с заданным темпом делает BEGIN IMMEDIATE и пять UPDATE случайных строк, это около пяти кадров WAL на коммит. Возврат -wal к началу (дальше «перемотка») я считаю по полю checkpoint sequence в заголовке файла, байты 12–16: SQLite увеличивает его при каждой перемотке.

Без паузы 50 мс и 20 секунд дают одно и то же

Писатель 200 коммитов/с, 20 секунд на сценарий, два повтора с одинаковым итогом.

Во всех трёх сценариях без паузы файл рос на 2 МиБ каждые 2 секунды, длина отдельного чтения ничего не меняла. Документация подтвердилась буквально.

Сколько нужно паузы

Следующий стенд: два читателя по 100 мс со сдвигом 50 мс, то есть 150 мс база занята, затем пауза G, когда не читает никто. Паузу я мерил по факту, медиана отличалась от заданной не больше чем на 0,1 мс. 12 секунд на точку. Раз в три секунды стороннее соединение снимало счётчики через PRAGMA wal_checkpoint(PASSIVE), а это тоже checkpoint.

В ячейке: перемоток за 12 с; наибольший -wal, МиБ.

Из проверенных значений срабатывали уже паузы в единицы миллисекунд, это не «20% времени». Шаг сетки грубый, и каждая точка — один прогон: в следующей серии при 200 коммитах/с хватило и 3 мс. Так что цифры в таблице — исходы отдельных прогонов на моём стенде; точную границу они не задают. Сдвигается она в неожиданную сторону. Я ждал, что пауза должна быть не короче интервала между коммитами, тогда при 1000 коммитах в секунду хватило бы миллисекунды. Вышло наоборот: при 50 коммитах/с хватило 2 мс, при 1000/с 2 мс уже не хватило.

Откуда могут браться эти миллисекунды

Чтобы понять, от чего зависит нужная пауза, я заставил писателя после каждого коммита записывать время начала, конца и номер перемотки из заголовка -wal, и сопоставил это с паузами. Из 266 перемоток 265 случились сразу после коммита, который целиком, вместе со своим checkpoint, поместился в паузу. Коммиты, которые начались в паузе, но закончились уже при новом читателе, я только подсчитал, с перемотками их не сопоставлял. Единственное исключение из 266 я не разобрал.

Похоже, в паузу должен влезть коммит с догоняющим checkpoint. Фазы checkpoint изнутри SQLite я не замерял, это вывод из совпадения по времени, зато он объясняет, почему быстрой записи понадобилась пауза шире. Длительность такого коммита зависит от fsync:

Почему догон дольше при быстрой записи, я прямо не мерил. Объяснение, которое сходится с цифрами и с кодом wal.c: пока читатель держит снимок, checkpoint не может перенести кадры новее этого снимка. Когда последний читатель уходит, переносить надо всё, что записали за время его чтения, а это темп записи, умноженный на длину чтения.

Одна оговорка из того же wal.c: читатель, начавший транзакцию в момент, когда весь -wal уже перенесён в базу, читает только основной файл и перемотке не мешает. Под непрерывной записью такое окно почти не открывается, поэтому дальше «читатель» означает того, кто видит кадры из -wal.

А если читатели случайные, как веб-запросы

Расписанное перекрытие бывает у фоновых заданий и опросов со сдвигом, а в обычном сервисе запросы приходят случайно. Я запускал читателей с пуассоновскими приходами (BEGIN; SELECT; работа d мс; COMMIT) при писателе 200 коммитов/с и мерил по среднему числу одновременных читателей L = частота × длительность. Без читателей при этом темпе -wal держится около 4 МиБ (первая строка таблицы выше).

Два повтора по 14 секунд, в строке разброс по повторам и длинам чтения. Перемотки продолжались во всех сериях, тогда как при расписанном перекрытии без паузы их не было ни одной. При L = 1 картина не отличается от работы без читателей, даже при 2000 чтений по 0,5 мс в секунду, когда пауз длиннее 4 мс остаётся меньше одной в секунду. При L = 4 перемоток остаётся 5–9 вместо 13, а файл вырастает до 7–17 МиБ. Остановился бы рост на долгом прогоне, по этим данным не сказать: я сохранял только наибольший и конечный размер.

Короткие частые чтения при той же нагрузке не хуже длинных, скорее лучше: при L = 3 чтения по 1–2 мс дали 4,8–5,7 МиБ, по 5 мс — 6,5–9,5. Это согласуется с объяснением выше: короткий читатель оставляет маленький догон, и checkpoint, видимо, успевает за более короткую паузу. Длительность checkpoint в этой серии я не мерил.

Как заметить, пока не кончился диск

Совет из поста на dev.to, следить за log − checkpointed, у меня starvation не показал. При паузе 0 и 200 коммитах/с сторонний PRAGMA wal_checkpoint(PASSIVE) раз в три секунды отвечал так:

(0, 3008, 2948)  (0, 6007, 5942)  (0, 9005, 8936)  (0, 12005, 11931)

Разница — 60–75 кадров, файл при этом 47 МиБ. Перенос почти успевает, не получается только перемотка. У автора поста разница доходила до 2751 кадра, потому что его читатели держали снимок по секунде. С короткими читателями она маленькая.

Работает другой признак: сам log. При паузе 50 мс тот же запрос возвращал 994, 988, 987, то есть log держался чуть ниже порога автоматического checkpoint в 1000 кадров. При starvation он растёт без остановки. Второй признак ещё проще: checkpoint sequence в заголовке -wal (четыре байта со смещения 12, big-endian) перестаёт меняться.

Что из этого следует

Если -wal растёт, посмотрите на расписание чтений. Пул воркеров, которые опрашивают базу со сдвигом, и задания по таймеру друг за другом без зазора не оставляют ни одного промежутка без читателей. Пока чтения так и перекрываются, укорачивание каждого из них не поможет; помогает оно, только если между ними появляются общие паузы. На моём стенде хватало паузы в 5 мс при темпе до 1000 коммитов/с и synchronous=NORMAL. Если паузу устроить нельзя, остаётся периодический wal_checkpoint(TRUNCATE) с заданным busy_timeout: без него вызов при активном читателе сразу вернёт busy. Цену этого для писателя измерил автор поста на dev.to, и у него один вызов из нескольких всё равно вернулся busy.

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

Границы проверки: один хост Linux, контейнер с 2 CPU, SQLite 3.46.1 из Debian-образа, Python-потоки (GIL добавляет свои задержки), один писатель с маленькими транзакциями. На другом диске и при больших коммитах нужные паузы будут другими: они зависят от времени коммита с checkpoint. Что длинная пауза нужна на перенос накопившихся за чтение кадров, я вывел из совпадения цифр; объём и время этого переноса отдельно не измерял.