Keeping a mautic database healthy

mautic is a very powerful marketing automation tool, but one of the drawbacks is how quickly the database can grow. This can cause issues for those with limited server space or sometimes the queries just take too long.

There are a number of approaches, each with positive and negative effects. Listed below is a summary of each.

Note & Disclaimer: Most of the work is done via command line. Do not attempt this if you have no experience. Always perform a database backup in case this does not work out.

How to solve

Each of the commands below should be run via shell using the mautic CLI. Each command can be run individually.

# Standard mautic cleanup up commands (delete only unused IPs and anonymous leads which are at least 3 weeks old)
# Process multiple times
php bin/console mautic:unusedip:delete -n --limit=99999999999
php bin/console mautic:unusedip:delete -n --limit=99999999999
php bin/console mautic:maintenance:cleanup --days-old=21 -n
php bin/console mautic:maintenance:cleanup --days-old=21 -n

# After 30 days clear info in email stats about specific email details => email is still in lead profile
php bin/console doctrine:query:sql "UPDATE mautic_email_stats SET tokens = NULL WHERE date_sent < (NOW() - INTERVAL 30 DAY);"
php bin/console doctrine:query:sql "UPDATE mautic_email_stats SET open_details = NULL WHERE date_sent < (NOW() - INTERVAL 30 DAY);"
php bin/console doctrine:query:sql "UPDATE mautic_email_stats SET last_opened = NULL WHERE date_sent < (NOW() - INTERVAL 30 DAY);"

# Delete email stats without existing lead
php bin/console doctrine:query:sql 'DELETE from mautic_email_stats where lead_id is null;'

# After 6 months delete email stats emails
php bin/console doctrine:query:sql 'DELETE from mautic_email_stats where date_sent < (NOW() - INTERVAL 180 DAY);'

# After 3 months store emails stats which were read by a user
php bin/console doctrine:query:sql 'DELETE from mautic_email_stats where date_sent < (NOW() - INTERVAL 90 DAY) and is_read=0;'

# Clean audit log from leads which do not exist
php bin/console doctrine:query:sql 'DELETE al from mautic_audit_log al where bundle="lead" and object="lead" and object_id not in (select id from leads where id=al.object_id);'

# Clear notifications
php bin/console doctrine:query:sql "TRUNCATE TABLE mautic_notifications;"

# Delete 1 month old import entries
php bin/console doctrine:query:sql 'DELETE FROM mautic_lead_event_log where bundle="lead" and object="import" AND date_added < (NOW() - INTERVAL 30 DAY);'

# Delete all pages hits no longer connected with any lead (anonymous or non anonymous)
php bin/console doctrine:query:sql 'DELETE from mautic_page_hits where lead_id is NULL;'

# << RISKY test first >> Delete event logs older than 30 days (Works fine in most scenarios)
php bin/console doctrine:query:sql "DELETE FROM mautic_campaign_lead_event_log WHERE date_triggered < (NOW() - INTERVAL 30 DAY);"

# << RISKY test first >> Delete event logs older than 30 days (Same as above, might be better solution)
#php bin/console doctrine:query:sql "UPDATE mautic_campaign_lead_event_log SET metadata = '' WHERE date_triggered < (NOW() - INTERVAL 30 DAY);"

# Cleanup campaign_lead_event_log after 30 days
php bin/console doctrine:query:sql "DELETE clel FROM mautic_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 mautic_campaign_lead_event_log  group by lead_id, campaign_id) AS tmptable );"

# IMPORTANT Free up MYSQL disk space
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE mautic_page_hits;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE mautic_audit_log;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE mautic_email_stats;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE mautic_notifications;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE mautic_campaign_lead_event_log;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE mautic_lead_event_log;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE mautic_ip_addresses;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE mautic_lead_ips_xref;"

Source: fahrenkrog

Taking it a step further

Now it does not make sense to run each command above manually, it will simply take too long, so ideally we should add this to a shell script and run a cronjob every few days or once a month depending on the size of the database and the data you would like to retain.

If you would like assistance with any shell script or mautic maintainance, do get in touch.