Как сделать ротационный лог средствами MySQL?

Question

[xSkyFoXx]

У менять есть достаточно большое приложение, которое работает с БД. При этом я логирую некоторые из событий в БД. Но лог на столько быстро разростается, что возникла острая необходимость как-то его чистить. Конечно можно дописать чистку и на стороне приложения, но очень хочется передать эту задачу самой СУБД.
Можно ли как-то средствами MySQL реализовать какой-то из сценариев?:
1) Лог не должен привышать, например, 2 ГБ. Где лог — это одна таблица, не содержащая бинарные данные. Когда достигается предел этого значения самые старые (по дате или первичному целочисленному ключу) удаляются, а новые — добавляются.
2) Лог (повторюсь, 1 таблица) не должен содержать записи, которые старше 2х месяцев. При этом поле, содержащее дату и время создания записи — присутсвует.
Конечно хочется обойтись без «задач по расписанию» т.к. не на всех серверах mysql обновлён до нужной версии (если не изменяет память, >=5.1.26). Но если без него никак — ну и ладно.

Answer 1

[Dmitry T.]

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

delimiter //
CREATE TRIGGER ins_logtable AFTER INSERT ON logtable
FOR EACH ROW
BEGIN
    SELECT NOW() - interval 2 month INTO @rotate_date;
    DELETE FROM logtable WHERE added_date < @rotate_date;
END;//
delimiter ;

Этот тригер будет выполнятся при каждом INSERT в таблицу и удалять все из таблицы старше 2-х месяцев.
Хотя я бы сделал все же скрипт запускаемый по крону, т.к. чистка через тригер может повлиять на производительность. :(

Answer 2

[Wott]

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

заведите юзера в mysql у которого минимум прав на эту таблицу и сделайте функцию, которая делает что нужно. из скрипта вызывай ее
mysql -u user -ppass -e 'select the_function();'

2. с датой просто:
delete from log_table where created<date_sub(now(), interval 2 month);

1. с размером чуть сложнее — в information_schema.tables есть data_length в байтах — можно взять разницу между 2*1024*1024 и data_length и поделить на data_length/table_rows — получим сколько строк, примерно, нужно удалить. что то типа
select if(data_length>2*1024*1024,floor((data_length-2*1024*1024)/(data_length/table_rows)),0) from information_schema.tables where table_schema='db_name' and table_name='log_table' into @row_cnt; delete from log_table order by created limit @row_cnt;

при этом конечно же надо завести индекс на created — поле где дата создания

Answer 3

[Alukardd]

Вопрос, конечно, интересный, но почему не обойтись классическим logrotate?

Comments

[xSkyFoXx]

logrotate следить за состоянием на более низком уровне. Он наблюдает за работой самого сервера БД и регистрирует именно системные события. У меня же несколько другой шаблон использования. Мой log engine пишет события, совершаемые пользователем в системе в нужном мне формате: кто, что, где и когда. Это совсем не накладывается на парадигму слежения за состоянием сервера БД. Хотя, возможно, я не верно понял mysql-log-rotate.

[Alukardd]

Извентиляюсь, я не сразу понял, что лог — это, как вы сказали, таблица.

Answer 4

[mgyk]

В mysql такой встроенной функциональности нет.
Теоритически можно подвесить триггер, который на вставку будет проверять размер базы через information_schema таблицу и удалять от туда записи. Но есть несколько минусов
1) При использовании innodb вы не сможете получить точный размер таблицы
2) Очередная вставка может вызвать удаление старых данных и если у вас лог пишется не асинхронно, то вызовет заметное притормаживание остальных операций.
Более правильный вариант это сделать отдельные таблицы по году+месяцу с engine=Archive и дропать старые по крону раз в день. Проверять общий размер архивных таблиц и если что — грохать самую старую из них

Answer 5

[strib]

1) Не надо крон. Есть events. dev.mysql.com/doc/refman/5.1/en/events.html
2) Секционирование таблицы (например по дням). А потом рубить партиции. Будет гораздо быстрее чем delete (т.к. не используется транзакционный механизм).

Answer 6

[charon]

мы сделали так: функция сохранения записи в БД вычисляет название таблицы по сегодняшней дате, например, the_log_2012_09, затем проверяется существование таблицы в БД и в случае неуспеха таблица создаётся, затем в таблицу добавляется запись. Старые таблицы можно на досуге переносить/удалять и всё такое.