Deleting old database records (from campaign_lead_event_log) due to extreme database CPU usage

Product Support
1

Your software
My Mautic version is: 3.2.0
My PHP version is: 7.3.26
My Database type and version is: MySQL v8

Your problem
I posted some context here, but this is a different question. Essentially, my mautic tables are huge — email_stats at 25GB, page_hits at 15GB, campaign_lead_event_log at 9GB, and lead_event_log at 3GB.

The DB size is no problem to me (I’ve been cleaning old records in page_hits and email_stats using advice in the original thread — anyone who wants to reduce the size of their DB, check that original thread out for some great advice!).

However, the Mautic cronjobs that execute campaigns also run mySQL queries that are hogging up a 90%+ of my CPU and result in an extreme amount of disk reads. My entire server goes extremely slow every time the cronjobs are scheduled.

From looking into the mysql queries, it seems like the problem here is the campaign_lead_event_log table.

I would like to clean up and reduce the size of the campaign_lead_event_log table — there are events in the table from 3 years ago, but only the last 2-3 months are relevant for campaigns.

I would like some advice on how to clean up that table which doesn’t result in any inconsistencies or issues in Mautic. Perhaps some help on the right SQL query I can use.

On the original thread, there was a post from @ekke that said:

“Most importantly: make sure that for every lead the youngest entry in campaign_lead_event_log does NOT get deleted!”

There was also a suggestion from @sfahrenkrog1 on a query that could do this (deletes the last 30 days of data while keeping the youngest record). Here it is:

select * FROM campaign_lead_event_log 
	WHERE date_triggered < (NOW() - INTERVAL 30 DAY) 
	and campaign_lead_event_log.id not in ( select max(id) FROM campaign_lead_event_log  group by lead_id, campaign_id )

I wanted to ask if 1) there is anyone who understands where campaign_lead_event_log is used, and 2) if so, is that query above ok to execute, and 3) is there anything else I need to be aware of when cleaning out the campaign_lead_event_log table?

I’d really appreciate any advice. Thank you!

2

HI instamaker

We use this query in production to cleanup the campaign_lead_event_log:

# cleanup campaign_lead_event_log after 30 days 
DELETE clel FROM campaign_lead_event_log clel WHERE date_triggered < (NOW() - INTERVAL 30 DAY) and clel.id not in ( SELECT max_id FROM ( select max(id) as max_id FROM campaign_lead_event_log  group by lead_id, campaign_id) AS tmptable );

and to free up the space:

OPTIMIZE LOCAL TABLE campaign_lead_event_log;

The campaign_lead_event_log “logs” every action a lead executed in the campaign. But the term “log” is confusing because it is not just logging.

Mautic checks in this table if the campaign action was already executed for the lead!
So: if you delete the wrong ones, you could trigger another execution of the campaign action for the lead.
Worst case scenario: Leads getting emails again from a campaign action … etc.

(That was the warning from ekke)

The solution to cleanup the table:
Don’t delete the last logged action per campaign per lead. Then nothing happens.

If you want to be safe then maybe restrict the query to old campaigns which are no longer published?

Greetings
Sebastian

3 Likes
3

Hey Sebastian, appreciate the thoughtful reply and explanation. That clears things up — I’m going to go in and clear that table now :slight_smile:

1 Like