Re: Airflow + MsSQL database hangs

[Arkadiusz Kołodziejski <ar...@gmail.com>]

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 <da...@gmail.com>:

> *to start the tuning process )
>
>
>
> --
> Ruslan Dautkhanov
>
> On Wed, Aug 30, 2017 at 3:10 PM, Ruslan Dautkhanov <da...@gmail.com>
> 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 <
> > arkadiusz.kolodziejski@gmail.com> 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.
> >>
> >
> >
>