We already have hourly archiving set-up and split-up the largest site to a separate archiving host. This host has now 64GB of RAM provisioned with archiving still failing.
It really looks like the approach of the yearly aggregation fetching and uncompressing the blob data in memory in a single process without freeing up (enough) memory inbetween cannot scale.
We have put traffic on this Matomo instance with late May and the yearly archiving does not finish since then for this site. Here some debug output with specific memory logging enabled.
Those Goals seem to be eCommerce related. Do we have an option but to disable eCommerce functionality for this host?
# /usr/bin/php7.3 /var/www/matomo/console --verbose --verbose --verbose climulti:request --matomo-domain='localhost' --superuser 'module=API&method=CoreAdminHome.archiveReports&idSite=3&period=year&date=2021-01-01&format=json&trigger=archivephp&pid=bdccec1d2674c7e2b1f782a01004ff2fc6eb98458b5cc1945fb6a41a310dbef8dcd43c1c128bd644b35dd0f116168e9d90620&runid=1409'
DEBUG [2021-07-05 14:41:29] 3666 Db::fetchAll() executing SQL: SELECT option_value, option_name FROM `option` WHERE autoload = 1
DEBUG [2021-07-05 14:41:29] 3666 Db::fetchOne() executing SQL: SELECT option_value FROM `option` WHERE option_name = ?
Memory usage: 22.9 M|Processing period 2021-05-24,2021-05-24, table archive_numeric_2021_05 ids 30012
Memory usage: 22.9 M|Processing period 2021-05-25,2021-05-25, table archive_numeric_2021_05 ids 150142
Memory usage: 23 M|Processing period 2021-05-26,2021-05-26, table archive_numeric_2021_05 ids 150147
Memory usage: 23 M|Processing period 2021-05-27,2021-05-27, table archive_numeric_2021_05 ids 150152
Memory usage: 23 M|Processing period 2021-05-28,2021-05-28, table archive_numeric_2021_05 ids 150157
Memory usage: 23.1 M|Processing period 2021-05-29,2021-05-29, table archive_numeric_2021_05 ids 150162
Memory usage: 23.1 M|Processing period 2021-05-30,2021-05-30, table archive_numeric_2021_05 ids 151081
recordname: Actions_actions, colaggop: Array
Memory usage: 24.7 M|Processing period 2021-05-24,2021-05-24, table archive_blob_2021_05 ids 30012
Memory usage: 24.9 M|Processing period 2021-05-25,2021-05-25, table archive_blob_2021_05 ids 150142
Memory usage: 25.7 M|Processing period 2021-05-26,2021-05-26, table archive_blob_2021_05 ids 150147
Memory usage: 26.6 M|Processing period 2021-05-27,2021-05-27, table archive_blob_2021_05 ids 150152
Memory usage: 27.5 M|Processing period 2021-05-28,2021-05-28, table archive_blob_2021_05 ids 150157
Memory usage: 28.4 M|Processing period 2021-05-29,2021-05-29, table archive_blob_2021_05 ids 150162
Memory usage: 29.2 M|Processing period 2021-05-30,2021-05-30, table archive_blob_2021_05 ids 151081
recordname: Actions_actions_url, colaggop: Array
Memory usage: 26.4 M|Processing period 2021-05-24,2021-05-24, table archive_blob_2021_05 ids 30012
Memory usage: 26.8 M|Processing period 2021-05-25,2021-05-25, table archive_blob_2021_05 ids 150142
Memory usage: 29.9 M|Processing period 2021-05-26,2021-05-26, table archive_blob_2021_05 ids 150147
Memory usage: 32.6 M|Processing period 2021-05-27,2021-05-27, table archive_blob_2021_05 ids 150152
Memory usage: 35.4 M|Processing period 2021-05-28,2021-05-28, table archive_blob_2021_05 ids 150157
Memory usage: 37.8 M|Processing period 2021-05-29,2021-05-29, table archive_blob_2021_05 ids 150162
Memory usage: 40.3 M|Processing period 2021-05-30,2021-05-30, table archive_blob_2021_05 ids 151081
recordname: Actions_downloads, colaggop:
Memory usage: 28.9 M|Processing period 2021-05-24,2021-05-24, table archive_blob_2021_05 ids 30012
Memory usage: 29 M|Processing period 2021-05-25,2021-05-25, table archive_blob_2021_05 ids 150142
Memory usage: 29.2 M|Processing period 2021-05-26,2021-05-26, table archive_blob_2021_05 ids 150147
Memory usage: 29.4 M|Processing period 2021-05-27,2021-05-27, table archive_blob_2021_05 ids 150152
Memory usage: 29.5 M|Processing period 2021-05-28,2021-05-28, table archive_blob_2021_05 ids 150157
Memory usage: 29.7 M|Processing period 2021-05-29,2021-05-29, table archive_blob_2021_05 ids 150162
Memory usage: 29.9 M|Processing period 2021-05-30,2021-05-30, table archive_blob_2021_05 ids 151081
recordname: Actions_outlink, colaggop:
Memory usage: 29.1 M|Processing period 2021-05-24,2021-05-24, table archive_blob_2021_05 ids 30012
Memory usage: 29.1 M|Processing period 2021-05-25,2021-05-25, table archive_blob_2021_05 ids 150142
Memory usage: 33.1 M|Processing period 2021-05-26,2021-05-26, table archive_blob_2021_05 ids 150147
Memory usage: 37.5 M|Processing period 2021-05-27,2021-05-27, table archive_blob_2021_05 ids 150152
recordname: Goals_ItemsName_Cart, colaggop:
Memory usage: 20.6 G|Processing period 2021-05-24,2021-05-24, table archive_blob_2021_05 ids 30017,30017,30017,30017,30017
# lines cut due to length
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 65123217408) (tried to allocate 9117744 bytes) on \/var\/www\/matomo\/core\/DataAccess\/ArchiveSelector.php(390)
…