ui,jobs: improve jobs overview page in DBConsole

[vy-ton]

In #44594, Schema is adding a retry mechanism to the jobs infrastructure. During this work, we plan to add additional columns to crdb_internal.jobs to surface more job metrics.

These new metrics would be helpful in the DBConsole Jobs Overview page.

• last execution time
• execution count
• updated status to indicate Retrying

FYI @ajwerner @sajjadrizvi

Epic CRDB-7912

[kevin-v-ngo]

Hi @vy-ton, @maryliag and I were wondering when the additional job metrics will be available so that we can coordinate the UX changes. Do you have an ETA?

[ajwerner]

cc @sajjadrizvi

[sajjadrizvi]

Hi @vy-ton, @maryliag and I were wondering when the additional job metrics will be available so that we can coordinate the UX changes. Do you have an ETA?

I am still working on adding the exponential backoff that is a pre-requisite for job metrics. I am expecting that it will take another day or two to complete. After that I can start working on adding the required metrics for observability. So I think the ETA is by the end of next week.

[sajjadrizvi]

I am thinking about the following.

We add three columns, last execution time, next execution time, and, number of times executed, that provide quick insights about the job and its current status.

In addition, we add a column that provides insights about each job's lifecycle. A user should be able to see that a job has transitioned from state_x to state_y at time_t due to error err. Moreover, what will happen next to the job? If it is going to be retried, when will it be?

To achieve that, we can add a repeated structure in jobs proto that keeps track of job history:

ExecutionLog struct {
  state
  execution_time
  execution_error
}

Each time a job runs stepThroughStateMachine, it create this structure and adds it in job's payload after populating state and execution_time fields. When the job finishes its current execution (before leaving stepThroughStateMachine), it populates the execution_error field with the last error.

This structure can be used to populate a column job status log that shows the list of those structures in reverse chronological order, in the form "Job attempt at execution_time with status state finished with error execution_error. If there was no error, the error part can be skipped. If the job will be retried, we should add "The job will be retried at time. May be we want to add next_state as well if it doesn't add too much complication.

A question is, how many log entries to show to a user? I think we should show only one log entry by default and provide an option to display N entries in reverse chronological order.

[ajwerner]

Can you be more concrete as to when these entries are actually written? I'm having a hard time understanding what happens before Resume/OnFailOrCancel and what happens after in terms of writes to the jobs table.

Also, let's isolate somewhat the discussion about what values we write to system.jobs from the implied changes to crdb_internal.jobs. The latter is a function of the former, so they are related.

[sajjadrizvi]

Currently a resumer runs in states running and reverting. In running state, we add this structure with nil execution_error before resuming. execution_error field is then populated here if there is an error.

In reverting state, we should add the structure with nil error before reverting. Error is then populated after onFailOrCancel.

In the cases of StatusSuceeded, StatusFailed, and StatusCanceled, we do not run the resumer. So we don't have to add those log entries.

[ajwerner]

That sounds good. In addition, I think it'd be nice to have the coordinator ID. It'd be good to have an invariant that when we write last_run then we populate an entry in the log.

[sajjadrizvi]

In addition, I think it'd be nice to have the coordinator ID

Great, that's a good suggestion.

It'd be good to have an invariant that when we write last_run then we populate an entry in the log.

That's precisely what we want.

[sajjadrizvi]

That's precisely what we want.

May be not! There is an exception. We update last_run in servePauseAndCancel as well. I think we should not add the entry at that time. So the invariant could be to populate an entry whenever we run a resumer.

[sajjadrizvi]

Another question is how to add the transition logs in the jobs table? On top of my head, I am thinking to add a column transition_logs. Each row then presents the logs as a string:

Log number: 1
Coordinator ID: node_id
Execution time: ts
Execution state: status
Execution error: nil or decoded error string
Log number: 2
...

An alternative is to have a separate table with jobID and four columns for each log entry. I think that's not the best way to go.

[ajwerner]

Firstly, when you say jobs table, please indicate which jobs table.

I don't think separate rows is going to leave to a happy outcome. My guess is the right answer is to use json.

[sajjadrizvi]

OK, I meant, crdb_internal.jobs. Sorry for the confusion.

[sajjadrizvi]

Also, I wanted to say that a row has. a string that contains the list of the entries. JSON seems appropriate here.

[kevin-v-ngo]

Synced with Andrew and for this issue, we'll track adding the 3 metrics (LAST EXECUTION TIME, NEXT EXECUTION TIME, and EXECUTION COUNT to the jobs overview page.

For error details, we'll track it with the following issue: #69170.

CC'ing @Annebirzin @vy-ton as FYI

[jocrl]

Filed https://github.com/cockroachdb/ui/issues/395 because the Tooltip component is mis-centered for inline elements (in this case, the status badge), and because the solution is move involved than I had thought.

edit: The new design has a pretty full table cell, so I centered the tooltip around the entire cell and so the bug no longer blocks this issue

[jocrl]

@Annebirzin , just want to re-ping you about how we should indicate that a job is running/reverting, but also retrying? I like Marylia's suggestion about the hyphenated statuses, though that raises the question of what to do about the % and timing remaining visualization that would usually show on running jobs.

The definition for a job that is retrying is status IN ('running', 'reverting') AND next_run > now() AND num_runs > 1 (i.e. it will be both running and retrying, or reverting and retrying)

@ajwerner , assuming you have no objections, I'm going to modify the endpoint as Marylia suggested (#68179 (comment)) to send retry-running and retry-reverting in the endpoint status field.

[Annebirzin]

@jocrl I wonder if for retrying-running instead of showing the status badge, we show the % bar with a 'retrying' label before the time remaining? (Figma design)

Does that make sense?

[jocrl]

@Annebirzin That makes sense! For consistency, do you think retry-running should be something like the lower two rows? Or just the first is good.

[Annebirzin]

@jocrl I do like the last row where they look like two separate badges. Maybe just a bit of space between them and remove the :

[jocrl]

@ajwerner if num_runs is null in the crdb_internal.jobs, should it show in the UI as 0, or just blank? (The endpoint defaults to 0 without further intervention).