Avoid inserting duplicates in invalidation table to reduce IO. (#16843)4.0.5-b1

* Avoid inserting duplicates in invalidation table to reduce IO.

* Limit to given sites to not select too much.

* fix build

* tweaks to duplicate checking query

* more pr feedback

* fix cron archive test

2 files changed, 234 insertions, 24 deletions

diff --git a/tests/PHPUnit/Integration/CronArchiveTest.php b/tests/PHPUnit/Integration/CronArchiveTest.php
index d7eece38dd..00a8b4867e 100644
--- a/tests/PHPUnit/Integration/CronArchiveTest.php
+++ b/tests/PHPUnit/Integration/CronArchiveTest.php
@@ -697,48 +697,48 @@ Checking for queued invalidations...
   Segment "actions>=2" was created or changed recently and will therefore archive today (for site ID = 1)
   Segment "actions>=4" was created or changed recently and will therefore archive today (for site ID = 1)
 Done invalidating
-Found invalidated archive we can skip (no visits): [idinvalidation = 75, idsite = 1, period = day(2020-02-03 - 2020-02-03), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 76, idsite = 1, period = week(2020-02-03 - 2020-02-09), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 74, idsite = 1, period = day(2020-02-02 - 2020-02-02), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 72, idsite = 1, period = day(2020-02-01 - 2020-02-01), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 73, idsite = 1, period = month(2020-02-01 - 2020-02-29), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 68, idsite = 1, period = week(2020-01-27 - 2020-02-02), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 65, idsite = 1, period = day(2020-01-01 - 2020-01-01), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 66, idsite = 1, period = month(2020-01-01 - 2020-01-31), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 67, idsite = 1, period = year(2020-01-01 - 2020-12-31), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 60, idsite = 1, period = day(2019-12-31 - 2019-12-31), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 58, idsite = 1, period = day(2019-12-30 - 2019-12-30), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 59, idsite = 1, period = week(2019-12-30 - 2020-01-05), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 56, idsite = 1, period = day(2019-12-23 - 2019-12-23), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 57, idsite = 1, period = week(2019-12-23 - 2019-12-29), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 54, idsite = 1, period = day(2019-12-16 - 2019-12-16), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Found invalidated archive we can skip (no visits): [idinvalidation = 55, idsite = 1, period = week(2019-12-16 - 2019-12-22), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 53, idsite = 1, period = day(2020-02-03 - 2020-02-03), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 54, idsite = 1, period = week(2020-02-03 - 2020-02-09), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 52, idsite = 1, period = day(2020-02-02 - 2020-02-02), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 50, idsite = 1, period = day(2020-02-01 - 2020-02-01), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 51, idsite = 1, period = month(2020-02-01 - 2020-02-29), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 46, idsite = 1, period = week(2020-01-27 - 2020-02-02), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 43, idsite = 1, period = day(2020-01-01 - 2020-01-01), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 44, idsite = 1, period = month(2020-01-01 - 2020-01-31), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 45, idsite = 1, period = year(2020-01-01 - 2020-12-31), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 35, idsite = 1, period = day(2019-12-31 - 2019-12-31), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 33, idsite = 1, period = day(2019-12-30 - 2019-12-30), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 34, idsite = 1, period = week(2019-12-30 - 2020-01-05), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 31, idsite = 1, period = day(2019-12-23 - 2019-12-23), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 32, idsite = 1, period = week(2019-12-23 - 2019-12-29), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 29, idsite = 1, period = day(2019-12-16 - 2019-12-16), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Found invalidated archive we can skip (no visits): [idinvalidation = 30, idsite = 1, period = week(2019-12-16 - 2019-12-22), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
 Processing invalidation: [idinvalidation = 5, idsite = 1, period = day(2019-12-12 - 2019-12-12), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
-Processing invalidation: [idinvalidation = 17, idsite = 1, period = day(2019-12-11 - 2019-12-11), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
-Processing invalidation: [idinvalidation = 29, idsite = 1, period = day(2019-12-10 - 2019-12-10), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
+Processing invalidation: [idinvalidation = 14, idsite = 1, period = day(2019-12-11 - 2019-12-11), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
+Processing invalidation: [idinvalidation = 17, idsite = 1, period = day(2019-12-10 - 2019-12-10), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
 Starting archiving for ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=day&date=2019-12-12&format=json&segment=actions%3E%3D2&trigger=archivephp
 Starting archiving for ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=day&date=2019-12-11&format=json&segment=actions%3E%3D2&trigger=archivephp
 Starting archiving for ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=day&date=2019-12-10&format=json&segment=actions%3E%3D2&trigger=archivephp
 Archived website id 1, period = day, date = 2019-12-12, segment = 'actions%3E%3D2', 0 visits found. Time elapsed: %fs
 Archived website id 1, period = day, date = 2019-12-11, segment = 'actions%3E%3D2', 0 visits found. Time elapsed: %fs
 Archived website id 1, period = day, date = 2019-12-10, segment = 'actions%3E%3D2', 0 visits found. Time elapsed: %fs
-Found invalidated archive we can skip (no visits): [idinvalidation = 52, idsite = 1, period = day(2019-12-09 - 2019-12-09), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
-Processing invalidation: [idinvalidation = 53, idsite = 1, period = week(2019-12-09 - 2019-12-15), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
-Processing invalidation: [idinvalidation = 49, idsite = 1, period = day(2019-12-02 - 2019-12-02), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
+Found invalidated archive we can skip (no visits): [idinvalidation = 28, idsite = 1, period = day(2019-12-09 - 2019-12-09), name = donee0512c03f7c20af6ef96a8d792c6bb9f]
+Processing invalidation: [idinvalidation = 6, idsite = 1, period = week(2019-12-09 - 2019-12-15), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
+Processing invalidation: [idinvalidation = 22, idsite = 1, period = day(2019-12-02 - 2019-12-02), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
 No next invalidated archive.
 Starting archiving for ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=week&date=2019-12-09&format=json&segment=actions%3E%3D2&trigger=archivephp
 Starting archiving for ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=day&date=2019-12-02&format=json&segment=actions%3E%3D2&trigger=archivephp
 Archived website id 1, period = week, date = 2019-12-09, segment = 'actions%3E%3D2', 0 visits found. Time elapsed: %fs
 Archived website id 1, period = day, date = 2019-12-02, segment = 'actions%3E%3D2', 0 visits found. Time elapsed: %fs
-Processing invalidation: [idinvalidation = 50, idsite = 1, period = week(2019-12-02 - 2019-12-08), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
+Processing invalidation: [idinvalidation = 23, idsite = 1, period = week(2019-12-02 - 2019-12-08), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
 No next invalidated archive.
 Starting archiving for ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=week&date=2019-12-02&format=json&segment=actions%3E%3D2&trigger=archivephp
 Archived website id 1, period = week, date = 2019-12-02, segment = 'actions%3E%3D2', 0 visits found. Time elapsed: %fs
-Processing invalidation: [idinvalidation = 51, idsite = 1, period = month(2019-12-01 - 2019-12-31), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
+Processing invalidation: [idinvalidation = 7, idsite = 1, period = month(2019-12-01 - 2019-12-31), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
 No next invalidated archive.
 Starting archiving for ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=month&date=2019-12-01&format=json&segment=actions%3E%3D2&trigger=archivephp
 Archived website id 1, period = month, date = 2019-12-01, segment = 'actions%3E%3D2', 0 visits found. Time elapsed: %fs
-Processing invalidation: [idinvalidation = 64, idsite = 1, period = year(2019-01-01 - 2019-12-31), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
+Processing invalidation: [idinvalidation = 8, idsite = 1, period = year(2019-01-01 - 2019-12-31), name = donee0512c03f7c20af6ef96a8d792c6bb9f].
 No next invalidated archive.
 Starting archiving for ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=year&date=2019-01-01&format=json&segment=actions%3E%3D2&trigger=archivephp
 Archived website id 1, period = year, date = 2019-01-01, segment = 'actions%3E%3D2', 0 visits found. Time elapsed: %fs
diff --git a/tests/PHPUnit/Integration/DataAccess/ArchiveInvalidatorTest.php b/tests/PHPUnit/Integration/DataAccess/ArchiveInvalidatorTest.php
index 9c9a4cb871..784146ce4f 100644
--- a/tests/PHPUnit/Integration/DataAccess/ArchiveInvalidatorTest.php
+++ b/tests/PHPUnit/Integration/DataAccess/ArchiveInvalidatorTest.php
@@ -1200,6 +1200,216 @@ class ArchiveInvalidatorTest extends IntegrationTestCase
             $expectedInvalidatedArchives, $report);
     }
 
+    public function test_markArchivesAsInvalidated_doesNotInsertDuplicateInvalidations()
+    {
+        $this->insertArchiveRowsForTest();
+
+        $segment = 'browserCode==IE';
+        $segment = new Segment($segment, [1]);
+
+        $segmentHash = $segment->getHash();
+
+        /** @var ArchiveInvalidator $archiveInvalidator */
+        $archiveInvalidator = self::$fixture->piwikEnvironment->getContainer()->get('Piwik\Archive\ArchiveInvalidator');
+
+        $existingInvalidations = [
+            ['name' => 'done' . $segmentHash, 'idsite' => 1, 'date1' => '2020-03-02', 'date2' => '2020-03-08', 'period' => 2, 'report' => null],
+
+            ['name' => 'done' . $segmentHash, 'idsite' => 1, 'date1' => '2020-05-04', 'date2' => '2020-05-04', 'period' => 1, 'report' => null],
+            ['name' => 'done' . $segmentHash, 'idsite' => 1, 'date1' => '2020-05-05', 'date2' => '2020-05-05', 'period' => 1, 'report' => null],
+            ['name' => 'done' . $segmentHash, 'idsite' => 1, 'date1' => '2020-05-06', 'date2' => '2020-05-06', 'period' => 1, 'report' => null],
+            ['name' => 'done' . $segmentHash, 'idsite' => 1, 'date1' => '2020-05-07', 'date2' => '2020-05-07', 'period' => 1, 'report' => null],
+            ['name' => 'done' . $segmentHash, 'idsite' => 1, 'date1' => '2020-05-08', 'date2' => '2020-05-08', 'period' => 1, 'report' => null],
+            ['name' => 'done' . $segmentHash, 'idsite' => 1, 'date1' => '2020-05-09', 'date2' => '2020-05-09', 'period' => 1, 'report' => null],
+            ['name' => 'done' . $segmentHash, 'idsite' => 1, 'date1' => '2020-05-10', 'date2' => '2020-05-10', 'period' => 1, 'report' => null],
+
+            ['name' => 'done' . $segmentHash, 'idsite' => 1, 'date1' => '2020-05-01', 'date2' => '2020-05-31', 'period' => 3, 'report' => null],
+        ];
+
+        $this->insertInvalidations($existingInvalidations);
+
+        $archiveInvalidator->markArchivesAsInvalidated([1], ['2020-03-04', '2020-05-06'], 'week',
+            $segment, $cascadeDown = true, false);
+
+        $expectedInvalidations = [
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-03-02',
+                'date2' => '2020-03-08',
+                'period' => '2',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-05-04',
+                'date2' => '2020-05-04',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-05-05',
+                'date2' => '2020-05-05',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-05-06',
+                'date2' => '2020-05-06',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-05-07',
+                'date2' => '2020-05-07',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-05-08',
+                'date2' => '2020-05-08',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-05-09',
+                'date2' => '2020-05-09',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-05-10',
+                'date2' => '2020-05-10',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-05-01',
+                'date2' => '2020-05-31',
+                'period' => '3',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-03-02',
+                'date2' => '2020-03-02',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-03-01',
+                'date2' => '2020-03-31',
+                'period' => '3',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-03-03',
+                'date2' => '2020-03-03',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-03-04',
+                'date2' => '2020-03-04',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-03-05',
+                'date2' => '2020-03-05',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-03-06',
+                'date2' => '2020-03-06',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-03-07',
+                'date2' => '2020-03-07',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-03-08',
+                'date2' => '2020-03-08',
+                'period' => '1',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-01-01',
+                'date2' => '2020-12-31',
+                'period' => '4',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+            array (
+                'idarchive' => NULL,
+                'idsite' => '1',
+                'date1' => '2020-05-04',
+                'date2' => '2020-05-10',
+                'period' => '2',
+                'name' => 'done5f4f9bafeda3443c3c2d4b2ef4dffadc',
+                'report' => NULL,
+            ),
+        ];
+
+        $actualInvalidations = $this->getInvalidatedArchiveTableEntries();
+
+        $this->assertEquals($expectedInvalidations, $actualInvalidations);
+    }
+
     public function test_reArchiveReport_createsCorrectInvalidationEntries_forAllSitesIfAllSpecified()
     {
         Date::$now = strtotime('2020-06-16 12:00:00');