Как вести логи изменений данных пользователями в базе данных, сохраняя их в другой базе данных

Уважаемые читатели. Это вторая статья из цикла по базам данных. Решил сделать некоторое оглавление по планируемым статьям этого цикла:

1. Как сделать разный часовой пояс в разных базах данных на одном сервере.
2. Как вести логи изменений данных пользователями в базе данных, сохраняя их в другой базе данных, для того чтобы основная база данных не забивалась мусором и не росла.
3. Как создать свою файловую систему на основе blob полей в базе данных. Почему это удобно. Вопросы эффективности хранения файлов: как получить максимальное быстродействие и при этом минимальное занимаемое место.

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

Так же я приветствую конструктивную критику. Бывает люди пишут интересные вещи и ты можешь взглянуть на проблему под углом, о котором не предполагал и как-то улучшить свои механизмы.

Итак начнем.

База данных firebird 3.

Формулировка задачи следующая: необходимо писать подробные логи изменений данных пользователями в базе данных (insert, update, delete), но при этом писать их в другой базе данных на другом сервере. Необходимо это для того чтобы размер основной базы данных не рос как на дрожжах, ее удобно было бекапить, ресторить, чтобы она работала быстро, не накапливала мусора, не содержала лишней и редконужной информации.

Разрабатывался данный механизм для облачной платформы по автоматизации предприятия (https://erp-platform.com), но впервые я столкнулся с данной проблемой работая в отделе биллинга, в телеком компании. Там была база данных биллинга (назовем ее Основная БД), с которой работали все системы, и база данных для личного кабинета клиентов (назовем ее БД ЛК), которая тоже должна была содержать большинство данных из основной БД. Это было сделано, потому что всем было когда-то стремно, что находящийся “снаружи” ЛК смотрит в главную базу данных компании, ибо мало ли…

Для того чтобы данные из основной базы, попадали в БД ЛК (и в некоторых случаях из БД ЛК в основную) разработчиками биллинга был реализован механизм репликации, делались триггеры на insert, update и delete, которые писали все изменения в некую “промежуточную” таблицу, внешний скрипт с определенной периодичностью выгружал новые записи из этой таблицы в текстовики, эти текстовики закидывались на сервер с БД ЛК и там исполнялись в той базе. Все вроде неплохо, НО, база росла как на дрожжах, достигая за несколько месяцев ГИГАНТСКИХ размеров за счет этой таблицы, куда сливались все логи. Устаревающие данные в ней оставались, а не сразу из нее не удалялись, ибо удаление – процесс долгий и способствует накоплению мусора и просто БД будет работать со временем медленнее. Так решили разработчики. В итоге доходило до того, что каждые 3 месяца, приходилось удалять эти данные, и делать бекап-ресторе базы данных, ибо место на сервере просто заканчивалось. Бекап-ресторе таких объемов и на таком сервере – чуть ли не сутки на всю работу. Это каждые 3 месяца остановка на сутки работы всей компании. И это приходилось делать. Хелп деск делал записи в это время на бумажке…

Разрабатывая свою облачную платформу, в виду опыта, я уже осознавал данную проблематику и решил сделать систему записи подробных логов лишенную данных недостатков. Тем более учитывая объемы, что может быть на один сервер не одна база данных, а 1000, и делать этой тысяче бекап-ресторе нереально.

Для начала хочу описать общую концепцию.

Чтобы структуиризировать ведение записей, они ведутся пакетным способом.
Есть две таблицы, таблица с информацией по пакету и таблица с данными этого пакета. Назовем их LOG_PACKET и LOG

LOG_PACKET

1. Уникальный идентификатор
2. Дата записи
3. Автор записи (ид юзера)
4. Признак обработки (0 или 1)
5. Тип записи (ins,up,del)
6. Номер таблицы (название таблицы из которой пишем логи)
7. Номер таблицы с данными
8. Идентификатор записи пакета
9. Количество попыток обработки
10. Дата последней обработки

LOG

1. Уникальный идентификатор
2. Блоб поле для данных OLD
3. Блоб поле для данных NEW
4. Идентификатор записи пакета
5. Номер поля таблицы (название поля таблицы)

В триггере при изменении данных, должна заполняться запись в LOG_PACKET с информацией о чем эта запись и данные (до изменения и после изменения) в таблицу LOG

Далее внешний скрипт, запускаемый с определенной периодичностью, находит не обработанные записи в LOG_PACKET, и делает их копию в базу данных логов на другом сервере, по их идентификаторам находит все их записи в таблице LOG и тоже делает копию.

Далее нам надо избавиться от мусора в основной БД. Удалять данные из LOG – тормозить базу и копить мусор. Есть более быстрая и лучшая процедура DROP TABLE.

PS: Почему DROP предпочтительнее DELETE подробно описывать не буду. Все очень хорошо описано в данной статье.

Поэтому применяется следующее решение.

Таблиц LOG делается 3 штуки. LOG_1 – в первый день данные пишутся сюда, LOG_2 – в следующий день данные пишутся сюда, и т.д. происходит чередование, один день в LOG_1 второй день в LOG_2. Когда данные пишутся в LOG_2 и все пакеты ссылающиеся на LOG_1 обработанны — происходит DROP LOG_1 и CREATE TABLE LOG_1. Таким образом, в момент удаляются все записи и таблица становится чистой. На следующий день аналогично с LOG_2.

НО не так все просто. Т.к. ссылки на LOG_1 и LOG_2 прописаны в триггерах всех логируемых таблиц – удалить их не получится. Для этого надо применить хитрость. Сделать их VIEW. И прописать добавление данных в триггерах через это VIEW.

CREATE OR ALTER VIEW V_LOG_1(
    ID,
    BLOB_OLD,
    BLOB_NEW,
    PACKET,
    NUM_POLE)
AS
select p.id, p.blob_old, p.blob_new, p.packet, p.num_pole from LOG_1 p;

Но даже так удалить таблицу имея триггер не получится ибо VIEW в триггере будет ссылаться на нее.

Для этого надо сделать таблицу LOG_3, которая буде копией по структуре LOG_1 и LOG_2. Заменяем в VIEW LOG_1 на LOG_3 и вауля, можно удалить и пересоздать таблицу LOG_1. После этого меняем в VIEW назад LOG_3 на LOG_1 – и все работает дальше.

Эту операцию можно проводить без проблем, в таблицу LOG_1 в этот момент гарантировано не будет происходить записи, т.к. в этот день запись ведется в LOG_2. Такая операция очистки происходит всегда с таблицей, в которую в этот день записи нет. На следующий день такая же операция произойдет с таблицей LOG_2.

Так же эта операция должна происходить, только если все данные в LOG_PACKET по этой таблице обработаны. Если они не обработаны — то трогать таблицу нельзя, ибо это будет потеря данных. Необходимо сначала разбираться почему данные не обработаны, например, может зависнуть скрипт который их перемещает в базу с логами. Если в данный день операция очистки была отменена, то следующая попытка будет проведена через день и т.д.

Концепцию мы разобрали, теперь разберем оптимальную схему работы.

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

Например

CREATE TABLE LOG_INFO (
    ZAPIS      SMALLINT,
    DATA  TIMESTAMP,
    ID_TABLE       SMALLINT
);

Во всех триггерах логируемых таблиц сначала проверяется, разрешена ли запись

select p.zapis from LOG_INFO p into: zapis;

Если она разрешена, то проверяется, в какую таблицу писать данные

select first 1 
case when p.data=current_date then p.id_table else 
case 
when p.id_table=1 then 2 
when p.id_table=2 then 1 
end 
end, 
case when p.data=current_date then 1 else -1 end 
from LOG_INFO p into: log_info, log_info_check;

PS: для скептиков, first 1 нужно для того, чтобы исключить вероятность вылета триггера если в таблице LOG_INFO вдруг случайно появится более одной записи. В этом случае будет ошибка записи данных во все логируемые таблицы (обычно ошибка multiple rows). А first 1 мы гарантировано исключаем данный вариант.

Данным запросом мы:

1) Проверяем, совпадает ли дата с текущей, если да то пишем в текущую таблицу, если нет, то меняем таблицу на другую (произошел переход дня на следующий);
2) Ставим признак того, что данную таблицу надо проапдейтить на другую при переходе дня.

Следующим шагом если нам необходим апдейт при переходя дня, апдейтим

if (log_info_check=-1) then
UPDATE LOG_INFO SET ID_TABLE = :log_info, DATA = current_date;

Т.е. первая же запись на следующий день апдейтит данные, что писать надо в следующую таблицу. И дальше на это уже ресурсы не тратятся.

Далее пишутся данные в LOG_PACKET и получаем его идентификатор

if (inserting) then TYPE_=1;
if (updating) then TYPE_=2;
if (deleting) then TYPE_=3;
if (TYPE_ in (1,2)) then
INSERT INTO LOG_PACKET (TYPE_, TABLE_, NUM_TABLE, AVTOR, ID_ZAPISI) VALUES (:TYPE_, 15, :log_info, new.avtor, new.id) RETURNING ID into: id_packet;
else
INSERT INTO LOG_PACKET (TYPE_, TABLE_, NUM_TABLE, AVTOR, ID_ZAPISI) VALUES (:TYPE_, 15, :log_info, old.avtor, old.id) RETURNING ID into: id_packet;

Далее в зависимости от полученного номера таблицы, данные должны писаться в V_LOG_1 или V_LOG_2.

Например, запись может выглядеть так:

if (log_info=1) then
begin
if (TYPE_=1) then
begin
INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (NULL, new.n_687, :id_packet, 687);
INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (NULL, new.n_688, :id_packet, 688);
INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (NULL, new.n_689, :id_packet, 689);
INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (NULL, new.n_690, :id_packet, 690);
INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (NULL, new.n_691, :id_packet, 691);

end
                      
if (TYPE_=2) then
begin
if (new.n_687<>old.n_687) then  INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_687, new.n_687, :id_packet, 687);
if (new.n_688<>old.n_688) then  INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_688, new.n_688, :id_packet, 688);
if (new.n_689<>old.n_689) then  INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_689, new.n_689, :id_packet, 689);
if (new.n_690<>old.n_690) then  INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_690, new.n_690, :id_packet, 690);
if (new.n_691<>old.n_691) then  INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_691, new.n_691, :id_packet, 691);

end
                      
if (TYPE_=3) then
begin
INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_687, NULL, :id_packet, 687);
INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_688, NULL, :id_packet, 688);
INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_689, NULL, :id_packet, 689);
INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_690, NULL, :id_packet, 690);
INSERT INTO V_LOG_1 (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE) VALUES (old.n_691, NULL, :id_packet, 691);

end
end

PS: Я себе немного упростил жизнь, тем, что у меня свой встроенный язык программирования в систему и все таблицы и поля у меня идут с номерами, а не текстом. Так же благодаря этому, такие триггера создаются абсолютно автоматически при появлении новой таблицы или пересоздаются при изменении ее полей. Т.е. пользователю достаточно княпнуть в интерфейсе кнопку в редакторе таблицы “Включить логи” и все дальше у него будет происходить автоматически. У читателей все выйдет посложнее, если это база данных простая, то такие триггера придется создавать вручную и типы данных в NUM_POLE и NUM_TABLE должны быть текстовые, т.е. туда надо записывать названия таблиц и полей. Или ввести некую таблицу, где таблицам и полям будут присваиваться номера и брать данные из нее.

Далее необходимо создать таблицу с флагом, произошла ли запись. Так мы будем экономить ресурсы системы, ибо сделать запрос по таблице с одним полем намного быстрее, чем перебирать LOG_PACKET в поисках есть ли флаги новых записей.

Например так:

CREATE TABLE LOG_PACKET_FLAG (ID  SMALLINT); 

И поставить на таблицу LOG_PACET в триггер при добавлении записи нового пакета

UPDATE LOG_PACKET_FLAG SET ID = 1;

При запуске скрипта переноса данных в базу логов надо делать проверку есть ли новые записи (если их нет то ничего не делать больше).

select first 1 p.id from LOG_PACKET_FLAG p

Это будет работать намного быстрее чем, например:

select count(*) from LOG_PACKET p where p.check_=0

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

UPDATE LOG_PACKET_FLAG SET ID = 0;

В общем, экономьте ресурсы своих систем.

Далее рассмотрим сам процесс переноса записей.

Сначала селектом получаем все новые записи LOG_PACKET из нашей основной БД, и в цикле делаем INSERT этих данных в БД логов.

После создания записи пакета в БД логов, нам надо перенести данные этого пакета из таблиц LOG_1(2).

Извлечение данных из блоб полей и добавление их в другое место – это определенный геморой и танцы с бубном. После многих экспериментов я пришел к выводу, что самый простой и эффективных путь, просто из скрипта запустить запрос в котором будет запрос в базу данных (в общем, сделать перетягивание блоб поля прямым запросом из БД логов в основную БД.

Процедура стягивания данных пакета, которую надо дернуть скриптом, после добавления пакета.

сreate or alter procedure BLOB_INS (
    SELECT_ varchar(250),
    BASE_ varchar(100),
    USER_ varchar(50),
    PASS_ varchar(50),
    PACKET integer)
AS
declare variable BLOB_OLD  BLOB SUB_TYPE 0 SEGMENT SIZE 80;
declare variable BLOB_NEW  BLOB SUB_TYPE 0 SEGMENT SIZE 80;
declare variable PACKET    BIGINT;
declare variable NUM_POLE  INTEGER;
begin
    FOR EXECUTE STATEMENT (:select_)

      ON EXTERNAL :base_
      AS USER :user_ PASSWORD :pass_
      INTO :BLOB_OLD,:BLOB_NEW,:NUM_POLE
    DO
        INSERT INTO LOG (BLOB_OLD, BLOB_NEW, PACKET, NUM_POLE)
               VALUES (:BLOB_OLD,:BLOB_NEW, :pacet, :NUM_POLE);
End

:select должен быть подаваться примерно такого вида из скрипта:

select p.blob_old, p.blob_new, p.num_pole from log_'.<номер таблицы>.' p where p.packet='.<ид пакета>

После успешной обработки пакета, надо в основной базе поставить флаг, что этот пакет обработан.

В случае ошибок добавления данных пакета я обновляю данные о количестве попыток, после 5 попыток ставится флаг, что данные обработаны с ошибкой и программа прекращает попытки их обработки. Если не сделать данный механизм, то можно уйти в бесконечный цикл в случае какой-то ошибки и программа перестанет выполнять свои функции. С ошибочными пакетами можно разобраться потом, а программа должна работать дальше.

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

Тут нет повышенного требования к быстродействию, т.к. это отложенная операция. Нам не важно запишутся логи через минуту или через 1.5 минуты, главное, что они запишутся. Пользователи к этим данным обращаются очень редко, только в случае каких-то проблем, и ничего страшного если страница с логами будет грузиться на 200 ms дольше.

В общем, этим вы просто будите экономить ресурсы, лучше эти ресурсы выделите нагруженным машинам.