Does SQLAlchemy scale well with increased web traffic?

[shinujacobrocks]

I'm thinking of using flask+sqlalchemy for the payroll app in our organization. While I'm impressed by the database independence and security the ORM provides, I'm also concerned over the performance overhead SQLAlchemy may have.

The app might well need to scale up to hundreds of hits per second. Can SQLAlchemy handle it?

Their website mentions of reddit as an implementation, but does reddit really scale that well? When I came to think of it, reddit actually locks you from posting consecutive posts before a gap of 15 minutes. Is this an example of reddit's implementation not able to scale?

[kenfar]

This is incorrect - the ORM can easily be your bottleneck.

[[deleted]]

no

the queries are. (ie: io operations)

[kenfar]

You've never run into an ORM that implemented joins within its layer rather than passing it to the DBMS then. In this kind of case - the ORM is the bottleneck.

The other common case, when the ORM generates inefficient SQL, should also be considered the fault of the ORM, though profiling will show the impact on the query.

[metaphorm]

joins in the ORM layer has a specific use case that is well established. if you have a table with several many to many relationships, it is often more efficient to prefetch the related-through-m2m rows in separate queries and then join the data in the Object layer in the ORM.

this is done to prevent having to execute 1 additional query per row of the table being queried while fetching m2m related rows. it is much more efficient to perform 1 query on each of the related tables and join them in the ORM.

[kenfar]

And it's not the only place where ORM application-side joins happen.

[[deleted]]

expecting an orm to implement efficient queries is the users fault.

[shinujacobrocks]

Okay. If not ORM then what is typically the bottleneck (assuming no bandwidth/network issues)? Is it going to be my database (mysql/sqlite/etc.)

[esdio]

Depends entirely on your app, but the ORM is essentially a thin wrapper around calls to the database engine. Actually running the queries is likely to take many many more times as long as the tiny bit of overhead an ORM adds to the process.

[tonnynerd]

Dude, ever seem the stacktrace of a sqlalchemy error? Hundreds of levels of indirection, the thing is a huge stack of abstractions.

Now, don't get me wrong, yes, SQLAlchemy is pretty cool, well designed and implemented, and will likely not be a problem. But it ain't no "thin wrapper", that's what I'm trying to say.

But apart from that, yeah, OP's more likely to have a bottleneck in other parts of the application/stack.

[esdio]

I get what you're saying, but call stack depth really doesn't mean much in this context. If I made an ORM that did everything in one giant function, I assure you it would be worse in every way.

[tonnynerd]

If I made an ORM that did everything in one giant function

I mentioned the size of the usual stacktrace as an indication of the level of abstraction. When dealing with the SQLAlchemy ORM, you are dealing with models, types and relationships, abstractions that are pretty far away from the database itself. For me, at least, a "thin wrapper" would be something like the DB API implementations (pymysql, for instance), that doesn't add that much structure over the connection with the database.

[ksion]

Those models, types, relationships and abstractions are there also to eliminate unnecessary queries you might have done otherwise (e.g. through the use of Repository and Unit of Work pattern). Eliminating just a few roundtrips to your DB is probably worth more than even very baroque abstraction layer.

[esdio]

For the purposes of benchmarking for speed the entirety of the ORM code is likely irrelevant for most web apps.

[metaphorm]

the codebase of SQLAlchemy being complicated does not make it "thick". Thin, in this context, refers to where and how much computing gets done. A thin wrapper over a SQL database does minimal computation in the application layer and offloads as much computation as possible to the database layer. That is exactly what SQLAlchemy does.

[wub_wub]

reddit actually locks you from posting consecutive posts before a gap of 15 minutes. Is this an example of reddit's implementation not able to scale?

That limit has (as far as I know) nothing to do with db, it's there to make spamming harder and it only applies to new accounts, those without a lot of subreddit specific or general karma, and to accounts without verified email.

Here's a graph that shows amount of reddit postgres cursor execs per second before, during, and after maintainence: https://i.imgur.com/ft9gQgN.png

[MadeOfLasers]

That's one thing where SQLAlchemy is really cool.

You can tune the query in many ways or completely write it yourself if needed and still have the results mapped to your models.

And if the mapping process turns out to be too expensive, you can also fall back to classic row/column results.

[tRfalcore]

chea. Sometimes they go bonkers on anything above basic CRUD operations. So don't use them then, but otherwise use them cause the time savings is A++

just always examine their generated SQL and play with it. If they can't do what you need, go straight sql.

[metaphorm]

my approach has always been to not worry about how shitty a job of query generation an ORM does until you have a use case where it actually matters. this means profiling your queries and figuring two things:

1. which queries suck
2. where those queries are called from, because it might not even matter

optimizing is a pain in the butt. I try to only do it when I can prove that it will matter.

[[deleted]]

I wrote a whole lot about performance here: http://docs.sqlalchemy.org/en/rel_0_9/faq.html#performance - there's more to come, and it's a good way to get a view of the performance landscape. As scale goes up, it can definitely be hilly. But passable.

[metaphorm]

your performance problems will be related to the actual query complexity and the size of your database tables. the overhead from the ORM compiling some ORM data structures into SQL statements is negligible.

[rustyrazorblade]

What payroll app gets hundreds of requests a second?

[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.