Deleting old log data in table log_action


#1

Recently I have discovered the feature to delete old visitor logs from the database via the PIWIK admintool and keeping the processed archived ones. I had a database size of about 17GB, that shrunk to 9GB. Great tool :wink:

Is it possible that the table log_action did not improve from this feature? It is still about 2.6GB with 23 million records. The problem is, that i have many URLs with a unique hash in it. These are one time URLs for my user to download something. Therefore I do not need them later, but they can still be found in log_action.

Is there a possibility to integrate log_action into the purge mechanism?


(Matthieu Aubry) #2

Unfortunately it’s not possible at this stage, because the table is a giant lookup table. However, you can probably delete most of it and it will not cause many problems (assuming you run How to Set up Auto-Archiving of Your Reports - Analytics Platform - Matomo AND you have done a recent full run).

So, if most reports are archived, you could then delete piwik_log_action … but maybe don’t delete the most recently used 100K or something like that: ?


#3

Thanks, I will just delete some records manually. Probably a “delete” clause with a “join” checking if the hash is still used in other tables could easily work when purging.

Best regards


(Matthieu Aubry) #4

Good idea. If you come up with a fast SQL query and test it, please then create a ticket in dev.piwik.org and we’ll add it to the core code!


(Matthieu Aubry) #5

I created a ticket: Delete old logs should also delete unused urls from piwik_log_action · Issue #2805 · matomo-org/matomo · GitHub

please post there your comments, if any :slight_smile:


#6

Matt, thanks a lot!