Hacker News Reader

Comment by cdelsolar | Hacker News Reader

what's the database timeout answer? i would say something along the lines of 5 seconds max.

Yeah, that's a pretty interesting question. For long-lived connections on networks I don't trust, I arbitrarily set the timeout at 10s. On networks I trust, I set it to one second. Looking at 99.99%-ile connection times on my production network, it's bimodal with peaks at 1ms and 28ms. (Guess which connections are from one physical machine to itself, and guess which connections are to a different machine.)

I have no idea what the correct answer to an interview question that asks this is, though. Like, I never connect to my database inside the request flow, I have a connection pool and wait for a connection to become free in the pool. So "connection time" is really a function of how utilized the pool is, and has only a passing resemblance to the health of the database. Maybe 10 connections are running "begin; lock table foo; select * from foo where expensive_condition". All 10 connections in the pool are blocked on a lock, and that doesn't mean the database is unhealthy and you want to shed load. So do you want to show the 11th person "sorry, our site is down, come back later", or do you want to wait for the random expensive queries to complete? I'd wait. (I suppose I wouldn't take an exclusive lock on a table to serve a web page either, but who knows what the interviewer is doing.)

The correct answer is to talk through how you'd evaluate making the decision based on the requirements and measured usage patterns, talk about what you've used in the past and why, if you haven't really been in a position to decide this or think about it, don't lie or randomly pick a number but just get out whatever you do know about it. So kind of like just talking through your second paragraph and maybe creating a hypothetical situation and answering based off of it.

Yeah, that makes sense. I feel like I can talk my way out of anything, but if there's a correct answer that exists without thinking, I'd love to know it. Not for interviews, but for In Real Life performance ;)

I lot of times questions like these are to see how someone thinks about problems. What questions do they ask to clarify the problem. I'm not saying this is good question for that, but I wouldn't assume questions have a "right" answer.

Exactly, this is a pretty bad sign for a senior dev, to just blurb out a number. What is the connection used for? End users, a backoffice tool, or a batch process? then he should have asked: what is our SLA? What's in the DB, how big are the objects, the queries? How frequent are they?

Even so, I wouldn't venture any number if you don't do some profiling or at least some tests with the most common data, and with edge cases. It can be anywhere between milliseconds and full seconds, maybe more?.

Reminds me of an interview once, I asked: the website is slow, you're in charge of it, what do you do? He just said "buy more servers"...slow down there, Rockefeller.

It's unclear from the question, but they were apparently looking for a very small number.

But is that connection timeout, or query timeout, or something else? If you have long-running complex data processing or ingestion jobs, query timeouts could well be measured in minutes or hours. So a small number doesn't make sense there, and the connection technically doesn't timeout.

For connection timeouts, if you run a connection pool then timeouts of a few seconds or more could be fine and be the difference between the system being completely down vs just slow in case the load / contention unexpectedly increases. Not great of course and it won't hold for long, but might help in some very specific bursty scenarios. If you are able to safely re-use the connections in the pool most of the time the timeout matters even less. Reusing connections is usually possible, but it depends on the database and what errors require establishing a new DB connection.

And of course with a pool you actually have 2 'connection timeouts': one for establishing a new connection to the DB and a second one for acquiring a connection from the pool. The second one is usually most relevant for an application, but both matter.

But even without a pool a higher number (say a few minutes) could possibly make sense given a specific scenario. A database with a connection limit is equivalent to decrementing a semaphore (acquiring a lock), and there are cases where you'd want to wait for just a bit longer to acquire that lock instead of just timing out. A slightly longer timeout won't hurt if e.g. you implement a retry mechanism anyways instead of aborting.

Not saying long timeouts are a sign of great engineering, but context matters a lot for these sorts of questions.

My guess is, very low: it's a shared resource for multiple clients, hit multiple times per request, and should probably be on a sub 100ms basis tops with a few retries.

Or it's a trick question: you should use shared connection pools to minimize the overhead of new connections, and should simply be part of the app startup.

Reply on news.ycombinator.com