diff options
author | diosmosis <diosmosis@users.noreply.github.com> | 2020-12-08 08:28:38 +0300 |
---|---|---|
committer | GitHub <noreply@github.com> | 2020-12-08 08:28:38 +0300 |
commit | 5377425108efe04af89ab34c2da28d264541f082 (patch) | |
tree | 903d977c76ebc7d166557b60907e404a4dcdaad5 /core/DataAccess/Model.php | |
parent | f0d6d1ad6213791c3f9bfa0caae32eb8368bcc75 (diff) |
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
Diffstat (limited to 'core/DataAccess/Model.php')
-rw-r--r-- | core/DataAccess/Model.php | 52 |
1 files changed, 44 insertions, 8 deletions
diff --git a/core/DataAccess/Model.php b/core/DataAccess/Model.php index 8f75740dd5..156cd14e8b 100644 --- a/core/DataAccess/Model.php +++ b/core/DataAccess/Model.php @@ -131,8 +131,9 @@ class Model FROM `$archiveTable` WHERE idsite IN (" . implode(',', $idSites) . ")"; + $periodCondition = ''; if (!empty($allPeriodsToInvalidate)) { - $sql .= " AND ("; + $periodCondition .= " AND ("; $isFirst = true; /** @var Period $period */ @@ -140,21 +141,22 @@ class Model if ($isFirst) { $isFirst = false; } else { - $sql .= " OR "; + $periodCondition .= " OR "; } if ($period->getLabel() == 'range') { // for ranges, we delete all ranges that contain the given date(s) - $sql .= "(period = " . (int)$period->getId() + $periodCondition .= "(period = " . (int)$period->getId() . " AND date2 >= '" . $period->getDateStart()->getDatetime() . "' AND date1 <= '" . $period->getDateEnd()->getDatetime() . "')"; } else { - $sql .= "(period = " . (int)$period->getId() + $periodCondition .= "(period = " . (int)$period->getId() . " AND date1 = '" . $period->getDateStart()->getDatetime() . "'" . " AND date2 = '" . $period->getDateEnd()->getDatetime() . "')"; } } - $sql .= ")"; + $periodCondition .= ")"; } + $sql .= $periodCondition; if (!empty($name)) { if (strpos($name, '.') !== false) { @@ -201,6 +203,8 @@ class Model $now = Date::now()->getDatetime(); + $existingInvalidations = $this->getExistingInvalidations($idSites, $periodCondition, $nameCondition); + $dummyArchives = []; foreach ($idSites as $idSite) { try { @@ -223,6 +227,12 @@ class Model $date1 = $period->getDateStart()->toString(); $date2 = $period->getDateEnd()->toString(); + + $key = $this->makeExistingInvalidationArrayKey($idSite, $date1, $date2, $period->getId(), $doneFlag); + if (!empty($existingInvalidations[$key])) { + continue; // avoid adding duplicates where possible + } + $idArchive = $archivesToCreateInvalidationRowsFor[$idSite][$period->getId()][$date1][$date2] ?? null; $dummyArchives[] = [ @@ -238,13 +248,39 @@ class Model } } - $fields = ['idarchive', 'name', 'report', 'idsite', 'date1', 'date2', 'period', 'ts_invalidated']; - - Db\BatchInsert::tableInsertBatch(Common::prefixTable('archive_invalidations'), $fields, $dummyArchives); + if (!empty($dummyArchives)) { + $fields = ['idarchive', 'name', 'report', 'idsite', 'date1', 'date2', 'period', 'ts_invalidated']; + Db\BatchInsert::tableInsertBatch(Common::prefixTable('archive_invalidations'), $fields, $dummyArchives); + } return count($idArchives); } + private function getExistingInvalidations($idSites, $periodCondition, $nameCondition) + { + $table = Common::prefixTable('archive_invalidations'); + + $idSites = array_map('intval', $idSites); + + $sql = "SELECT idsite, date1, date2, period, name, COUNT(*) as `count` FROM `$table` + WHERE idsite IN (" . implode(',', $idSites) . ") AND status = " . ArchiveInvalidator::INVALIDATION_STATUS_QUEUED . " + $periodCondition AND $nameCondition + GROUP BY idsite, date1, date2, period, name"; + $rows = Db::fetchAll($sql); + + $invalidations = []; + foreach ($rows as $row) { + $key = $this->makeExistingInvalidationArrayKey($row['idsite'], $row['date1'], $row['date2'], $row['period'], $row['name']); + $invalidations[$key] = $row['count']; + } + return $invalidations; + } + + private function makeExistingInvalidationArrayKey($idSite, $date1, $date2, $period, $name) + { + return implode('.', [$idSite, $date1, $date2, $period, $name]); + } + /** * @param string $archiveTable Prefixed table name * @param int[] $idSites |