You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "vchiapaikeo (via GitHub)" <gi...@apache.org> on 2024/07/09 17:31:36 UTC
[I] Expensive Trigger Query Does Not Use Index (even after forcing a hint) Due to Additional Column [airflow]
vchiapaikeo opened a new issue, #40674:
URL: https://github.com/apache/airflow/issues/40674
### Apache Airflow version
2.9.2
### If "Other Airflow 2 version" selected, which one?
_No response_
### What happened?
We are planning to upgrade from 2.7.3 to 2.9.2. However, we've observed issues in our sandbox MySQL db while doing so. Specifically, [this query](https://github.com/apache/airflow/blob/main/airflow/models/trigger.py#L306-L314) fails to use the ti_trigger_id index on the task_instance table. This is likely a result of the addition of the `coalesce(TaskInstance.priority_weight, 0).desc()` which forces MySQL to need to perform lookups after the fact.
Slow Query Log (showing that this query takes over 11s to run):
```
# Query_time: 11.081314 Lock_time: 0.000299 Rows_sent: 0 Rows_examined: 2819837
SET timestamp=1720544726;
SELECT `trigger`.id
FROM `trigger` INNER JOIN task_instance ON `trigger`.id = task_instance.trigger_id
WHERE `trigger`.triggerer_id IS NULL OR (`trigger`.triggerer_id NOT IN (SELECT job.id
FROM job
WHERE job.end_date IS NULL AND job.latest_heartbeat > '2024-07-09 17:04:56.519611' AND job.job_type = 'TriggererJob')) ORDER BY coalesce(task_instance.priority_weight, 0) DESC, `trigger`.created_date
LIMIT 9968 FOR UPDATE SKIP LOCKED;
```
### What you think should happen instead?
The index on trigger_id should be modified to include priority_weight so that the query chooses to use the index. Currently, the index is defined as
```
CREATE INDEX ti_trigger_id
ON task_instance (trigger_id ASC);
```
However, it should be defined as the following so that the index gets used:
```
CREATE INDEX idx_trigger_priority ON task_instance(trigger_id, priority_weight);
```
### How to reproduce
Run Airflow w/ a MySQL backend and turn on slow query logging. Airflow should be bootstrapped with a signficant number of tasks in the task_instance table
### Operating System
Debian 11
### Versions of Apache Airflow Providers
https://raw.githubusercontent.com/apache/airflow/constraints-2.9.2/constraints-3.11.txt
### Deployment
Official Apache Airflow Helm Chart
### Deployment details
KubernetesExecutor on GKE
### Anything else?
_No response_
### Are you willing to submit PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Expensive Trigger Query Does Not Use Index (even after forcing a hint) Due to Additional Column [airflow]
Posted by "mattp- (via GitHub)" <gi...@apache.org>.
mattp- commented on issue #40674:
URL: https://github.com/apache/airflow/issues/40674#issuecomment-2477158994
@vchiapaikeo i see a similar slowdown on postgres, though not to the same extent. the query is taking on average around 300ms in our installation
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Expensive Trigger Query Does Not Use Index (even after forcing a hint) Due to Additional Column [airflow]
Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk commented on issue #40674:
URL: https://github.com/apache/airflow/issues/40674#issuecomment-3262451357
Assuming fixed in Airflow 3. Does not seem worth / critical to backport to 2.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Expensive Trigger Query Does Not Use Index (even after forcing a hint) Due to Additional Column [airflow]
Posted by "vchiapaikeo (via GitHub)" <gi...@apache.org>.
vchiapaikeo commented on issue #40674:
URL: https://github.com/apache/airflow/issues/40674#issuecomment-2220618288
Actually, I'm not convinced this additional index will fix things. I'm going to attempt to revise the query instead as that seems to produce consistently better results in the explain plan.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Expensive Trigger Query Does Not Use Index (even after forcing a hint) Due to Additional Column [airflow]
Posted by "vchiapaikeo (via GitHub)" <gi...@apache.org>.
vchiapaikeo commented on issue #40674:
URL: https://github.com/apache/airflow/issues/40674#issuecomment-2220804204
There's a small chance that we may need to add an index hint to the query as well. Reopening the PR and we can come back to adding that index hint in the query if we need to
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Expensive Trigger Query Does Not Use Index (even after forcing a hint) Due to Additional Column [airflow]
Posted by "notatallshaw-gts (via GitHub)" <gi...@apache.org>.
notatallshaw-gts commented on issue #40674:
URL: https://github.com/apache/airflow/issues/40674#issuecomment-5838500763
FWIW, we have a number of Airflow 2 instances internally and one of them hit this performance problem so badly it caused the scheduler to crash every few seconds. I was able to manually backport the fix for that instance without any issues.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Expensive Trigger Query Does Not Use Index (even after forcing a hint) Due to Additional Column [airflow]
Posted by "mattusifer (via GitHub)" <gi...@apache.org>.
mattusifer commented on issue #40674:
URL: https://github.com/apache/airflow/issues/40674#issuecomment-3253902818
This looks like it was fixed in https://github.com/apache/airflow/pull/46303
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Expensive Trigger Query Does Not Use Index (even after forcing a hint) Due to Additional Column [airflow]
Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk closed issue #40674: Expensive Trigger Query Does Not Use Index (even after forcing a hint) Due to Additional Column
URL: https://github.com/apache/airflow/issues/40674
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org