Managing your database’s size

Matomo (Piwik) is designed to be run in any environment, which means no matter how much database space you have available, Matomo will work for you. In fact, Matomo grants you the power to choose exactly what data to keep, and what data to delete.

This functionality is available on the Privacy > Anonymize data admin page in the Delete old visitor logs and Delete old reports sections.

Here you can pick whether you want to keep or delete visitor logs (the data that is recorded each time a visitor action is tracked) and whether you want to keep or delete processed reports (the data that is created when your visitor logs are analyzed). By default, nothing is purged.

Additionally, you can also see how much space your database is taking up and get an estimate for how large your database will be after the next purge:

Using these tools, you can keep your Matomo database at a manageable size, regardless of your space limitations.

NOTE: Though Matomo allows you to delete your data, we strongly recommend that if you have no space constraints, you keep all your data.

Deleting old unprocessed data

When Matomo tracks a visit, action, conversion or something else, data describing this action is stored in the database. This data is raw visit information and does not show you any trends or analytical information. It is processed later during the archiving process which creates the analytical information (referred to as report data).

After this data is analyzed, it is no longer needed, so it can be a good idea to delete it if you have limited database space.

To set this up, go to Administration > Privacy > Anonymize data and enable Regularly delete old visitor logs from the database. Then experiment with how old you want the purged log data to be. Everytime you modify the value, a new database size estimate is calculated, so you figure out exactly how much data you need to delete.

When done, click Save and then Yes after reading the prompt.

WARNING

Although this data is not necessary after your reports are processed, it is still needed if you want to use the Visitor Log to view old visits or if you want to reprocess your reports. There are a couple reasons you may want to do this, including:

  • There was a bug in Matomo that affected some reports and you’d like to see old reports that are not affected by it.
  • There are new reports in a new version of Matomo, and you want to see them for old data.
  • You want to apply geolocation to old visits.

Deleting old reports

When Matomo analyzes your tracked data, it stores the result of this analysis in the database so the analyisis does not have to be run every time you view a report. This stored data are referred to as reports.

If for some reason you do not have to or want to analyze old visit data, or if you don’t normally look at this old data, you can delete it to save database space.

To set this up, go to Administration > Privacy > Anonymize data > Delete old aggregated report data and enable Regularly delete old reports from the database. Then decide which reports/metrics you don’t want and which you do.

You have the choice of deleting all your reports & metrics, or just some. Everytime you make a change to these options, a new database size estimate is calculated so you can see how much space you will save.

When done, click Save and then Yes after reading the prompt.

If later you want to see the reports you deleted, you can reprocess your reports.

WARNING

Deleting your old reports means you won’t be able to see long term trends.

Additionally, if you also delete old log data, you will not be able to reprocess your reports, meaning report data will be permanently lost! Please think carefully before deleting both unprocessed data and reports.

Manually triggering a data purge

Data purging is done on a regular basis. You can decide how long to wait between purges on the screen:

You may find, however, that you need to execute a purge right away. If you want to do this, there is a link you can click:

Clicking it will start a purge. When the purge is complete a new database size estimate will be displayed.

Note: Purging data can take a LONG time.

Purging and optimizing the database using a console command

You may also run two commands to force the purging of old archived reports and to force optimizing your database tables. You can run the following two console commands in your Matomo directory:

./console core:purge-old-archive-data all
./console database:optimize-archive-tables all

These two commands will:
1. 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.
2. trigger an OPTIMIZE TABLE on all Matomo tables in the database and maybe lower your disk usage.

Note: The core:purge-old-archive-data will automatically execute the optimization query for all report tables, so it isn’t necessary to manually execute the database:optimize-archive-tables if you’re already executing the purge command.

To reduce execution time, you can select specific date periods to run these scripts for. This means that you can target just the recent months, for which report data is likely to have changed. Note that data for annual reports is stored in the archives for January of the appropriate year, so it is a good idea to optimize these regularly as well, e.g.

./console database:optimize-archive-tables last2
./console database:optimize-archive-tables january

We recommend to set up above two commands as a cronjob and have them executed once monthly.

Viewing reports about how your Matomo database size is used

In order to get a detailed view of how your Matomo MySQL database disk space is being used, we have developed a plugin called DBStats which is by default bundled with your Matomo platform. Before you can see the Database usage reports, you need to Activate the plugin DBStats in Matomo > Administration > Plugins. The database usage reports may take a few hours to be created.

The Database Usage includes all the information you need to know about your Matomo database which is very helpful to understand better how to mange the database size. For example, ratio of disk used for storing Raw log data VS storing aggregate report data, The data size by table, The data size by plugin and by report, and more.

You now know everything you need to know to keep your database size manageable!