Cockroach: ui: performance debugging brain dump

[petermattis]

While investigating TPC-C performance recently I had the opportunity to interact with the admin UI and found it wanting for portions of my investigation. This is a brain dump of some of the questions which I wanted to answer:

• Which nodes are overloaded? Some of the graphs show a timeseries per node while others are aggregate. For example, the "commit" latency on the Storage dashboard show per-node numbers. These were highlighting that 1 or 2 nodes out of 16 were slow. It would be useful to be able to switch from per-node to aggregate display for any graph. The slow commit latencies were caused by excessive CPU usage on those nodes. The excessive CPU usage was caused by too many leaseholders on those nodes. And in later debugging another issue caused excessive CPU usage which was a small table was spread over only a few nodes.
• What table data is on a node? How many ranges? Which are the most frequently accessed ranges on a node? Are those frequently accessed ranges being read or written to. These questions tie into the first bullet in trying to understand why a node in a cluster seems to be under more load than its peers. Some of this can be understood via SHOW TESTING_RANGES.
• What are the hot ranges across the entire cluster? Where are those ranges located? What tables do those ranges correspond to?
• For a given table: How is the data spread across the nodes in the cluster? How big are the indexes (including the primary index)?

I can give more context for the above. Ask me questions.

[a-robinson]

It would be useful to be able to switch from per-node to aggregate display for any graph.

I've wanted to be able to get a view that shows all the node/store lines separately on multiple occasions before. Clicking through each per-node view does the job, but it's a lot more work and a lot tougher to compare between nodes.

[petermattis]

• From the "Databases" tab, I want to be able to click through on a table to see a list of ranges and the size of each range. During debugging I noticed some tables had more ranges than expected and I wanted to understand why. For example, a 10MiB table had 3 ranges.

[couchand]

Thanks for writing this up @petermattis.

Re the per-node vs. aggregate question, it's something we've talked about, and was briefly mentioned on #18452.

A lot of your other thoughts are in line with #20500, or would be natural extensions to that view.

[dianasaur323]

we have the majority of these written as user stories either under the admin UI or with query analysis, so we'll hopefully get these covered. Once we figure out an approach, it would be good to run it by you to get feedback.

[petermattis]

I've wanted to be able to get a view that shows all the node/store lines separately on multiple occasions before. Clicking through each per-node view does the job, but it's a lot more work and a lot tougher to compare between nodes.

This came up again today. My short-term solution was to use a Custom Chart (via the /debug page).

I'm going to reiterate my desire for this functionality. Adding a toggle button on each chart to switch between aggregated and per-node would be incredibly useful in many of my debugging sessions. And this seems relatively trivial (aside from the design aspect) given that the functionality already exists on the Custom Chart page.

Cc @piyush-singh

[tbg]

I've wanted this on multiple occasions as well, especially now that we see folks run 15+ node clusters it's really tedious to scope out the outliers manually.

[piyush-singh]

We'll be tackling this in an upcoming milestone for this release. I'll be sure to reach out when we have designs for how we want to tackle this to get feedback from you both.

[vilterp]

Update:

• the hardware dashboard shows per-node hardware stats graphs, although we don't have the toggle on each graph yet. (Tracking issue here: #27856) This provides a basic sense of what nodes are overloaded.
• the data distribution debug page is merged, and shows the number of replicas for each table on each node. #26906 (WIP) extends it down to the level of individual replicas, and adds more metrics like size in bytes, QPS, and leaseholders. Hopefully in 2.2 we will have time to merge that PR, polish this page, and bring it forward from the debug section so it's easily accessible from the databases page.