Opened 2 years ago

Closed 21 months ago

Last modified 20 months ago

#3003 closed Bug (fixed)

Huge DB Disk space savings! Database purge should trigger on all archive_* tables at least once a day

Reported by: matt Owned by:
Priority: critical Milestone: 1.8.3 - Piwik 1.8.3
Component: Core Keywords:
Cc: Sensitive: no

Description (last modified by matt)

The purge old temporary archives is probably not run on every table once in a while. Some tables are huge, wasting a lot of valuable mysql DB space (especially for shared hosts where this is a real problem).

Bugs:

  • From looking at "Database Usage" I see that this table is much bigger than others that are only 65M.

piwik_archive_blob_2011_10 4.6 M 1.4 Gb 144.9 Mb 1.6 Gb

But there are others: The one for Jan 2012 seems a bit big too, maybe temporary yearly reports not purged? Same for Jan 2011, 3-4 times bigger normal table, and for Jan-Feb 2008.

  • It also appears that the archive_numeric are not purged neither. These are huge it hurts. Why would August 2008 numeric table for the demo has 119,800 records?

We should check WHY this data is not purged yet. This will free a lot of space and will make everyone very happy!

Change History (8)

comment:1 Changed 2 years ago by matt (mattab)

  • Description modified (diff)
  • Summary changed from Database purge should trigger on all archive_* tables at least once a month to Database purge should trigger on all archive_* tables at least once a day

comment:2 Changed 23 months ago by falco55

comment:3 Changed 22 months ago by matt (mattab)

  • Milestone changed from 1.8.x - Piwik 1.8.x to 1.8.3 - Piwik 1.8.3

This was also reported in forum specifically for the Janauary table -- maybe yearly reports are not deleted correctly. Especially reports with timing out memory/incomplete?

comment:4 Changed 22 months ago by parisbonbon

comment:6 Changed 21 months ago by matt (mattab)

  • Resolution set to fixed
  • Status changed from new to closed

(In [6692]) Fixes #3003

  • Added new daily scheduled task: it will go through all archive table and delete outdater or incorrect records
  • Refactored code see getTablesArchivesInstalled()
  • Added test for core scheduled tasks

This will result in much disk space currently wasted being deleted the first time the new scheduled task runs after update!

comment:7 Changed 21 months ago by matt (mattab)

(In [6699]) Refs #3003 Enable task

comment:8 Changed 20 months ago by matt (mattab)

  • Summary changed from Database purge should trigger on all archive_* tables at least once a day to Huge DB Disk space savings! Database purge should trigger on all archive_* tables at least once a day
Note: See TracTickets for help on using tickets.