[
https://issues.apache.org/jira/browse/AIRFLOW-2059?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Tao Feng reassigned AIRFLOW-2059:
---------------------------------
Assignee: Tao Feng
> 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@<redacted> ~]$ nproc
> 4
> [nhanlon@<redacted> ~]$ free -g
> total used free shared buffers cached
> Mem: 7 5 1 0 0 1
> -/+ buffers/cache: 4 3
> Swap: 0 0 0
> [nhanlon@<redacted> ~]$ 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@<redacted> ~]$ 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}
> <redacted>: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)
> <redacted>:airflow> select count(*) from task_instance;
> +----------+
> | count(*) |
> +----------+
> | 2984749 |
> +----------+
> 1 row in set (1.67 sec)
> <redacted>:airflow> show profile for query 2;
> +----------------------+------------+
> | Status | Duration |
> +----------------------+------------+
> | starting | 0.000157 |
> | checking permissions | 0.000017 |
> | Opening tables | 0.000033 |
> | init | 0.000046 |
> | System lock | 0.000017 |
> | optimizing | 0.000010 |
> | statistics | 0.000022 |
> | preparing | 0.000020 |
> | Sorting result | 0.000010 |
> | executing | 0.000008 |
> | Sending data | 0.000151 |
> | Creating sort index | 248.955841 |
> | end | 0.015358 |
> | query end | 0.000012 |
> | closing tables | 0.000019 |
> | freeing items | 0.000549 |
> | logging slow query | 0.000007 |
> | logging slow query | 0.000901 |
> | cleaning up | 0.000030 |
> +----------------------+------------+
> 19 rows in set, 1 warning (0.00 sec)
> {code}
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
