FWIW we use the following DAG at Airbnb to reap the task instances table (this is a stopgap):
# DAG to delete old TIs so that UI operations on the webserver are fast. This DAG is a # stopgap, ideally we would make the UI not query all task instances and add indexes to # the task_instance table where appropriate to speed up the remaining webserver table # queries. # Note that there is a slight risk that some of these deleted task instances may break # the depends_on_past dependency for the following tasks but this should rarely happy # and is easy to diagnose and fix. from datetime import datetime from airflow import DAG from airflow.operators import MySqlOperator args = { 'owner': 'xxx', 'email': ['xxx'], 'start_date': datetime(2017, 1, 30), 'mysql_conn_id': 'airflow_db', } dag = DAG( 'airflow_old_task_instance_pruning', default_args=args, ) # TODO: TIs that have are successful without a start date will never be # reaped because they have been mark-success'd in the UI. One fix for this would be to # make airflow set start_date when mark-success-ing. sql = """\ DELETE ti FROM task_instance ti LEFT OUTER JOIN dag_run dr ON ti.execution_date = dr.execution_date AND ti.dag_id = dr.dag_id WHERE ((ti.start_date <= DATE_SUB(NOW(), INTERVAL 30 DAY) AND ti.state != "running") OR (ISNULL(ti.start_date) AND ti.state = "failed")) AND (ISNULL(dr.id) OR dr.state != "running") """ MySqlOperator( task_id='delete_old_tis', sql=sql, dag=dag, ) On Tue, Mar 7, 2017 at 5:39 PM, Jason Chen <chingchien.c...@gmail.com> wrote: > Hi Bolke, > > Thanks, but it looks you are actually talking about Harish's use case. > > My use case is about 50 Dags (each one with about 2-3 tasks). I feel our > run interval setting for the dags are too low (~15 mins). It may result in > high CPU of MySQL. > > Meanwhile, I dig to MySQL and I noticed a frequently running SQL statement > as below. It's without proper index on column task_instance.state. > > Shouldn't it index "state", given that there could be million of rows in > task_instance? > > SQL Statement: > "SELECT task_instance.task_id AS task_instance_task_id, > task_instance.dag_id AS task_instance_dag_id,.... FROM task_instance WHERE > task_instance.state = 'queued'" > > Also, is there a possibility to clean some "unneeded" entries in the tables > (say, task_instance) ? I mean, for example, removing task states older > than 6 months? > > Feedback are welcome. > > Thanks. > > -Jason > > > > On Tue, Mar 7, 2017 at 11:45 AM, Bolke de Bruin <bdbr...@gmail.com> wrote: > > > Hi Jason > > > > I think you need to back it up with more numbers. You assume that a load > > of 100% is bad and also that 16GB of mem is a lot. > > > > 30x25 = 750 tasks per hour = 12,5 tasks per minute. For every task we > > launch a couple of processes (at least 2) that do not share memory, this > is > > to ensure tasks cannot hurt each other. Curl tasks are probably launched > by > > using a BashOperator, which means another process. Curl is itself another > > process. So 4 processes per task, that cannot share memory. Curl can > cache > > memory itself as well. You probably have peak times and longer running > > tasks so it is not evenly spread, then it starts adding up quickly? > > > > Bolke. > > > > > > > On 7 Mar 2017, at 19:41, Jason Chen <chingchien.c...@gmail.com> wrote: > > > > > > Hi Harish, > > > Thanks for the fast response and feedback. > > > Yeah, I want to see the fix or more discussion ! > > > > > > BTW, I assume that, given your 30 dags, airflow runs fine after your > > > increase of heartbeat ? > > > The default is 5 secs. > > > > > > > > > Thanks. > > > Jason > > > > > > > > > On Tue, Mar 7, 2017 at 10:24 AM, harish singh < > harish.sing...@gmail.com> > > > wrote: > > > > > >> I had seen a similar behavior, a year ago, when we were are < 5 Dags. > > Even > > >> then the cpu utilization was reaching 100%. > > >> One way to deal with this is - You could play with "heatbeat" numbers > > (i.e > > >> increase heartbeat). > > >> But then you are introducing more delay to start jobs that are ready > to > > run > > >> (ready to be queued -> queued -> run) > > >> > > >> Right now, we have more than 30 dags (each with ~ 20-25 tasks) that > runs > > >> every hour. > > >> We are giving airflow about 5-6 cores (which still seems less for > > airflow). > > >> Also, for so many tasks every hour, our mem consumption is over 16G. > > >> All our tasks are basically doing "curl". So 16G seems too high. > > >> > > >> Having said that, I remember reading somewhere that there was a fix > > coming > > >> for this. > > >> If not, I would definitely want to see more discussion on this. > > >> > > >> Thanks for opening this. I would love to hear on how people are > working > > >> around this. > > >> > > >> > > >> > > >> > > >> > > >> On Tue, Mar 7, 2017 at 9:42 AM, Jason Chen <chingchien.c...@gmail.com > > > > >> wrote: > > >> > > >>> Hi team, > > >>> > > >>> We are using airflow v1.7.1.3 and schedule about 50 dags (each dags > is > > >>> about 10 to one hour intervals). It's with LocalExecutor. > > >>> > > >>> Recently, we noticed the RDS (MySQL 5.6.x with AWS) runs with ~100% > > CPU. > > >>> I am wondering if airflow scheduler and webserver can cause high CPU > > load > > >>> of MySQL, given ~50 dags? > > >>> I feel MySQL should be light load.. > > >>> > > >>> Thanks. > > >>> -Jason > > >>> > > >> > > > > >