Re: [sqlalchemy] Hang when >= 15 MS SQL Server requests have been made
On Sat, Dec 28, 2019, at 11:12 PM, Brian Paterni wrote:
> Hi,
>
> I seemingly have a problem with flask/socketio/eventlet/sqlalchemy + MSSQL
> when >= 15 parallel requests have been made. I've built a test app:
>
> https://github.com/bpaterni/flask-app-simple-blocking-eventlet
I can't run the test app however 15 seems like your connection pool is set up
at its default size of 5 connections + 10 overflow, all connections are being
checked out, and none are being returned.
while I strongly recommend against using eventlet with Python DBAPI drivers or
SQLAlchemy, when using eventlet or gevent with SQLAlchemy you need to ensure
that a full monkeypatch of "thereading" / "socket" and everything is performed
before anything else is imported. SQLAlchemy's pool makes use of a port of the
Queue class which makes use of threading mutexes all of which will wreck an
eventlet application that did not correctly monkeypatch these.
I'm also not familiar with any driver for MSSQL that supports implicit or
explicit async. SQLAlchemy only works with PyODBC or pymssql neither of which
have async support that I'm aware of, what driver are you using ?
>
> that can be used to reproduce the problem. It will hang if >= 15 parallel
> request have been made to the '/api/busy/mssql' endpoint.
>
> I'm not sure if the root cause of the problem is based in the SQL Server ODBC
> Driver, sqlalchemy, or eventlet, but I've already paid the microsoft support
> tax only to be told that there's insufficient evidence to indicate the ODBC
> driver is at fault. So I thought I would post the issue here to see if
> anybody would be able to help in pinpointing the code that is at fault with
> this problem.
>
> Once the test app above is running and has a valid SQL server to query, you
> should be able to reproduce the hang with
>
> seq 15 | parallel -j0 "curl -s localhost:5000/api/busy/mssql && echo {}"
>
> The hang seems lo occur consistently on the 15th request. This happens even
> when connection pool_size/max_overflow are adjusted away from their
> respective default values which leads me to believe that exhausting the
> connection pool is not the cause of the problem. Though there may be some
> other reason behind the scenes for the hang occurring at the 15th
> connection(?)
>
> Thanks very much for any help that can be provided in resolving this issue!
> :)
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/26971819-0cf1-4158-83b4-6a4972f7e755%40googlegroups.com
>
> .
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/085eaa9d-65b0-4cf5-8055-2e6c516937cd%40www.fastmail.com.
[sqlalchemy] Re: Hang when >= 15 MS SQL Server requests have been made
Plus, if it's any help. the *does* seem resolve itself after ~2 hours.
That, or it can be side-stepped by sending a SIGINT signal (ctrl-c) to the
flask app when it is hung. the SIGINT seems to kill the 15th (hung) request
and allows the app to continue processing other requests successfully.
On Saturday, December 28, 2019 at 10:12:13 PM UTC-6, Brian Paterni wrote:
>
> Hi,
>
> I seemingly have a problem with flask/socketio/eventlet/sqlalchemy + MSSQL
> when >= 15 parallel requests have been made. I've built a test app:
>
> https://github.com/bpaterni/flask-app-simple-blocking-eventlet
>
> that can be used to reproduce the problem. It will hang if >= 15 parallel
> request have been made to the '/api/busy/mssql' endpoint.
>
> I'm not sure if the root cause of the problem is based in the SQL Server
> ODBC Driver, sqlalchemy, or eventlet, but I've already paid the microsoft
> support tax only to be told that there's insufficient evidence to indicate
> the ODBC driver is at fault. So I thought I would post the issue here to
> see if anybody would be able to help in pinpointing the code that is at
> fault with this problem.
>
> Once the test app above is running and has a valid SQL server to query,
> you should be able to reproduce the hang with
>
> seq 15 | parallel -j0 "curl -s localhost:5000/api/busy/mssql && echo {}"
>
> The hang seems lo occur consistently on the 15th request. This happens
> even when connection pool_size/max_overflow are adjusted away from their
> respective default values which leads me to believe that exhausting the
> connection pool is not the cause of the problem. Though there may be some
> other reason behind the scenes for the hang occurring at the 15th
> connection(?)
>
> Thanks very much for any help that can be provided in resolving this issue!
> :)
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/36aa8c3b-d960-4238-bc83-4d33ed325435%40googlegroups.com.
[sqlalchemy] Hang when >= 15 MS SQL Server requests have been made
Hi,
I seemingly have a problem with flask/socketio/eventlet/sqlalchemy + MSSQL
when >= 15 parallel requests have been made. I've built a test app:
https://github.com/bpaterni/flask-app-simple-blocking-eventlet
that can be used to reproduce the problem. It will hang if >= 15 parallel
request have been made to the '/api/busy/mssql' endpoint.
I'm not sure if the root cause of the problem is based in the SQL Server
ODBC Driver, sqlalchemy, or eventlet, but I've already paid the microsoft
support tax only to be told that there's insufficient evidence to indicate
the ODBC driver is at fault. So I thought I would post the issue here to
see if anybody would be able to help in pinpointing the code that is at
fault with this problem.
Once the test app above is running and has a valid SQL server to query, you
should be able to reproduce the hang with
seq 15 | parallel -j0 "curl -s localhost:5000/api/busy/mssql && echo {}"
The hang seems lo occur consistently on the 15th request. This happens even
when connection pool_size/max_overflow are adjusted away from their
respective default values which leads me to believe that exhausting the
connection pool is not the cause of the problem. Though there may be some
other reason behind the scenes for the hang occurring at the 15th
connection(?)
Thanks very much for any help that can be provided in resolving this issue!
:)
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/26971819-0cf1-4158-83b4-6a4972f7e755%40googlegroups.com.
