Add option to archive january archive of this / last year #14064

garas · 2019-02-01T16:50:34Z

I have set:

Delete old aggregated report data after 12 months
Keep all data for: Yearly+Monthly reports
MySQL has innodb_file_per_table=ON.

After Matomo deleted old data, but left yearly/monthly reports, table size for piwik_archive_blob_2017_01 reported by phpMyAdmin reduced 150MB to 4MB. However actual .ibd size left 150MB.

After running OPTIMIZE piwik_archive_blob_2017_01, the .ibd file was rebuilt and now takes 4MB.

You can see data, index and free space by running

SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `DATA_LENGTH`, `INDEX_LENGTH`, `DATA_FREE`
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` = 'piwikdb' AND `TABLE_NAME` LIKE '%archive_blob%'

Could Matomo run OPTIMIZE on archive tables after deleting much data?

In my case it reduced Matomo total DB space 4GB to 1GB.

The text was updated successfully, but these errors were encountered:

tsteur · 2019-02-01T18:28:52Z

There is a command ./console database:optimize-archive-tables to do that.

We also run Optimize automatically sometimes but usually not when it is InnoDB unless it is MariaDB

tsteur · 2019-02-01T18:29:11Z

I recommend you set up a cronjob maybe to do it monthly for example.

garas · 2019-02-01T21:03:51Z

I've tried that command. Options 'all', 'now', hidden lastN are not useful, I don't need re-optimize all or recent tables each month.

Last option is specify concrete months. But it is also hard generate month in shell script. But finally I found solution: php console database:optimize-archive-tables 'now - 14 months'. 13th month still has daily reports, 14th is the one I need, better keep not optimized data 1 month longer than forever.

However I still think Matomo could be run in this predictable (deleting daily, but keeping monthly or yearly reports) time when optimization is very useful. Maybe checkbox at Keep all data for.

tsteur · 2019-02-03T06:18:49Z

I'm not sure if optimize table will work on all versions of MySQL 4.1+ (or 5.1+) with InnoDb (I know it's basically just rebuilding the table, but not sure what happens in older versions).

We could enable it for InnoDb for archive tables only. Log tables be too risky re downtime etc. Would for sure not enable it for log tables. In general, personally, I would disable enable_sql_optimize_queries = 0 in config and only run the command regularly. Might be good to recommend this some day or make it default (you don't want to lock log tables)... @mattab

Re the command: I think in general lastMonth, and first month of the year (because it stores yearly archives there) be useful. When running this regularly, it should be a lot more efficient than optimizing all archives tables all over again.

So it would be definitely good to add an option for first month of the year (last month should be doable with last1).

tsteur · 2019-02-03T06:20:15Z

Put it into 3.10 as it would make it our optimize tables much more efficient by not optimizing all tables again and again.

garas · 2019-02-03T16:23:48Z

Yes, it should be enabled for archive tables only because of predictable high data savings and because of locking of archive tables would only disrupt Matomo dashboard, not tracking.

For predictable, I mean it could shrink tables ~35 times! (30 day + 4 weeks + 1 month ->1 month aggregated info. It is assuming 1 day takes about same amount of space as 1 week or 1 month). For January, it would be ~18x savings, as it will contain 1 year + 1 month.

After removing daily data, these tables usually won't be written anymore, so it is good time prepare for long term storage by running one last final optimization and this would remove need for optimizing all tables again and again without predicted outcome.

For log tables, locking is problem, also savings are unpredictable because sites traffic can increase/decrease, also space will be reused after deleting data. Optimization here is manual decision.

katebutler · 2019-05-07T05:48:03Z

If the dates option doesn't match any of the predefined values ("all", "now" or the lastN options) then it is passed to the DateFactory - this means that "january" will already work.

There's no guide for the optimize tables command on matomo.org at the moment, so perhaps we should write some so that we can document this properly.

mattab · 2019-07-15T01:06:54Z

For now created a short new section in
https://matomo.org/docs/managing-your-databases-size/#purging-and-optimizing-the-database-using-a-console-command

edited After reading #10439 also updated the doc to list another command as well, that may free DB space:

What do you think is the next step for this issue @katebutler ?

garas · 2019-07-15T15:34:27Z

php console core:purge-old-archive-data already optimizes tables after purging.

But it just optimizes all dates independently if it was purged or not any data.
I should filter $dates to optimize only if ($archivePurger->purgeArchivesWithPeriodRange($date) > 0)).

matomo/plugins/CoreAdminHome/Commands/PurgeOldArchiveData.php

Lines 110 to 124 in 52f4230

    
           } else { 
        
               foreach ($dates as $date) { 
        
                   $message = sprintf("Purging custom range archives for %s...", $date->toString('Y_m')); 
        
                   $this->performTimedPurging($output, $message, function () use ($date, $archivePurger) { 
        
                       $archivePurger->purgeArchivesWithPeriodRange($date); 
        
                   }); 
        
               } 
        
           } 
        
           $skipOptimizeTables = $input->getOption('skip-optimize-tables'); 
        
           if ($skipOptimizeTables) { 
        
               $output->writeln("Skipping OPTIMIZE TABLES."); 
        
           } else { 
        
               $this->optimizeArchiveTables($output, $dates, $input->getOption('force-optimize-tables')); 
        
           }

katebutler · 2019-07-15T22:31:54Z

@mattab Thanks, I'll flesh out that doc a bit more and explain the options that are available, think that's all we need to do.

mattab · 2019-10-22T04:53:53Z

Do you maybe have an update on this one @katebutler?

katebutler · 2019-11-06T23:00:31Z

Suggested addition to the https://matomo.org/docs/managing-your-databases-size/#purging-and-optimizing-the-database-using-a-console-command section - I don't have WP access so @tsteur can you please add it?

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

tsteur · 2019-11-07T00:16:12Z

Cheers, done.

tsteur closed this as completed Feb 1, 2019

tsteur added the answered For when a question was asked and we referred to forum or answered it. label Feb 1, 2019

tsteur reopened this Feb 3, 2019

tsteur added the Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. label Feb 3, 2019

tsteur added this to the 3.10.0 milestone Feb 3, 2019

tsteur changed the title ~~Reclaim InnoDB disk space after deleting old data~~ Add option to archive january archive of this and last year Feb 3, 2019

tsteur changed the title ~~Add option to archive january archive of this and last year~~ Add option to archive january archive of this / last year Feb 3, 2019

tsteur removed the answered For when a question was asked and we referred to forum or answered it. label Feb 3, 2019

tsteur modified the milestones: 3.10.0, Backlog (Help wanted) Feb 3, 2019

tsteur added the Help wanted Beginner friendly issues or issues where we'd highly appreciate community's help and involvement. label Feb 3, 2019

tsteur modified the milestones: Backlog (Help wanted), 3.10.0 Feb 3, 2019

katebutler self-assigned this Mar 21, 2019

mattab modified the milestones: 3.11.0, 3.12.0 Jul 23, 2019

mattab modified the milestones: 3.12.0, 3.13.0 Oct 22, 2019

tsteur closed this as completed Nov 7, 2019

tsteur added the not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. label Nov 7, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add option to archive january archive of this / last year #14064

Add option to archive january archive of this / last year #14064

garas commented Feb 1, 2019

tsteur commented Feb 1, 2019

tsteur commented Feb 1, 2019

garas commented Feb 1, 2019

tsteur commented Feb 3, 2019

tsteur commented Feb 3, 2019

garas commented Feb 3, 2019 •

edited

katebutler commented May 7, 2019

mattab commented Jul 15, 2019 •

edited

garas commented Jul 15, 2019

katebutler commented Jul 15, 2019

mattab commented Oct 22, 2019

katebutler commented Nov 6, 2019

tsteur commented Nov 7, 2019

Add option to archive january archive of this / last year #14064

Add option to archive january archive of this / last year #14064

Comments

garas commented Feb 1, 2019

tsteur commented Feb 1, 2019

tsteur commented Feb 1, 2019

garas commented Feb 1, 2019

tsteur commented Feb 3, 2019

tsteur commented Feb 3, 2019

garas commented Feb 3, 2019 • edited

katebutler commented May 7, 2019

mattab commented Jul 15, 2019 • edited

garas commented Jul 15, 2019

katebutler commented Jul 15, 2019

mattab commented Oct 22, 2019

katebutler commented Nov 6, 2019

tsteur commented Nov 7, 2019

garas commented Feb 3, 2019 •

edited

mattab commented Jul 15, 2019 •

edited