flask-sqlalchemy pysybase connections

[Kevin S]

I am running into a problem while developing a flask application using flask-sqlalchemy. Now, I'm not even 100% sure my problem is sqlalchemy related, but I don't know how to debug this particular issue.

To start, I have a sybase database that I want to see if I can build a report generating application for. The reports will all be custom SQL queries that are requested by our users, and they will be able to refresh throughout the day as they edit and clean up their data (we focus on a lot of data curation). We plan to do other things that merit the use of an ORM, and we have a lot of complex relationships. Anyway, that's why I'm first trying to get this to work in our flask + sqlalchemy stack. And it does work in fact.

Now the problem is, my current application is not scalable, because any time I do a long query (say several seconds or more), flask will not accept any additional requests until that query finishes. (Note: I am running the application through cherrypy). I have tested various things to ensure that the application can handle multiple incoming requests. If I have it just loop through a big file, or even just sleep instead of doing a query, then I can bang away at it all I want from other browser windows, and it's fine. 

We also have a copy of our database that is in postgres (this is only for testing, and can't be a final solution, because it gets updated only once a week). So, I've found that if I hook the application up to the postgres version, I don't have this problem. I can initiate a long query in one browser tab, and any other page requests in subsequent windows come back fine. The problem is only when using Sybase. We have other applications that are not flask or sqlalchemy, and they don't seem to have this limitation. As far as I can tell, I've narrowed it down to as soon as it executes a query. The entire app will wait until that query finishes, not allowing any new connections. I have log statements in my request handlers, and even in my before_request method, and those will not print a thing until the moment that first query returns. 

Additional info: I am using Sybase 15 with the pysybase driver. 

I initiate the raw SQL queries like this:

con = db.session.connection()

results = con.execute(query)

But I also see the same problem if I use object relationships via Object.query.all() or whatever.

I don't expect anyone to specifically know about this sybase driver, but I'm wondering what more can I do to try to debug this? I'm mostly interested in figuring out where the limitation is coming from, i.e. is it the database, the driver, or the way I'm using the session. I can provide additional details if needed.

[Michael Bayer]

well it's not a pooling issue because you don't have the issue with Postgresql, so its a Sybase driver issue. you'd need to see if you can boil down this same behavior to a single Python test script that uses the Sybase DBAPI directly.

Though that might only manage to prove its the Sybase DBAPI, and im not sure how much those drivers are being supported. Have you tried a different DBAPI ?

[Kevin S]

I can try to get another dbapi installed later this week and see if that works. However, I had to jump through some hoops just to get pysybase working in the first place, so I'm not terribly looking forward to trying to tackle another one.

I don't know much about how sessions are managed (I believe flask creates scoped-session objects). Could it be something that is just not implemented in the pysybase sqlalchemy dialect, but available in the dbapi? I'm not sure exactly what to look for.

[Michael Bayer]

On Jun 16, 2013, at 12:55 PM, Kevin S <kevin...@gmail.com> wrote:

I can try to get another dbapi installed later this week and see if that works. However, I had to jump through some hoops just to get pysybase working in the first place, so I'm not terribly looking forward to trying to tackle another one.

I don't know much about how sessions are managed (I believe flask creates scoped-session objects). Could it be something that is just not implemented in the pysybase sqlalchemy dialect, but available in the dbapi? I'm not sure exactly what to look for.

not really.  The DBAPI is a very simple API, it's pretty much mostly execute(), rollback(), and commit().   We have a test suite that runs against pysybase as well, it certainly has a lot of glitches, not the least of which is that pysybase last time I checked could not handle non-ASCII data in any way.     

If pysybase is halting the entire intepreter on a query, there's nothing about the DBAPI in the abstract which refers to that.   It sounds like pysybase probably grabs the GIL on execute() while waiting for results, which would be pretty bad.   Perhaps it has settings, either run time or compile time, which can modify its behavior in this regard.   

If it were me, I'd probably seek some way to not produce a web application directly against a Sybase database, as the severe lack of driver support will probably lead to many unsolvable scaling issues.  I'd look to mirror the Sybase data in some other more modern system, either another RDBMS or a more cache-like system like Redis.

On Saturday, June 15, 2013 3:33:36 PM UTC-4, Michael Bayer wrote:

On Jun 14, 2013, at 3:18 PM, Kevin S <kevin...@gmail.com> wrote:

> I am running into a problem while developing a flask application using flask-sqlalchemy. Now, I'm not even 100% sure my problem is sqlalchemy related, but I don't know how to debug this particular issue.
>
> To start, I have a sybase database that I want to see if I can build a report generating application for. The reports will all be custom SQL queries that are requested by our users, and they will be able to refresh throughout the day as they edit and clean up their data (we focus on a lot of data curation). We plan to do other things that merit the use of an ORM, and we have a lot of complex relationships. Anyway, that's why I'm first trying to get this to work in our flask + sqlalchemy stack. And it does work in fact.
>
> Now the problem is, my current application is not scalable, because any time I do a long query (say several seconds or more), flask will not accept any additional requests until that query finishes. (Note: I am running the application through cherrypy). I have tested various things to ensure that the application can handle multiple incoming requests. If I have it just loop through a big file, or even just sleep instead of doing a query, then I can bang away at it all I want from other browser windows, and it's fine.
>
> We also have a copy of our database that is in postgres (this is only for testing, and can't be a final solution, because it gets updated only once a week). So, I've found that if I hook the application up to the postgres version, I don't have this problem. I can initiate a long query in one browser tab, and any other page requests in subsequent windows come back fine. The problem is only when using Sybase. We have other applications that are not flask or sqlalchemy, and they don't seem to have this limitation. As far as I can tell, I've narrowed it down to as soon as it executes a query. The entire app will wait until that query finishes, not allowing any new connections. I have log statements in my request handlers, and even in my before_request method, and those will not print a thing until the moment that first query returns.
>
> Additional info: I am using Sybase 15 with the pysybase driver.
> I initiate the raw SQL queries like this:
>
> con = db.session.connection()
> results = con.execute(query)
>
> But I also see the same problem if I use object relationships via Object.query.all() or whatever.
>
> I don't expect anyone to specifically know about this sybase driver, but I'm wondering what more can I do to try to debug this? I'm mostly interested in figuring out where the limitation is coming from, i.e. is it the database, the driver, or the way I'm using the session. I can provide additional details if needed.

well it's not a pooling issue because you don't have the issue with Postgresql, so its a Sybase driver issue.   you'd need to see if you can boil down this same behavior to a single Python test script that uses the Sybase DBAPI directly.

Though that might only manage to prove its the Sybase DBAPI, and im not sure how much those drivers are being supported.   Have you tried a different DBAPI ?

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ladislav Lenart]

Hello.

This will probably be completely off-topic, but we have recently solved a
similar issue. In our case it was cherrypy's fault, because it uses 'implicit'
'sesssions.locking' by default. It acquires web session's lock at the beginning
of a web request processing and releases it at the end of the processing. If
more web requests use the same web session, they will be serialized (e.g. more
tabs in one browser, more AJAX calls on the page). We solved this by using
'explicit' instead and locking the web session manually only when we need to via
web_session.acquire_lock() and web_session.release_lock().


HTH,

Ladislav Lenart

On 16.6.2013 19:10, Michael Bayer wrote:
>
> On Jun 16, 2013, at 12:55 PM, Kevin S <kevin...@gmail.com

> <mailto:kevin...@gmail.com>> wrote:
>
>> I can try to get another dbapi installed later this week and see if that
>> works. However, I had to jump through some hoops just to get pysybase working
>> in the first place, so I'm not terribly looking forward to trying to tackle
>> another one.
>>
>> I don't know much about how sessions are managed (I believe flask creates
>> scoped-session objects). Could it be something that is just not implemented in
>> the pysybase sqlalchemy dialect, but available in the dbapi? I'm not sure
>> exactly what to look for.
>
>
> not really. The DBAPI is a very simple API, it's pretty much mostly execute(),
> rollback(), and commit(). We have a test suite that runs against pysybase as
> well, it certainly has a lot of glitches, not the least of which is that
> pysybase last time I checked could not handle non-ASCII data in any way.
>
> If pysybase is halting the entire intepreter on a query, there's nothing about
> the DBAPI in the abstract which refers to that. It sounds like pysybase
> probably grabs the GIL on execute() while waiting for results, which would be
> pretty bad. Perhaps it has settings, either run time or compile time, which
> can modify its behavior in this regard.
>
> If it were me, I'd probably seek some way to not produce a web application
> directly against a Sybase database, as the severe lack of driver support will
> probably lead to many unsolvable scaling issues. I'd look to mirror the Sybase
> data in some other more modern system, either another RDBMS or a more cache-like
> system like Redis.
>
>
>
>
>
>
>>
>> On Saturday, June 15, 2013 3:33:36 PM UTC-4, Michael Bayer wrote:
>>
>>

>> On Jun 14, 2013, at 3:18 PM, Kevin S <kevin...@gmail.com <javascript:>>

>> <mailto:sqlalchemy+...@googlegroups.com>.

>> To post to this group, send email to sqlal...@googlegroups.com

>> <mailto:sqlal...@googlegroups.com>.

[Kevin S]

Unfortunately, we cannot switch off of Sybase. It is a future project, but we cannot go there right now (I would love to). I also have not been able to find any other sybase dbapis that work with sqlalchemy and are free.

I did set up some tests against pysybase directly, omitting the sqlalchemy and cherrypy pieces. I'm having two threads each create a connection and execute a query. After littering debug statements in the test code, and throughout the pysybase library, I can see that as soon as the first thread executes its query, which is done through a c extension, everything else halts, the main function, as well as the second thread. 

Here is the test code (minus debugging lines):

def sy_query(query):

        db = Sybase.connect(dbServer,dbUser,dbPass,dbName)

        cur = db.cursor()

        cur.execute(query)

t1 = Thread(target=sy_query,args=("select * from blah",))

t2 = Thread(target=sy_query,args=("select something from blah2",))

t1.start()

t2.start()

t1.join()

t2.join()

It gets as far as one print statement after "t1.start()". I have a print at the beginning of "sy_query()" that does not execute for the second thread until the first has finished its query. Now, I'm not very familiar with threads in python, or the GIL in general. Inside pysybase's library, there is essentially this call to the c extension:

status, result = self._cmd.ct_results()

where ct_results() is defined in the c file.

Is there some easy or brute force way to force that call to NOT "grab the GIL"? From my brief reading it sounded like c extensions are supposed to get around GIL issues, but again I am naive on the subject.

[Michael Bayer]

On Jun 19, 2013, at 3:07 PM, Kevin S <kevin...@gmail.com> wrote:

Unfortunately, we cannot switch off of Sybase. It is a future project, but we cannot go there right now (I would love to). I also have not been able to find any other sybase dbapis that work with sqlalchemy and are free.

you can use FreeTDS with Pyodbc, and you might have much better results with that.   

where ct_results() is defined in the c file.

Is there some easy or brute force way to force that call to NOT "grab the GIL"? From my brief reading it sounded like c extensions are supposed to get around GIL issues, but again I am naive on the subject.

There's a C macro Py_BEGIN_ALLOW_THREADS/Py_END_ALLOW_THREADS that must bridge areas where the C extension should release the GIL.  The main documentation on this is at http://docs.python.org/2/c-api/init.html#releasing-the-gil-from-extension-code.

[Kevin S]

Ah ok, I did not know you could use FreeTDS with pyodbc. It was a bit more complicated to set up, but I finally got pyodbc installed and working. I reran my thread tests, and they seem to be working. They don't have the locking issue anymore. 

However, I have one small (hopefully small) issue when trying to use the sybase-pyodbc dialect with flask-sqlalchemy. Flask is by default passing a 'convert_unicode=True' argument to the create_engine() calls when building its connection pool. It gives the following error on initialization:

TypeError: Invalid argument(s) 'convert_unicode' sent to create_engine(), using configuration SybaseDialect_pyodbc/QueuePool/Engine.  Please check that the keyword arguments are appropriate for this combination of components.

I assume this is because Flask is all unicode based. I can't find any obvious difference between the pysybase and the sybase-pyodbc dialects that suggest why that argument is valid for one, but not the other... Any hints?

[Michael Bayer]

On Jun 20, 2013, at 12:54 PM, Kevin S <kevin...@gmail.com> wrote:

> Ah ok, I did not know you could use FreeTDS with pyodbc. It was a bit more complicated to set up, but I finally got pyodbc installed and working. I reran my thread tests, and they seem to be working. They don't have the locking issue anymore.
>
> However, I have one small (hopefully small) issue when trying to use the sybase-pyodbc dialect with flask-sqlalchemy. Flask is by default passing a 'convert_unicode=True' argument to the create_engine() calls when building its connection pool. It gives the following error on initialization:
>
> TypeError: Invalid argument(s) 'convert_unicode' sent to create_engine(), using configuration SybaseDialect_pyodbc/QueuePool/Engine. Please check that the keyword arguments are appropriate for this combination of components.

This is a bug that was reported in the Sybase dialect recently and is fixed in 0.8.2. The convert_unicode flag should be accepted by all dialects. If you go get the 0.8 or master branch from "Development Versions" at http://www.sqlalchemy.org/download.html it should be working.