11 ms·
Here's a fun bug I had a few years ago - Had a postgres database which was using pgbouncer for connection pooling. The most senior developer (24yo or so) we ha
by chaps 7y ago
Here's a fun bug I had a few years ago -
Had a postgres database which was using pgbouncer for connection pooling. The most senior developer (24yo or so) we had on the project was using Go to connect to the database to write some simple reports, but each report took hours to run, and often had to sleep for 30+ minutes. So, after a while, pgbouncer would kill their connection, and their report would die. No other application did this among the many that we had connect to that DB, so it was definitely strange.
Found out pretty early on in troubleshooting it that they had no mechanism to keep the connection alive, which makes total sense for why his app died. So, they put the library standard keepalive function in a loop if the report wasn't doing anything.. but that didn't fix it.. it made no friggin' sense. After bashing my head against that for a while, I finally threw my hands up and asked if they could just run a "SELECT 1" as a keepalive instead of whatever the Go library was doing. Got a bit of pushback, but just told him to do it and walked away. That ended up fixing the problem.
Turns out the Go library was trying to be clever in its keepalives (can't remember what it was doing exactly), in that it made some silly assumptions that there was nothing in the middle managing connections.
I like to think that dev learned a lot about trust in "magical" libraries after that.
- user5994461 7y agowas the go library fixed?
- chaps 7y agoNo clue!
- cpufry 7y agowhich library?
- atian 7y agoSounds like pgx. Spent forever debugging this myself. https://github.com/jackc/pgx/issues/494 https://github.com/jackc/pgx/issues/494
- harikb 7y agoGo sql/database uses its own connection pool. But still that shouldn't create any problems. I have seen the reverse where apps that assumed temporary tables stick around from statement to statement without an explict `txn` (which regular postgres connections don't need) clearly failed. But I have not seen the issue you talk about. My wild guess would be that the Go code never closed the result/rows which caused either a pool exhaustion / left the connections hanging and eventually got a timeout. Consider this similar to how http client's need to close the response.Body or else connections can't be reused.
- randomdata 7y agoThe database/sql package isn’t very magical. The comment about magical library makes me think it was some other package.
- bregma 7y agoAny sufficiently advanced technology is indistinguishable from magic. If your source of knowledge is stack overflow or a youtube video posted on linkedin you already live in a world filled with magic.
- hermitdev 7y agoAt a previous employer, I was forced to use a certain 3rd party ODBC library. Under certain circumstances, it would just do `exit(1)`, with nothing even to stderr. Very frustrating and annoying to debug/fix. Had I physically known the developer responsible for that behavior, I'd probably have faced murder charges and plead temporary insanity.
- seanc 7y ago"Always code as if the person who ends up maintaining your code is a violent psychopath who knows where you live. I usually maintain my own code, so the as-if is true. " http://wiki.c2.com/?CodeForTheMaintainer http://wiki.c2.com/?CodeForTheMaintainer
- zerd 7y agoI've heard about some database drivers/middleware being smart and optimizing/caching queries like "SELECT 1" to save the round trip, so now I do something like "SELECT now()" or similar to do health checks.
- eeZah7Ux 7y ago> The most senior developer (24yo or so) I see the problem there.
- osrec 7y agoI've seen older developers that call themselves senior, but lack basic knowledge. I've seen younger developers, wise well beyond their years. Age simply isn't a big factor in how you judge a developer.
- brabel 7y agoJust because you saw a few exception does not mean the rule does not hold in general. Or are you saying most people don't learn with time (a corollary of your theory that age is not a big factor)?
- codegladiator 7y ago> most people don't learn with time I think this is correct. The curve flattens with age (in my opinion). > you saw a few exception I think the seniors you met (the knowledgeable ones) /were/ the exceptions. Most seniors I have seen have convinced themselves that they know all there is to know.
- brabel 7y ago> Most seniors I have seen have convinced themselves that they know all there is to know. Yeah, seems we've met different kinds of seniors... I don't know any senior who wouldn't understand that "the more you know, the more you understand just how much you still don't know".
- collyw 7y agoThat quote is very true!
- collyw 7y ago
- nuggien 7y agoNothing about go is magical, and you probably solved the problem blindly.
- chaps 7y agoWow, okay. You alright over there? Edit, because meh: I'm making no claims about go itself. No idea what makes you think that's what I'm saying, since I'm clearly talking about a library, and not even any stdlibs. "Magic" is just a term useful for describing systems that sweep much of their abstractions under the carpet in a way that probably has gotchas. Granted, the term itself is magical. In terms of fixing the problem, I knew for a fact that the keepalives that I was seeing were nothing like what I've seen in the past, at many companies, across Oracle, postgres, and MySQL, all who've implemented "SELECT 1" for the sake keep alives, by devs who've been in the field for much longer than me. The suggestion was by no means blind, unless you consider implementing a widely used method for this exact purpose, "blind". Had I gone a different route in fixing the problem within the stable, existing system, it would have likely broken many of the other database connections by many teams. I'll pass on that, since frankly, even ignoring the risk of such a change, the dev should have done the investigation themselves. Your post was unnecessarily aggressive and seems to come from me having struck a nerve somehow. Genuinely hoping you're doing alright. Peace.
- bdcravens 7y agoI presume they took it as an attack on Go. Truth is, it's an attack on the library developer who themselves may have found their keep-alive solution by stumbling blindly on it.
- nuggien 7y agoI have no affection towards go or any language. They're just tools. You sounded elitist calling something magic and pointing out someone's age as part of your point. And your passive aggressiveness to my response is proof of it. I "genuinely" hope you're doing alright too.
- chaps 7y ago
- kerng 7y agoGo code often reinvents/reimplements a lot of things from scratch, reintroducing problems that have been addressed long ago in other systems. It's like this new trend, let's rewrite everything in Go to be cool. Financially makes little to no sense.
- inopinatus 7y agoLet’s not just target Go with that sentiment, it applies almost universally, just in varying degree. Counterpoint: how is anyone supposed to learn, if not from their mistakes? We might worry about the blast radius, but there’s no compression algorithm for experience.
- rimliu 7y agoAnother option is to learn from the mistakes of others.
- gfxgirl 7y agoThe problem is you don't even know where to learn about the mistakes of others until you make the mistake yourself and in making the mistake you get some clue was to what to search for that then uncovers the mistakes of others.
- kerkeslager 7y agoI've found that when I ask others what mistakes I should avoid, they tend to answer the question. Another way to discover any mistakes is to ask people why they didn't do certain things which you think are good ideas. Often that reveals that they did do that, and it turned out poorly.
- bregma 7y agoPeople rarely post their mistakes to stack overflow.
- 7y ago
- taf2 7y agoInteresting I’ve run into similar issues when we put network load balancer from aws in front of a db. It has a fixed tcp connection timeout so similar if the query takes a long time it’ll disconnect. We fixed this at the socket level by ensuring our linux syscnf was set to keep connections alive at an interval below network load balancer. Was a tricky problem to figure out.
- ikiris 7y ago"that there was nothing in the middle managing connections" Found the real bug, and it wasn't in the library.
- fendy3002 7y agoNodejs Sequelize is transparently doing this when ping connection. That aside, I wonder why you need to keep the connection alive for > 30 mins, while usually sql con is short lived. Why can't you just close and reopen them, is it temporary table?
- zbentley 7y agoNot the GP, but I assume transactional consistency was important for the report, hence the need to keep the connection alive. That's a pretty common situation.
- fendy3002 7y agoDoes it means the report take the tables hostage for 30+ minutes? How can transactional consistency be achieved without write lock? Temporary table?
- blattimwind 7y agoMVCC.
- CodesInChaos 7y agoReporting can typically be split into two transactions. One long-running readonly transaction with snapshot consistency obtaining the data for the report and a separate transaction which publishes the result.
- kyllo 7y agoSQL connections can be arbitrarily long-lived. ETL processes and other data-intensive jobs often take multiple hours.
- fendy3002 7y agoOP said it need to sleep for 30+ minutes. Is it common for connection to idle for half hours for etl processes?
- atombender 7y agoGo also commits another un-Unix-y sin, in my opinion, in that it doesn't respect kernel keepalive. In other words, you can't use sysctl to configure it. Every single Go app has its own behaviour, requiring a recompile to change. As far as remember, Go didn't enable TCP keepalives until 2018.
- deleted 7y ago[deleted]
- listenallyall 7y ago>> That ended up fixing the problem Interesting take, since you're still stuck with "simple reports" that take hours to run. What about writing more efficient queries, adding indexes, normalizing the DB... ?
- reallydontask 7y ago> What about writing more efficient queries, adding indexes, normalizing the DB... ? Adding indexes seems like the best effort/reward benefits of those. In any case, I can easily imagine the following workflow: 1. Kick off report after first logging in in the morning 2. Get on with your day 3. Check report after first coffee break
- shifto 7y agoWouldn't be the first developer to just SELECT * with a thousand joins and then sort data in app(!).
- baud147258 7y agoOr even worst/better, thousand of SELECT with joins returning one result and then sort in app.
- UncleSam 7y agoThis sounds like the Hibernate Execution plan I found one time when it wasn't configured properly.
- collyw 7y agoIt amazes me the complexity of solutions these days, when 20 years ago almost everything ran in relational databases and query tuning was usually the solution to performance problems.
- devonkim 7y agoBecause we got rid of DBAs in favor of “big data” developers that never learned much about SQL in the first place.
- aryamaan 7y agoTangential: any good sql libraries in go? gorm lacks in features like prepared statements, batch inserts and we end up knitting raw SQL queries.