Hacker News Reader

Comment by fizx | Hacker News Reader

I use SQLAlchemy to serve tens of thousands of requests per second on one of my services at Reddit. It's painful, but possible.

SQLAlchemy uses the data mapper + unit of work patterns. This means that your connections and your record objects have the same lifecycle. If your request takes 200ms, then you are likely holding open your connections for 190ms, even though the only SQL query takes 20ms. If connections are scarce, and they usually are, you will not utilize them well at all.

There are two ways that I know of to solve this, neither of which is pretty: (1) run pgbouncer as a sidecar on your python processes. This has the effect of making connections a far cheaper resource. (2) After every single query, manually close the related transaction/connection/cursor in your code.

ActiveRecord defaults to giving up the connection after every query, which in a high-traffic webapp, is much less of a footgun.

View on HN

(No significant experience with either SQLAlchemy or ActiveRecord, but work in Java at a shop that really strongly emphasizes the unit-of-work pattern.)

"give up connection after every query" is, IMO, not _less_ of a footgun, but a _more subtle_ footgun. That is to say, sure, for the most part, it will work ok.

_But when it doesn't_... like when your database goes down for a couple hours, or your rack loses network, or your proxy to whatever cloud provider is running your database, or maybe some other dependency had an outage between the queries... you end up not really knowing what executed and what didn't, with some fraction of your 10k req/sec leaving behind some dangling state -- a partially processed $whatsit -- and no clear/easy way to fix it or even necessarily identify how many $whatsits were affected. At least a few times, I've needed to either wait until records make their way into some OLAP system for offline analysis, or deploy some fixup code, or emulate either/both to fix stuff up after something weird happens.

At least that's what I am constantly afraid of. Do you also worry about this kind of stuff? If not, what prevents it from being a problem in your environment?

Signed,

The guy constantly badgering seemingly everyone about minding their transaction boundaries.

From my experience, there’s always one developer in every enterprise dev shop who understands transaction boundaries, isolation levels, and concurrency/reliability scenarios that do happen....and 10 other devs constantly instilling fear in him/her

But he's not saying "don't use transactions." He's saying "Don't keep your transaction open for your entire user connection for no reason. Close your completed transactions as soon as you can."

Great point, thanks!

Fun story: A coworker and I are in the process of tracking down exactly violations of this in a misbehaving app, due to an accumulation of RPC within transactions across the entire app. (I used to be responsible for it as a greenhorn ~5years ago, but walked away for the last several years to work in a different part of the org.)

The approach is actually kinda interesting: we add an interceptor in the RPC layer for every outgoing call that asks the DB machinery “am I currently in a transaction?”, and emits a warning (containing at least the RPC being made, perhaps a (partial) stack trace) if so. We had so many occurrences of those warnings that our logging framework proactively started dropping log lines! So the next step is to only emit the warnings once per $duration per message.

Another day in the life...

Just to hopefully further contribute, one of the top-deck features of the new Rails 6.0 release is its really thoughtful engineering work around multiple database support. At least, that's how it's named or described; the actual net result is a lot of smart people exhausting themselves making n-instance DB stack reliability and performance better on every chart... including setups where the value of n is 1.

What this translates to is that things should be impressively stable when your DB nodes go down, the backhoe shows up to party, or your BGP is routed to North Korea.

You can divorce your objects from the session if you want. But you lose transactions then, so with any approach that involves giving up the connection it seems to me you’ll create a whole other class of headaches for yourself.

I’m lucky in that I have low requests per second so I never have to worry about any of that too much. I don’t envy you having to deal with that sort of load.

Quick reminder that advisory locks may need to be released by the same connection that acquired them...

pretty sure pgbouncer should be a sidecar to postgres database itself and not the individual process. but otherwise, yes, it's a great answer.

Reply on news.ycombinator.com