Documentation: How to set limits on data

[NickMRamirez]

The documentation does not say. How do I prevent the MariaDB database from growing infinitely? Is there a way to limit the number of events stored, such as "Only keep a month's worth of data?"

[klaubert]

Hi NickMRamirez,

currently waf-fle cant limit the number of events stored on database. From performance perspective the relevant number of events/days and total events stored. Your environment will dictate your break point. To make things smooth, your can/should use a clean-up routine, running on cron, with commands like this (bellow, two options to delete events older than 30 days, performance can be different):

Option A

USE waffle

delete events, events_messages, events_messages_tag, events_full_sections FROM events 
LEFT JOIN events_messages ON events.event_id = events_messages.event_id 
LEFT JOIN events_messages_tag ON events_messages.msg_id = events_messages_tag.msg_id 
LEFT JOIN events_full_sections ON events.event_id = events_full_sections.event_id 
WHERE events.a_date < DATE_SUB(NOW(), INTERVAL 30 DAY)

DELETE from events_hostname where host_id not in (SELECT b_host FROM `events` GROUP BY b_host);

Option B

USE waffle

DELETE FROM events WHERE a_date < DATE_SUB(NOW(), INTERVAL 30 DAY);
DELETE from events_full_sections where event_id not in (select event_id from events);
DELETE from events_messages where event_id not in (select event_id from events);
DELETE from events_messages_tag where msg_id not in (select msg_id from events);
DELETE from events_hostname where host_id not in (SELECT b_host FROM `events` GROUP BY b_host);

After delete old events, is good to optmize table to reclaim the disk space

OPTIMIZE TABLE events_messages_tag;
OPTIMIZE TABLE events_hostname;
OPTIMIZE TABLE events;
OPTIMIZE TABLE events_full_sections;
OPTIMIZE TABLE events_messages;

best regards

Klaubert