[jira] [Commented] (AIRFLOW-2059) taskinstance query is awful, un-indexed, and does not scale
[
https://issues.apache.org/jira/browse/AIRFLOW-2059?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16383340#comment-16383340
]
ASF subversion and git services commented on AIRFLOW-2059:
--
Commit d4dfe2654e16d1ae2e7464e642a3520de04496e2 in incubator-airflow's branch
refs/heads/master from Tao feng
[ https://git-wip-us.apache.org/repos/asf?p=incubator-airflow.git;h=d4dfe26 ]
[AIRFLOW-2059] taskinstance query is awful, un-indexed, and does not scale
Closes #3086 from feng-tao/airflow-2059
> taskinstance query is awful, un-indexed, and does not scale
> ---
>
> Key: AIRFLOW-2059
> URL: https://issues.apache.org/jira/browse/AIRFLOW-2059
> Project: Apache Airflow
> Issue Type: Bug
> Components: db, webserver
>Affects Versions: Airflow 1.8
> Environment: [nhanlon@ ~]$ nproc
> 4
> [nhanlon@ ~]$ free -g
> total used free sharedbuffers cached
> Mem: 7 5 1 0 0 1
> -/+ buffers/cache: 4 3
> Swap:0 0 0
> [nhanlon@ ~]$ cat /etc/*release*
> CentOS release 6.7 (Final)
> CentOS release 6.7 (Final)
> CentOS release 6.7 (Final)
> cpe:/o:centos:linux:6:GA
> [nhanlon@ ~]$ mysqld --version
> mysqld Ver 5.6.31-77.0 for Linux on x86_64 (Percona Server (GPL), Release
> 77.0, Revision 5c1061c)
>Reporter: Neil Hanlon
>Assignee: Tao Feng
>Priority: Critical
>
>
> The page at /admin/taskinstance/ can reach a point where it blocks loading
> the page and crushes the database. It appears this is because the
> task_instance.job_id column is unindexed. On our database, getting the
> results for this query took over four minutes, locking the table for the
> duration.
>
> 500 rows in set (4 min 8.93 sec)
>
> Query:
>
> {code:java}
> SELECT task_instance.task_id AS task_instance_task_id, task_instance.dag_id
> AS task_instance_dag_id, task_instance.execution_date AS
> task_instance_execution_date, task_instance.start_date AS
> task_instance_start_date, task_instance.end_date AS task_instance_end_date,
> task_instance.duration AS task_instance_duration, task_instance.state AS
> task_instance_state, task_instance.try_number AS task_instance_try_number,
> task_instance.hostname AS task_instance_hostname, task_instance.unixname AS
> task_instance_unixname, task_instance.job_id AS task_instance_job_id,
> task_instance.pool AS task_instance_pool, task_instance.queue AS
> task_instance_queue, task_instance.priority_weight AS
> task_instance_priority_weight, task_instance.operator AS
> task_instance_operator, task_instance.queued_dttm AS
> task_instance_queued_dttm, task_instance.pid AS task_instance_pid
> FROM task_instance ORDER BY task_instance.job_id DESC
> LIMIT 500;
> {code}
> Profile, explain:
>
> {code:java}
> :airflow> EXPLAIN SELECT task_instance.task_id AS
> task_instance_task_id, task_instance.dag_id AS task_instance_dag_id,
> task_instance.execution_date AS task_instance_execution_date,
> task_instance.start_date AS task_instance_start_date, task_instance.end_date
> AS task_instance_end_date, task_instance.duration AS task_instance_duration,
> task_instance.state AS task_instance_state, task_instance.try_number AS
> task_instance_try_number, task_instance.hostname AS task_instance_hostname,
> task_instance.unixname AS task_instance_unixname, task_instance.job_id AS
> task_instance_job_id, task_instance.pool AS task_instance_pool,
> task_instance.queue AS task_instance_queue, task_instance.priority_weight AS
> task_instance_priority_weight, task_instance.operator AS
> task_instance_operator, task_instance.queued_dttm AS
> task_instance_queued_dttm, task_instance.pid AS task_instance_pid
> -> FROM task_instance ORDER BY task_instance.job_id DESC
> -> LIMIT 500;
> ++-+---+--+---+--+-+--+-++
> | id | select_type | table | type | possible_keys | key | key_len | ref |
> rows | Extra |
> ++-+---+--+---+--+-+--+-++
> | 1 | SIMPLE | task_instance | ALL | NULL | NULL | NULL | NULL | 2542776 |
> Using filesort |
> ++-+---+--+---+--+-+--+-++
> 1 row in set (0.00 sec)
> :airflow> select count(*) from task_instance;
> +--+
> | count(*) |
> +--+
> | 2984749 |
> +--+
> 1 row in set (1.67 sec)
> :airflow> show profile for query 2;
> +--++
> | Status | Duration |
> +--++
> | starting | 0.000157 |
> | checking permissions | 0.17 |
> | Opening tables | 0.33 |
> | init |
[jira] [Commented] (AIRFLOW-2059) taskinstance query is awful, un-indexed, and does not scale
[
https://issues.apache.org/jira/browse/AIRFLOW-2059?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16383269#comment-16383269
]
Tao Feng commented on AIRFLOW-2059:
---
pr created: [https://github.com/apache/incubator-airflow/pull/3086] . I don't
see any issues by indexing the job_id column as its type is integer type. Let
me know if I miss anything.
> taskinstance query is awful, un-indexed, and does not scale
> ---
>
> Key: AIRFLOW-2059
> URL: https://issues.apache.org/jira/browse/AIRFLOW-2059
> Project: Apache Airflow
> Issue Type: Bug
> Components: db, webserver
>Affects Versions: Airflow 1.8
> Environment: [nhanlon@ ~]$ nproc
> 4
> [nhanlon@ ~]$ free -g
> total used free sharedbuffers cached
> Mem: 7 5 1 0 0 1
> -/+ buffers/cache: 4 3
> Swap:0 0 0
> [nhanlon@ ~]$ cat /etc/*release*
> CentOS release 6.7 (Final)
> CentOS release 6.7 (Final)
> CentOS release 6.7 (Final)
> cpe:/o:centos:linux:6:GA
> [nhanlon@ ~]$ mysqld --version
> mysqld Ver 5.6.31-77.0 for Linux on x86_64 (Percona Server (GPL), Release
> 77.0, Revision 5c1061c)
>Reporter: Neil Hanlon
>Assignee: Tao Feng
>Priority: Critical
>
>
> The page at /admin/taskinstance/ can reach a point where it blocks loading
> the page and crushes the database. It appears this is because the
> task_instance.job_id column is unindexed. On our database, getting the
> results for this query took over four minutes, locking the table for the
> duration.
>
> 500 rows in set (4 min 8.93 sec)
>
> Query:
>
> {code:java}
> SELECT task_instance.task_id AS task_instance_task_id, task_instance.dag_id
> AS task_instance_dag_id, task_instance.execution_date AS
> task_instance_execution_date, task_instance.start_date AS
> task_instance_start_date, task_instance.end_date AS task_instance_end_date,
> task_instance.duration AS task_instance_duration, task_instance.state AS
> task_instance_state, task_instance.try_number AS task_instance_try_number,
> task_instance.hostname AS task_instance_hostname, task_instance.unixname AS
> task_instance_unixname, task_instance.job_id AS task_instance_job_id,
> task_instance.pool AS task_instance_pool, task_instance.queue AS
> task_instance_queue, task_instance.priority_weight AS
> task_instance_priority_weight, task_instance.operator AS
> task_instance_operator, task_instance.queued_dttm AS
> task_instance_queued_dttm, task_instance.pid AS task_instance_pid
> FROM task_instance ORDER BY task_instance.job_id DESC
> LIMIT 500;
> {code}
> Profile, explain:
>
> {code:java}
> :airflow> EXPLAIN SELECT task_instance.task_id AS
> task_instance_task_id, task_instance.dag_id AS task_instance_dag_id,
> task_instance.execution_date AS task_instance_execution_date,
> task_instance.start_date AS task_instance_start_date, task_instance.end_date
> AS task_instance_end_date, task_instance.duration AS task_instance_duration,
> task_instance.state AS task_instance_state, task_instance.try_number AS
> task_instance_try_number, task_instance.hostname AS task_instance_hostname,
> task_instance.unixname AS task_instance_unixname, task_instance.job_id AS
> task_instance_job_id, task_instance.pool AS task_instance_pool,
> task_instance.queue AS task_instance_queue, task_instance.priority_weight AS
> task_instance_priority_weight, task_instance.operator AS
> task_instance_operator, task_instance.queued_dttm AS
> task_instance_queued_dttm, task_instance.pid AS task_instance_pid
> -> FROM task_instance ORDER BY task_instance.job_id DESC
> -> LIMIT 500;
> ++-+---+--+---+--+-+--+-++
> | id | select_type | table | type | possible_keys | key | key_len | ref |
> rows | Extra |
> ++-+---+--+---+--+-+--+-++
> | 1 | SIMPLE | task_instance | ALL | NULL | NULL | NULL | NULL | 2542776 |
> Using filesort |
> ++-+---+--+---+--+-+--+-++
> 1 row in set (0.00 sec)
> :airflow> select count(*) from task_instance;
> +--+
> | count(*) |
> +--+
> | 2984749 |
> +--+
> 1 row in set (1.67 sec)
> :airflow> show profile for query 2;
> +--++
> | Status | Duration |
> +--++
> | starting | 0.000157 |
> | checking permissions | 0.17 |
> | Opening tables | 0.33 |
> | init | 0.46 |
> | System lock | 0.17 |
> | optimizing | 0.10 |
> | statistics | 0.22 |
> | preparing | 0.20 |
> | Sorting result | 0.10 |
> | executing |
