airflow-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ruslan Dautkhanov <dautkha...@gmail.com>
Subject Re: Airflow + MsSQL database hangs
Date Wed, 30 Aug 2017 21:10:59 GMT
*to start the tuning process )



-- 
Ruslan Dautkhanov

On Wed, Aug 30, 2017 at 3:10 PM, Ruslan Dautkhanov <dautkhanov@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.
>>
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message