Airflow database CPU usage

[zstoth]

After adding the plugin to our installation the Airflow postgres DB reported ~100% CPU usage until the plugin was removed. This made it impossible for the scheduler to schedule new tasks, Airflow basically stopped functioning.
Our airflow contains around 100 dags, some with a few thousand dag runs - I think reloading this was too much for the DB. Do you know any workaround for this issue?

[seelmann]

We experienced the same problem today.

The problem is that it is also activated on the workers whenever a task instance is executed. So for each started task at plugin registration the collect() function is called.

In pg_stat_activity we saw especially the query from get_task_state_info() is heavy and takes multiple seconds to execute when the database is at 100% CPU. Postgres does a full table scan for that groupby+count subquery. We have currently around 500k rows in the task_instance table and run a db.m4.large AWS RDS instance.

A simple solution may be to implement the describe() function which is called instead of collect() at registration time (see https://github.com/prometheus/client_python#custom-collectors). Otherwise the plugin should not be enabled on the worker nodes but only on the webserver.

[elephantum]

@zstoth @seelmann can you please check if v0.4.3 fixes this issue?

[zstoth]

It helps, yes. I applied the same solution locally, it prevents the scheduler calling the plugin. The DB operations are still heavy, but setting the Prometheus scrape interval to 5m causes only ~50% CPU usage spikes instead of 100%, so it's possible to live with that.
Maybe a note about that in the README would be helpful. Thanks for the fix!

[elephantum]

Cool!

@zstoth If you think clarifications in README are necessary - feel free to make a PR.

Also, I'll add another bug for heavy CPU usage, maybe some extra indexes here and there will help.