Root Cause and Context

Mautic's database can become bloated over time due to the accumulation of data in tables like page_hits, email_stats, and campaign_lead_event_log. This bloat often leads to slow queries, particularly when generating reports or updating segments. While Mautic includes built-in cleanup commands, they may not be sufficient for large installations or long-running instances.

The primary culprits are:

  • Page Hits: Every visit to a tracked page adds a new entry, quickly leading to millions of records.
  • Email Stats: Detailed email interaction data, including tokens and open details, can consume significant space.
  • Campaign Event Logs: Records of lead interactions with campaigns accumulate over time.

These tables grow rapidly, especially in installations with high traffic or extensive email campaigns. The result is slower query performance, increased database load, and occasional timeouts during critical operations like segment updates.

Diagnosing Database Bloat

Before taking any action, it's essential to assess the current state of your Mautic database. Using phpMyAdmin (accessible via cPanel or DirectAdmin), you can:

  1. Navigate to your Mautic database.
  2. Review the size of key tables: page_hits, email_stats, campaign_lead_event_log.
  3. Check index sizes and fragmentation using the 'Overhead' column.

For example, a healthy email_stats table might be a few hundred MB, while a bloated one could exceed 19GB. Similarly, page_hits should ideally be under 1GB. If these tables are significantly larger, it's time for cleanup.

Automated Cleanup Strategies

Mautic provides several commands for automated cleanup. To implement these:

  1. Access your hosting account's terminal or cron job interface.
  2. Set up recurring tasks using the following commands:
php bin/console mautic:unusedip:delete -n --limit=99999999999
php bin/console mautic:maintenance:cleanup --days-old=21 -n
php bin/console doctrine:query:sql "UPDATE email_stats SET tokens = NULL WHERE date_sent < (NOW() - INTERVAL 30 DAY);"
php bin/console doctrine:query:sql "UPDATE email_stats SET open_details = NULL WHERE date_sent < (NOW() - INTERVAL 30 DAY);"

These commands handle:

  • Deleting unused IP addresses
  • Cleaning up old records
  • Nullifying detailed email stats older than 30 days

For more granular control, consider using the Leuchtfeuer Housekeeping Bundle, which allows targeted cleanup of specific tables and campaigns.

Manual Cleanup and Optimization

When automated methods aren't sufficient, manual intervention may be necessary. In phpMyAdmin:

  1. Identify orphaned records: Check for leads that haven't been active in campaigns for extended periods.
  2. Delete old page hits: Remove records older than a set period (e.g., 6 months) using SQL queries.
  3. Optimize tables: After deletions, use the 'Optimize Table' feature to reclaim space and improve performance.

Always back up your database before performing manual deletions. Additionally, monitor query performance using EXPLAIN statements to identify bottlenecks, particularly in complex operations like segment updates.