sql/stats: reduce number of automatic table stats jobs/attempted job creations

Currently every time the system determines that it should potentially recompute table statistics, it searched for any existing running recompilations, and then creates a new table statistics job if it does not find one.

This approach results in only one job being running at a time, but has two downsides: First, searching all existing jobs to see if any are that stats recompilation jobs on the same table is somewhat expensive, and in particular prone to contention as various jobs are constantly updating their state.

Second, even if the search succeeds and could be made cheaper (though this in and of itself could be a challenge), creating a new job for each modified table frequently -- sometimes only minutes apart -- results in a very large number of distinct jobs in the system. This both feeds back into the cost of the aforementioned search, but also has other negative externalities. A job starting, running, and completing are typically interesting events in a cluster, and are thus logged. Jobs are often relevant to the state of a cluster or its health, and are included, in their entirety, in debugging information. The job system often performs scans over all jobs to produce certain metrics, find jobs which need to start or stop or pause, or detect orphaned jobs. Especially in cluster with thousands of tables, the current approach of creating a new job every time stats are updated can result in a very large number of jobs, which then means more log lines, more noise in debugging artifacts, more work done by the system's scans, etc.

Instead, an alternative approach might be to create one automatic stats job (or one job per table or database), that runs indefinitely. This job would spend most of its time idle and waiting for work. When the mutation tracker determines that a given table requires recompilation of its table stats, it would write this fact to a key to the system.job_info table, using the ID of aforementioned singleton job, enqueuing the modified table for recompilation by that job when it is next active.

In such a system, regardless of the frequency at which stats need to be recomputed, and perhaps regardless of the number of tables or databases in the cluster, the number of jobs in the job system related to automatic stats computation would remain constant; the cost to search the existing jobs would be wholly avoided and the negative effects of having thousands of additional jobs in the system on things like logs, debugging information, and internal scans would be significantly reduced.

Jira issue: CRDB-30471

Epic CRDB-32494

cockroachdb / cockroach

sql/stats: reduce number of automatic table stats jobs/attempted job creations #108435