The great Mautic weight control

My example instance is quite large for an average Mautic instance. It serves a website with daily 30.000 pageviews and 120.000 – 150.000 emails sent in average monthly.

It’s been online for 15 months, and noone ever trimmed it before, and it started to get slow. I logged in into the DB and checked the size.

sudo mysql;
use mymauticdb;
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema;

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "mauticdb"
ORDER BY (data_length + index_length) DESC;

Gave me:

Okay, so 90% of our problem is email stats and page hits. Everything else is managable. Let’s start with email stats.

Trimming Email stats

This table can be super large, because a column called “tokens” saves the huge chunks og html for each individual email you send out. Not the whole email, but depending on your design it can contribute to hoarding unneccessary information. This column will enable you to open and display an email after you sent it out.

I decided to NULL out this column for every email older then 3 months. I think any email content will be expired after so long time.

SELECT id FROM email_stats WHERE date_sent < (NOW() - INTERVAL 90 DAY) ORDER BY id DESC LIMIT 10;

This gave me the id of sent email, which is: 1886949. Anything with smaller id, then this has to loose that overweight “tokens” column content. In other words I’ll replace with a NULL.

My command would look like this:

UPDATE email_stats SET tokens = NULL WHERE id < 1886949

But it gives me an error. The dataset is just way too large, I have to break it down into two parts:

OPTIMIZE table email_stats;

DELETE from page_hits where lead_id is NULL;

SELECT COUNT(page_hits.id) from page_hits, leads WHERE page_hits.date_hit < (NOW() - INTERVAL 360 DAY) AND page_hits.lead_id = leads.id AND leads.email IS NULL

That is a large dataset. We probably will have to clean it in chunks. I will used the following command:

DELETE page_hits
FROM page_hits
INNER JOIN leads ON page_hits.lead_id = leads.id
WHERE page_hits.date_hit < (NOW() - INTERVAL 360 DAY)
AND leads.email IS NULL;

Since the dataset is too large, I had to go step by step.

As you can see, it took 45 minutes to clean it out, but finally the job is done.

After cleaning the table optimization is also super important:

OPTIMIZE LOCAL TABLE mauticdb.page_hits;

Now let’s see how much our database has shrank:

SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "mauticdb" ORDER BY (data_length + index_length) DESC;

Wow, our database shrank form the original 47 GB to less, then half, 23 GB.

Why not just do an official Mautic maintanance?

It is also an option to use the mautic:maintenance:cleanup command, it would remove some info what I’d like to keep, and leave some bloated tables, that I want to remove. As you could see I’m more strick with the old email contents, I deleted those bloated tables after 90 days. All the tracking will be intact, just the webview is lost in this case. I’d like to keep page view information even if it’s older then a year if we are talking about identified visitors. Unknown visitors year old pageviews can be deleted.