sql: add option to keep all table descriptor leases active on all nodes

[nvanbenschoten]

#19005 made progress towards preventing table lease acquisition from impacting tail latency. We now renew table leases when they are in use and begin getting too close (< 1 minute) to their expiration.

However, if a table is not used for more than a minute (DefaultTableDescriptorLeaseRenewalTimeout), the next request that needs it may find that its old lease has expired. Further, if a table is not used for more than five minutes (DefaultTableDescriptorLeaseDuration), the next request is guaranteed to find that its old lease has expired. In these cases, the next request to use the table will first need to pay the latency cost of acquiring a table lease before it can do anything else. This is both surprising and potentially concerning for users of Cockroach who have strict latency requirements. Further, the effect of this issue is more pronounced in a geo-distributed cluster where a lease acquisition requires multiple round-trips that may take a significant amount of time.

Poorly remembered quote from @bdarnell:

"Back when using BigTable, I used to run a script to continually renew all leases. I never want to pay the latency cost of renewing a table lease synchronously."

We should add an option to automatically renew table leases continuously in the background even if they are not being used actively. This option could apply to all tables or to only a subset of tables. It would probably take the form of a SESSION SETTING, but it may make sense as a CLUSTER SETTING as well.

The downsides to this are:

• lease acquisitions for a table may be wasted work if the table is never used

cc. @andreimatei @vivekmenezes

Jira issue: CRDB-5813

[andreimatei]

But they why "keep all table descs" rather than acquire all leases on server startup?

[nvanbenschoten]

But then why "keep all table descs" rather than acquire all leases on server startup?

That's another option that's mostly equivalent. Either way, the important part is that we'd indefinitely renew table leases.

[petermattis]

I'm moving this to 2.1 as it doesn't seem very hard and the effect is being seen in the real world.

[awoods187]

@robert-s-lee says he keeps encountering this as a problem in pocs. He has workarounds for customers that talk to him but many people will take the perf hit here without ever knowing it.

We should figure out if we can do it in 2.1

[nstewart]

@mjibson can you take a look at this from a cost perspective? We can see how it compares to our other priorities for this milestone or the next after you've reviewed it.

[nstewart]

Spoke with the team. We are targeting this for 2.1

[dt]

An all/nothing setting seems like it'd be risky to default to on, as it could be a problem for a cluster with thousands of tables, but would be unlikely to benefit to majority of users if defaulted to off.

I'm thinking a simple option might be instead to just pre-lease the first X tables, with X controlled via a setting and has a default s.t. we'd expect it to be "all" for most clusters, but still avoid issues for clusters with very large numbers of tables. Since we expect it to be "all tables" most of the time, this could be something simple, like just the first X in the descriptor table?

[asubiotto]

@lucy-zhang to triage/close

[postamar]

@ajwerner had explained to me that he wanted to replace the expiration column in system.lease with a session_id reference to sql liveness. I'm going to close this issue because whatever we end up doing, it's almost certainly not going to be what's described here. Please correct me and reopen if I'm wrong and/or completely off base here.

[postamar]

Here's the issue I was referring to above: #61419