sql: virtual table interface for resolved contention events #75904
Assignees
Labels
A-sql-observability
Related to observability of the SQL layer
C-enhancement
Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Comments
|
Hi @Azhng, please add a C-ategory label to your issue. Check out the label system docs. While you're here, please consider adding an A- label to help keep our repository tidy. 🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan. |
20 tasks
Azhng
added
A-sql-observability
Azhng
added
the
C-enhancement
Azhng
added a commit
to Azhng/cockroach
that referenced
this issue
Feb 24, 2022
This commit introduces `crdb_internal.historical_contention_events` virtual table. This virtual tables exposes historical contention events annoated with transaction fingerprint IDs for transactions that finishes execution. This allows this virtual table to be joined into the statement statistics and transaction statistics table. The new virtual table require at least VIEWACTIVITYREDACTED permission to access. However, in order to view the contending keys, it would require at least VIEWACTIVITY permission or above. Resolves cockroachdb#75904 Release note (sql change): introducing `crdb_internal.historical_contention_events` virtual table, that exposes historical contention events. The events exposed in the new virtual table also include transaction fingerprint IDs for both blocking and waiting transactions. This allows the new virtual table to be joined into statement statistics and transaction statistics tables. The new virtual table require at least VIEWACTIVITYREDACTED permission to access. However, in order to view the contending keys, it would require at least VIEWACTIVITY permission or above. Release note (api change): introducing GET `/_status/historicalcontentionevents` endpoint, that returns cluster-wide in-memory historical contention events. The endpoint require at least VIEWACTIVITYREDACTED permission to access. However, in order to expose the contending keys, it would require at least VIEWACTIVITY permission or above.
Azhng
added a commit
to Azhng/cockroach
that referenced
this issue
Feb 24, 2022
This commit introduces `crdb_internal.historical_contention_events` virtual table. This virtual tables exposes historical contention events annoated with transaction fingerprint IDs for transactions that finishes execution. This allows this virtual table to be joined into the statement statistics and transaction statistics table. The new virtual table require at least VIEWACTIVITYREDACTED permission to access. However, in order to view the contending keys, it would require at least VIEWACTIVITY permission or above. Resolves cockroachdb#75904 Release note (sql change): introducing `crdb_internal.historical_contention_events` virtual table, that exposes historical contention events. The events exposed in the new virtual table also include transaction fingerprint IDs for both blocking and waiting transactions. This allows the new virtual table to be joined into statement statistics and transaction statistics tables. The new virtual table require at least VIEWACTIVITYREDACTED permission to access. However, in order to view the contending keys, it would require at least VIEWACTIVITY permission or above. Release note (api change): introducing GET `/_status/historicalcontentionevents` endpoint, that returns cluster-wide in-memory historical contention events. The endpoint require at least VIEWACTIVITYREDACTED permission to access. However, in order to expose the contending keys, it would require at least VIEWACTIVITY permission or above.
Azhng
added a commit
to Azhng/cockroach
that referenced
this issue
Feb 24, 2022
This commit introduces `crdb_internal.historical_contention_events` virtual table. This virtual tables exposes historical contention events annoated with transaction fingerprint IDs for transactions that finishes execution. This allows this virtual table to be joined into the statement statistics and transaction statistics table. The new virtual table require at least VIEWACTIVITYREDACTED permission to access. However, in order to view the contending keys, it would require at least VIEWACTIVITY permission or above. Resolves cockroachdb#75904 Release note (sql change): introducing `crdb_internal.historical_contention_events` virtual table, that exposes historical contention events. The events exposed in the new virtual table also include transaction fingerprint IDs for both blocking and waiting transactions. This allows the new virtual table to be joined into statement statistics and transaction statistics tables. The new virtual table require at least VIEWACTIVITYREDACTED permission to access. However, in order to view the contending keys, it would require at least VIEWACTIVITY permission or above. Release note (api change): introducing GET `/_status/historicalcontentionevents` endpoint, that returns cluster-wide in-memory historical contention events. The endpoint require at least VIEWACTIVITYREDACTED permission to access. However, in order to expose the contending keys, it would require at least VIEWACTIVITY permission or above.
Azhng
added a commit
to Azhng/cockroach
that referenced
this issue
Feb 24, 2022
This commit introduces `crdb_internal.historical_contention_events` virtual table. This virtual tables exposes historical contention events annoated with transaction fingerprint IDs for transactions that finishes execution. This allows this virtual table to be joined into the statement statistics and transaction statistics table. The new virtual table require at least VIEWACTIVITYREDACTED permission to access. However, in order to view the contending keys, it would require at least VIEWACTIVITY permission or above. Resolves cockroachdb#75904 Release note (sql change): introducing `crdb_internal.historical_contention_events` virtual table, that exposes historical contention events. The events exposed in the new virtual table also include transaction fingerprint IDs for both blocking and waiting transactions. This allows the new virtual table to be joined into statement statistics and transaction statistics tables. The new virtual table require at least VIEWACTIVITYREDACTED permission to access. However, in order to view the contending keys, it would require at least VIEWACTIVITY permission or above. Release note (api change): introducing GET `/_status/historicalcontentionevents` endpoint, that returns cluster-wide in-memory historical contention events. The endpoint require at least VIEWACTIVITYREDACTED permission to access. However, in order to expose the contending keys, it would require at least VIEWACTIVITY permission or above.
Azhng
added a commit
to Azhng/cockroach
that referenced
this issue
Feb 24, 2022
This commit introduces `crdb_internal.historical_contention_events` virtual table. This virtual tables exposes historical contention events annoatetd with transaction fingerprint IDs for transactions that have finished executing. This allows this virtual table to be joined into the statement statistics and transaction statistics tables. The new virtual table require at least VIEWACTIVITYREDACTED permission to access. However, in order to view the contending keys, it would require at least VIEWACTIVITY permission or above. Resolves cockroachdb#75904 Release note (sql change): introducing `crdb_internal.historical_contention_events` virtual table, that exposes historical contention events. The events exposed in the new virtual table also include transaction fingerprint IDs for both blocking and waiting transactions. This allows the new virtual table to be joined into statement statistics and transaction statistics tables. The new virtual table require at least VIEWACTIVITYREDACTED permission to access. However, in order to view the contending keys, it would require at least VIEWACTIVITY permission or above. The contention events are stored in memory. The amount of contention events stored is controlled via 'sql.contention.event_store.capacity' cluster setting. Release note (api change): introducing GET `/_status/historicalcontentionevents` endpoint, that returns cluster-wide in-memory historical contention events. The endpoint require at least VIEWACTIVITYREDACTED permission to access. However, in order to expose the contending keys, it would require at least VIEWACTIVITY permission or above. The contention events are stored in memory. The amount of contention events stored is controlled via 'sql.contention.event_store.capacity' cluster setting.
Azhng
added a commit
to Azhng/cockroach
that referenced
this issue
Feb 28, 2022
This commit introduces `crdb_internal.historical_contention_events` virtual table. This virtual tables exposes historical contention events annotated with transaction fingerprint IDs for transactions that have finished executing. This allows this virtual table to be joined into the statement statistics and transaction statistics tables. The new virtual table require at least VIEWACTIVITYREDACTED permission to access. However, in order to view the contending keys, it would require at least VIEWACTIVITY permission or above. Resolves cockroachdb#75904 Release note (sql change): introducing `crdb_internal.historical_contention_events` virtual table, that exposes historical contention events. The events exposed in the new virtual table also include transaction fingerprint IDs for both blocking and waiting transactions. This allows the new virtual table to be joined into statement statistics and transaction statistics tables. The new virtual table require either VIEWACTIVITYREDACTED OR VIEWACTIVITY role option to access. However, in order to view the contending keys, it would require VIEWACTIVITY role option. The contention events are stored in memory. The amount of contention events stored is controlled via 'sql.contention.event_store.capacity' cluster setting. Release note (api change): introducing GET `/_status/historicalcontentionevents` endpoint, that returns cluster-wide in-memory historical contention events. The endpoint require either VIEWACTIVITYREDACTED OR VIEWACTIVITY role option to access. However, in order to view the contending keys, it would require VIEWACTIVITY role option. The contention events are stored in memory. The amount of contention events stored is controlled via 'sql.contention.event_store.capacity' cluster setting.
Azhng
added a commit
to Azhng/cockroach
that referenced
this issue
Feb 28, 2022
This commit introduces `crdb_internal.historical_contention_events` virtual table. This virtual tables exposes historical contention events annotated with transaction fingerprint IDs for transactions that have finished executing. This allows this virtual table to be joined into the statement statistics and transaction statistics tables. The new virtual table require either VIEWACTIVITYREDACTED OR VIEWACTIVITY role option to access. However, if user has VIEWACTIVTYREDACTED role, the contending key will be redacted. The contention events are stored in memory. The amount of contention events stored is controlled via 'sql.contention.event_store.capacity' cluster setting. Resolves cockroachdb#75904 Release note (sql change): introducing `crdb_internal.historical_contention_events` virtual table, that exposes historical contention events. The events exposed in the new virtual table also include transaction fingerprint IDs for both blocking and waiting transactions. This allows the new virtual table to be joined into statement statistics and transaction statistics tables. The new virtual table require either VIEWACTIVITYREDACTED OR VIEWACTIVITY role option to access. However, if user has VIEWACTIVTYREDACTED role, the contending key will be redacted. The contention events are stored in memory. The amount of contention events stored is controlled via 'sql.contention.event_store.capacity' cluster setting. Release note (api change): introducing GET `/_status/historicalcontentionevents` endpoint, that returns cluster-wide in-memory historical contention events. The endpoint require either VIEWACTIVITYREDACTED OR VIEWACTIVITY role option to access. However, if user has VIEWACTIVTYREDACTED role, the contending key will be redacted. The contention events are stored in memory. The amount of contention events stored is controlled via 'sql.contention.event_store.capacity' cluster setting. Release Justification: Low risk, high benefit change
craig bot
pushed a commit
that referenced
this issue
Mar 1, 2022
76886: sql: scheduled logger to capture index usage stats r=THardy98 a=THardy98 Resolves: #72486 This change introduces a scheduled logger that captures index usage statistics logs on a time interval. Release note (sql change): Initial implementation of a scheduled logger used to capture index usage statistics to the telemetry logging channel. Release justification: Category 4: Low risk, high benefit changes to existing functionality. 76917: sql: introduce crdb_internal.transaction_contention_events virtual table r=maryliag,matthewtodd a=Azhng This commit introduces `crdb_internal.transaction_contention_events` virtual table. This virtual tables exposes transaction contention events annotated with transaction fingerprint IDs for transactions that have finished executing. This allows this virtual table to be joined into the statement statistics and transaction statistics tables. The new virtual table require either VIEWACTIVITYREDACTED OR VIEWACTIVITY role option to access. However, if user has VIEWACTIVTYREDACTED role, the contending key will be redacted. The contention events are stored in memory. The amount of contention events stored is controlled via 'sql.contention.event_store.capacity' cluster setting. The new table has the following schema: CREATE TABLE crdb_internal.transaction_contention_events ( collection_ts TIMESTAMPTZ NOT NULL, blocking_txn_id UUID NOT NULL, blocking_txn_fingerprint_id BYTES NOT NULL, waiting_txn_id UUID NOT NULL, waiting_txn_fingerprint_id BYTES NOT NULL, contention_duration INTERVAL NOT NULL, contending_key BYTES NOT NULL ) * collected_ts: stores the timestamp of when the contention event was collected * blocking_txn_id: stores the transaction ID of the blocking transaction of the contention event. This column can be joined into `crdb_internal.cluster_contention_events` or `crdb_internal.node_contention_events` table. * blocking_txn_fingerprint_id: stores the transaction fingerprint ID of the blocking transaction fingerprint IDs. This can be used to join into the `crdb_internal.statement_statistics` and `crdb_internal.transaction_statistics` tables to surface historical information of the transactions that caused the contention. * waiting_txn_id: stores the transaction ID of the waiting transaction in the contention event. Similar to `blocking_txn_id`, this column can be joined into `crdb_internal.cluster_contention_events` and `crdb_internal.node_contention_events` tables. * waiting_txn_fingerprint_id: stores the transaction fingerprint ID of the waiting transaction. Similar to `blocking_txn_fingerprint_id`, this column can be joined to `crdb_internal.statement_statistics` and `crdb_internal.transaction_statistics` tables. * contention_duration: stores the amount of time the waiting transaction spent waiting for the blocking transaction. * contending_key: stores the key that caused the contention. If the user has VIEWACTIVITYREDACTED role option, this column is redacted. Resolves #75904 Release note (sql change): introducing `crdb_internal.transaction_contention_events` virtual table, that exposes historical transaction contention events. The events exposed in the new virtual table also include transaction fingerprint IDs for both blocking and waiting transactions. This allows the new virtual table to be joined into statement statistics and transaction statistics tables. The new virtual table require either VIEWACTIVITYREDACTED OR VIEWACTIVITY role option to access. However, if user has VIEWACTIVTYREDACTED role, the contending key will be redacted. The contention events are stored in memory. The amount of contention events stored is controlled via 'sql.contention.event_store.capacity' cluster setting. Release note (api change): introducing GET `/_status/transactioncontentionevents` endpoint, that returns cluster-wide in-memory historical transaction contention events. The endpoint require either VIEWACTIVITYREDACTED OR VIEWACTIVITY role option to access. However, if user has VIEWACTIVTYREDACTED role, the contending key will be redacted. The contention events are stored in memory. The amount of contention events stored is controlled via 'sql.contention.event_store.capacity' cluster setting. Release Justification: Low risk, high benefit change Co-authored-by: Thomas Hardy <[email protected]> Co-authored-by: Azhng <[email protected]>
RajivTS
pushed a commit
to RajivTS/cockroach
that referenced
this issue
Mar 6, 2022
This commit introduces `crdb_internal.transaction_contention_events`
virtual table. This virtual tables exposes transaction contention events
annotated with transaction fingerprint IDs for transactions that have
finished executing. This allows this virtual table to be joined into the
statement statistics and transaction statistics tables.
The new virtual table require either VIEWACTIVITYREDACTED OR
VIEWACTIVITY role option to access. However, if user has
VIEWACTIVTYREDACTED role, the contending key will be redacted. The contention
events are stored in memory. The amount of contention events stored is
controlled via 'sql.contention.event_store.capacity' cluster setting.
The new table has the following schema:
CREATE TABLE crdb_internal.transaction_contention_events (
collection_ts TIMESTAMPTZ NOT NULL,
blocking_txn_id UUID NOT NULL,
blocking_txn_fingerprint_id BYTES NOT NULL,
waiting_txn_id UUID NOT NULL,
waiting_txn_fingerprint_id BYTES NOT NULL,
contention_duration INTERVAL NOT NULL,
contending_key BYTES NOT NULL
)
* collected_ts: stores the timestamp of when the contention event was
collected
* blocking_txn_id: stores the transaction ID of the blocking transaction
of the contention event. This column can be joined into
`crdb_internal.cluster_contention_events` or
`crdb_internal.node_contention_events` table.
* blocking_txn_fingerprint_id: stores the transaction fingerprint ID of
the blocking transaction fingerprint IDs. This can be used to join into
the `crdb_internal.statement_statistics` and
`crdb_internal.transaction_statistics` tables to surface historical
information of the transactions that caused the contention.
* waiting_txn_id: stores the transaction ID of the waiting transaction
in the contention event. Similar to `blocking_txn_id`, this column can
be joined into `crdb_internal.cluster_contention_events` and
`crdb_internal.node_contention_events` tables.
* waiting_txn_fingerprint_id: stores the transaction fingerprint ID of
the waiting transaction. Similar to `blocking_txn_fingerprint_id`, this
column can be joined to `crdb_internal.statement_statistics` and
`crdb_internal.transaction_statistics` tables.
* contention_duration: stores the amount of time the waiting transaction
spent waiting for the blocking transaction.
* contending_key: stores the key that caused the contention. If
the user has VIEWACTIVITYREDACTED role option, this column is redacted.
Resolves cockroachdb#75904
Release note (sql change): introducing
`crdb_internal.transaction_contention_events` virtual table, that exposes
historical transaction contention events. The events exposed in the new virtual
table also include transaction fingerprint IDs for both blocking and
waiting transactions. This allows the new virtual table to be joined
into statement statistics and transaction statistics tables.
The new virtual table require either VIEWACTIVITYREDACTED OR
VIEWACTIVITY role option to access. However, if user has
VIEWACTIVTYREDACTED role, the contending key will be redacted. The contention
events are stored in memory. The amount of contention events stored is
controlled via 'sql.contention.event_store.capacity' cluster setting.
Release note (api change): introducing
GET `/_status/transactioncontentionevents` endpoint, that returns
cluster-wide in-memory historical transaction contention events.
The endpoint require either VIEWACTIVITYREDACTED OR VIEWACTIVITY role
option to access. However, if user has VIEWACTIVTYREDACTED role, the
contending key will be redacted. The contention events are stored in memory.
The amount of contention events stored is controlled via
'sql.contention.event_store.capacity' cluster setting.
Release Justification: Low risk, high benefit change
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
After we have the ability to resolve the transaction ID in contention events into transaction fingerprint ID and store them in an in-memory store (#74487), we should expose that information in the CLI via a virtual table.
Epic: CRDB-9226
Jira issue: CRDB-12875
The text was updated successfully, but these errors were encountered: