Быстрый рост размера БД Firebird

27.07.2013, upd: 04.05.2020

За последний год некоторые разработчики и администраторы СУБД стали сообщать о внезапном росте баз данных Firebird.
 

"За несколько дней с 30 MB до 500 MB."
"Прирост размера базы составляет около 1-1.5 Гб/сутки."
И т. д.

Симптомы

• за сутки база может вырастать на несколько гигабайт, непропорционально количеству входных и изменяемых данных (собственно, входных или изменяемых данных может даже и не быть)
• если сделать backup/restore, то эти лишние гигабайты исчезают
• в статистике (gstat -r, IBAnalyst, dbinfo) лишние мегабайты-гигабайты не видны (статистика по Blob показывается в InterBase XE7 и Firebird 3, но только для постоянных blob, не временных)

Причины

У проблемы есть две причины, и они взаимосвязаны. Первая, и основная в отношении роста объемов БД причина – временные блобы. В Firebird 2.1 появилась функция LIST (возвращает блоб), а также возможность выполнять операции конкатенации над блобами, так же как и над строками (сюда относятся и любые строковые функции, которые могут работать с блобами). Обе эти операции создают временные блобы, которые сохраняются в БД. Освобождаются временные блобы только при завершении транзакции, в которой они были созданы (commit, rollback, не retaining).

Отсюда вторая, но необязательная причина – плохое управление транзакциями в приложениях. То есть, приложения долго удерживают транзакции открытыми, в результате чего накапливаются версии, что увеличивает объем БД и со временем ухудшает производительность. Если бы речь шла только про накопление версий записей, то рост объема БД не был бы таким большим. А вот если в этой длительной транзакции выполняется много операций, приводящих к созданию временных блобов – рост БД будет значительным.

Причем, даже если транзакция имеет параметр read only (или даже read only read committed rec_version), сервер все равно будет создавать временные блобы при указанных выше операциях.

Подробно

Гаджимурадов Рустам, sql.ru:

Что такое временные BLOB-ы?

Это, в общем-то, обычные BLOB-ы, которые сформированы, но пока не записаны (не «присвоены») в обычную таблицу БД. При создании и даже изменении любого не временного BLOB-объекта всегда сначала создаётся временный BLOB-объект, который позже будет либо удалён, либо материализован (превращён в обычный) путём записи ссылки на него в таблицу БД. Содержимое временного BLOB-а (тело, поток байтов) хранится в БД, точнее, в страничном кеше (на специальных BLOB-страницах). Можно было бы хранить временные BLOB-ы не в самой БД (страничном кеше), а во временном файле (общем или отдельном), но тогда при необходимости сохранить этот BLOB в таблицу БД пришлось бы его заново копировать целиком. Поэтому из двух зол было выбрано наименьшее, при котором меньше дисковый I/O. Таким образом, материализация BLOB-а представляет собой простую и быструю операцию без необходимости копирования тела BLOB-а. Удаление BLOB-а тоже быстрая операция – занятые им страницы просто помечаются как свободные и могут быть повторно использованы для других BLOB-ов или по другому назначению.

В каких случаях могут создаваться временные BLOB-ы?

Временные BLOB-ы создаются при многих операциях, но наиболее частыми и потенциально приводящими к увеличению БД являются следующие:

• все операции конкатенации, в которых участвуют BLOB-ы (даже временные), т. е. если BLOB складывается со строкой, результатом операции будет временный BLOB (а не строка), при чём если операция конкатенации будет делаться в цикле – будут созданы временные BLOB-ы по количеству отработавших операций конкатенации (не по количеству итераций);

• функция LIST – результатом функции всегда является BLOB, независимо от типа операндов и содержимого, хотя функция не создаёт промежуточных BLOB-ов во время своего выполнения (несмотря на то, что внешне алгоритм похож на конкатенацию, временный BLOB формируется всего один);
• функции и операторы, использующие или возвращающие не созданный заранее BLOB-объект – case, iif, coalesce, substring, replace, cast и т. д.

Когда удаляются временные BLOB-ы?

Временные BLOB-ы удаляются (занятые ими страницы помечаются как свободные) при

• завершении транзакции, если BLOB-id входит в список возвращаемых запросами столбцов.
• закрытии SQL-оператора, если BLOB-id не выдаются запросом "наружу", т. е. клиентское приложение не получает BLOB-id.

Касательно места хранения временных BLOB-ов нужно отметить ещё 2 нюанса:

1. Даже BLOB-ы «предназначенные» для GTT (Global Temporary Table, временные таблицы, хранимые вне файла БД) всё равно сначала являются временными и хранятся в БД (поскольку на момент и создания/редактирования ещё неизвестно в какую таблицу они попадут или вообще будут удалены), но после update/insert в GTT будут перенесены в «её» временный файл и освобождены в БД. Почему это будет делаться сразу, не дожидаясь окончания транзакции? Потому что если он понадобится для записи в обычную таблицу БД, его всё равно придётся копировать (дублировать).
2. Начиная с версии 2.1, в Firebird API появилась возможность при создании BLOB-а с клиента сразу сохранять его во временный файл, а не в БД путём присвоения параметру isc_bpb_storage значения isc_bpb_storage_temp (по умолчанию isc_bpb_storage_main, т. е. в БД). На текущий момент неизвестны библиотеки доступа к Firebird, использующие эту возможность.

Примеры

Если бы речь шла об одном временном блобе, создаваемом в короткой транзакции, то никакого "внезапного роста БД" не было бы. Рост БД из-за временных блобов возникает в двух случаях:

• когда в одной длительной транзакции делается много одиночных или масовых операций, создающих временные блобы
• когда в относительно короткой транзакции выполняется операция, создающая много временных блобов

Буквально, если в одной транзакции повторить 1000 раз вызов то мы получим 1000 временных блобов, которые освободят занимаемое ими место только при завершении этой транзакции (не смотря на то, что результат первого из тысячи запросов уже давно никому не нужен).

Более легко получить увеличение БД можно операциями LIST или конкатенации, если обрабатывать большие объемы данных в процедуре/триггере или даже одним оператором SQL. Например, в таблице T 500 тысяч записей, столбец s – блоб. Запрос обрабатывает все записи, в результате возникает 500 тысяч временных блобов (функция replace над блобом порождает временный блоб). Однако, поскольку результирующий blob id "наружу" запроса не выдается, все временные блобы, созданные этим запросом, будут освобождены при закрытии запроса, а не при завершении транзакции.
  Еще вариант от Arioch: создано view с конкатенацией строковых столбцов. Затем, через некоторое время, тип одного из столбцов заменяется на текстовый blob, потому что длины строк мало для хранимых данных. Результат – на каждую запись, возвращаемую view, создается временный blob. Неожиданно при обращении к этому view база начинает пухнуть.
 

Вывод

Радости, предоставляемые LIST и конкатенацией блобов, оборачиваются "внезапным" ростом БД. "Внезапным", например, потому, что вчера был написан некий код (добавление данных к блобу, получаемому с клиента, или отчет, и т. д.), а сегодня наблюдаем рост БД. Или, вчера тестировали код на 10 записях, а сегодня – на миллионе. Или, мы написали отчет, а им пользуется (пока) только на одном сервере, и только на этом сервере БД резко растет.
 

Как найти проблемный код?

Поскольку речь идет о функциональности Firebird 2.1 и выше, к счастью, есть таблицы mon$, которые позволяют мониторить текущую активность сервера. Как вы уже поняли из изложенного выше, проблемные запросы, транзакции и коннекты должны иметь достаточно высокий объем записи страниц в БД.
См. столбцы mon$page_reads и mon$page_writes

Дальше, зная, что это за пользователь, можно выяснить, какие действия он выполнял в приложении, а уже затем – к каким операциям над БД приводят эти действия. Если все пользователи примерно равны, то придется искать упомянутые в примерах варианты в коде запросов, процедур и триггеров.