Capture historical executions and their statistics #86955

kevin-v-ngo · 2022-08-26T18:14:48Z

This issue tracks capturing and surfacing recently completed/failed executions. This enhances our 'active executions' view with 'recently active' to help users continue to troubleshoot workload performance in near real time.

The current experience for active executions is subpar due to executions disappearing from the UI during ongoing investigations. The retention period (row-based) should limited due to the potential volume executions.

Related issues:

Feature brief: https://cockroachlabs.atlassian.net/wiki/spaces/SQLOBS/pages/2228224025/Real-time+troubleshooting+for+an+active+workload+90

Jira issue: CRDB-19058

Epic: CRDB-24527

This change implements and tests a new RecentStatementsCache that is used to store the recent statements that were executed. This RecentStatementsCache is added to the ExecutorConfig. The Server writes to this cache whenever a statement finishes. The Server also filters data in the cache by Session ID, and surfaces the data through the ListSessions API. This change also adds Cancelled, Timed Out, Completed, and Failed as possible query phases. A future PR for the UI will implement reading from this cache, and displaying the statement information and status on the frontend. Another future PR will tune the Cache Capacity cluster setting, and possibly add a Time To Live cluster setting. Partially Closes cockroachdb#86955 Release note: None

This change implements and tests a new RecentStatementsCache that is used to store the recent statements that were executed. This RecentStatementsCache is added to the ExecutorConfig. The Server writes to this cache whenever a statement finishes. The Server also filters data in the cache by Session ID, and surfaces the data through the ListSessions API. This change also adds Cancelled, Timed Out, Completed, and Failed as possible query phases. A future PR for the UI will implement reading from this cache, and displaying the statement information and status on the frontend. Another future PR will tune the Cache Capacity cluster setting, and possibly add a Time To Live cluster setting. Partially Closes cockroachdb#86955 Release note (general change): Added a new recent statements field to the ListSessions API that contains information on recently completed statements.

This change implements and tests a new RecentStatementsCache that is used to store the recent statements that were executed. This RecentStatementsCache is added to the ExecutorConfig. The Server writes to this cache whenever a statement finishes. The Server also filters data in the cache by Session ID, and surfaces the data through the ListSessions API. This change also adds Cancelled, Timed Out, Completed, and Failed as possible query phases. A future PR for the UI will implement reading from this cache, and displaying the statement information and status on the frontend. Another future PR will tune the Cache Capacity cluster setting, and possibly add a Time To Live cluster setting. Part Of cockroachdb#86955 Release note (sql change): Added a new recent statements field to the ListSessions API that contains information on recently completed statements.

This change implements and tests a new RecentStatementsCache that is used to store the recent statements that were executed. The cache has two new cluster settings that tune the capacity and time that a statement lives in the cache. This RecentStatementsCache is added to the ExecutorConfig. The Server writes to this cache whenever a non-internal statement finishes. The Server also filters data in the cache by Session ID, and surfaces the data through the ListSessions API. This change also adds Canceled, Timed Out, Completed, and Failed as possible query phases. A future PR for the UI will implement reading from this cache, and displaying the statement information and status on the frontend. Part Of cockroachdb#86955 Release note (sql change): Added a new recent statements field to the ListSessions API that contains information on recently completed statements.

This change renames the active execution types and functions to "recent" types and functions. This is in preparation for the addition of recent executions to the current active executions table. The file names will be changed in a later PR. Part of cockroachdb#86955 Release note: None

…ter-ui This change renames the active execution types and functions to "recent" types and functions. This is in preparation for the addition of recent executions to the current active executions table. Part of cockroachdb#86955 Release note: None

…console This change renames the active execution types and functions to "recent" types and functions. This is in preparation for the addition of recent executions to the current active executions table. Part of cockroachdb#86955 Release note: None

This change implements and tests a new RecentStatementsCache that is used to store the recent statements that were executed. The cache has two new cluster settings that tune the capacity and time that a statement lives in the cache. This RecentStatementsCache is added to the ExecutorConfig. The Server writes to this cache whenever a non-internal statement finishes. The Server also filters data in the cache by Session ID, and surfaces the data through the ListSessions API. This change also adds Canceled, Timed Out, Completed, and Failed as possible query phases. A future PR for the UI will implement reading from this cache, and displaying the statement information and status on the frontend. Part Of cockroachdb#86955 Fixes cockroachdb#91556 Release note (sql change): Added a new recent statements field to the ListSessions API that contains information on recently completed statements.

Previously, we only displayed Active Statements on the SQL Activity Page. This change adds Recent Statements to the previous Active Executions table. This change also fixes pagination on that table, and adds four new possible Execution Statuses: Failed, Timed Out, Canceled, and Completed, along with a corresponding icon color. Part of cockroachdb#86955 Release note (ui change): adds Recent Statements to the SQL Activity page

This change implements and tests a new RecentStatementsCache that is used to store the recent statements that were executed. The cache has two new cluster settings that tune the capacity and time that a statement lives in the cache. This change also implements writing ActiveQueries to the cache. The RecentStatementsCache is added to the ExecutorConfig, and the Server writes to this cache whenever a non-internal statement finishes. This change also removes queryMeta.phase, and replaces it with ActiveQuery.Phase. The phases Canceled, Timed Out, Completed, and Failed are also as possible values to ActiveQuery.Phase. Part Of cockroachdb#86955 Fixes cockroachdb#91556 Release note: None

This change implements the ListRecentStatements API that returns all of the statements in the Recent Statements Cache. A call to this API populates two new virtual tables: cluster/node_recent_statements. This PR must be merged after the recent statements cache PR: cockroachdb#93270. A future PR will implement reading from these virtual tables in order to display recent statement info on the UI. Part Of cockroachdb#86955 Release note (sql change): Added the ListRecentStatements API and cluster/node_recent_statements tables. These contain information on recently completed statements.

This change implements reading from the recent_statements virtual tables using sql over http. It then adds the info from the tables to redux, and displays it on the SQL Activity Page under a Recent Executions tab. This has only been tested on DB Console. Changed should be tested on CC Console when the new virtual tables are added to the staging cluster. This PR must be merged after the recent statements cache and virtual tables PRs: cockroachdb#93270 and cockroachdb#94158. Part Of cockroachdb#86955 Release note (ui change): Adds recent statements to the SQL Activity Page on DB Console.

93270: sql, server: implement and write to new recent statements cache r=amyyq2 a=amyyq2 This change implements and tests a new RecentStatementsCache that is used to store the recent statements that were executed. The cache has two new cluster settings that tune the capacity and time that a statement lives in the cache. This change also implements writing ActiveQueries to the cache. The RecentStatementsCache is added to the ExecutorConfig, and the Server writes to this cache whenever a non-internal statement finishes. This change also removes queryMeta.phase, and replaces it with ActiveQuery.Phase. The phases Canceled, Timed Out, Completed, and Failed are also as possible values to ActiveQuery.Phase. Part Of #86955 Fixes #91556 Release note: None 94102: build: add tooling to mirror NPM dependencies to GCS r=sjbarag a=sjbarag Note that this doesn't actually _apply_ these changes yet! That'll happen in a separate PR so we can have a better discussion about the mechanics involved here (and also to make backporting significantly simpler) ----- CockroachDB's NPM dependencies are currently provided by the git submodule in pkg/ui/yarn-vendored [1] and are installed during the Bazel build with the '--offline' flag [2] to avoid relying on public NPM registries. Bazel's rules_nodejs unfortunately doesn't work well with dependencies that are vendored on-disk in the Bazel workspace via yarn-offline-mirror, which led to some unfortunate hacks [3,4] being introduced during the initial Bazel setup. In the time since those hacks were added, two significant events occured: 1. Bazel's rules_nodejs was deprecated in favor of rules_js [5], which manually implements the pnpm resolver algorithm and doesn't support yarn's yarn-offline-mirror configuration value. 2. Bazel 6 was released [6], which removed the 'managed_directories' attribute to the top-level workspace() rule that makes yarn_install work in CockroachDB at all. The use of yarn-vendored therefore blocks both migration away from rules_nodejs and an upgrade to Bazel 6. Instead of relying on a git submodule and --offline installation, it's possible to use the pattern already used by CockroachDB's go dependencies: copy them from public locations to a storage bucket managed by Cockroach Labs, then ensure the bucket is used for future downloads. Doing so allows the yarn_vendored submodule and --offline installation to be removed in favor of _online_ builds from a location Cockroach Labs controls, thus unblocking both a migration away from rules_nodejs and an upgrade to Bazel 6. Add a //pkg/cmd/mirror/npm tree (and supporting ./dev tooling) that downloads NPM dependencies from public registries, uploads them to a public-readable bucket, and rewrites yarn.lock files to use that bucket, along with a test to ensure those mirrored dependencies continue to be used in the future. [1] https://github.com/cockroachdb/yarn-vendored [2] https://classic.yarnpkg.com/en/docs/cli/install#toc-yarn-install-offline [3] ./build/bazelutil/seed_yarn_cache.bzl [4] ./build/bazelutil/seed_yarn_cache.sh [5] https://blog.aspect.dev/rulesjs-launch [6] https://github.com/bazelbuild/bazel/releases/tag/6.0.0 Part of: #85038 Release note: None 94147: release: pass GCS credentials to publish-provisional-artifacts r=rickystewart a=rail Previously, publish-provisional-artifacts requires `GOOGLE_APPLICATION_CREDENTIALS` environment variable when `--gcs-bucket` is passed. This was not the case for the case where we upload the latest binaries (`--bless`). This PR adds required steps to set up and pass the credentials. Epic: none Release note: None Co-authored-by: amyyq2 <[email protected]> Co-authored-by: Sean Barag <[email protected]> Co-authored-by: Rail Aliiev <[email protected]>

This change implements a new RecentTransactionsCache that is used to store the recent transactions that were executed. This new cache "wraps around" the existing RecentStatements Cache, as each txn has a cache of stmts associated with it. The cache also has two new cluster settings that tune the capacity and time that a transaction lives in the cache. This change also implements a new RecentTransactions API and new recent transactions virtual tables. The virtual tables are a WIP, as we still need to add all the necessary fields from TxnInfo. Future work for implementing recent transactions includes: - testing adding to the transactions cache - fixing up the cache implementation to be consistent with the statements cache - fixing and testing the GetRecentTransactions API that populates new recent transactions virtual tables - surfacing information from the virtual tables onto the UI, this should be very similar to the UI implementation for statements Part Of cockroachdb#86955 Release note: None

This change is the beginning implementation/thoughts behind paginating query responses for recent statements. Paginating this response is necessary for the future, because our current max response size does not allow us to display every statement inside the cache. Part Of cockroachdb#86955 Release note: None

94793: sql, server: revert new recent statements cache r=matthewtodd a=matthewtodd This reverts #93270. We've seen a number of roachtest and roachperf failures as a result of this work, so let's revert until we can address them. Part of #86955 Addresses #94205 Addresses #94371 Addresses #94676 Release note: None Co-authored-by: Matthew Todd <[email protected]>

Part of #86955. This is a re-implementation of #93270, solely focusing on adding the in-memory cache for recent stmts. This commit introduces two new cluster settings: `sql.statements_cache.capacity` which is the maximum number of statements in the cache, and `sql.statements_cache.time_to_live` which is the maximum time to live for an statement in the cache. Release note: None

Part of cockroachdb#86955. This is a re-implementation of cockroachdb#93270, solely focusing on adding the in-memory cache for recent stmts. This commit introduces two new cluster settings: `sql.statements_cache.capacity` which is the maximum number of statements in the cache, and `sql.statements_cache.time_to_live` which is the maximum time to live for an statement in the cache. Release note: None

kevin-v-ngo added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Aug 26, 2022

blathers-crl bot added the T-sql-observability label Aug 26, 2022

kevin-v-ngo assigned maryliag Aug 29, 2022

maryliag assigned amyyq2 and unassigned maryliag Sep 6, 2022

amyyq2 mentioned this issue Oct 24, 2022

sql, server: implement writing/reading from new recent statements cache #90567

Closed

amyyq2 mentioned this issue Dec 22, 2022

server, sql: add ListRecentStatements api and virtual table #94158

Open

amyyq2 mentioned this issue Dec 22, 2022

ui: add recent statements to sql activity page #94159

Closed

amyyq2 mentioned this issue Dec 22, 2022

sql, server: implement adding to new recent transactions cache #94168

Draft

amyyq2 mentioned this issue Dec 22, 2022

ui: initial implementation for paginating recent stmts response #94174

Draft

matthewtodd mentioned this issue Jan 5, 2023

sql, server: revert new recent statements cache #94793

Merged

maryliag assigned matthewtodd and unassigned amyyq2 Jan 13, 2023

maryliag unassigned matthewtodd Feb 27, 2023

gtr self-assigned this Apr 18, 2023

gtr mentioned this issue Apr 18, 2023

sql: re-add in-memory cache for recent stmts #101740

Closed

exalate-issue-sync bot added T-observability and removed T-sql-observability labels Mar 21, 2024

exalate-issue-sync bot unassigned gtr Jul 29, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Capture historical executions and their statistics #86955

Capture historical executions and their statistics #86955

kevin-v-ngo commented Aug 26, 2022 •

edited

Loading

Capture historical executions and their statistics #86955

Capture historical executions and their statistics #86955

Comments

kevin-v-ngo commented Aug 26, 2022 • edited Loading

kevin-v-ngo commented Aug 26, 2022 •

edited

Loading