Delete-logs-data never completes

Support & Bugs
1

Good Day Everyone,

We have a large Matomo presence and are having issues with the delete-logs-data never completing when it is scheduled to run via Crontab.

Database size is currently over 400GB. We are trying to run the following to delete 30 days of old log data and it will run for an entire week and never finish.

example: “console core:delete-logs-data --no-interaction --dates=2021-01-01,2021-02-01”

We average around 6 million page views a day hitting this system. We are currently running version 4.2.1 and we see a ticket that may help this with 4.3.1 and plan to update sometime this week.

Wondering if anyone else has been having similar issues or have an idea of a work around to help get the old data cleaned up? We have about 18 months of data today and are thinking the delete maybe has never worked properly? So may have 18 months of log data?

2

Anyone have any suggestions here on how to best start a cleanup process of the raw log data? 4.3.1 didn’t seem to help with the cleanup. I even have tried running some straight SQL scripts to help cleanup and they take the system offline by maxing out MySQL connections.

Example of script tried.
DELETE analytics.matomo_log_visit, analytics.matomo_log_link_visit_action, analytics.matomo_log_conversion, analytics.matomo_log_conversion_item
FROM analytics.matomo_log_visit
LEFT JOIN analytics.matomo_log_link_visit_action ON matomo_log_visit.idvisit = matomo_log_link_visit_action.idvisit
LEFT JOIN analytics.matomo_log_action ON matomo_log_action.idaction = matomo_log_link_visit_action.idaction_url
LEFT JOIN analytics.matomo_log_conversion ON matomo_log_visit.idvisit = matomo_log_conversion.idvisit
LEFT JOIN analytics.matomo_log_conversion_item ON matomo_log_visit.idvisit = matomo_log_conversion_item.idvisit
WHERE visit_last_action_time <= ‘2021-01-01 00:00:00’;

We have over 1 billion rows in the tracker tables today consuming over 220G in data and 100G in index.

I have been running into a “Lock wait timeout exceeded; try restarting transaction” when set to 3600 when running above script…

3

Hi, we faced the same issue and I was able to delete data by passing in the “limit” option

./console core:delete-logs-data --dates=2019-01-01,2019-02-31 --limit 1000

I have been running into a “Lock wait timeout exceeded; try restarting transaction” when set to 3600 when running above script…

This might imply that the delete is not actually running, but instead waiting for a table lock due to another process. I would first try to run with a small limit, for us it took over 10 days to remove a few years of data. Then you might try running the delete in maintenance mode. We use the Queued tracking plugin to capture data during upgrades:

Another tip that helps us reclaim hundreds of GB of disk space us running

mysqlcheck -o

This runs mysql OPTIMIZE on all tables warning it will take a while and requires a table lock but reduced our matomo database from 296GB to about 150Gb.