Database Size 4.4GB - with campaign_lead_event_log and lead_event_log accounting for 3.7GB

[dirk_s]

Your software
My Mautic version is: 2.6.15
My PHP version is: 7.3

Your problem
Database over time grew quite large. Almost all of the data is in tables:

• campaign_lead_event_log and
• lead_event_log

Is there a safe way to get rid of them? The number of contacts is already trimmed to 365 days with the mautic:maintenance:cleanup

However - this seems not to cleanup the events logs?
How can we save remove records?

[joeyk]

How old is your oldest event?
Joey

[dirk_s]

Earliest event is from 05/2018…

[joeyk]

Maybe you should delete everything, that is really old:

Housekeeping: Help the Mautic Datenbank Stay Lean

[dirk_s]

@joeyk

Housekeeping: Help the Mautic Datenbank Stay Lean

Unfortunately those tipps seem always to be contact related… I would rather cleanup old events, than contacts

[EJL]

I don’t recommend it to everyone, but I use Dbeaver and SQLyog to tidy up.
Add your DB credentials
connect
select the table
use the filters to identify events you want to delete
Select all and delete.

Make a backup before you start of course

[joeyk]

I do the same.
And I also don’t recommend it

[joeyk]

php /path/to/mautic/app/console mautic:maintenance:cleanup --days-old=365 --dry-run

This only deletes:

The above entries (apprx 900 entries) happened in 4-5 days on a site where I only have around 50 visitors / day. You should try the dry run and see what happens, it won’t delete anything just give you heads up.

[dirk_s]

thanks, thats what I did first
will investigate into what you both don’t recommend.
probably I will not recommend it either and do it too.

[sfahrenkrog1]

Hi Dirk

We did the following to cleanup our mautic database:

DELETE FROM campaign_lead_event_log WHERE date_triggered < (NOW() - INTERVAL 60 DAY);
UPDATE campaign_lead_event_log SET metadata = ‘’;

DELETE from lead_event_log where bundle=“lead” and object=“import” WHERE date_added < (NOW() - INTERVAL 30 DAY);

But very important:
Dont forget to do the folowing

OPTIMIZE LOCAL TABLE campaign_lead_event_log;
OPTIMIZE LOCAL TABLE lead_event_log;

Only after this command you free up the space.

We got this from this ticket here:

email_stats table bloat

Greetings
Sebastian

[sfahrenkrog1]

Sorry one thing:
Maybe you want to increase the INTERVAL xx DAY to fit your needs

[dirk_s]

So there are no vital dependencies, that expect the event logs to exist completely?

[sfahrenkrog1]

I used the statements several times and did not recognized any issue with that.

But in the issue one user wrotes:

Be careful with it!

If you have some campaign waiting for “page visits” you can break it.

I tested these queries, and I have caused many messages in my GDPR workflow to be sent again.

My campaign looks like “for new contacts in segment A, send an email, and wait for visits to confirmation-page to move them to another segment.”

I hope you find this message useful.

You could also adapt the sql query and use it only for campaigns which are no longer are active.

[sfahrenkrog1]

I would suggest:
Copy your installation to a staging installation and stop the mail cronjobs for this installation.

Clean the database on the staging installation like you would on the live enviroment and check the mail spool folder for emails to appear.

If there are emails in the spool, chances are high that you trigger some campaigns again. But in my experience this happens rarely if you use an day interval big enough in the query.

[EJL]

You can run mautic:campaigns:validate to see if there are inactive contacts hung up in your campaign as well

[alexhammer]

Hi Sebastian,
why did you use this line

DELETE from lead_event_log where bundle=“lead” and object=“import” WHERE date_added < (NOW() - INTERVAL 30 DAY);

?
Is it only to get rid of the import event in the logs? I mean from imported contacts?

Cheers,
Alex

[sfahrenkrog1]

HI Alex

Is it only to get rid of the import event in the logs? I mean from imported contacts?

Yes, exactly. If your clients use a lot of imports (or if you have something like a hotfolder import ) this gets really huge. But you don’t need to delete this.

greetings
Sebastian

[alexhammer]

Yes I thought so. Thank you Sebastian

[sfahrenkrog1]

Hey Alex

I would like to recommend you this thread regarding the db cleanup:

Mautic database is huge, can I manually delete old records?

Hey Instamaker You could do this commands on the shell (backup + on your own risk!) We do them regularly but I could give you no garanty. There is a new plugin which does some campaign_lead_event_log cleanups: https://github.com/Leuchtfeuer/mautic-housekeeping-bundle **Update: ** Sorry made a mistake in the audit log statement. Fixed it in the gist. Greetings Sebastian

I also published our intern db cleanup script there.

Direct link to my gist in the thread:

https://gist.github.com/sebastian-fahrenkrog/0d53ced6a50ed680383f4680c6398635

Greetings
Sebastian