show most used fingerprint per index #93087

maryliag · 2022-12-05T22:04:05Z

This issue tracks correlating the index details console page with transactions and statements (SQL Activity). Few options we can consider includes

Time range selector to enable users to filter and sort by various dimensions (e.g., executions stats, application name, etc)
Top N fingerprints based on executions stats (e.g., execution count, % total runtime, etc.)

Note the following flows that will guide users to this page:

Databases page -> ... -> Index details
Plan details page -> index details
Failed transaction (40001) conflicting location/list of conflicting transactions -> ... index details
Hot spots/ranges UX

@dongniwang

Jira issue: CRDB-22150

…tatistics This commits adds a new index `indexes_usage` virtual column on value `(statistics-> 'statistics'-> 'indexes')` with respective `indexes_usage_idx` index on `system.statement_statistics` table. Part Of cockroachdb#93087 Release note (sql change): Add column `indexes_usage` and index `indexes_usage_idx` on value on table `system.statement_statistics`.

93089: sql, clusterversion: add new index usage column on system.statement_statistics r=maryliag a=maryliag This commits adds a new index `indexes_usage` virtual column on value `(statistics-> 'statistics'-> 'indexes')` with respective `indexes_usage_idx` index on `system.statement_statistics` table. Part Of #93087 Performance Tests: YCSB Workload | branch | elapsed | errors | ops(total) | ops/sec(cum) | avg(ms) | p50(ms) | p95(ms) | p99(ms) | pMax(ms)| | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | main | 1860.0s | 0 | 37623701 | 20227.8 | 1.0 | 0.8 | 2.5 | 4.5 | 134.2 | | idx | 1860.0s | 0 | 44831436 | 24102.9 | 0.8 | 0.7 | 2.1 | 3.7 | 352.3 | TPC C Workload | branch | elapsed | tpmC | efc | avg(ms) | p50(ms) | p90(ms) | p95(ms) | p99(ms) | pMax(ms)| | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | main | 1860.0s | 12.4 | 96.3% | 29.8 | 29.4 | 35.7 | 39.8 | 44.0 | 46.1 | | idx | 1860.0s | 12.3 | 95.3% | 28.0 | 28.3 | 37.7 | 39.8 | 46.1 | 52.4 | Release note (sql change): Add column `indexes_usage` and index `indexes_usage_idx` on value on table `system.statement_statistics`. 93990: colexecjoin: clarify handling of INTERSECT ALL joins r=yuzefovich a=yuzefovich This commit clarifies how the INTERSECT ALL joins are handled by the hash joiner. Previously, this type of join would be handled via the same collection method as other non-outer joins with non-distinct keys. However, there is a crucial difference - `Same` slice would always remain unpopulated for INTERSECT ALL, so the collection method would be reduced to a much simpler version. This is the case since for this type of join we only need to include the left tuple once if it has a match and there is no need to traverse the hash chain trying to find more matches. In fact, the collection method for INTERSECT ALL is very similar to that of EXCEPT ALL with only a condition being negated, so this commit takes advantage of this observation and refactors `collectLeftAnti` to also power the INTERSECT ALL collection. This makes the code easier to follow and also allows us to not instantiate `Same` array for the INTERSECT ALL joins. Additionally, it allows us to remove some duplicated code in the `Check` function which was previously conditioned on `Same` being non-nil even though both branches had exactly the same code. The dead code in `distinctCollect` is also now removed. Epic: None Release note: None 94108: go.mod: bump Raft to 65a0bf3 r=nvanbenschoten a=nvanbenschoten This picks up etcd-io/raft#8. However, it doesn't enabled the `AsyncStorageWrites` configuration, so we do not expect any impact from the change. Let's hope there are no surprises. Release note: None Epic: CRDB-6037 94109: workloadccl: skip TestDeterministicInitialData to avoid flakes r=rytaft a=rytaft I'm skipping this test until I have time to debug the reason for the flake. Informs #93958 Release note: None Co-authored-by: maryliag <marylia@cockroachlabs.com> Co-authored-by: Yahor Yuzefovich <yahor@cockroachlabs.com> Co-authored-by: Nathan VanBenschoten <nvanbenschoten@gmail.com> Co-authored-by: Rebecca Taft <becca@cockroachlabs.com>

…tatistics This commits adds a new index `indexes_usage` virtual column on value `(statistics-> 'statistics'-> 'indexes')` with respective `indexes_usage_idx` index on `system.statement_statistics` table. Part Of cockroachdb#93087 Release note (sql change): Add column `indexes_usage` and index `indexes_usage_idx` on value on table `system.statement_statistics`.

Part Of cockroachdb#93087 This commits adds on the Index Details page the list of fingerprints that were used by that index. This first commit shows all statement fingerprints, follow PRs will add features such as: search, filter, time selection and listing Transactions. Release note (ui change): Adds on the Index Details page a list of all statement fingerprints that used that index.

The get the list of fingerprints used by an index, we use the `system.statement_statistics` directly, but non-admins don't have access to system table. Until cockroachdb#95756 or cockroachdb#95770 are done, we need to hide this feature for non-admins. Part Of cockroachdb#93087 Release note (ui change): Hide list of used fingerprint per index on Index Details page, for non-admin users.

94153: sql: Remove type annotations from `column_default` in `information_schema.columns` r=ZhouXing19 a=e-mbrown Informs: #87774 The type annotation on `column_default` caused confusion in postgres compatible apps. This change formats the `column_default` without the type annotation. It does not address the incorrect type annotation. Release note (bug fix): The content of `column_default` in `information_schema.columns` no longer have type annotations. 95855: backupccl: run restore/tpce/32tb/aws/nodes=15/cpus=16 once a week r=benbardin a=msbutler This patch adds the restore/tpce/32TB/aws/nodes=15/cpus=16 test to our weekly test suite. The backup fixture was generated by initing a tpce cluster with 2 Million customers, and running this workload while 48 incremental backups were taken at 15 minute increments. The roachtest restores from a system time captured in the 24th backup. The cluster used to restore the backup will run on aws with 15 nodes each with 16 vcpus. To verify this test exists, run: `roachtest list tag:weekly` Release note: None Epic: none 95861: kv: remove lastToReplica and lastFromReplica from raftMu r=nvanbenschoten a=nvanbenschoten Extracted from #94165 without modification. ---- In 410ef29, we moved these fields from under the Replica.mu to under the Replica.raftMu. This was done to avoid lock contention. In this commit, we move these fields under their own mutex so that they can be accessed without holding the raftMu. This allows us to send Raft messages from other goroutines. The commit also switches from calling RawNode.ReportUnreachable directly in sendRaftMessage to using the more flexible unreachablesMu set, which defers the call to ReportUnreachable until the next Raft tick. As a result, the commit closes #84246. Release note: None Epic: None 95876: sql: remove round-trips from common DISCARD ALL r=ajwerner a=ajwerner #### sql: avoid checking if a role exists when setting to the current role This is an uncached round-trip. It makes `DISCARD` expensive. In #86485 we implemented `SET SESSION AUTHORIZATION DEFAULT`, this added an extra sql query to `DISCARD ALL` to check if the role we'd become exists. This is almost certainly unintentional in the case where the role we'd become is the current session role. I think this just happened because of code consolidation. We now no longer check if the current session role exists. This removes the last round-trip from DISCARD ALL. #### sql: use in-memory session data to decide what to do in DISCARD In #86246 we introduced logic to discard schemas when running DISCARD ALL and DISCARD TEMP. This logic did expensive kv operations unconditionally; if the session knew it had never created a temporary schema, we'd still fetch all databases and proceed to search all databases for a temp schema. This is very expensive. Fixes: #95864 Release note (performance improvement): In 22.2, logic was added to make `SET SESSION AUTHORIZATION DEFAULT` not a no-op. This implementation used more general code for setting the role for a session which made sure that the role exists. The check for whether a role exists is currently uncached. We don't need to check if the role we already are exists. This improves the performance of `DISCARD ALL` in addition to `SET SESSION AUTHORIZATION DEFAULT`. Release note (performance improvement): In 22.2, we introduced support for `DISCARD TEMP` and made `DISCARD ALL` actually discard temp tables. This implementation ran expensive logic to discover temp schemas rather than consulting in-memory data structures. As a result, `DISCARD ALL`, which is issued regularly by connection pools, became an expensive operation when it should be cheap. This problem is now resolved. 95997: ui: hide list of statement for non-admins r=maryliag a=maryliag To get the list of fingerprints used by an index, we use the `system.statement_statistics` directly, but non-admins don't have access to system table. Until #95756 or #95770 are done, we need to hide this feature for non-admins. Part Of #93087 Release note (ui change): Hide list of used fingerprint per index on Index Details page, for non-admin users. 95998: roachtest: update activerecord blocklist r=ZhouXing19 a=andyyang890 This patch removes a few tests from the blocklist that do not exist in the test suite for rails 7.0.3, which were being run prior to the fix for version pinning. Informs #94211 Release note: None 96003: kv: add MVCC local timestamp details to ReadWithinUncertaintyIntervalError r=nvanbenschoten a=nvanbenschoten This PR adds details about MVCC local timestamps to ReadWithinUncertaintyIntervalErrors. This provides more information about the cause of uncertainty errors. The PR also includes a series of minor refactors to clean up this code. Release note: None Epic: None Co-authored-by: e-mbrown <ebsonari@gmail.com> Co-authored-by: Michael Butler <butler@cockroachlabs.com> Co-authored-by: Nathan VanBenschoten <nvanbenschoten@gmail.com> Co-authored-by: Andrew Werner <awerner32@gmail.com> Co-authored-by: maryliag <marylia@cockroachlabs.com> Co-authored-by: Andy Yang <yang@cockroachlabs.com>

Fixes cockroachdb#93087 This commit adds search, filters and time picker for the list of fingerprints most used per index, on the Index Details page. It also limits to 20 fingerprints until we can make improvements to use pagination on our calls with sql-over-http. Release note (ui change): Add search, filter and time picker for the list of most used statement fingerprints on the Index Details page.

96112: ui: add search, filter and time picker on index details r=maryliag a=maryliag Fixes #93087 This commit adds search, filters and time picker for the list of fingerprints most used per index, on the Index Details page. It also limits to 20 fingerprints until we can make improvements to use pagination on our calls with sql-over-http. With no filters <img width="1669" alt="Screen Shot 2023-01-27 at 1 37 35 PM" src="https://user-images.githubusercontent.com/1017486/215167830-bd119e13-a875-49bd-9c8d-305230dd4b01.png"> With filters <img width="1508" alt="Screen Shot 2023-01-27 at 1 37 49 PM" src="https://user-images.githubusercontent.com/1017486/215167846-c668d79a-7a59-420e-b96c-2eff6a50aca6.png"> https://www.loom.com/share/d4129452593a40198902a5f2539d8568 Release note (ui change): Add search, filter and time picker for the list of most used statement fingerprints on the Index Details page. 96117: schemachanger: improve state management r=postamar a=postamar This commit endows the scpb.CurrentState with the set of element statuses which are in force at the beginning of the statement transaction. These are then used when planning the pre-commit reset stage. This commit also rewrites the target-setting logic in the scbuild package in a more explicit manner with extra commentary. This commit does not change any functional behavior in production. Informs #88294. Release note: None 96232: sql: move copy_in_test to pkg/sql/copy to have all copy tests together r=cucaroach a=cucaroach Epic: CRDB-18892 Informs: #91831 Release note: None 96272: backupccl,changefeedccl: properly display name if resource exists r=msbutler,HonoreDB a=renatolabs Previously, we were passing a `*string` to a string formatting function (`pgnotice.Newf`) with the `%q` verb. This leads to messages being displayed to the user that look like: ``` NOTICE: schedule %!q(*string=0xc006b324e0) already exists, skipping ``` This properly dereferences the pointer before printing. Epic: none Release note: None Co-authored-by: maryliag <marylia@cockroachlabs.com> Co-authored-by: Marius Posta <marius@cockroachlabs.com> Co-authored-by: Tommy Reilly <treilly@cockroachlabs.com> Co-authored-by: Renato Costa <renato@cockroachlabs.com>

maryliag added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) A-sql-observability Related to observability of the SQL layer T-sql-observability labels Dec 5, 2022

maryliag self-assigned this Dec 5, 2022

maryliag mentioned this issue Dec 5, 2022

sql, clusterversion: add new index usage column on system.statement_statistics #93089

Merged

maryliag mentioned this issue Jan 6, 2023

ui: add list of fingerprints used by index #94863

Merged

maryliag mentioned this issue Jan 26, 2023

ui: hide list of statement for non-admins #95997

Merged

maryliag mentioned this issue Jan 27, 2023

ui: add search, filter and time picker on index details #96112

Merged

craig bot closed this as completed in 0b405dc Jan 31, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

show most used fingerprint per index #93087

show most used fingerprint per index #93087

maryliag commented Dec 5, 2022 •

edited by kevin-v-ngo

Loading

show most used fingerprint per index #93087

show most used fingerprint per index #93087

Comments

maryliag commented Dec 5, 2022 • edited by kevin-v-ngo Loading

maryliag commented Dec 5, 2022 •

edited by kevin-v-ngo

Loading