Add Insights information to SQL Activity page #83780

maryliag · 2022-07-04T16:21:23Z

Add insights information to the SQL Activity page as:

New column on the Statement/Transactions overview, with the total count of insights
Statement/Transaction Details page info (including the Explain plan)

Figma: https://www.figma.com/file/xpnRkP9cLXERVceRC4cec4/22.2_SQL-obsrv_query-performance?node-id=1086%3A89095

https://www.loom.com/share/daee54ca31644ba4b9b64784c0228b8e?t=317

Epic CRDB-20388

This commit adds a column for the distinct insight types for each fingerprint listed on the Statement and Transaction Fingerprint Overview pages. Part of cockroachdb#83780. Release note (ui change): Added Insights column to the SQL Activity Fingerprint Overview pages of the DB Console.

This commit adds a time picker to the workload insights overview pages. The time picker store is shared across SQL activity and insights components, enabling us to better correlate the insight events with fingerprints on the SQL activity pages by time interval. The start and end values of the time picker (stored in the timeScale/SQLActivity local setting) form the request to the insights "backend", where we use these start and end values to filter queries to the internal insights and contention tables. The "backend" queries select events across the interval, then partition and filter on rank, ordered by descending end time. Part of cockroachdb#83780. Release note (ui change): Added a time picker to the Workload Insights Overview pages in the DB Console.

92285: ui: add time picker to insights pages r=ericharmeling a=ericharmeling This commit adds a time picker to the workload insights overview pages. Loom (CC Console): https://www.loom.com/share/379aa57e40934bdb92e9c212f4c31735 Loom (DB Console): https://www.loom.com/share/1a54e02644e84941af0a54e4cb88cff2 Fixes #94761. Fixes #94380. Part of #83780. Release note (ui change): Added a time picker to the Workload Insights Overview pages in the DB Console. Co-authored-by: Eric Harmeling <[email protected]>

…etails This commit adds execution insights to the Statement Fingerprint and Transaction Fingerprint Details pages. Part of cockroachdb#83780. Release note (ui change): Added execution insights to the Statement Fingerprint Details and Transaction Fingerprint Details Pages.

96031: sql: add mixed version test for system.role_members user ids upgrade r=rafiss a=andyyang890 This patch adds a mixed version logictest that ensures that GRANT ROLE continues to work properly in a cluster with both 22.2 and 23.1 nodes (i.e. nodes that have run the system.role_members user ids upgrade). Part of #92342 Release note: None 96127: kvserver: introduce cpu rebalancing r=nvanbenschoten a=kvoli This patch allows the store rebalancer to use CPU in place of QPS when balancing load on a cluster. This patch adds `cpu` as an option with the cluster setting: `kv.allocator.load_based_rebalancing.objective` When set to `cpu`, rather than `qps`. The store rebalancer will perform a mostly identical function, however target balancing the sum of all replica's cpu time on each store, rather than qps. The default remains as `qps` here. Similar to QPS, the rebalance threshold can be set to allow controlling the range above and below the mean store CPU is considered imbalanced, either overfull or underfull respectively: `kv.allocator.cpu_rebalance_threshold`: 0.1 In order to manage with mixed versions during upgrade and some architectures not supporting the cpu sampling method, a rebalance objective manager is introduced in `rebalance_objective.go`. The manager mediates access to the rebalance objective and overwrites it in cases where the objective set in the cluster setting cannot be supported. The results when using CPU in comparison to QPS can be found [here](https://docs.google.com/document/d/1QLhD20BTamjj3-dSG9F1gW7XMBy9miGPpJpmu2Dn3yo/edit#) (internal). <details> <summary>Results Summary</summary> ![image](https://user-images.githubusercontent.com/39606633/215580650-b12ff509-5cf5-4ffa-880d-8387e2ef0afa.png) ![image](https://user-images.githubusercontent.com/39606633/215580626-3d748ba1-e9a4-4abb-8acd-2c319203932e.png) ![image](https://user-images.githubusercontent.com/39606633/215580585-58e6000d-b6cf-430a-b4b7-d14a77eab3bd.png) </details> <details> <summary>Detailed Allocbench Results</summary> ``` kv/r=0/access=skew master median cost(gb):05.81 cpu(%):14.97 write(%):37.83 stddev cost(gb):01.87 cpu(%):03.98 write(%):07.01 cpu rebalancing median cost(gb):08.76 cpu(%):14.42 write(%):36.61 stddev cost(gb):02.66 cpu(%):01.85 write(%):04.80 kv/r=0/ops=skew master median cost(gb):06.23 cpu(%):26.05 write(%):57.33 stddev cost(gb):02.92 cpu(%):05.83 write(%):08.20 cpu rebalancing median cost(gb):04.28 cpu(%):11.45 write(%):31.28 stddev cost(gb):02.25 cpu(%):02.51 write(%):06.68 kv/r=50/ops=skew master median cost(gb):04.36 cpu(%):22.84 write(%):48.09 stddev cost(gb):01.12 cpu(%):02.71 write(%):05.51 cpu rebalancing median cost(gb):04.64 cpu(%):13.49 write(%):43.05 stddev cost(gb):01.07 cpu(%):01.26 write(%):08.58 kv/r=95/access=skew master median cost(gb):00.00 cpu(%):09.51 write(%):01.24 stddev cost(gb):00.00 cpu(%):01.74 write(%):00.27 cpu rebalancing median cost(gb):00.00 cpu(%):05.66 write(%):01.31 stddev cost(gb):00.00 cpu(%):01.56 write(%):00.26 kv/r=95/ops=skew master median cost(gb):0.00 cpu(%):47.29 write(%):00.93 stddev cost(gb):0.09 cpu(%):04.30 write(%):00.17 cpu rebalancing median cost(gb):0.00 cpu(%):08.16 write(%):01.30 stddev cost(gb):0.01 cpu(%):04.59 write(%):00.20 ``` </details> resolves: #95380 Release note (ops change) Add option to balance cpu time (cpu) instead of queries per second (qps) among stores in a cluster. This is done by setting `kv.allocator.load_based_rebalancing.objective='cpu'`. `kv.allocator.cpu_rebalance_threshold` is also added, similar to `kv.allocator.qps_rebalance_threshold` to control the target range for store cpu above and below the cluster mean. 96440: ui: add execution insights to statement and transaction fingerprint details r=ericharmeling a=ericharmeling This commit adds execution insights to the Statement Fingerprint and Transaction Fingerprint Details pages. Part of #83780. Loom: https://www.loom.com/share/98d2023b672e43fa8016829aa641a829 Note that the SQL queries against the `*_execution_insights` tables are updated to `SELECT DISTINCT ON (*_fingerprint_id, problems, causes)` (equivalent to `GROUP BY (*_fingerprint_id, problems, causes)`) from the latest results in the tables, rather than `row_number() OVER ( PARTITION BY stmt_fingerprint_id, problem, causes ORDER BY end_time DESC ) AS rank... WHERE rank = 1`. Both patterns return the same result, but one uses aggregation and the other uses a window function. I find the `DISTINCT ON/GROUP BY` pattern easier to understand, I'm not seeing much difference in the planning/execution time between the two over the same set of data, and I'm seeing `DISTINCT ON/GROUP BY` coming up as more performant in almost all the secondary sources I've encountered. Release note (ui change): Added execution insights to the Statement Fingerprint Details and Transaction Fingerprint Details Pages. 96828: collatedstring: support default, C, and POSIX in expressions r=otan a=rafiss fixes #50734 fixes #95667 informs #57255 --- ### collatedstring: create new package Move the small amount of code from tree/collatedstring.go --- ### collatedstring: support C and POSIX in expressions Release note (sql change): Expressions of the form `COLLATE "default"`, `COLLATE "C"`, and `COLLATE "POSIX"` are now supported. Since the default collation cannot be changed currently, these expressions are all equivalent. The expressions are evaluated by treating the input as a normal string, and ignoring the collation. This means that comparisons between strings and collated strings that use "default", "C", or "POSIX" are now supported. Creating a column with the "C" or "POSIX" collations is still not supported. 96870: kvserver: use replicasByKey addition func in snapshot path r=tbg a=pavelkalinnikov This commit makes one step towards better code sharing between `Replica` initialization paths: split trigger and snapshot application. It makes both to use the same method to check and insert the initialized `Replica` to `replicasByKey` map. Touches #94912 96874: roachtest: run scheduled backup only on clusters with enterprise license r=stevendanna a=msbutler Epic: none Release note: None 96883: go.mod: bump Pebble to 829675f94811 r=RaduBerinde a=RaduBerinde 829675f9 db: fix ObsoleteSize stat 2f086b74 db: refactor compaction splitting to reduce key comparisons Release note: None Epic: none Co-authored-by: Andy Yang <[email protected]> Co-authored-by: Austen McClernon <[email protected]> Co-authored-by: Eric Harmeling <[email protected]> Co-authored-by: Rafi Shamim <[email protected]> Co-authored-by: Pavel Kalinnikov <[email protected]> Co-authored-by: Michael Butler <[email protected]> Co-authored-by: Radu Berinde <[email protected]>

This commit adds a column for the distinct insight types for each fingerprint listed on the Statement and Transaction Fingerprint Overview pages. Fixes cockroachdb#83780. Release note (ui change): Added Insights column to the SQL Activity Fingerprint Overview pages of the DB Console.

This commit adds a column for the distinct insight types for each fingerprint listed on the Statement and Transaction Fingerprint Overview pages. The commit also reverts some of the additional middleware added in service of insights on the fingerprint details pages, in favor of reusing existing insights middleware. Fixes cockroachdb#83780. Release note (ui change): Added Insights column to the SQL Activity Fingerprint Overview pages of the DB Console.

maryliag added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Jul 4, 2022

blathers-crl bot added the T-sql-observability label Jul 4, 2022

maryliag assigned ericharmeling Sep 28, 2022

exalate-issue-sync bot unassigned ericharmeling Oct 13, 2022

ericharmeling self-assigned this Nov 1, 2022

ericharmeling mentioned this issue Nov 10, 2022

ui: add insight count to sql activity execution fingerprint overviews #91687

Closed

ericharmeling mentioned this issue Nov 21, 2022

ui: add time picker to insights pages #92285

Merged

This was referenced Nov 29, 2022

ui: link to waiting, blocking txn details from waiting txn details #87876

Closed

Acceptance testing 22.2 - Transaction high contention insights do not link users to transaction that blocked it #87784

Closed

ericharmeling mentioned this issue Dec 28, 2022

ui: transaction insight details page hangs on refresh in CC console #94380

Closed

ericharmeling mentioned this issue Feb 2, 2023

ui: add execution insights to statement and transaction fingerprint details #96440

Merged

ericharmeling mentioned this issue Feb 21, 2023

sql: add insights to statement, transaction statistics #97403

Closed

ericharmeling removed their assignment Mar 29, 2023

maryliag closed this as completed Nov 3, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add Insights information to SQL Activity page #83780

Add Insights information to SQL Activity page #83780

maryliag commented Jul 4, 2022 •

edited by exalate-issue-sync bot

Loading

Add Insights information to SQL Activity page #83780

Add Insights information to SQL Activity page #83780

Comments

maryliag commented Jul 4, 2022 • edited by exalate-issue-sync bot Loading

maryliag commented Jul 4, 2022 •

edited by exalate-issue-sync bot

Loading