Delete-logs-data never completes

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?

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…