Collect additional Session details such as sessions variables and the last statement execution timestamp #74257

kevin-v-ngo · 2021-12-23T21:05:32Z

There has been feedback on improving SQL observability at the Sessions level. Specifically, we should provide additional observability information into Sessions such as

#transaction executions (done)
List of transaction fingerprints (IDs) (done)
total 'active' time (done)
Session variables
Last statement execution time (today we have oldest_query_start which represents the current start time of the active statement)
Last transaction execution time and the text
Most Recent Transaction (entire SQL text of the most recent transaction)

Feedback from customers:

It is not easily identifiable as to what is keeping a session connection open
Are connection parameters having an adverse affect within the environment?

Related issue: #67888

Epic: CRDB-9087

Jira issue: CRDB-11992

Partially addresses cockroachdb#74257. Previously, the sql server did not provide additional session details such as total number of transactions executed, transaction fingerprint IDs, and total active time. This change adds the aforementioned session details to the `serverpb.Session` struct. The transaction fingerprint IDs are collected from the `ApplicationStats` for each connExecutor. The total number of transactions executed is the length of the list of transaction fingerprint IDs. The total active time is calculated by a `timeutil.StopWatch` which is started and stopped when a transaction is started, restarted, and finished. Release note: none

Partially addresses cockroachdb#74257. Previously, the sql server did not provide additional session details such as total number of transactions executed, transaction fingerprint IDs, and total active time. This change adds the aforementioned session details to the `serverpb.Session` struct. The transaction fingerprint IDs are collected from the `ApplicationStats` for each connExecutor. The total number of transactions executed is accumulated using the `txnCounter` from the `extraTxnState` in `connExecutor`. The total active time is calculated by a `timeutil.StopWatch` which is started and stopped when a transaction is started, restarted, and finished. Release note: none

Partially addresses cockroachdb#74257. Previously, the sql server did not provide additional session details such as total number of transactions executed, transaction fingerprint IDs, and total active time. This change adds the aforementioned session details to the `serverpb.Session` struct. To track transaction fingerprint IDs, a new circular buffer `TxnFingerprintIDBuffer` is introduced with its corresponding cluster setting `TxnFingerprintIDBufferCapacity` which controls the capacity. The total number of transactions executed is accumulated using the `txnCounter` from the `extraTxnState` in `connExecutor`. The total active time is calculated by a `timeutil.StopWatch` which is started and stopped when a transaction is started, restarted, and finished. Release note (api change): the `serverpb.Session` struct now has three new fields: number of transactions executed, transaction fingerprint IDs, and total active time.

Partially addresses cockroachdb#74257. Previously, the status server did not provide session details such as total number of transactions executed, transaction fingerprint IDs, and total active time. This change adds the aforementioned session details to the `serverpb.Session` struct. To track recently executed transaction fingerprint IDs, a FIFO cache `TxnFingerprintIDCache` is introduced with its corresponding cluster setting `TxnFingerprintIDBufferCapacity` to control the capacity. The default capacity is set at 100 fingerprints. The total number of transactions executed is filled using the existing `txnCounter` from the `extraTxnState` in `connExecutor`. The total active time is calculated by introducing a `timeutil.StopWatch` to the connection executor, which is started and stopped when a transaction is started and finished respectively. Release note (api change): the `serverpb.Session` struct now has three new fields: number of transactions executed, transaction fingerprint IDs, and total active time.

82352: server, sql: surface session txnCount, recent txn fingerprints, active time r=xinhaoz a=xinhaoz Finishing up Gerardo's PR, original review here: #80717 -------------------------------------------------- Partially addresses #74257. Previously, the status server did not provide session details such as total number of transactions executed, transaction fingerprint IDs, and total active time. This change adds the aforementioned session details to the `serverpb.Session` struct. To track recently executed transaction fingerprint IDs, a FIFO cache `TxnFingerprintIDCache` is introduced with its corresponding cluster setting `TxnFingerprintIDBufferCapacity` to control the capacity. The default capacity is set at 100 fingerprints. The total number of transactions executed is filled using the existing `txnCounter` from the `extraTxnState` in `connExecutor`. The total active time is calculated by introducing a `timeutil.StopWatch` to the connection executor, which is started and stopped when a transaction is started and finished respectively. Release note (api change): the `serverpb.Session` struct now has three new fields: number of transactions executed, transaction fingerprint IDs, and total active time. 82623: backupinfo: introduce a backupinfo package r=stevendanna a=adityamaru The backupinfo package contains logic related to interacting with information and metadata describing the backup. After this change we have `backupdest` depending on `backupinfo`. Release note: None 82718: kvserver: emit MVCC range tombstones over rangefeeds r=aliher1911 a=erikgrinaker This patch adds MVCC range tombstone support in rangefeeds. Whenever an MVCC range tombstone is written, a new `MVCCDeleteRangeOp` logical op is recorded and emitted across the rangefeed as a `RangeFeedDeleteRange` event. MVCC range tombstones will only be written when the `MVCCRangeTombstones` version gate has been enabled. Changefeeds will emit an error for these events. We do not expect to see these in online spans with changefeeds, since they are initially only planned for use with schema GC and import rollbacks. The rangefeed client library has been extended with support for these events, but no existing callers handle them for the same reason as changefeeds. Initial scans do not emit regular tombstones, and thus not range tombstones either, but catchup scans will emit them if encountered. This patch has rudimentary testing of MVCC range tombstones in rangefeeds. A later patch will add a data-driven test harness for rangefeeds with more exhaustive tests. Resolves #82449. Touches #70433. Release note: None 82936: sql/schemachanger: implement DROP OWNED BY r=jasonmchan a=jasonmchan Previously, we did not support the DROP OWNED BY statement (#55381). This commit adds partial support for DROP OWNED BY in the declarative schema changer. Followup work is needed to support the CASCADE modifier. Release note (sql change): Support `DROP OWNED BY`. 83229: ui: remove option 10/30 min from SQL Activity page r=maryliag a=maryliag Note to reviewers: only 2nd commit is relevant to this PR Previously we had the options for 10 and 30min on SQL Activity pages, which created some confusion, since we would always show the last 1h info. This commit remove those 2 options. If the user select any of those options on the Metrics page, it will get updated to 1h on the SQL Activity pages. <img width="444" alt="Screen Shot 2022-06-22 at 5 43 53 PM" src="https://user-images.githubusercontent.com/1017486/175144243-2f084e0b-5e09-4874-9640-e7eea6179343.png"> https://www.loom.com/share/226e54322df6456aa2039b5c54f72eb1 Fixes #82914 Release note (ui change): Removal of the 10 and 30min options on the SQL Activity page. 83420: ui: improve tooltip UX with text updates r=ericharmeling a=ericharmeling Fixes #81374. Fixes #83256. Fixes #81248. Fixes #79018. Note the following: - The updates resolving #79018 effectively revert the tooltip text for Rows Read to the original wording (which [was updated for accuracy](e379e9d#diff-492398441e971e355a687a4ce333a9766e2195287d0227682444d5dc0eb7ee1a)). I assume this is okay. `@kevin-v-ngo` - The updates resolving #81248 do not in fact refer to the time intervals as date ranges, as this language is misleading (a 1h interval is an interval and not a date range). Instead, this update just removes the anchor and the link to the non-existent Interval Range section of https://www.cockroachlabs.com/docs/stable/ui-statements-page.html. We may want to consider updating the docs to call the "time picker" data type a time interval and not a date range. This appears to have been the case in previous releases (https://www.cockroachlabs.com/docs/v21.1/ui-statements-page#time-interval). `@stbof` Release note (ui change): Updated tooltips on the Statements and Transactions pages in the DB Console for improved UX. 83428: sql: rename anonymizedStmt in sqlstats pkg to stmtNoConstants r=ericharmeling a=ericharmeling Note that this commit does not change any files outside the sqlstats package. Fixes #80725. Release note: None 83468: ui: update all dates to use same format r=maryliag a=maryliag Update all dates to use the same format. Fixes #81159 Release note: None 83520: kv: don't try to reject lease transfer when flushing proposal buffer r=nvanbenschoten a=nvanbenschoten Fixes #83498. Fixes #83402. Fixes #83308. This was fallout from #82758. This commit adds logic to `propBuf.maybeRejectUnsafeProposalLocked` to avoid trying to reject proposals based on the state of the raft group when the group is not provided (e.g. when flushing the buffer). We already had this logic for `RequestLease` (indirectly), but did not for `TransferLease`. Co-authored-by: Gerardo Torres <[email protected]> Co-authored-by: Aditya Maru <[email protected]> Co-authored-by: Erik Grinaker <[email protected]> Co-authored-by: Jason Chan <[email protected]> Co-authored-by: Marylia Gutierrez <[email protected]> Co-authored-by: Eric Harmeling <[email protected]> Co-authored-by: Nathan VanBenschoten <[email protected]>

blathers-crl bot added the T-sql-observability label Dec 23, 2021

kevin-v-ngo mentioned this issue Jan 28, 2022

Provide a historical view of all sessions including session statistics in the DB console and SQL CLI #67888

Closed

kevin-v-ngo mentioned this issue Feb 8, 2022

ui: Add column selector and filter to sessions page #75965

Merged

gtr mentioned this issue Apr 28, 2022

server, sql: collect additional session details #80717

Closed

jlinder added sync-me-3 and removed sync-me-3 labels May 24, 2022

xinhaoz mentioned this issue Jun 2, 2022

server, sql: surface session txnCount, recent txn fingerprints, active time #82352

Merged

kevin-v-ngo mentioned this issue Aug 10, 2022

Sessions page: Add Active Session Duration, Transaction Execution Count, and Recent Transaction Fingerprint Ids #85912

Closed

maryliag closed this as completed Nov 3, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Collect additional Session details such as sessions variables and the last statement execution timestamp #74257

Collect additional Session details such as sessions variables and the last statement execution timestamp #74257

kevin-v-ngo commented Dec 23, 2021 •

edited

Loading

Collect additional Session details such as sessions variables and the last statement execution timestamp #74257

Collect additional Session details such as sessions variables and the last statement execution timestamp #74257

Comments

kevin-v-ngo commented Dec 23, 2021 • edited Loading

kevin-v-ngo commented Dec 23, 2021 •

edited

Loading