3 ms·
Disclaimer: IAAPDBA (I am a PostgreSQL DBA) I have been employed thusly for a decade now, and have worked in environments ranging from tens of concurrent users
by rosser 9y ago
Disclaimer: IAAPDBA (I am a PostgreSQL DBA) I have been employed thusly for a decade now, and have worked in environments ranging from tens of concurrent users to hundreds of thousands. Previously, I was a developer.
A few jobs ago, I encountered a similar situation. That stack was Python-based. The engineers had apparently not realized that the DBAPI's default is to silently wrap all database interactions in an explicit transaction. (That is, before it submits your query to the db, it transmits a "BEGIN", and expects you to "COMMIT" or "ROLLBACK" as appropriate. I, personally, think that's a terrible choice, but whatever.)
Everywhere else in the stack, this wasn't an issue — or at least not a particularly troublesome one. In the job queue, it was. The reason is simple: the job workers were long-lived, holding those (implicitly opened) explicit transactions for the lifetime of the connection. In the rest of the stack, the app code would connect, interact, and disconnect cleanly, including COMMIT-ing. How folks realized the need to do that in one place, but not the other, no-one could adequately explain to me.
When I started, they were uncomplainingly habituated to shutting down their entire jobs processing infrastructure (which had customer-visible impact) for hours, every week, to do a "VACUUM FULL"/"REINDEX" cycle on the queue table. That table's disk footprint followed a perfect "sawtooth" pattern, growing linearly "up and to the right", well into the gibibytes (for, on average, a few hundred to a few thousand relatively narrow, "live" rows), until the maintenance event, where it would drop off vertically. Lather, rinse, repeat. They had been running this way for years.
It took a one-line code change to disable that behavior, and hours of downtime per week were eliminated.
IMO, this even less PostgreSQL's "fault" than the "write amplification" issue that Uber flounced over last year. They Did It Wrong, and it bit them.
TL;DR, it's the poor craftsman who blames his tools, and when you use a tool incorrectly, that tends to be consequential.
EDIT: phrasing.
- k__ 9y agoWould you say the DBA job is easy mostly because non-DBAs simply don't bother with getting to know their DB?
- rosser 9y agoNo, I would say that makes it harder. People have all kinds of crazy, naïve assumptions about how databases work. I often don't see the result of those assumptions until late in the process, when correcting them is significantly more difficult. EDIT: It does create a lot of teachable moments, however. Over time, many of those assumptions are mitigated.
- lmm 9y agoI'd say that's the reason it's a job at all, at any but the largest companies. 90% of a DBA's time is spent preventing or solving problems that wouldn't be there if every developer had a level of basic competence with their DB.
- inopinatus 9y ago> How folks realized the need to do that in one place, but not the other, no-one could adequately explain to me I have seen this pattern before in job queueing code that used a RDBMS backend, and it was due to a conflation in the developer's understanding of database semantics, between an open transaction and a lock. Essentially the long-running transaction was being abused in the hope of achieving exactly-once semantics. Facepalm, I know; one could list many failure modes resulting from such a naive approach, but that was the rationale nevertheless. As you said in another remark, these are teachable moments.
- dkersten 9y ago> How folks realized the need to do that in one place, but not the other, no-one could adequately explain to me Chances are that whatever web framework they were using was doing the commits/rollbacks per-request automatically but when they were doing queries outside of a request context, they had to do it manually (and didn't). That is, its possible they just didn't know/realise what was happening.