Page MenuHomeMiraheze

Add a cron to regularly optimise Matomo archive tables
Closed, ResolvedPublic

Description

Today I ran:

./srv/matomo/console database:optimize-archive-tables last2

Which just optimising the last two, cleared about 5GB of storage, through optimisation and may help with some of our piwik database size issues. Additionally this should probably be in the cron script also:

./srv/matomo/console database:optimize-archive-tables january

All annual data is included in January, and additionally, the Matomo docs mention putting these two as a cron script is recommended and a good idea to do.

https://matomo.org/faq/on-premise/managing-your-databases-size/#purging-and-optimizing-the-database-using-a-console-command

I am also not sure if it would be a good idea to run this or not as well:

./srv/matomo/console core:purge-old-archive-data all

The docs state that will:

purge any stale reports in the database as well as reports that were marked for deletion, or were for old date custom ranges, or reports that were invalidated.

I would like to note that it can be significantly slow to run the optimisation, and probably at most it might be good to run once a month.

Event Timeline

Unknown Object (User) triaged this task as Normal priority.Feb 27 2023, 04:28
Unknown Object (User) created this task.
Unknown Object (User) moved this task from Backlog to Matomo on the Monitoring board.Feb 27 2023, 04:31
Unknown Object (User) moved this task from Incoming to Short Term on the Infrastructure (SRE) board.
Unknown Object (User) added a comment.Feb 27 2023, 06:44

Just to note, I have now also ran:

./srv/matomo/console database:optimize-archive-tables all

Which freed a few more GBs.

So should the cron include all of these?

Unknown Object (User) added a comment.Feb 28 2023, 00:38

So should the cron include all of these?

No, just:

./srv/matomo/console database:optimize-archive-tables last2
./srv/matomo/console database:optimize-archive-tables january
Unknown Object (User) unsubscribed.Mar 18 2023, 03:40

Drafted https://github.com/miraheze/puppet/pull/3178, should be good for review, though I'll merge in a few days if no objections.