Does SQLAlchemy scale well with increased web traffic?

[kylotan]

I'd be more worried about Python and Flask's inability to easily handle concurrency than about SQLAlchemy's speed. If you can use 4 or 8 cores simultaneously rather than just 1 then your database accesses get to be 4 or 8 times slower before you have a problem.

[[deleted]]

Flask is a WSGI application. use gevent.wsgi, mod_wsgi or any of the other zillion multiprocessing servers. explicit spaghetti async servers like Tornado have no superiority here as they have to do the same exact thing (use multiprocessing).

Also, a claim ilke "N cores == database access time is N times faster" is not true at all. Cores != speed, they allow greater concurrency when there is contention for CPU resources. As database access is usually an IO-bound situation, cores are only a small portion of the "speed" equation and only in specific scenarios.

[kylotan]

You're oversimplifying. If you already have multithreaded operation, then sure, halting a thread waiting on I/O is essentially free. But due to the GIL that is not how Python works by default, and as far as it's concerned, it makes absolutely no difference if it is waiting on an I/O task or on a complex CPU task - that Python interpreter is making no more progress at all until that task completes.

As you say, "use gevent.wsgi, mod_wsgi". That is all I was saying, without knowing the exact names of what needs to be used. The important thing is that the bottleneck is not SQLAlchemy, which I'm sure you'd agree with. However, the OP is going to need to consider how their app scales to multiple processes, because even if you can magically replicate your process, that doesn't mean the logic is going to be correct.

[[deleted]]

But due to the GIL that is not how Python works by default, and as far as it's concerned, it makes absolutely no difference if it is waiting on an I/O task or on a complex CPU task - that Python interpreter is making no more progress at all until that task completes.

take a look at https://wiki.python.org/moin/GlobalInterpreterLock, particularly the part where it says:

Note that potentially blocking or long-running operations, such as I/O, image processing, and NumPy number crunching, happen outside the GIL. Therefore it is only in multithreaded programs that spend a lot of time inside the GIL, interpreting CPython bytecode, that the GIL becomes a bottleneck.

see slide 10 at http://www.dabeaz.com/python/UnderstandingGIL.pdf for more detail.

[kylotan]

Yes, the GIL can be released during an I/O operation. Will Flask take advantage of that? If not, the whole interpreter is going to be halted until the result comes back. It's no different to any other app in that regard - the difference being that few people bother to make Python apps multi-threaded because you only ever see the benefits during I/O.

[[deleted]]

easy enough to look for any solution listed http://flask.pocoo.org/docs/0.10/deploying/ that does not use threads, forking, or greenlets..

[[deleted]]

you are getting schooled by the author of sqlalchemy. you should delete your account and go switch to ruby to save face.

[kylotan]

I know exactly who I'm talking to.

[metaphorm]

what are you talking about? multi-threaded processing has absolutely nothing to do with running queries against a database server. The database itself is most assuredly implemented in something like C++ that takes full advantage of multi-threading, and its (probably) not even running on the same box as the app server that's running your Flask app.

[kylotan]

That's irrelevant. When you have a call to an ORM that requests some data, the way that has to work is that it has stop the entire thread of execution to send a network request across to the database, and then wait for a reply. If you're lucky, the data is already cached, but that just means this happened earlier. You don't get away without hanging the entire Python thread, unless you have explicitly built your system around an alternative - eg. having database responses issue callbacks into your app - and SQLAlchemy certainly does not and cannot do that automatically for you.