sql: allow monitoring and controlling long running queries

[a6802739]

Original question: will cocoroachdb have plans to support 'watching thread running state' like show processlist in mysql.

Users should be able to observe progress of long-running operations like online schema changes or large queries; and potentially interrupt operations asynchronously.

[knz]

@a6802739 yes this is on the roadmap.

[knz]

@petermattis is there a way to link particular issues like this one to our roadmap, so we can update them when we get there?

[petermattis]

@knz Sure, edit the wiki and add a link to the issue. Someone recently did this for common table expressions.

[petermattis]

Tracking progress of schema changes and backup / restore operations is targeted for 1.0. We'll likely have a different mechanism for tracking long running queries.

[spencerkimball]

Retitling this to target long running queries. See #7554 for the system jobs table.

[a6802739]

@knz , @petermattis any suggestions to start with for this feature, we need this in our production urgently.

[petermattis]

@a6802739 We need to sketch out a design first. In addition to being able to display running queries, we need to be able to cancel them too. Presumably we'd want to be able to show all of the SQL sessions (i.e. connections) and what queries are running on them.

[knz]

FWIW #10317 provides a mechanism to inspect currently open sessions on a node. I could complete the PR using the design direction suggested by Andrei (and Ben) in there. The missing piece would be cancellation, but perhaps context cancellation would help us here.

[petermattis]

For SHOW PROCESSLIST (or whatever we call it) we'd want to be able to show the sessions across the entire cluster. Some sort of fan out query to retrieve the sessions per node.

[itsbilal]

Both SHOW QUERIES and CANCEL QUERY is in master. See PRs: #16072 #17003 #17527