Hi,
We have faced with an issue when a slow query executed against `job` table:
There are 1350000 records in the table and the query takes about 10-14 seconds.
After a quick research we found a drawback and found a way to optimise the code - see the screenshot attached.
The proposed patch for version 6.1.7:
A patch for `master` branch:
Hope to see this patch in the upcoming release (6.1.8?).
Thank you!
We have faced with an issue when a slow query executed against `job` table:
Code:
# Query_time: 12.193867 Lock_time: 0.000081 Rows_sent: 0 Rows_examined: 144349 SELECT job.id AS `id` FROM `job` WHERE job.scheduled_job_id = 'xxxxxyyyyxxxxiiiiixxxx' AND job.execute_time LIKE '2021-05-21 02:14:%' AND job.deleted = 0 LIMIT 0, 1;
There are 1350000 records in the table and the query takes about 10-14 seconds.
After a quick research we found a drawback and found a way to optimise the code - see the screenshot attached.
The proposed patch for version 6.1.7:
Code:
$ git diff diff --git a/application/Espo/Core/Utils/Cron/Job.php b/application/Espo/Core/Utils/Cron/Job.php index ee5b550d2..4900e3af5 100644 --- a/application/Espo/Core/Utils/Cron/Job.php +++ b/application/Espo/Core/Utils/Cron/Job.php @@ -165,13 +165,17 @@ class Job public function hasScheduledJobOnMinute(string $scheduledJobId, string $time) : bool {[INDENT]$dateObj = new DateTime($time);[/INDENT] - $timeWithoutSeconds = $dateObj->format('Y-m-d H:i:'); + $minuteBegin = $dateObj->format('Y-m-d H:i:00'); + $minuteEnd = $dateObj->format('Y-m-d H:i:59'); $job = $this->getEntityManager()->getRepository('Job') ->select(['id']) ->where([ 'scheduledJobId' => $scheduledJobId, - 'executeTime*' => $timeWithoutSeconds . '%', + 'AND' => [ + 'executeTime>=' => $minuteBegin, + 'executeTime<=' => $minuteEnd + ] ]) ->findOne(); diff --git a/application/Espo/Resources/metadata/entityDefs/Job.json b/application/Espo/Resources/metadata/entityDefs/Job.json index 71d45b0bf..125b5ecbd 100644 --- a/application/Espo/Resources/metadata/entityDefs/Job.json +++ b/application/Espo/Resources/metadata/entityDefs/Job.json @@ -107,6 +107,9 @@ "textFilterFields": ["id", "name", "methodName", "serviceName", "scheduledJob"] }, "indexes": { + "executeTimeExclusive": { + "columns": ["executeTime"] + }, "executeTime": { "columns": ["status", "executeTime"] },
A patch for `master` branch:
Code:
$ git diff diff --git a/application/Espo/Core/Job/QueueUtil.php b/application/Espo/Core/Job/QueueUtil.php index 49f97ca98..14b5c744a 100644 --- a/application/Espo/Core/Job/QueueUtil.php +++ b/application/Espo/Core/Job/QueueUtil.php @@ -154,14 +154,18 @@ class QueueUtil { $dateObj = new DateTime($time); - $timeWithoutSeconds = $dateObj->format('Y-m-d H:i:'); + $minuteBegin = $dateObj->format('Y-m-d H:i:00'); + $minuteEnd = $dateObj->format('Y-m-d H:i:59'); $job = $this->entityManager ->getRDBRepository('Job') ->select(['id']) ->where([ 'scheduledJobId' => $scheduledJobId, - 'executeTime*' => $timeWithoutSeconds . '%', + 'AND' => [ + 'executeTime>=' => $minuteBegin, + 'executeTime<=' => $minuteEnd + ] ]) ->findOne(); diff --git a/application/Espo/Resources/metadata/entityDefs/Job.json b/application/Espo/Resources/metadata/entityDefs/Job.json index 71d45b0bf..125b5ecbd 100644 --- a/application/Espo/Resources/metadata/entityDefs/Job.json +++ b/application/Espo/Resources/metadata/entityDefs/Job.json @@ -107,6 +107,9 @@ "textFilterFields": ["id", "name", "methodName", "serviceName", "scheduledJob"] }, "indexes": { + "executeTimeExclusive": { + "columns": ["executeTime"] + }, "executeTime": { "columns": ["status", "executeTime"] },
Hope to see this patch in the upcoming release (6.1.8?).
Thank you!
Comment