Thanks for the reply. We are using 1.7.1.3 and it looks the index is not there. https://github.com/apache/incubator-airflow/blob/1.7.1.3/airflow/models.py#L660-#L664
Is Airflow 1.8 officially released ? I saw the version tag and discussion, but not saw it in pypi.. I did run Dan's SQL statement to clean up table "task_instance" for entries older than 30 days. It seems not help on the CPU load. We are using AWS RDS and not able to ssh to top the processes. "show processlist;" from MySQL not help much. In general, what scenario will cause the Airflow MySQL CPU highly loaded ? We have about 50 Dags (each one has 3-4 tasks) with high frequencies (20 mins to one hour). Perhaps because of that ? Thanks. -Jason On Wed, Mar 8, 2017 at 2:55 PM, Maxime Beauchemin < maximebeauche...@gmail.com> wrote: > Wait. That field does have an index and it looks like Dan added it 8 months > ago. > https://github.com/apache/incubator-airflow/blame/ > master/airflow/models.py#L744 > > Here's the related DB migration script: > https://github.com/apache/incubator-airflow/blob/master/ > airflow/migrations/versions/211e584da130_add_ti_state_index.py > > Maybe it didn't make the previous release, it should be there in 1.8.0 if > it wasn't in the release you are runing. > > You can go ahead and create the index manually as well, though that may > break the migration script as you upgrade if you do. > > Max > > On Tue, Mar 7, 2017 at 9:57 PM, Dan Davydov <dan.davy...@airbnb.com. > invalid> > wrote: > > > We will need to come up with a plan soon (better DB indexes and/or the > > ability to rotate out old task instances according to some policy). > Nothing > > concrete as of yet though. > > > > On Tue, Mar 7, 2017 at 6:18 PM, Jason Chen <chingchien.c...@gmail.com> > > wrote: > > > > > Hi Dan, > > > > > > Thanks so much. This is exactly what I am looking for. > > > > > > Is there a plan on the future airflow road map to clean this up from > > > Airflow system level? Say, in airflow.cfg, a setting to clean up data > > older > > > than specified time. > > > > > > Your solution is to run an airflow job to clean up the data. That's > > great. > > > In a short term for us, I will be just running the SQL command directly > > > from MySQL CLI and then setup an airflow job to do that periodically. > > > > > > Thanks. > > > -Jason > > > > > > On Tue, Mar 7, 2017 at 5:47 PM, Dan Davydov <dan.davy...@airbnb.com. > > > invalid> > > > wrote: > > > > > > > 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 > > > > > > >>> > > > > > > >> > > > > > > > > > > > > > > > > > > > > > > > > > > >
