[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&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@ ~]$ 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
[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&focusedCommentId=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 |