Tried decreasing sql_alchemy_pool_size, parallelism, dag_concurrency to 1 and still Hanging Tried row versioning-based isolation levels on DB and still Hanging.
NullPoll is working but has not acceptable performance. Thanks, Arek 2017-08-30 23:10 GMT+02:00 Ruslan Dautkhanov <[email protected]>: > *to start the tuning process ) > > > > -- > Ruslan Dautkhanov > > On Wed, Aug 30, 2017 at 3:10 PM, Ruslan Dautkhanov <[email protected]> > wrote: > > > >> 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/pytho > >> n3.5/site-packages/sqlalchemy/engine/default.py", > >> line 440 in do_rollback > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/lib/pytho > >> n3.5/site-packages/sqlalchemy/pool.py", > >> line 829 in _reset > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/lib/pytho > >> n3.5/site-packages/sqlalchemy/pool.py", > >> line 687 in _finalize_fairy > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/lib/pytho > >> n3.5/site-packages/sqlalchemy/pool.py", > >> line 811 in _checkin > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/lib/pytho > >> n3.5/site-packages/sqlalchemy/pool.py", > >> line 960 in close > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/lib/pytho > >> n3.5/site-packages/sqlalchemy/engine/base.py", > >> line 859 in close > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/lib/pytho > >> n3.5/site-packages/sqlalchemy/orm/session.py", > >> line 542 in close > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/lib/pytho > >> n3.5/site-packages/sqlalchemy/orm/session.py", > >> line 473 in commit > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/lib/pytho > >> n3.5/site-packages/sqlalchemy/orm/session.py", > >> line 906 in commit > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/src/apach > >> e-airflow/airflow/jobs.py", > >> line 161 in heartbeat > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/src/apach > >> e-airflow/airflow/jobs.py", > >> line 1454 in _execute_helper > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/src/apach > >> e-airflow/airflow/jobs.py", > >> line 1311 in _execute > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/src/apach > >> e-airflow/airflow/jobs.py", > >> line 201 in run > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/src/apach > >> e-airflow/airflow/bin/cli.py", > >> line 882 in scheduler > >> > >> File > >> "/home/administrator/code/wf/workflow-airflow/venv/src/apach > >> e-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. > >> > > > > >
