[ 
https://issues.apache.org/jira/browse/AIRFLOW-2059?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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@<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)

Reply via email to