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