>> I tried to use NullPool ( change in Airflow) but creating over 1000 >> connections in minutes is to high time overhead.
It might be a tuning problem with your backend mssql database. Try decreasing sql_alchemy_pool_size, parallelism, dag_concurrency to a fairly small values to see if you can reproduce this issue? Out of the box SQL Server not necessarily scales well (unlike Oracle for example). SQL Server defaults to blocking selects on uncommitted data. It seems in that exception stack hanging happens in commits. For highly concurrent workloads it's recommended row versioning-based isolation levels to start the runing process https://msdn.microsoft.com/en-us/library/ms175095.aspx -- Ruslan Dautkhanov On Wed, Aug 30, 2017 at 8:53 AM, Arkadiusz Kołodziejski < [email protected]> wrote: > Hello, > > I tried to use Airflow 1.8.2RC2 and 1.8.2.RC4 with MSSQL database. > Unfortunately I always got Airflow Scheduler hangs. It is always after a > few minutes after 'airflow scheduler ' process start. I would like to share > results of my investigation of this problem. > > > > Here is example stacktrace from hanged process: > > Current thread 0x00007f08faed0700 (most recent call first): > > File > "/home/administrator/code/wf/workflow-airflow/venv/lib/ > python3.5/site-packages/sqlalchemy/engine/default.py", > line 440 in do_rollback > > File > "/home/administrator/code/wf/workflow-airflow/venv/lib/ > python3.5/site-packages/sqlalchemy/pool.py", > line 829 in _reset > > File > "/home/administrator/code/wf/workflow-airflow/venv/lib/ > python3.5/site-packages/sqlalchemy/pool.py", > line 687 in _finalize_fairy > > File > "/home/administrator/code/wf/workflow-airflow/venv/lib/ > python3.5/site-packages/sqlalchemy/pool.py", > line 811 in _checkin > > File > "/home/administrator/code/wf/workflow-airflow/venv/lib/ > python3.5/site-packages/sqlalchemy/pool.py", > line 960 in close > > File > "/home/administrator/code/wf/workflow-airflow/venv/lib/ > python3.5/site-packages/sqlalchemy/engine/base.py", > line 859 in close > > File > "/home/administrator/code/wf/workflow-airflow/venv/lib/ > python3.5/site-packages/sqlalchemy/orm/session.py", > line 542 in close > > File > "/home/administrator/code/wf/workflow-airflow/venv/lib/ > python3.5/site-packages/sqlalchemy/orm/session.py", > line 473 in commit > > File > "/home/administrator/code/wf/workflow-airflow/venv/lib/ > python3.5/site-packages/sqlalchemy/orm/session.py", > line 906 in commit > > File > "/home/administrator/code/wf/workflow-airflow/venv/src/ > apache-airflow/airflow/jobs.py", > line 161 in heartbeat > > File > "/home/administrator/code/wf/workflow-airflow/venv/src/ > apache-airflow/airflow/jobs.py", > line 1454 in _execute_helper > > File > "/home/administrator/code/wf/workflow-airflow/venv/src/ > apache-airflow/airflow/jobs.py", > line 1311 in _execute > > File > "/home/administrator/code/wf/workflow-airflow/venv/src/ > apache-airflow/airflow/jobs.py", > line 201 in run > > File > "/home/administrator/code/wf/workflow-airflow/venv/src/ > apache-airflow/airflow/bin/cli.py", > line 882 in scheduler > > File > "/home/administrator/code/wf/workflow-airflow/venv/src/ > apache-airflow/airflow/bin/airflow", > line 28 in <module> > > File "/home/administrator/code/wf/workflow-airflow/venv/bin/airflow", > line 6 in <module> > > > > But sometimes hangs on other method of cursor. > > > > Facts: > > - Hangs on MSSQL DB and do not on POSTGRESQL > - Hangs on remote DB connections , i do not observe this on local DB > connections > - Hangs with pymssql and pyodbc dialects > - Hangs in 1.8.2RC2 and 1.8.2RC4 > - Hangs with SQLAlchemy Engine StaticPool, SingletonThreadPool and > QueuePool > - *Works with NullPool* ( new connection on get from pool) > > > > I tried to use NullPool ( change in Airflow) but creating over 1000 > connections in minutes is to high time overhead. > > > > Has anyone faced this kind on probles with MSSQL DB ? > > > Thanks, > > Arek > > > ================ > I am an Intel employee. All comments and opinions are my own and do not > represent the views of Intel. >
