Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Limiting ecommerce product reports to 10,000 to avoid memory issues with new INI config setting to customise #17816

Closed
pepl opened this issue Jul 26, 2021 · 24 comments · Fixed by #17823
Closed

Limiting ecommerce product reports to 10,000 to avoid memory issues with new INI config setting to customise #17816

pepl opened this issue Jul 26, 2021 · 24 comments · Fixed by #17823
Labels
Bug For errors / faults / flaws / inconsistencies etc.

Comments

@pepl
Copy link

pepl commented Jul 26, 2021

We’ve migrated from GA to Matomo with end of May and are experiencing issues with the archiving of the “yearly” period for our two largest sites.

PHP has been configured to not apply memory limits. After processing daily, weekly and monthly periods, the archiving process starts with the yearly period and quickly - in a matter of seconds - eats up all the RAM of the processing host. In our case that is currently 15GB. Before we increase the RAM of the host, we’d like to understand the root cause (and if e.g. doubling it would actually help with the problem).

The issue has been discussed already via https://forum.matomo.org/t/memory-usage-issues-with-archiving-high-traffic-ecommerce-site/42334/13 but it looks like this is a dead end for us for now. Our archiving process is still regularly failing unfortunately.

It looks like the tracking of things like SKU data (and product name) per product page impression (see arrow towards the hover popup) seems to be a size issue as we have hundreds of thousands of those in a comparatively short amount of time of those.

https://forum.matomo.org/uploads/db1181/original/2X/5/5cd51893f2e864279473210c5c03ff6cfe2d8a50.jpeg
@pepl pepl added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label Jul 26, 2021
@diosmosis
Copy link
Member

Hi @pepl, the memory use comes from trying to load reports before aggregating them. Most reports have a limit to the number of rows but the product reports don't seem to have any (so your hunch is probably correct). Would you be able to check the individual month reports for the product reports (for example "Product SKU", "Product Name") and tell me how many total rows they have?

@pepl
Copy link
Author

pepl commented Jul 27, 2021

Hi @diosmosis, For the largest site and June as an example month it is 541,645 "Product SKU" rows. The "Product Name" aggregation for June is empty but in the overview I see "1,665,030 Purchased Products".

For May (about a week of data) it is 162,901 "Product Name" rows and 162,484 "Product SKU" rows.

@diosmosis diosmosis added Bug For errors / faults / flaws / inconsistencies etc. and removed Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. labels Jul 27, 2021
@diosmosis
Copy link
Member

diosmosis commented Jul 27, 2021 •
edited

Hi @pepl, this is definitely due to a bug. I've created a preliminary fix here: #17823 . Once it's reviewed, you could apply it then invalidate the month archives and run core:archive. They will be recomputed with the row limits so php won't run out of memory.

You may also want to set custom values that make sense for you in the new INI config settings:

  • [General] datatable_archiving_maximum_rows_products
  • [General] datatable_archiving_maximum_rows_subtable_products

@pepl
Copy link
Author

pepl commented Jul 28, 2021 •
edited

I've tried the patch and limited the new config settings values down to 10. However, it looks like the memory exhaustion occurs even before.

The following stack trace looks like this, including some debug output I've added along this patch:

# diff -ub core/DataAccess/ArchiveSelector.php.orig core/DataAccess/ArchiveSelector.php
--- core/DataAccess/ArchiveSelector.php.orig	2021-07-05 15:33:03.051164947 +0200
+++ core/DataAccess/ArchiveSelector.php	2021-07-05 16:41:14.650559122 +0200
@@ -23,6 +23,8 @@
 use Piwik\SettingsServer;
 use Psr\Log\LoggerInterface;
 
+use Piwik\Metrics\Formatter;
+
 /**
  * Data Access object used to query archives
  *
@@ -325,7 +327,9 @@
                 $table = ArchiveTableCreator::getBlobTable($date);
             }
 # mk	 
-$logger->warning('02 Processing period ' . $period . ', table ' . $table . 'ids ' . implode(',', $ids));
+$formatter = new Formatter();
+echo "Memory usage: " . $formatter->getPrettySizeFromBytes(memory_get_usage()) . "|Processing period " . $period . ', table ' . $table . ' ids ' . implode(',', $ids) . "\n";
 
             $sql      = sprintf($getValuesSql, $table, implode(',', $ids));
             $dataRows = $db->fetchAll($sql, $bind);
@@ -343,6 +347,9 @@
                     }
                 }
             }
+
+	    # mk
+	    $dataRows = null;
         }
 
         return $rows;
# diff -ub core/ArchiveProcessor.php.orig core/ArchiveProcessor.php
--- core/ArchiveProcessor.php.orig	2021-07-05 15:33:20.483008852 +0200
+++ core/ArchiveProcessor.php	2021-07-05 16:26:07.510675539 +0200
@@ -18,6 +18,7 @@
 use Piwik\DataTable\Map;
 use Piwik\DataTable\Row;
 use Piwik\Segment\SegmentExpression;
+use Piwik\Container\StaticContainer;
 use Psr\Log\LoggerInterface;
 
 /**
@@ -208,9 +209,14 @@
         }
 
         $nameToCount = array();
+# mk
+$logger = StaticContainer::get(LoggerInterface::class);
         foreach ($recordNames as $recordName) {
             $latestUsedTableId = Manager::getInstance()->getMostRecentTableId();
 
+# mk
+$logger->warning("recordname: " . $recordName . ", colaggop: " . $columnsAggregationOperation);	  
             $table = $this->aggregateDataTableRecord($recordName, $columnsAggregationOperation, $columnsToRenameAfterAggregation);
 
             $nameToCount[$recordName]['level0'] = $table->getRowsCount();

# diff -ub plugins/Goals/Archiver.php-orig plugins/Goals/Archiver.php
--- plugins/Goals/Archiver.php-orig	2021-07-28 09:30:20.619550424 +0200
+++ plugins/Goals/Archiver.php	2021-07-28 11:04:46.441500189 +0200
@@ -9,6 +9,9 @@
 
 namespace Piwik\Plugins\Goals;
 
+use Piwik\ArchiveProcessor;
+use Piwik\Common;
+use Piwik\Config;
 use Piwik\DataAccess\LogAggregator;
 use Piwik\DataArray;
 use Piwik\DataTable;
@@ -16,6 +19,7 @@
 use Piwik\Plugin\Manager;
 use Piwik\Tracker\GoalManager;
 use Piwik\Plugins\VisitFrequency\API as VisitFrequencyAPI;
+use Psr\Log\LoggerInterface;
 
 class Archiver extends \Piwik\Plugin\Archiver
 {
@@ -94,6 +98,25 @@
      */
     protected $itemReports = [];
 
+    /**
+     * @var int
+     */
+    private $productReportsMaximumRows;
+
+    /**
+     * @var int
+     */
+    private $productReportsMaximumRowsForSubtables;
+
+    public function __construct(ArchiveProcessor $processor)
+    {
+        parent::__construct($processor);
+
+        $general = Config::getInstance()->General;
+        $this->productReportsMaximumRows = $general['datatable_archiving_maximum_rows_products'];
+        $this->productReportsMaximumRowsForSubtables = $general['datatable_archiving_maximum_rows_subtable_products'];
+    }
+
     public function aggregateDayReport()
     {
         $this->aggregateGeneralGoalMetrics();
@@ -277,14 +300,26 @@
 
     protected function insertItemReports()
     {
+
+# mk
+$logger = StaticContainer::get(LoggerInterface::class);
+
         foreach ($this->itemReports as $dimension => $itemAggregatesByType) {
+$logger->warning("dimension: " . $dimension);
             foreach ($itemAggregatesByType as $ecommerceType => $itemAggregate) {
                 $recordName = $this->dimensionRecord[$dimension];
+$logger->warning("recordName: " . $recordName);
                 if ($ecommerceType == GoalManager::IDGOAL_CART) {
                     $recordName = self::getItemRecordNameAbandonedCart($recordName);
                 }
                 $table = $itemAggregate->asDataTable();
-                $this->getProcessor()->insertBlobRecord($recordName, $table->getSerialized());
+                $blobData = $table->getSerialized($this->productReportsMaximumRows, $this->productReportsMaximumRowsForSubtables,
+                    Metrics::INDEX_ECOMMERCE_ITEM_REVENUE);
+                $this->getProcessor()->insertBlobRecord($recordName, $blobData);
+
+                Common::destroy($table);
             }
         }
     }

And here the output including stracktrace:

# /usr/bin/php7.3 -q  /var/www/matomo/console climulti:request -q --matomo-domain='localhost' --superuser 'module=API&method=CoreAdminHome.archiveReports&idSite=3
&period=year&date=2021-01-01&format=json&trigger=archivephp&pid=somepid&runid=22788'
Memory usage: 23.6 M|Processing period 2021-05-24,2021-05-24, table archive_numeric_2021_05 ids 30012
Memory usage: 23.6 M|Processing period 2021-05-25,2021-05-25, table archive_numeric_2021_05 ids 150142
…
lines truncated
…
recordname: Goals_ItemsSku, colaggop: 
Memory usage: 140.3 M|Processing period 2021-05-24,2021-05-24, table archive_blob_2021_05 ids 30017
Memory usage: 140.5 M|Processing period 2021-05-25,2021-05-25, table archive_blob_2021_05 ids 150483,150145,150143,150114,150112,150073,150071,40054,40055,40030,40031,40006,40007,39982,39983,39957,39958,39933,39934,39909,39910,39885,39886,39861,39862,39837,39838,39813,39814,39789,39790,39765,39766,39741,39742,39717,39718,39693,39694,39669,39670,39645,39646,39621,39622,39597,39598,39573,39574,39533,39534,39509,39510,39485,39486,39461,39462,39437,39438,39413,39414,39389,39390,39365,39366,39341,39342,39317,39318,39293,39294,39269,39270,39245,39246,39221,39222,39197,39198,39173,39174,39149,39150,39124,39125,39100,39101,39076,39077,39052,39053,39028,39029,39004,39005,38980,38981,38956,38957,38932,38933,38908,38909,38884,38885,38860,38861,38836,38837,38812,38813,38788,38789,38764,38765,38740,38741,38700,38701,38676,38677,38652,38653,38628,38629,38604,38605,38580,38581,38556,38557,38532,38533,38508,38509,38484,38485,38460,38461,38436,38437,38412,38413,38388,38389,38364,38365,38340,38341,38316,38317,38291,38292,38267,38268,38243,38244,38219,38220,38195,38196,38171,38172,38147,38148,38123,38124,38099,38100,38075,38076,38051,38052,38027,38028,38002,38003,37982,37983,37962,37963,37942,37943,37922,37923,37887,37888,37867,37868,37847,37848,37827,37828,37807,37808,37787,37788,37767,37768,37747,37748,37727,37728,37707,37708,37687,37688,37667,37668,37647,37648,37627,37628,37607,37608,37587,37588,37567,37568,37546,37547,37526,37527,37506,37507,37486,37487,37466,37467,37446,37447,37426,37427,37406,37407,37386,37387,37366,37367,37346,37347,37326,37327,37306,37307,37286,37287,37266,37267,37246,37247,37226,37227,37191,37192,37171,37172,37151,37152,37131,37132,37111,37112,37091,37092,37071,37072,37051,37052,37031,37032,37011,37012,36991,36992,36971,36972,36951,36952,36931,36932,36911,36912,36891,36892,36871,36872,36850,36851,36830,36831,36810,36811,36790,36791,36770,36771,36750,36751,36730,36731,36710,36711,36690,36691,36670,36671,36650,36651,36630,36631,36610,36611,36590,36591,36570,36571,36550,36551,36530,36531,36495,36496,36475,36476,36455,36456,36435,36436,36415,36416,36395,36396,36375,36376,36355,36356,36335,36336,36315,36316,36295,36296,36275,36276,36255,36256,36235,36236,36215,36216,36195,36196,36175,36176,36154,36155,36134,36135,36114,36115,36094,36095,36074,36075,36054,36055,36033,36034,36017,36018,36001,36002,35985,35986,35969,35970,35953,35954,35937,35938,35921,35922,35905,35906,35889,35890,35873,35874,35843,35844,35827,35828,35811,35812,35795,35796,35779,35780,35763,35764,35747,35748,35731,35732,35715,35716,35699,35700,35683,35684,35667,35668,35651,35652,35635,35636,35619,35620,35603,35604,35587,35588,35570,35571,35554,35555,35538,35539,35522,35523,35506,35507,35490,35491,35474,35475,35458,35459,35442,35443,35426,35427,35400,35411,35384,35385,35368,35369,35352,35353,35336,35337,35320,35321,35304,35305,35279,35280,35263,35264,35247,35248,35231,35232,35215,35216,35199,35200,35107,35108,35091,35092,35075,35076,35059,35060,35043,35044,35027,35028,35011,35012,34995,34996,34971,34972,34933,34934,34917,34918,34900,34901,34884,34885,34868,34869,34852,34853,34836,34837,34808,34821,34760,34761,34744,34745,34728,34729,34712,34713,34696,34697,34680,34681,34664,34665,34648,34649,34632,34633,34616,34617,34600,34601,34303,34304,34287,34288,34271,34272,34255,34256,34239,34240,34223,34224,34207,34208,34191,34192,34175,34176,34159,34160,34143,34144,34127,34128,34111,34112,34095,34096,34079,34080,34063,34064,34048,34049,34036,33689,33690,33677,33678,33665,33666,33653,33654,33641,33642,33629,33630,33617,33618,33605,33606,33593,33594,33581,33582,33569,33570,33557,33558,33545,33546,33533,33534,33308,33309,33296,33297,33284,33285,33272,33273,33260,33261,33248,33249,33236,33237,33224,33225,33212,33213,33200,33201,33188,33189,33176,33177,33146,33147,33134,33135,33122,33123,33106,33111,33061,33066,32984,32751,32752,32739,32740,32727,32728,32715,32716,32703,32704,32685,32686,32676,32437,32080,32081,32068,32069,32048,32049,31971,31974,31959,31960,31947,31948,31935,31936,31923,31924,31898,31899,31886,31887,31874,31875,31862,31863,31850,31851,31838,31839,31826,31827,31814,31815,31802,31803,31790,31791,31778,31779,31766,31767,31754,31755,31742,31743,31730,31731,31718,31719,31706,31707,31693,31694,31681,31682,31669,31670,31657,31658,31645,31646,31633,31634,31621,31622,31609,31610,31597,31598,31585,31586,31573,31574,31561,31562,31549,31550,31537,31538,31525,31526,31513,31514,31501,31502,31476,31477,31464,31465,31452,31453,31440,31441,31428,31429,31416,31417,31404,31405,31392,31393,31380,31381,31368,31369,31356,31357,31344,31345,31332,31333,31320,31321,31308,31309,31296,31297,31284,31285,31271,31272,31259,31260,31247,31248,31235,31236,31223,31224,31211,31212,31199,31200,31187,31188,31175,31176,31163,31164,31151,31152,31139,31140,31127,31128,31114,31115,31106,31107,31098,31099,31090,31091,31070,31071,31062,31063,31054,31055,31046,31047,31038,31039,31030,31031,31022,31023,31014,31015,31006,31007,30998,30999,30990,30991,30982,30983,30974,30975,30966,30967,30958,30959,30950,30951,30942,30943,30933,30934,30925,30926,30917,30918,30909,30910,30901,30902,30893,30894,30885,30886,30877,30878,30869,30870,30861,30862,30853,30854,30845,30846,30837,30838,30829,30830,30821,30822,30813,30814,30805,30806,30785,30786,30777,30778,30769,30770,30761,30762,30753,30754,30745,30746,30737,30738,30729,30730,30721,30722,30713,30714,30705,30706,30697,30698,30689,30690,30681,30682,30673,30674,30665,30666,30657,30658,30648,30649,30640,30641,30632,30633,30611,30612,30603,30604,30595,30596,30587,30588,30579,30580,30571,30572,30563,30564,30555,30556,30547,30548,30539,30540,30531,30532,30523,30524,30515,30516,30507,30508,30492,30493,30484,30485,30476,30477,30468,30469,30455,30456,30447,30448,30394,30436,30386,30387,30306,30342,30201,30212,30193,30194,30185,30186,30177,30178,30169,30170,30161,30162,30153,30154,30145,30146,30118,30129,30102,30106,30094,30095,30086,30087,30078,30079,30070,30071,30062,30063,30022,30047
Memory usage: 9.2 G|Processing period 2021-05-26,2021-05-26, table archive_blob_2021_05 ids 150150,150148,150119,150117,150078,150076,40058,40059,40034,40035,40010,40011,39986,39987,39961,39962,39937,39938,39913,39914,39889,39890,39865,39866,39841,39842,39817,39818,39793,39794,39769,39770,39745,39746,39721,39722,39697,39698,39673,39674,39649,39650,39625,39626,39601,39602,39577,39578,39537,39538,39513,39514,39489,39490,39465,39466,39441,39442,39417,39418,39393,39394,39369,39370,39345,39346,39321,39322,39297,39298,39273,39274,39249,39250,39225,39226,39201,39202,39177,39178,39153,39154,39128,39129,39104,39105,39080,39081,39056,39057,39032,39033,39008,39009,38984,38985,38960,38961,38936,38937,38912,38913,38888,38889,38864,38865,38840,38841,38816,38817,38792,38793,38768,38769,38744,38745,38704,38705,38680,38681,38656,38657,38632,38633,38608,38609,38584,38585,38560,38561,38536,38537,38512,38513,38488,38489,38464,38465,38440,38441,38416,38417,38392,38393,38368,38369,38344,38345,38320,38321,38295,38296,38271,38272,38247,38248,38223,38224,38199,38200,38175,38176,38151,38152,38127,38128,38103,38104,38079,38080,38055,38056,38031,38032,38006,38007,37986,37987,37966,37967,37946,37947,37926,37927,37891,37892,37871,37872,37851,37852,37831,37832,37811,37812,37791,37792,37771,37772,37751,37752,37731,37732,37711,37712,37691,37692,37671,37672,37651,37652,37631,37632,37611,37612,37591,37592,37571,37572,37550,37551,37530,37531,37510,37511,37490,37491,37470,37471,37450,37451,37430,37431,37410,37411,37390,37391,37370,37371,37350,37351,37330,37331,37310,37311,37290,37291,37270,37271,37250,37251,37230,37231,37195,37196,37175,37176,37155,37156,37135,37136,37115,37116,37095,37096,37075,37076,37055,37056,37035,37036,37015,37016,36995,36996,36975,36976,36955,36956,36935,36936,36915,36916,36895,36896,36875,36876,36854,36855,36834,36835,36814,36815,36794,36795,36774,36775,36754,36755,36734,36735,36714,36715,36694,36695,36674,36675,36654,36655,36634,36635,36614,36615,36594,36595,36574,36575,36554,36555,36534,36535,36499,36500,36479,36480,36459,36460,36439,36440,36419,36420,36399,36400,36379,36380,36359,36360,36339,36340,36319,36320,36299,36300,36279,36280,36259,36260,36239,36240,36219,36220,36199,36200,36179,36180,36158,36159,36138,36139,36118,36119,36098,36099,36078,36079,36058,36059,36037,36038,36021,36022,36005,36006,35989,35990,35973,35974,35957,35958,35941,35942,35925,35926,35909,35910,35893,35894,35877,35878,35847,35848,35831,35832,35815,35816,35799,35800,35783,35784,35767,35768,35751,35752,35735,35736,35719,35720,35703,35704,35687,35688,35671,35672,35655,35656,35639,35640,35623,35624,35607,35608,35591,35592,35574,35575,35558,35559,35542,35543,35526,35527,35510,35511,35494,35495,35478,35479,35462,35463,35446,35447,35430,35431,35414,35415,35388,35389,35372,35373,35356,35357,35340,35341,35324,35325,35308,35309,35283,35284,35267,35268,35251,35252,35235,35236,35219,35220,35203,35204,35111,35185,35095,35096,35079,35080,35063,35064,35047,35048,35031,35032,35015,35016,34999,35000,34975,34976,34937,34938,34921,34922,34904,34905,34888,34889,34872,34873,34856,34857,34840,34841,34824,34825,34764,34765,34748,34749,34732,34733,34716,34717,34700,34701,34684,34685,34668,34669,34652,34653,34636,34637,34620,34621,34604,34605,34307,34308,34291,34292,34275,34276,34259,34260,34243,34244,34227,34228,34211,34212,34195,34196,34179,34180,34163,34164,34147,34148,34131,34132,34115,34116,34099,34100,34083,34084,34067,34068,34052,34053,34039,33693,33694,33681,33682,33669,33670,33657,33658,33645,33646,33633,33634,33621,33622,33609,33610,33597,33598,33585,33586,33573,33574,33561,33562,33549,33550,33537,33538,33312,33313,33300,33301,33288,33289,33276,33277,33264,33265,33252,33253,33240,33241,33228,33229,33216,33217,33204,33205,33192,33193,33180,33181,33150,33162,33138,33139,33126,33127,33114,33115,33070,33074,33027
…
lines truncated
…
Memory usage: 26.3 G|Processing period 2021-05-30,2021-05-30, table archive_blob_2021_05 ids 151082
recordname: Goals_ItemsName, colaggop: 
Memory usage: 4.9 G|Processing period 2021-05-24,2021-05-24, table archive_blob_2021_05 ids 30017,30017
Memory usage: 4.9 G|Processing period 2021-05-25,2021-05-25, table archive_blob_2021_05 ids 150483,150145,150143,150114,150112,150073,150071,40054,40055,40030,40031,40006,40007,39982,39983,39957,39958,39933,39934,39909,39910,39885,39886,39861,39862,39837,39838,39813,39814,39789,39790,39765,39766,39741,39742,39717,39718,39693,39694,39669,39670,39645,39646,39621,39622,39597,39598,39573,39574,39533,39534,39509,39510,39485,39486,39461,39462,39437,39438,39413,39414,39389,39390,39365,39366,39341,39342,39317,39318,39293,39294,39269,39270,39245,39246,39221,39222,39197,39198,39173,39174,39149,39150,39124,39125,39100,39101,39076,39077,39052,39053,39028,39029,39004,39005,38980,38981,38956,38957,38932,38933,38908,38909,38884,38885,38860,38861,38836,38837,38812,38813,38788,38789,38764,38765,38740,38741,38700,38701,38676,38677,38652,38653,38628,38629,38604,38605,38580,38581,38556,38557,38532,38533,38508,38509,38484,38485,38460,38461,38436,38437,38412,38413,38388,38389,38364,38365,38340,38341,38316,38317,38291,38292,38267,38268,38243,38244,38219,38220,38195,38196,38171,38172,38147,38148,38123,38124,38099,38100,38075,38076,38051,38052,38027,38028,38002,38003,37982,37983,37962,37963,37942,37943,37922,37923,37887,37888,37867,37868,37847,37848,37827,37828,37807,37808,37787,37788,37767,37768,37747,37748,37727,37728,37707,37708,37687,37688,37667,37668,37647,37648,37627,37628,37607,37608,37587,37588,37567,37568,37546,37547,37526,37527,37506,37507,37486,37487,37466,37467,37446,37447,37426,37427,37406,37407,37386,37387,37366,37367,37346,37347,37326,37327,37306,37307,37286,37287,37266,37267,37246,37247,37226,37227,37191,37192,37171,37172,37151,37152,37131,37132,37111,37112,37091,37092,37071,37072,37051,37052,37031,37032,37011,37012,36991,36992,36971,36972,36951,36952,36931,36932,36911,36912,36891,36892,36871,36872,36850,36851,36830,36831,36810,36811,36790,36791,36770,36771,36750,36751,36730,36731,36710,36711,36690,36691,36670,36671,36650,36651,36630,36631,36610,36611,36590,36591,36570,36571,36550,36551,36530,36531,36495,36496,36475,36476,36455,36456,36435,36436,36415,36416,36395,36396,36375,36376,36355,36356,36335,36336,36315,36316,36295,36296,36275,36276,36255,36256,36235,36236,36215,36216,36195,36196,36175,36176,36154,36155,36134,36135,36114,36115,36094,36095,36074,36075,36054,36055,36033,36034,36017,36018,36001,36002,35985,35986,35969,35970,35953,35954,35937,35938,35921,35922,35905,35906,35889,35890,35873,35874,35843,35844,35827,35828,35811,35812,35795,35796,35779,35780,35763,35764,35747,35748,35731,35732,35715,35716,35699,35700,35683,35684,35667,35668,35651,35652,35635,35636,35619,35620,35603,35604,35587,35588,35570,35571,35554,35555,35538,35539,35522,35523,35506,35507,35490,35491,35474,35475,35458,35459,35442,35443,35426,35427,35400,35411,35384,35385,35368,35369,35352,35353,35336,35337,35320,35321,35304,35305,35279,35280,35263,35264,35247,35248,35231,35232,35215,35216,35199,35200,35107,35108,35091,35092,35075,35076,35059,35060,35043,35044,35027,35028,35011,35012,34995,34996,34971,34972,34933,34934,34917,34918,34900,34901,34884,34885,34868,34869,34852,34853,34836,34837,34808,34821,34760,34761,34744,34745,34728,34729,34712,34713,34696,34697,34680,34681,34664,34665,34648,34649,34632,34633,34616,34617,34600,34601,34303,34304,34287,34288,34271,34272,34255,34256,34239,34240,34223,34224,34207,34208,34191,34192,34175,34176,34159,34160,34143,34144,34127,34128,34111,34112,34095,34096,34079,34080,34063,34064,34048,34049,34036,33689,33690,33677,33678,33665,33666,33653,33654,33641,33642,33629,33630,33617,33618,33605,33606,33593,33594,33581,33582,33569,33570,33557,33558,33545,33546,33533,33534,33308,33309,33296,33297,33284,33285,33272,33273,33260,33261,33248,33249,33236,33237,33224,33225,33212,33213,33200,33201,33188,33189,33176,33177,33146,33147,33134,33135,33122,33123,33106,33111,33061,33066,32984,32751,32752,32739,32740,32727,32728,32715,32716,32703,32704,32685,32686,32676,32437,32080,32081,32068,32069,32048,32049,31971,31974,31959,31960,31947,31948,31935,31936,31923,31924,31898,31899,31886,31887,31874,31875,31862,31863,31850,31851,31838,31839,31826,31827,31814,31815,31802,31803,31790,31791,31778,31779,31766,31767,31754,31755,31742,31743,31730,31731,31718,31719,31706,31707,31693,31694,31681,31682,31669,31670,31657,31658,31645,31646,31633,31634,31621,31622,31609,31610,31597,31598,31585,31586,31573,31574,31561,31562,31549,31550,31537,31538,31525,31526,31513,31514,31501,31502,31476,31477,31464,31465,31452,31453,31440,31441,31428,31429,31416,31417,31404,31405,31392,31393,31380,31381,31368,31369,31356,31357,31344,31345,31332,31333,31320,31321,31308,31309,31296,31297,31284,31285,31271,31272,31259,31260,31247,31248,31235,31236,31223,31224,31211,31212,31199,31200,31187,31188,31175,31176,31163,31164,31151,31152,31139,31140,31127,31128,31114,31115,31106,31107,31098,31099,31090,31091,31070,31071,31062,31063,31054,31055,31046,31047,31038,31039,31030,31031,31022,31023,31014,31015,31006,31007,30998,30999,30990,30991,30982,30983,30974,30975,30966,30967,30958,30959,30950,30951,30942,30943,30933,30934,30925,30926,30917,30918,30909,30910,30901,30902,30893,30894,30885,30886,30877,30878,30869,30870,30861,30862,30853,30854,30845,30846,30837,30838,30829,30830,30821,30822,30813,30814,30805,30806,30785,30786,30777,30778,30769,30770,30761,30762,30753,30754,30745,30746,30737,30738,30729,30730,30721,30722,30713,30714,30705,30706,30697,30698,30689,30690,30681,30682,30673,30674,30665,30666,30657,30658,30648,30649,30640,30641,30632,30633,30611,30612,30603,30604,30595,30596,30587,30588,30579,30580,30571,30572,30563,30564,30555,30556,30547,30548,30539,30540,30531,30532,30523,30524,30515,30516,30507,30508,30492,30493,30484,30485,30476,30477,30468,30469,30455,30456,30447,30448,30394,30436,30386,30387,30306,30342,30201,30212,30193,30194,30185,30186,30177,30178,30169,30170,30161,30162,30153,30154,30145,30146,30118,30129,30102,30106,30094,30095,30086,30087,30078,30079,30070,30071,30062,30063,30022,30047,150483,150145,150143,150114,150112,150073,150071,40054,40055,40030,40031,40006,40007,39982,39983,39957,39958,39933,39934,39909,39910,39885,39886,39861,39862,39837,39838,39813,39814,39789,39790,39765,39766,39741,39742,39717,39718,39693,39694,39669,39670,39645,39646,39621,39622,39597,39598,39573,39574,39533,39534,39509,39510,39485,39486,39461,39462,39437,39438,39413,39414,39389,39390,39365,39366,39341,39342,39317,39318,39293,39294,39269,39270,39245,39246,39221,39222,39197,39198,39173,39174,39149,39150,39124,39125,39100,39101,39076,39077,39052,39053,39028,39029,39004,39005,38980,38981,38956,38957,38932,38933,38908,38909,38884,38885,38860,38861,38836,38837,38812,38813,38788,38789,38764,38765,38740,38741,38700,38701,38676,38677,38652,38653,38628,38629,38604,38605,38580,38581,38556,38557,38532,38533,38508,38509,38484,38485,38460,38461,38436,38437,38412,38413,38388,38389,38364,38365,38340,38341,38316,38317,38291,38292,38267,38268,38243,38244,38219,38220,38195,38196,38171,38172,38147,38148,38123,38124,38099,38100,38075,38076,38051,38052,38027,38028,38002,38003,37982,37983,37962,37963,37942,37943,37922,37923,37887,37888,37867,37868,37847,37848,37827,37828,37807,37808,37787,37788,37767,37768,37747,37748,37727,37728,37707,37708,37687,37688,37667,37668,37647,37648,37627,37628,37607,37608,37587,37588,37567,37568,37546,37547,37526,37527,37506,37507,37486,37487,37466,37467,37446,37447,37426,37427,37406,37407,37386,37387,37366,37367,37346,37347,37326,37327,37306,37307,37286,37287,37266,37267,37246,37247,37226,37227,37191,37192,37171,37172,37151,37152,37131,37132,37111,37112,37091,37092,37071,37072,37051,37052,37031,37032,37011,37012,36991,36992,36971,36972,36951,36952,36931,36932,36911,36912,36891,36892,36871,36872,36850,36851,36830,36831,36810,36811,36790,36791,36770,36771,36750,36751,36730,36731,36710,36711,36690,36691,36670,36671,36650,36651,36630,36631,36610,36611,36590,36591,36570,36571,36550,36551,36530,36531,36495,36496,36475,36476,36455,36456,36435,36436,36415,36416,36395,36396,36375,36376,36355,36356,36335,36336,36315,36316,36295,36296,36275,36276,36255,36256,36235,36236,36215,36216,36195,36196,36175,36176,36154,36155,36134,36135,36114,36115,36094,36095,36074,36075,36054,36055,36033,36034,36017,36018,36001,36002,35985,35986,35969,35970,35953,35954,35937,35938,35921,35922,35905,35906,35889,35890,35873,35874,35843,35844,35827,35828,35811,35812,35795,35796,35779,35780,35763,35764,35747,35748,35731,35732,35715,35716,35699,35700,35683,35684,35667,35668,35651,35652,35635,35636,35619,35620,35603,35604,35587,35588,35570,35571,35554,35555,35538,35539,35522,35523,35506,35507,35490,35491,35474,35475,35458,35459,35442,35443,35426,35427,35400,35411,35384,35385,35368,35369,35352,35353,35336,35337,35320,35321,35304,35305,35279,35280,35263,35264,35247,35248,35231,35232,35215,35216,35199,35200,35107,35108,35091,35092,35075,35076,35059,35060,35043,35044,35027,35028,35011,35012,34995,34996,34971,34972,34933,34934,34917,34918,34900,34901,34884,34885,34868,34869,34852,34853,34836,34837,34808,34821,34760,34761,34744,34745,34728,34729,34712,34713,34696,34697,34680,34681,34664,34665,34648,34649,34632,34633,34616,34617,34600,34601,34303,34304,34287,34288,34271,34272,34255,34256,34239,34240,34223,34224,34207,34208,34191,34192,34175,34176,34159,34160,34143,34144,34127,34128,34111,34112,34095,34096,34079,34080,34063,34064,34048,34049,34036,33689,33690,33677,33678,33665,33666,33653,33654,33641,33642,33629,33630,33617,33618,33605,33606,33593,33594,33581,33582,33569,33570,33557,33558,33545,33546,33533,33534,33308,33309,33296,33297,33284,33285,33272,33273,33260,33261,33248,33249,33236,33237,33224,33225,33212,33213,33200,33201,33188,33189,33176,33177,33146,33147,33134,33135,33122,33123,33106,33111,33061,33066,32984,32751,32752,32739,32740,32727,32728,32715,32716,32703,32704,32685,32686,32676,32437,32080,32081,32068,32069,32048,32049,31971,31974,31959,31960,31947,31948,31935,31936,31923,31924,31898,31899,31886,31887,31874,31875,31862,31863,31850,31851,31838,31839,31826,31827,31814,31815,31802,31803,31790,31791,31778,31779,31766,31767,31754,31755,31742,31743,31730,31731,31718,31719,31706,31707,31693,31694,31681,31682,31669,31670,31657,31658,31645,31646,31633,31634,31621,31622,31609,31610,31597,31598,31585,31586,31573,31574,31561,31562,31549,31550,31537,31538,31525,31526,31513,31514,31501,31502,31476,31477,31464,31465,31452,31453,31440,31441,31428,31429,31416,31417,31404,31405,31392,31393,31380,31381,31368,31369,31356,31357,31344,31345,31332,31333,31320,31321,31308,31309,31296,31297,31284,31285,31271,31272,31259,31260,31247,31248,31235,31236,31223,31224,31211,31212,31199,31200,31187,31188,31175,31176,31163,31164,31151,31152,31139,31140,31127,31128,31114,31115,31106,31107,31098,31099,31090,31091,31070,31071,31062,31063,31054,31055,31046,31047,31038,31039,31030,31031,31022,31023,31014,31015,31006,31007,30998,30999,30990,30991,30982,30983,30974,30975,30966,30967,30958,30959,30950,30951,30942,30943,30933,30934,30925,30926,30917,30918,30909,30910,30901,30902,30893,30894,30885,30886,30877,30878,30869,30870,30861,30862,30853,30854,30845,30846,30837,30838,30829,30830,30821,30822,30813,30814,30805,30806,30785,30786,30777,30778,30769,30770,30761,30762,30753,30754,30745,30746,30737,30738,30729,30730,30721,30722,30713,30714,30705,30706,30697,30698,30689,30690,30681,30682,30673,30674,30665,30666,30657,30658,30648,30649,30640,30641,30632,30633,30611,30612,30603,30604,30595,30596,30587,30588,30579,30580,30571,30572,30563,30564,30555,30556,30547,30548,30539,30540,30531,30532,30523,30524,30515,30516,30507,30508,30492,30493,30484,30485,30476,30477,30468,30469,30455,30456,30447,30448,30394,30436,30386,30387,30306,30342,30201,30212,30193,30194,30185,30186,30177,30178,30169,30170,30161,30162,30153,30154,30145,30146,30118,30129,30102,30106,30094,30095,30086,30087,30078,30079,30070,30071,30062,30063,30022,30047
Memory usage: 19.1 G|Processing period 2021-05-26,2021-05-26, table archive_blob_2021_05 ids 150150,150148,150119,150117,150078,150076,40058,40059,40034,40035,40010,40011,39986,39987,39961,39962,39937,39938,39913,39914,39889,39890,39865,39866,39841,39842,39817,39818,39793,39794,39769,39770,39745,39746,39721,39722,39697,39698,39673,39674,39649,39650,39625,39626,39601,39602,39577,39578,39537,39538,39513,39514,39489,39490,39465,39466,39441,39442,39417,39418,39393,39394,39369,39370,39345,39346,39321,39322,39297,39298,39273,39274,39249,39250,39225,39226,39201,39202,39177,39178,39153,39154,39128,39129,39104,39105,39080,39081,39056,39057,39032,39033,39008,39009,38984,38985,38960,38961,38936,38937,38912,38913,38888,38889,38864,38865,38840,38841,38816,38817,38792,38793,38768,38769,38744,38745,38704,38705,38680,38681,38656,38657,38632,38633,38608,38609,38584,38585,38560,38561,38536,38537,38512,38513,38488,38489,38464,38465,38440,38441,38416,38417,38392,38393,38368,38369,38344,38345,38320,38321,38295,38296,38271,38272,38247,38248,38223,38224,38199,38200,38175,38176,38151,38152,38127,38128,38103,38104,38079,38080,38055,38056,38031,38032,38006,38007,37986,37987,37966,37967,37946,37947,37926,37927,37891,37892,37871,37872,37851,37852,37831,37832,37811,37812,37791,37792,37771,37772,37751,37752,37731,37732,37711,37712,37691,37692,37671,37672,37651,37652,37631,37632,37611,37612,37591,37592,37571,37572,37550,37551,37530,37531,37510,37511,37490,37491,37470,37471,37450,37451,37430,37431,37410,37411,37390,37391,37370,37371,37350,37351,37330,37331,37310,37311,37290,37291,37270,37271,37250,37251,37230,37231,37195,37196,37175,37176,37155,37156,37135,37136,37115,37116,37095,37096,37075,37076,37055,37056,37035,37036,37015,37016,36995,36996,36975,36976,36955,36956,36935,36936,36915,36916,36895,36896,36875,36876,36854,36855,36834,36835,36814,36815,36794,36795,36774,36775,36754,36755,36734,36735,36714,36715,36694,36695,36674,36675,36654,36655,36634,36635,36614,36615,36594,36595,36574,36575,36554,36555,36534,36535,36499,36500,36479,36480,36459,36460,36439,36440,36419,36420,36399,36400,36379,36380,36359,36360,36339,36340,36319,36320,36299,36300,36279,36280,36259,36260,36239,36240,36219,36220,36199,36200,36179,36180,36158,36159,36138,36139,36118,36119,36098,36099,36078,36079,36058,36059,36037,36038,36021,36022,36005,36006,35989,35990,35973,35974,35957,35958,35941,35942,35925,35926,35909,35910,35893,35894,35877,35878,35847,35848,35831,35832,35815,35816,35799,35800,35783,35784,35767,35768,35751,35752,35735,35736,35719,35720,35703,35704,35687,35688,35671,35672,35655,35656,35639,35640,35623,35624,35607,35608,35591,35592,35574,35575,35558,35559,35542,35543,35526,35527,35510,35511,35494,35495,35478,35479,35462,35463,35446,35447,35430,35431,35414,35415,35388,35389,35372,35373,35356,35357,35340,35341,35324,35325,35308,35309,35283,35284,35267,35268,35251,35252,35235,35236,35219,35220,35203,35204,35111,35185,35095,35096,35079,35080,35063,35064,35047,35048,35031,35032,35015,35016,34999,35000,34975,34976,34937,34938,34921,34922,34904,34905,34888,34889,34872,34873,34856,34857,34840,34841,34824,34825,34764,34765,34748,34749,34732,34733,34716,34717,34700,34701,34684,34685,34668,34669,34652,34653,34636,34637,34620,34621,34604,34605,34307,34308,34291,34292,34275,34276,34259,34260,34243,34244,34227,34228,34211,34212,34195,34196,34179,34180,34163,34164,34147,34148,34131,34132,34115,34116,34099,34100,34083,34084,34067,34068,34052,34053,34039,33693,33694,33681,33682,33669,33670,33657,33658,33645,33646,33633,33634,33621,33622,33609,33610,33597,33598,33585,33586,33573,33574,33561,33562,33549,33550,33537,33538,33312,33313,33300,33301,33288,33289,33276,33277,33264,33265,33252,33253,33240,33241,33228,33229,33216,33217,33204,33205,33192,33193,33180,33181,33150,33162,33138,33139,33126,33127,33114,33115,33070,33074,33027,150150,150148,150119,150117,150078,150076,40058,40059,40034,40035,40010,40011,39986,39987,39961,39962,39937,39938,39913,39914,39889,39890,39865,39866,39841,39842,39817,39818,39793,39794,39769,39770,39745,39746,39721,39722,39697,39698,39673,39674,39649,39650,39625,39626,39601,39602,39577,39578,39537,39538,39513,39514,39489,39490,39465,39466,39441,39442,39417,39418,39393,39394,39369,39370,39345,39346,39321,39322,39297,39298,39273,39274,39249,39250,39225,39226,39201,39202,39177,39178,39153,39154,39128,39129,39104,39105,39080,39081,39056,39057,39032,39033,39008,39009,38984,38985,38960,38961,38936,38937,38912,38913,38888,38889,38864,38865,38840,38841,38816,38817,38792,38793,38768,38769,38744,38745,38704,38705,38680,38681,38656,38657,38632,38633,38608,38609,38584,38585,38560,38561,38536,38537,38512,38513,38488,38489,38464,38465,38440,38441,38416,38417,38392,38393,38368,38369,38344,38345,38320,38321,38295,38296,38271,38272,38247,38248,38223,38224,38199,38200,38175,38176,38151,38152,38127,38128,38103,38104,38079,38080,38055,38056,38031,38032,38006,38007,37986,37987,37966,37967,37946,37947,37926,37927,37891,37892,37871,37872,37851,37852,37831,37832,37811,37812,37791,37792,37771,37772,37751,37752,37731,37732,37711,37712,37691,37692,37671,37672,37651,37652,37631,37632,37611,37612,37591,37592,37571,37572,37550,37551,37530,37531,37510,37511,37490,37491,37470,37471,37450,37451,37430,37431,37410,37411,37390,37391,37370,37371,37350,37351,37330,37331,37310,37311,37290,37291,37270,37271,37250,37251,37230,37231,37195,37196,37175,37176,37155,37156,37135,37136,37115,37116,37095,37096,37075,37076,37055,37056,37035,37036,37015,37016,36995,36996,36975,36976,36955,36956,36935,36936,36915,36916,36895,36896,36875,36876,36854,36855,36834,36835,36814,36815,36794,36795,36774,36775,36754,36755,36734,36735,36714,36715,36694,36695,36674,36675,36654,36655,36634,36635,36614,36615,36594,36595,36574,36575,36554,36555,36534,36535,36499,36500,36479,36480,36459,36460,36439,36440,36419,36420,36399,36400,36379,36380,36359,36360,36339,36340,36319,36320,36299,36300,36279,36280,36259,36260,36239,36240,36219,36220,36199,36200,36179,36180,36158,36159,36138,36139,36118,36119,36098,36099,36078,36079,36058,36059,36037,36038,36021,36022,36005,36006,35989,35990,35973,35974,35957,35958,35941,35942,35925,35926,35909,35910,35893,35894,35877,35878,35847,35848,35831,35832,35815,35816,35799,35800,35783,35784,35767,35768,35751,35752,35735,35736,35719,35720,35703,35704,35687,35688,35671,35672,35655,35656,35639,35640,35623,35624,35607,35608,35591,35592,35574,35575,35558,35559,35542,35543,35526,35527,35510,35511,35494,35495,35478,35479,35462,35463,35446,35447,35430,35431,35414,35415,35388,35389,35372,35373,35356,35357,35340,35341,35324,35325,35308,35309,35283,35284,35267,35268,35251,35252,35235,35236,35219,35220,35203,35204,35111,35185,35095,35096,35079,35080,35063,35064,35047,35048,35031,35032,35015,35016,34999,35000,34975,34976,34937,34938,34921,34922,34904,34905,34888,34889,34872,34873,34856,34857,34840,34841,34824,34825,34764,34765,34748,34749,34732,34733,34716,34717,34700,34701,34684,34685,34668,34669,34652,34653,34636,34637,34620,34621,34604,34605,34307,34308,34291,34292,34275,34276,34259,34260,34243,34244,34227,34228,34211,34212,34195,34196,34179,34180,34163,34164,34147,34148,34131,34132,34115,34116,34099,34100,34083,34084,34067,34068,34052,34053,34039,33693,33694,33681,33682,33669,33670,33657,33658,33645,33646,33633,33634,33621,33622,33609,33610,33597,33598,33585,33586,33573,33574,33561,33562,33549,33550,33537,33538,33312,33313,33300,33301,33288,33289,33276,33277,33264,33265,33252,33253,33240,33241,33228,33229,33216,33217,33204,33205,33192,33193,33180,33181,33150,33162,33138,33139,33126,33127,33114,33115,33070,33074,33027
…
lines truncated
…
Memory usage: 54.5 G|Processing period 2021-05-29,2021-05-29, table archive_blob_2021_05 ids 150165,150163,150134,150132,150093,150091,40070,40071,40046,40047,40022,40023,39998,39999,39973,39974,39949,39950,39925,39926,39901,39902,39877,39878,39853,39854,39829,39830,39805,39806,39781,39782,39757,39758,39733,39734,39709,39710,39685,39686,39661,39662,39637,39638,39613,39614,39589,39590,39549,39550,39525,39526,39501,39502,39477,39478,39453,39454,39429,39430,39405,39406,39381,39382,39357,39358,39333,39334,39309,39310,39285,39286,39261,39262,39237,39238,39213,39214,39189,39190,39165,39166,39140,39141,39116,39117,39092,39093,39068,39069,39044,39045,39020,39021,38996,38997,38972,38973,38948,38949,38924,38925,38900,38901,38876,38877,38852,38853,38828,38829,38804,38805,38780,38781,38756,38757,38716,38717,38692,38693,38668,38669,38644,38645,38620,38621,38596,38597,38572,38573,38548,38549,38524,38525,38500,38501,38476,38477,38452,38453,38428,38429,38404,38405,38380,38381,38356,38357,38332,38333,38307,38308,38283,38284,38259,38260,38235,38236,38211,38212,38187,38188,38163,38164,38139,38140,38115,38116,38091,38092,38067,38068,38043,38044,38018,38019,37998,37999,37978,37979,37958,37959,37938,37939,37903,37904,37883,37884,37863,37864,37843,37844,37823,37824,37803,37804,37783,37784,37763,37764,37743,37744,37723,37724,37703,37704,37683,37684,37663,37664,37643,37644,37623,37624,37603,37604,37583,37584,37562,37563,37542,37543,37522,37523,37502,37503,37482,37483,37462,37463,37442,37443,37422,37423,37402,37403,37382,37383,37362,37363,37342,37343,37322,37323,37302,37303,37282,37283,37262,37263,37242,37243,37207,37208,37187,37188,37167,37168,37147,37148,37127,37128,37107,37108,37087,37088,37067,37068,37047,37048,37027,37028,37007,37008,36987,36988,36967,36968,36947,36948,36927,36928,36907,36908,36887,36888,36866,36867,36846,36847,36826,36827,36807,36806,36786,36787,36767,36766,36746,36747,36726,36727,36707,36706,36687,36686,36666,36667,36646,36647,36626,36627,36607,36606,36587,36586,36566,36567,36546,36547,36511,36512,36492,36491,36471,36472,36452,36451,36431,36432,36412,36411,36391,36392,36371,36372,36351,36352,36331,36332,36312,36311,36292,36291,36272,36271,36251,36252,36232,36231,36211,36212,36192,36191,36171,36170,36151,36150,36131,36130,36110,36111,36091,36090,36071,36070,36051,36050,150165,150163,150134,150132,150093,150091,40070,40071,40046,40047,40022,40023,39998,39999,39973,39974,39949,39950,39925,39926,39901,39902,39877,39878,39853,39854,39829,39830,39805,39806,39781,39782,39757,39758,39733,39734,39709,39710,39685,39686,39661,39662,39637,39638,39613,39614,39589,39590,39549,39550,39525,39526,39501,39502,39477,39478,39453,39454,39429,39430,39405,39406,39381,39382,39357,39358,39333,39334,39309,39310,39285,39286,39261,39262,39237,39238,39213,39214,39189,39190,39165,39166,39140,39141,39116,39117,39092,39093,39068,39069,39044,39045,39020,39021,38996,38997,38972,38973,38948,38949,38924,38925,38900,38901,38876,38877,38852,38853,38828,38829,38804,38805,38780,38781,38756,38757,38716,38717,38692,38693,38668,38669,38644,38645,38620,38621,38596,38597,38572,38573,38548,38549,38524,38525,38500,38501,38476,38477,38452,38453,38428,38429,38404,38405,38380,38381,38356,38357,38332,38333,38307,38308,38283,38284,38259,38260,38235,38236,38211,38212,38187,38188,38163,38164,38139,38140,38115,38116,38091,38092,38067,38068,38043,38044,38018,38019,37998,37999,37978,37979,37958,37959,37938,37939,37903,37904,37883,37884,37863,37864,37843,37844,37823,37824,37803,37804,37783,37784,37763,37764,37743,37744,37723,37724,37703,37704,37683,37684,37663,37664,37643,37644,37623,37624,37603,37604,37583,37584,37562,37563,37542,37543,37522,37523,37502,37503,37482,37483,37462,37463,37442,37443,37422,37423,37402,37403,37382,37383,37362,37363,37342,37343,37322,37323,37302,37303,37282,37283,37262,37263,37242,37243,37207,37208,37187,37188,37167,37168,37147,37148,37127,37128,37107,37108,37087,37088,37067,37068,37047,37048,37027,37028,37007,37008,36987,36988,36967,36968,36947,36948,36927,36928,36907,36908,36887,36888,36866,36867,36846,36847,36826,36827,36807,36806,36786,36787,36767,36766,36746,36747,36726,36727,36707,36706,36687,36686,36666,36667,36646,36647,36626,36627,36607,36606,36587,36586,36566,36567,36546,36547,36511,36512,36492,36491,36471,36472,36452,36451,36431,36432,36412,36411,36391,36392,36371,36372,36351,36352,36331,36332,36312,36311,36292,36291,36272,36271,36251,36252,36232,36231,36211,36212,36192,36191,36171,36170,36151,36150,36131,36130,36110,36111,36091,36090,36071,36070,36051,36050,150165,150163,150134,150132,150093,150091,40070,40071,40046,40047,40022,40023,39998,39999,39973,39974,39949,39950,39925,39926,39901,39902,39877,39878,39853,39854,39829,39830,39805,39806,39781,39782,39757,39758,39733,39734,39709,39710,39685,39686,39661,39662,39637,39638,39613,39614,39589,39590,39549,39550,39525,39526,39501,39502,39477,39478,39453,39454,39429,39430,39405,39406,39381,39382,39357,39358,39333,39334,39309,39310,39285,39286,39261,39262,39237,39238,39213,39214,39189,39190,39165,39166,39140,39141,39116,39117,39092,39093,39068,39069,39044,39045,39020,39021,38996,38997,38972,38973,38948,38949,38924,38925,38900,38901,38876,38877,38852,38853,38828,38829,38804,38805,38780,38781,38756,38757,38716,38717,38692,38693,38668,38669,38644,38645,38620,38621,38596,38597,38572,38573,38548,38549,38524,38525,38500,38501,38476,38477,38452,38453,38428,38429,38404,38405,38380,38381,38356,38357,38332,38333,38307,38308,38283,38284,38259,38260,38235,38236,38211,38212,38187,38188,38163,38164,38139,38140,38115,38116,38091,38092,38067,38068,38043,38044,38018,38019,37998,37999,37978,37979,37958,37959,37938,37939,37903,37904,37883,37884,37863,37864,37843,37844,37823,37824,37803,37804,37783,37784,37763,37764,37743,37744,37723,37724,37703,37704,37683,37684,37663,37664,37643,37644,37623,37624,37603,37604,37583,37584,37562,37563,37542,37543,37522,37523,37502,37503,37482,37483,37462,37463,37442,37443,37422,37423,37402,37403,37382,37383,37362,37363,37342,37343,37322,37323,37302,37303,37282,37283,37262,37263,37242,37243,37207,37208,37187,37188,37167,37168,37147,37148,37127,37128,37107,37108,37087,37088,37067,37068,37047,37048,37027,37028,37007,37008,36987,36988,36967,36968,36947,36948,36927,36928,36907,36908,36887,36888,36866,36867,36846,36847,36826,36827,36807,36806,36786,36787,36767,36766,36746,36747,36726,36727,36707,36706,36687,36686,36666,36667,36646,36647,36626,36627,36607,36606,36587,36586,36566,36567,36546,36547,36511,36512,36492,36491,36471,36472,36452,36451,36431,36432,36412,36411,36391,36392,36371,36372,36351,36352,36331,36332,36312,36311,36292,36291,36272,36271,36251,36252,36232,36231,36211,36212,36192,36191,36171,36170,36151,36150,36131,36130,36110,36111,36091,36090,36071,36070,36051,36050,150165,150163,150134,150132,150093,150091,40070,40071,40046,40047,40022,40023,39998,39999,39973,39974,39949,39950,39925,39926,39901,39902,39877,39878,39853,39854,39829,39830,39805,39806,39781,39782,39757,39758,39733,39734,39709,39710,39685,39686,39661,39662,39637,39638,39613,39614,39589,39590,39549,39550,39525,39526,39501,39502,39477,39478,39453,39454,39429,39430,39405,39406,39381,39382,39357,39358,39333,39334,39309,39310,39285,39286,39261,39262,39237,39238,39213,39214,39189,39190,39165,39166,39140,39141,39116,39117,39092,39093,39068,39069,39044,39045,39020,39021,38996,38997,38972,38973,38948,38949,38924,38925,38900,38901,38876,38877,38852,38853,38828,38829,38804,38805,38780,38781,38756,38757,38716,38717,38692,38693,38668,38669,38644,38645,38620,38621,38596,38597,38572,38573,38548,38549,38524,38525,38500,38501,38476,38477,38452,38453,38428,38429,38404,38405,38380,38381,38356,38357,38332,38333,38307,38308,38283,38284,38259,38260,38235,38236,38211,38212,38187,38188,38163,38164,38139,38140,38115,38116,38091,38092,38067,38068,38043,38044,38018,38019,37998,37999,37978,37979,37958,37959,37938,37939,37903,37904,37883,37884,37863,37864,37843,37844,37823,37824,37803,37804,37783,37784,37763,37764,37743,37744,37723,37724,37703,37704,37683,37684,37663,37664,37643,37644,37623,37624,37603,37604,37583,37584,37562,37563,37542,37543,37522,37523,37502,37503,37482,37483,37462,37463,37442,37443,37422,37423,37402,37403,37382,37383,37362,37363,37342,37343,37322,37323,37302,37303,37282,37283,37262,37263,37242,37243,37207,37208,37187,37188,37167,37168,37147,37148,37127,37128,37107,37108,37087,37088,37067,37068,37047,37048,37027,37028,37007,37008,36987,36988,36967,36968,36947,36948,36927,36928,36907,36908,36887,36888,36866,36867,36846,36847,36826,36827,36807,36806,36786,36787,36767,36766,36746,36747,36726,36727,36707,36706,36687,36686,36666,36667,36646,36647,36626,36627,36607,36606,36587,36586,36566,36567,36546,36547,36511,36512,36492,36491,36471,36472,36452,36451,36431,36432,36412,36411,36391,36392,36371,36372,36351,36352,36331,36332,36312,36311,36292,36291,36272,36271,36251,36252,36232,36231,36211,36212,36192,36191,36171,36170,36151,36150,36131,36130,36110,36111,36091,36090,36071,36070,36051,36050,150165,150163,150134,150132,150093,150091,40070,40071,40046,40047,40022,40023,39998,39999,39973,39974,39949,39950,39925,39926,39901,39902,39877,39878,39853,39854,39829,39830,39805,39806,39781,39782,39757,39758,39733,39734,39709,39710,39685,39686,39661,39662,39637,39638,39613,39614,39589,39590,39549,39550,39525,39526,39501,39502,39477,39478,39453,39454,39429,39430,39405,39406,39381,39382,39357,39358,39333,39334,39309,39310,39285,39286,39261,39262,39237,39238,39213,39214,39189,39190,39165,39166,39140,39141,39116,39117,39092,39093,39068,39069,39044,39045,39020,39021,38996,38997,38972,38973,38948,38949,38924,38925,38900,38901,38876,38877,38852,38853,38828,38829,38804,38805,38780,38781,38756,38757,38716,38717,38692,38693,38668,38669,38644,38645,38620,38621,38596,38597,38572,38573,38548,38549,38524,38525,38500,38501,38476,38477,38452,38453,38428,38429,38404,38405,38380,38381,38356,38357,38332,38333,38307,38308,38283,38284,38259,38260,38235,38236,38211,38212,38187,38188,38163,38164,38139,38140,38115,38116,38091,38092,38067,38068,38043,38044,38018,38019,37998,37999,37978,37979,37958,37959,37938,37939,37903,37904,37883,37884,37863,37864,37843,37844,37823,37824,37803,37804,37783,37784,37763,37764,37743,37744,37723,37724,37703,37704,37683,37684,37663,37664,37643,37644,37623,37624,37603,37604,37583,37584,37562,37563,37542,37543,37522,37523,37502,37503,37482,37483,37462,37463,37442,37443,37422,37423,37402,37403,37382,37383,37362,37363,37342,37343,37322,37323,37302,37303,37282,37283,37262,37263,37242,37243,37207,37208,37187,37188,37167,37168,37147,37148,37127,37128,37107,37108,37087,37088,37067,37068,37047,37048,37027,37028,37007,37008,36987,36988,36967,36968,36947,36948,36927,36928,36907,36908,36887,36888,36866,36867,36846,36847,36826,36827,36807,36806,36786,36787,36767,36766,36746,36747,36726,36727,36707,36706,36687,36686,36666,36667,36646,36647,36626,36627,36607,36606,36587,36586,36566,36567,36546,36547,36511,36512,36492,36491,36471,36472,36452,36451,36431,36432,36412,36411,36391,36392,36371,36372,36351,36352,36331,36332,36312,36311,36292,36291,36272,36271,36251,36252,36232,36231,36211,36212,36192,36191,36171,36170,36151,36150,36131,36130,36110,36111,36091,36090,36071,36070,36051,36050

mmap() failed: [12] Cannot allocate memory

mmap() failed: [12] Cannot allocate memory
{"result":"error","message":"Out of memory (allocated 65016569856) (tried to allocate 7058941 bytes) on \/var\/www\/matomo\/core\/DataAccess\/ArchiveSelector.php(390) #0 \/var\/www\/matomo\/core\/ArchiveProcessor.php(220): Piwik\\ArchiveProcessor->aggregateDataTableRecord(name="Goals_ItemsName_Cart") #1 \/var\/www\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(168): Piwik\\Plugins\\Goals\\Archiver->callAggregateMultipleReports() #2 \/var\/www\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(168): Piwik\\Plugins\\Goals\\Archiver->callAggregateMultipleReports() ","backtrace":"Out of memory (allocated 65016569856) (tried to allocate 7058941 bytes) on \/var\/www\/matomo\/core\/DataAccess\/ArchiveSelector.php(390)\n#0 \/var\/www\/matomo\/core\/ArchiveProcessor.php(220): Piwik\\ArchiveProcessor->aggregateDataTableRecord(name=\"Goals_ItemsName_Cart\")\n#1 \/var\/www\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(168): Piwik\\Plugins\\Goals\\Archiver->callAggregateMultipleReports()\n#2 \/var\/www\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(168): Piwik\\Plugins\\Goals\\Archiver->callAggregateMultipleReports()\n\n#0 [internal function]: Piwik\\Plugins\\CorePluginsAdmin\\Controller->safemode(Array)\n#1 \/var\/www\/dbuser\/core\/FrontController.php(615): call_user_func_array(Array, Array)\n#2 \/var\/www\/dbuser\/core\/FrontController.php(167): Piwik\\FrontController->doDispatch('CorePluginsAdmi...', 'safemode', Array)\n#3 \/var\/www\/dbuser\/core\/FrontController.php(98): Piwik\\FrontController->dispatch('CorePluginsAdmi...', 'safemode', Array)\n#4 \/var\/www\/dbuser\/core\/FrontController.php(271): Piwik\\FrontController::generateSafeModeOutputFromError(Array)\n#5 [internal function]: Piwik\\FrontController::triggerSafeModeWhenError()\n#6 {main}"}{"result":"error","message":"Out of memory (allocated 65016569856) (tried to allocate 7058941 bytes) on \/var\/www\/matomo\/core\/DataAccess\/ArchiveSelector.php(390) #0 \/var\/www\/matomo\/core\/ArchiveProcessor.php(220): Piwik\\ArchiveProcessor->aggregateDataTableRecord(name="Goals_ItemsName_Cart") #1 \/var\/www\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(168): Piwik\\Plugins\\Goals\\Archiver->callAggregateMultipleReports() #2 \/var\/www\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(168): Piwik\\Plugins\\Goals\\Archiver->callAggregateMultipleReports() ","backtrace":"Out of memory (allocated 65016569856) (tried to allocate 7058941 bytes) on \/var\/www\/matomo\/core\/DataAccess\/ArchiveSelector.php(390)\n#0 \/var\/www\/matomo\/core\/ArchiveProcessor.php(220): Piwik\\ArchiveProcessor->aggregateDataTableRecord(name=\"Goals_ItemsName_Cart\")\n#1 \/var\/www\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(168): Piwik\\Plugins\\Goals\\Archiver->callAggregateMultipleReports()\n#2 \/var\/www\/matomo\/core\/ArchiveProcessor\/PluginsArchiver.php(168): Piwik\\Plugins\\Goals\\Archiver->callAggregateMultipleReports()\n\n#0 [internal function]: Piwik\\Plugins\\CorePluginsAdmin\\Controller->safemode(Array)\n#1 \/var\/www\/dbuser\/core\/FrontController.php(615): call_user_func_array(Array, Array)\n#2 \/var\/www\/dbuser\/core\/FrontController.php(167): Piwik\\FrontController->doDispatch('CorePluginsAdmi...', 'safemode', Array)\n#3 \/var\/www\/dbuser\/core\/FrontController.php(98): Piwik\\FrontController->dispatch('CorePluginsAdmi...', 'safemode', Array)\n#4 \/var\/www\/dbuser\/core\/FrontController.php(271): Piwik\\FrontController::generateSafeModeOutputFromError(Array)\n#5 [internal function]: Piwik\\FrontController::triggerSafeModeWhenError()\n#6 {main}"}root@web-analytics-02:/var/www/matomo#

No debug statements from plugins/Goals/Archiver.php seen (also tried with echo instead of logger as unsure about output diversions).

@diosmosis
Copy link
Member

@pepl Can you tell me if you're using the core:archive command? If you day periods and process them does the error still occur? And this is for the All Visits segment correct?

@pepl
Copy link
Author

pepl commented Jul 29, 2021 •
edited

@diosmosis Yes, I've used the core:archive command and just executed climulti:request directly to get the specific debug output. That's what I've done to get there:

  1. Applied the patch (and added a bit more debugging output as quoted above)
  2. Invalidated yearly archive via (first) /usr/bin/php /var/www/matomo/console core:invalidate-report-data --sites=3 --dates=2021-01-01 --periods=year --plugin=Goals --verbose and after archiving after that did not make a difference then /usr/bin/php /var/www/matomo/console core:invalidate-report-data --sites=3 --dates=2021-01-01 --periods=year --verbose
  3. Tried to archive the yearly period: /usr/bin/php /var/www/matomo/console core:archive --url=http://localhost:10623/ --concurrent-requests-per-website=1 --force-periods=year --force-idsites=3 --verbose --verbose --verbose >/root/archive_3_year.out 2>&1

Day, Week and Month (at least the (few days from May 24th to the end of month) periods run through fine.

And this is for the All Visits segment correct?

Guess so, no segments selected explicitly there as far as I can tell.

@diosmosis
Copy link
Member

@pepl You'll need to invalidate the month or lower archives, not the year archive. The year archive will just try to add up the month archives. And since the current report in the month archives has so many rows, it will run out of memory when trying to load them. So you have to re-archive at least each month so the limit will be applied to them. Then when archiving the year period, the system won't have to load as many rows when getting month reports.

@pepl
Copy link
Author

pepl commented Jul 30, 2021

@diosmosis It looked good for May and June after I've invalidated and re-archived on a per day basis first. Will continue with July followed by the re-archiving of the yearly period over the week-end

@pepl
Copy link
Author

pepl commented Jul 31, 2021

Unfortunately it still fails at the yearly aggregation even after re-archiving days and months before. Anything wrong with the way invalidation and re-archiving is done there?

# days and months
/usr/bin/php /var/www/matomo/console core:invalidate-report-data --sites=3 --dates=2021-05-24,2021-05-31 --periods=day --verbose
/usr/bin/php /var/www/matomo/console core:invalidate-report-data --sites=3 --dates=2021-05-01 --periods=month --verbose
/usr/bin/php /var/www/matomo/console core:archive --url=http://localhost:10623/ --concurrent-requests-per-website=1 --force-periods=day --force-date-range 2021-05-24,2021-05-31 --force-idsites=3 --verbose --verbose --verbose
/usr/bin/php /var/www/matomo/console core:archive --url=http://localhost:10623/ --concurrent-requests-per-website=1 --force-periods=month --force-date-range 2021-05-01,2021-05-31 --force-idsites=3 --verbose --verbose --verbose
/usr/bin/php /var/www/matomo/console core:invalidate-report-data --sites=3 --dates=2021-06-01,2021-06-30 --periods=day --verbose
/usr/bin/php /var/www/matomo/console core:invalidate-report-data --sites=3 --dates=2021-06-01 --periods=month --verbose
/usr/bin/php /var/www/matomo/console core:archive --url=http://localhost:10623/ --concurrent-requests-per-website=1 --force-periods=day --force-date-range 2021-06-01,2021-06-30 --force-idsites=3 --verbose --verbose --verbose
/usr/bin/php /var/www/matomo/console core:archive --url=http://localhost:10623/ --concurrent-requests-per-website=1 --force-periods=month --force-date-range 2021-06-01,2021-06-30 --force-idsites=3 --verbose --verbose --verbose
/usr/bin/php /var/www/matomo/console core:invalidate-report-data --sites=3 --dates=2021-07-01,2021-07-31 --periods=day --verbose
/usr/bin/php /var/www/matomo/console core:archive --url=http://localhost:10623/ --concurrent-requests-per-website=1 --force-periods=day --force-date-range 2021-07-01,2021-07-31 --force-idsites=3 --verbose --verbose --verbose
/usr/bin/php /var/www/matomo/console core:invalidate-report-data --sites=3 --dates=2021-07-01 --periods=month --verbose
/usr/bin/php /var/www/matomo/console core:archive --url=http://localhost:10623/ --concurrent-requests-per-website=1 --force-periods=month --force-date-range 2021-07-01,2021-07-31 --force-idsites=3 --verbose --verbose --verbose

# period year, this fails after 65G of host memory are exhausted…
/usr/bin/php /var/www/matomo/console core:archive --url=http://localhost:10623/ --concurrent-requests-per-website=1 --force-periods=year --force-date-range=2021-01-01,2021-12-31 --force-idsites=3 --verbose --verbose --verbose

@diosmosis
Copy link
Member

@pepl did you invalidate for every month in the year? Assuming you have data for those months, they could have > 100,000 rows as well. For the data you did invalidate and rearchive, can you check in the UI for those days/weeks/months and see if the product reports have the correct, limited, number of rows?

These are the commands you would need to run to re-process the relevant data:

./console core:invalidate-report-data --sites=3 --dates=2021-01-01,2021-08-01 --periods=month -vvv # one day later because I don't know if you'll read this in August
./console core:archive --url=http://localhost:10623 --force-idsites=3 -vvv

Can you run these commands and post the output for each?

@pepl
Copy link
Author

pepl commented Jul 31, 2021

Sure:

./console core:invalidate-report-data --sites=3 --dates=2021-01-01,2021-08-01 --periods=month -vvv > /tmp/invalidate.log 2>&1

./console core:archive --url=http://localhost:10623 --force-idsites=3 -vvv > /tmp/rearchive.log 2>&1

@diosmosis
Copy link
Member

@pepl what version of matomo are you using and can you tell me what the row counts for the month product reports between 2021-01-01 and 2021-08-01 are?

@pepl
Copy link
Author

pepl commented Jul 31, 2021 via email

@diosmosis
Copy link
Member

@pepl I meant the counts in the UI:
image

At the bottom there's 1-10 of 13. 13 is the amount of rows in that report. The change in the linked pull request should limit those counts the values specified in the configuration, which will allow the reports to be loaded in memory when aggregating year reports.

@pepl
Copy link
Author

pepl commented Aug 2, 2021

It's the default of 10 products per row and here's the row-counts:

Jan 2356
Feb 0
Mar 2432
Apr 9
May 258236
Jun 2687
Jul 2992

We went live with Matomo May 24th. Looks like something went wrong for those last days of May?

@diosmosis
Copy link
Member

@pepl yes, that looks strange, do that many different products exist in your ecommerce site? I suppose it's conceivable. It might help to explore the data in May and see if it's only that large for a single day or week or more, to get more context.

Can you also provide the current config for:

    [General] datatable_archiving_maximum_rows_products
    [General] datatable_archiving_maximum_rows_subtable_products

in your config.ini.php?

@pepl
Copy link
Author

pepl commented Aug 3, 2021

It looks like we have faulty Goals-data for the days 2021-05-25 to 2021-05-29 in our database which amount to a significant data size as seen below in the code.

As the blob entries themselves are comparatively hard to dissect, I was thinking about just getting rid of the records in question (like 'Goals%') for this site and date range. That should leave a data gap but still work, right?

Regarding datatable_archiving_maximum_rows_*products: We have the value of 500 configured there on our default host. For the re-archiving I've used a separate host (with more memory) and went down to the value of 10 while re-archiving May,June and July for that site. The archiving on the default host was suspended during the interactive re-archiving.

MariaDB [matomo]> select count(*),period,date1,date2,name from archive_blob_2021_05 where idsite=3 and name like 'Goals%' and period=1 group by 2,3,4,5;
+----------+--------+------------+------------+--------------------------+
| count(*) | period | date1      | date2      | name                     |
+----------+--------+------------+------------+--------------------------+
|        3 |      1 | 2021-05-01 | 2021-05-01 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-01 | 2021-05-01 | Goals_ItemsName          |
|        3 |      1 | 2021-05-01 | 2021-05-01 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-02 | 2021-05-02 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-02 | 2021-05-02 | Goals_ItemsName          |
|        3 |      1 | 2021-05-02 | 2021-05-02 | Goals_ItemsSku           |
|      195 |      1 | 2021-05-03 | 2021-05-03 | Goals_ItemsCategory      |
|      192 |      1 | 2021-05-03 | 2021-05-03 | Goals_ItemsCategory_Cart |
|      195 |      1 | 2021-05-03 | 2021-05-03 | Goals_ItemsName          |
|      192 |      1 | 2021-05-03 | 2021-05-03 | Goals_ItemsName_Cart     |
|      195 |      1 | 2021-05-03 | 2021-05-03 | Goals_ItemsSku           |
|      192 |      1 | 2021-05-03 | 2021-05-03 | Goals_ItemsSku_Cart      |
|        3 |      1 | 2021-05-04 | 2021-05-04 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-04 | 2021-05-04 | Goals_ItemsName          |
|        3 |      1 | 2021-05-04 | 2021-05-04 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-05 | 2021-05-05 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-05 | 2021-05-05 | Goals_ItemsName          |
|        3 |      1 | 2021-05-05 | 2021-05-05 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-06 | 2021-05-06 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-06 | 2021-05-06 | Goals_ItemsName          |
|        3 |      1 | 2021-05-06 | 2021-05-06 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-07 | 2021-05-07 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-07 | 2021-05-07 | Goals_ItemsName          |
|        3 |      1 | 2021-05-07 | 2021-05-07 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-08 | 2021-05-08 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-08 | 2021-05-08 | Goals_ItemsName          |
|        3 |      1 | 2021-05-08 | 2021-05-08 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-09 | 2021-05-09 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-09 | 2021-05-09 | Goals_ItemsName          |
|        3 |      1 | 2021-05-09 | 2021-05-09 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-10 | 2021-05-10 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-10 | 2021-05-10 | Goals_ItemsName          |
|        3 |      1 | 2021-05-10 | 2021-05-10 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-11 | 2021-05-11 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-11 | 2021-05-11 | Goals_ItemsName          |
|        3 |      1 | 2021-05-11 | 2021-05-11 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-12 | 2021-05-12 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-12 | 2021-05-12 | Goals_ItemsName          |
|        3 |      1 | 2021-05-12 | 2021-05-12 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-13 | 2021-05-13 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-13 | 2021-05-13 | Goals_ItemsName          |
|        3 |      1 | 2021-05-13 | 2021-05-13 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-14 | 2021-05-14 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-14 | 2021-05-14 | Goals_ItemsName          |
|        3 |      1 | 2021-05-14 | 2021-05-14 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-15 | 2021-05-15 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-15 | 2021-05-15 | Goals_ItemsName          |
|        3 |      1 | 2021-05-15 | 2021-05-15 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-16 | 2021-05-16 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-16 | 2021-05-16 | Goals_ItemsName          |
|        3 |      1 | 2021-05-16 | 2021-05-16 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-17 | 2021-05-17 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-17 | 2021-05-17 | Goals_ItemsName          |
|        3 |      1 | 2021-05-17 | 2021-05-17 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-18 | 2021-05-18 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-18 | 2021-05-18 | Goals_ItemsName          |
|        3 |      1 | 2021-05-18 | 2021-05-18 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-19 | 2021-05-19 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-19 | 2021-05-19 | Goals_ItemsName          |
|        3 |      1 | 2021-05-19 | 2021-05-19 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-20 | 2021-05-20 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-20 | 2021-05-20 | Goals_ItemsName          |
|        3 |      1 | 2021-05-20 | 2021-05-20 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-21 | 2021-05-21 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-21 | 2021-05-21 | Goals_ItemsName          |
|        3 |      1 | 2021-05-21 | 2021-05-21 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-22 | 2021-05-22 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-22 | 2021-05-22 | Goals_ItemsName          |
|        3 |      1 | 2021-05-22 | 2021-05-22 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-23 | 2021-05-23 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-23 | 2021-05-23 | Goals_ItemsName          |
|        3 |      1 | 2021-05-23 | 2021-05-23 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-24 | 2021-05-24 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-24 | 2021-05-24 | Goals_ItemsName          |
|        3 |      1 | 2021-05-24 | 2021-05-24 | Goals_ItemsSku           |
|     1059 |      1 | 2021-05-25 | 2021-05-25 | Goals_ItemsCategory      |
|     1056 |      1 | 2021-05-25 | 2021-05-25 | Goals_ItemsCategory_Cart |
|     1059 |      1 | 2021-05-25 | 2021-05-25 | Goals_ItemsName          |
|     1056 |      1 | 2021-05-25 | 2021-05-25 | Goals_ItemsName_Cart     |
|     1059 |      1 | 2021-05-25 | 2021-05-25 | Goals_ItemsSku           |
|     1056 |      1 | 2021-05-25 | 2021-05-25 | Goals_ItemsSku_Cart      |
|      700 |      1 | 2021-05-26 | 2021-05-26 | Goals_ItemsCategory      |
|      700 |      1 | 2021-05-26 | 2021-05-26 | Goals_ItemsCategory_Cart |
|      700 |      1 | 2021-05-26 | 2021-05-26 | Goals_ItemsName          |
|      700 |      1 | 2021-05-26 | 2021-05-26 | Goals_ItemsName_Cart     |
|      700 |      1 | 2021-05-26 | 2021-05-26 | Goals_ItemsSku           |
|      700 |      1 | 2021-05-26 | 2021-05-26 | Goals_ItemsSku_Cart      |
|      601 |      1 | 2021-05-27 | 2021-05-27 | Goals_ItemsCategory      |
|      601 |      1 | 2021-05-27 | 2021-05-27 | Goals_ItemsCategory_Cart |
|      601 |      1 | 2021-05-27 | 2021-05-27 | Goals_ItemsName          |
|      601 |      1 | 2021-05-27 | 2021-05-27 | Goals_ItemsName_Cart     |
|      601 |      1 | 2021-05-27 | 2021-05-27 | Goals_ItemsSku           |
|      601 |      1 | 2021-05-27 | 2021-05-27 | Goals_ItemsSku_Cart      |
|      565 |      1 | 2021-05-28 | 2021-05-28 | Goals_ItemsCategory      |
|      565 |      1 | 2021-05-28 | 2021-05-28 | Goals_ItemsCategory_Cart |
|      565 |      1 | 2021-05-28 | 2021-05-28 | Goals_ItemsName          |
|      565 |      1 | 2021-05-28 | 2021-05-28 | Goals_ItemsName_Cart     |
|      565 |      1 | 2021-05-28 | 2021-05-28 | Goals_ItemsSku           |
|      565 |      1 | 2021-05-28 | 2021-05-28 | Goals_ItemsSku_Cart      |
|      373 |      1 | 2021-05-29 | 2021-05-29 | Goals_ItemsCategory      |
|      373 |      1 | 2021-05-29 | 2021-05-29 | Goals_ItemsCategory_Cart |
|      373 |      1 | 2021-05-29 | 2021-05-29 | Goals_ItemsName          |
|      373 |      1 | 2021-05-29 | 2021-05-29 | Goals_ItemsName_Cart     |
|      373 |      1 | 2021-05-29 | 2021-05-29 | Goals_ItemsSku           |
|      373 |      1 | 2021-05-29 | 2021-05-29 | Goals_ItemsSku_Cart      |
|        3 |      1 | 2021-05-30 | 2021-05-30 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-30 | 2021-05-30 | Goals_ItemsCategory_Cart |
|        3 |      1 | 2021-05-30 | 2021-05-30 | Goals_ItemsName          |
|        3 |      1 | 2021-05-30 | 2021-05-30 | Goals_ItemsName_Cart     |
|        3 |      1 | 2021-05-30 | 2021-05-30 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-30 | 2021-05-30 | Goals_ItemsSku_Cart      |
|        3 |      1 | 2021-05-31 | 2021-05-31 | Goals_ItemsCategory      |
|        3 |      1 | 2021-05-31 | 2021-05-31 | Goals_ItemsCategory_Cart |
|        3 |      1 | 2021-05-31 | 2021-05-31 | Goals_ItemsName          |
|        3 |      1 | 2021-05-31 | 2021-05-31 | Goals_ItemsName_Cart     |
|        3 |      1 | 2021-05-31 | 2021-05-31 | Goals_ItemsSku           |
|        3 |      1 | 2021-05-31 | 2021-05-31 | Goals_ItemsSku_Cart      |
+----------+--------+------------+------------+--------------------------+
117 rows in set (2.149 sec)

W

MariaDB [matomo]> select sum(octet_length(value)) from archive_blob_2021_05 where idsite=3 and name='Goals_ItemsName';
+--------------------------+
| sum(octet_length(value)) |
+--------------------------+
|               6457405766 |
+--------------------------+
1 row in set (57.111 sec)

MariaDB [matomo]> select sum(octet_length(value)) from archive_blob_2021_06 where idsite=3 and name='Goals_ItemsName';
+--------------------------+
| sum(octet_length(value)) |
+--------------------------+
|                 60869427 |
+--------------------------+
1 row in set (10.865 sec)

MariaDB [matomo]> select sum(octet_length(value)) from archive_blob_2021_07 where idsite=3 and name='Goals_ItemsName';
+--------------------------+
| sum(octet_length(value)) |
+--------------------------+
|                 36756970 |
+--------------------------+
1 row in set (4.862 sec)

MariaDB [matomo]> select count(*) from archive_blob_2021_05 where idsite=3 and name='Goals_ItemsName' limit 1;
+----------+
| count(*) |
+----------+
|     3599 |
+----------+
1 row in set (1.768 sec)

MariaDB [matomo]> select count(*) from archive_blob_2021_06 where idsite=3 and name='Goals_ItemsName';
+----------+
| count(*) |
+----------+
|      118 |
+----------+
1 row in set (0.071 sec)

MariaDB [matomo]> select count(*) from archive_blob_2021_07 where idsite=3 and name='Goals_ItemsName';
+----------+
| count(*) |
+----------+
|      111 |
+----------+
1 row in set (0.015 sec)

@diosmosis
Copy link
Member

@pepl I guess this means that the limiting code introduced isn't having an effect for some reason... Would you be able to apply this change (which has a couple log statements): https://github.com/matomo-org/matomo/compare/17816-debug?expand=1#diff-783509f06c0033f400318631fbe69e779f8be86572982bcd4e643303a98006bdR298-R318 ? Then invalidate the days in the month, archive, and check the logs?

Actually please also apply https://github.com/matomo-org/matomo/compare/17816-debug?expand=1#diff-783509f06c0033f400318631fbe69e779f8be86572982bcd4e643303a98006bdR462-R464, I see now the original change was incomplete.

@pepl
Copy link
Author

pepl commented Aug 4, 2021

@diosmosis I've applied the patch(es) and re-ran invalidation and re-archiving using

/var/www/matomo# /usr/bin/php /var/www/matomo/console core:invalidate-report-data --sites=3 --dates=2021-05-24,2021-05-31 --periods=day --verbose
Invalidating day periods in 2021-05-24,2021-05-31 [segment = ]...
Success. The following dates were invalidated successfully: 2021-05-24, 2021-05-25, 2021-05-26, 2021-05-27, 2021-05-28, 2021-05-29, 2021-05-30, 2021-05-31
/var/www/matomo# ./console core:archive --url=http://localhost:10623 --force-idsites=3 -vvv > /tmp/rearchive-202108041326-largehost-logwarning-datatable_archiving_maximum_rows_products_eq_100.log 2>&1

and

/var/www/matomo# /usr/bin/php /var/www/matomo/console core:invalidate-report-data --sites=3 --dates=2021-05-24,2021-05-31 --periods=day --plugin=Goals -vvv
Invalidating day periods in 2021-05-24,2021-05-31 [segment = ]...
Success. The following dates were invalidated successfully: 2021-05-24, 2021-05-25, 2021-05-26, 2021-05-27, 2021-05-28, 2021-05-29, 2021-05-30, 2021-05-31
/var/www/matomo# ./console core:archive --url=http://localhost:10623 --force-idsites=3 -vvv > /tmp/rearchive-202108041326-largehost-logwarning-datatable_archiving_maximum_rows_products_eq_100-invalidatedGoalsPlugins.log 2>&1

respectively (second one after first one failed).

Before that, I've tried with the new default of 10k for datatable_archiving_maximum_rows_products before moving that down to 100.

With all attempts it looks like the new code in Goals/Archiver.php is not hit (or the respective log messages lost in STDERR/STDOUT redirection?)

Here the output of the re-archiving commands:
https://gist.github.com/pepl/e7cdbfba70ee51a8ae19028c70928b78

@diosmosis
Copy link
Member

@pepl sorry this would be in the tmp/logs/matomo.log file, which you can setup via https://matomo.org/faq/troubleshooting/faq_115/. If you still don't see it there, would you be able to add a log to the start of the Goals\Archiver::aggregateDayReport() and Goals\Archiver::aggregateMultipleReports() methods as a sanity check to see the logs get output?

@pepl
Copy link
Author

pepl commented Aug 4, 2021

Logging worked! datatable_archiving_maximum_rows_products still configured with 100 on that box. Command was ./console core:archive --url=http://localhost:10623 --force-idsites=3 -vvv after invalidating like above (no Plugin restriction)

matomo.log: https://gist.github.com/pepl/9e42b29ea1ce751a4ae0a519423413ae

@diosmosis
Copy link
Member

@pepl ok, I see where the problem is. There are still reports for segments that have the high row counts as well. The easiest way to deal with this would be to truncate the archive tables (numeric + blob) for may, then invalidate every day again and run core:archive. Can you try this?

@pepl
Copy link
Author

pepl commented Aug 4, 2021

@diosmosis That worked! Removed the buggy data via

MariaDB [matomo]> delete from archive_blob_2021_05 where idsite=3 and name like 'Goals%' and period=1 and date1='2021-05-03';
Query OK, 1161 rows affected (55.383 sec)

MariaDB [matomo]> delete from archive_blob_2021_05 where idsite=3 and name like 'Goals%' and period=1 and date1 between '2021-05-25' and '2021-05-29';
Query OK, 19809 rows affected (1 min 3.387 sec)

MariaDB [matomo]> delete from archive_numeric_2021_05 where idsite=3 and period=1 and date1='2021-05-03' and name like '%Goals';
Query OK, 296 rows affected (1.796 sec)

MariaDB [matomo]> delete from archive_numeric_2021_05 where idsite=3 and period=1 and date1 between '2021-05-25' and '2021-05-29' and name like '%Goals';
Query OK, 5048 rows affected (8.822 sec)

and re-archiving went through fine afterwards. Thanks!

INFO [2021-08-04 20:17:35] 16633  Archived website id 3, period = year, date = 2021-01-01, segment = '', 12014035 visits found. Time elapsed: 2822.580s
DEBUG [2021-08-04 20:17:35] 16633  No next invalidated archive.
DEBUG [2021-08-04 20:17:35] 16633  Archiving for imported site was finished, but date environment variable not set. Cannot mark day as complete.
INFO [2021-08-04 20:17:35] 16633  Finished archiving for site 3, 8 API requests, Time elapsed: 5186.625s [1 / 1 done]
DEBUG [2021-08-04 20:17:35] 16633  No more sites left to archive, stopping.
INFO [2021-08-04 20:17:35] 16633  Done archiving!
INFO [2021-08-04 20:17:35] 16633  ---------------------------
INFO [2021-08-04 20:17:35] 16633  SUMMARY
INFO [2021-08-04 20:17:35] 16633  Processed 8 archives.
INFO [2021-08-04 20:17:35] 16633  Total API requests: 8
INFO [2021-08-04 20:17:35] 16633  done: 8 req, 5186656 ms, no error
INFO [2021-08-04 20:17:35] 16633  Time elapsed: 5186.656s
INFO [2021-08-04 20:17:35] 16633  ---------------------------

@diosmosis
Copy link
Member

Great! I'll close this once the other issue is dealt with.

@diosmosis diosmosis linked a pull request Aug 4, 2021 that will close this issue
limit the size of product reports #17823
Merged
10 tasks
@justinvelluppillai justinvelluppillai changed the title Memory usage issues with archiving high-traffic eCommerce site Added new INI config setting datatable_archiving_maximum_rows_products to avoid memory usage issues with archiving high traffic sites Oct 6, 2021
@justinvelluppillai justinvelluppillai changed the title Added new INI config setting datatable_archiving_maximum_rows_products to avoid memory usage issues with archiving high traffic sites Limiting ecommerce product reports to 10,000 to avoid memory issues with new INI config setting to customize Oct 6, 2021
@justinvelluppillai justinvelluppillai changed the title Limiting ecommerce product reports to 10,000 to avoid memory issues with new INI config setting to customize Limiting ecommerce product reports to 10,000 to avoid memory issues with new INI config setting to customise Oct 6, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

limit the size of product reports matomo-org/matomo
2 participants
@pepl @diosmosis