If you are running a larger Mautic instance with a bunch of daily pageviews and lot’s of emails sent, you might experience some slowdown with time. Once you look under the hood, you might even find a bloated database, over 60 GIG, and wonder how to go further.

In this tutorial I’m talking about controlling the weight giving you example outcomes.

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;

WOW! 47 GIG! No wonder it’s slowing down. So I checked which table is the culprit:

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:

Yes, you see it correctly. Took me half an hour. And I don’t even have a slow server…

Okay great, now it’s time to optimize the whole email_stats table:

OPTIMIZE table email_stats;

This will restructure things in the database, and free up a bunch of space. It took me 10 minutes:

And the results are pretty impressive:

The space we take in the DB is now 500% smaller 🙂

Trimming the Page hits

The second largest table was page hits. First I’ll remove the orphaned page hits, those where the lead is already gone:

DELETE from page_hits where lead_id is NULL;

With the next steps I want to be less brutal. I only want to trim away anonymous page hits wich are older then one year. I wondered how many are we talking about:

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.