sql: introduce crdb_internal.transaction_contention_events virtual table

[cockroach-teamcity]

Exalate commented:

cockroachdb/cockroach#76917 --- 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

Jira Issue: DOC-2780

Jira Issue: DOC-4354

[exalate-issue-sync]

Stephanie Bodoff (stbof) commented:
Kevin Ngo Should this table be labeled supported in production? I normally document the schema only for tables supported in production: https://www.cockroachlabs.com/docs/dev/crdb-internal.html#tables

Also, the description for some of the columns in this table say: This column can be joined into
{{crdb_internal.cluster_contention_events}} or
{{crdb_internal.node_contention_events}} table. However, {{node_contention_events}} is not indicated as being supported in production nor is the schema documented.

[exalate-issue-sync]

Kevin Ngo (kevin-v-ngo) commented:
Hi Stephanie, I’d say yes for production. We haven’t ran into any issues internally when testing and we also have safeguards (cluster settings) should users need to turn it off. Adding Archer Zhang as fyi.

And i think we’d want to join with {{cluster_contention_events}} since this is already a cluster wide view. Here’s also a few examples Archer put together for our docs (thanks Archer!):

{noformat}- raw data

SELECT
collection_ts,
blocking_txn_id,
encode(blocking_txn_fingerprint_id, 'hex') as blocking_txn_fingerprint_id,
waiting_txn_id,
encode(waiting_txn_fingerprint_id, 'hex') as waiting_txn_fingerprint_id
FROM
crdb_internal.transaction_contention_events
WHERE
encode(blocking_txn_fingerprint_id, 'hex') != '0000000000000000' AND
encode(waiting_txn_fingerprint_id, 'hex') != '0000000000000000'
LIMIT 10{noformat}

{noformat}-- removing in-progress txns and simple aggregation

SELECT
encode(hce.blocking_txn_fingerprint_id, 'hex') as blocking_txn_fingerprint_id,
encode(hce.waiting_txn_fingerprint_id, 'hex') as waiting_txn_fingerprint_id,
count(*) AS contention_count
FROM
crdb_internal.transaction_contention_events hce
WHERE
blocking_txn_fingerprint_id != '0000000000000000' AND
waiting_txn_fingerprint_id != '0000000000000000'
GROUP BY
hce.blocking_txn_fingerprint_id, hce.waiting_txn_fingerprint_id
ORDER BY
contention_count
DESC{noformat}

{noformat}-- showing blocking statement
SELECT
hce.blocking_txn_fingerprint_id,
hce.waiting_txn_fingerprint_id,
hce.contention_count,
ss.metadata ->> 'query' AS blocking_statement
FROM [SELECT
encode(hce.blocking_txn_fingerprint_id, 'hex') as blocking_txn_fingerprint_id,
encode(hce.waiting_txn_fingerprint_id, 'hex') as waiting_txn_fingerprint_id,
count(*) AS contention_count
FROM
crdb_internal.transaction_contention_events hce
GROUP BY
hce.blocking_txn_fingerprint_id, hce.waiting_txn_fingerprint_id
] hce,
crdb_internal.statement_statistics ss
WHERE
hce.blocking_txn_fingerprint_id != '0000000000000000' AND
hce.waiting_txn_fingerprint_id != '0000000000000000' AND
hce.blocking_txn_fingerprint_id = encode(ss.transaction_fingerprint_id, 'hex')
ORDER BY
contention_count
DESC{noformat}

{noformat}-- showing blocking statement and waiting statements
SELECT
hce.blocking_statement,
ss2.metadata ->> 'query' AS waiting_statement,
hce.contention_count
FROM [SELECT
hce.blocking_txn_fingerprint_id,
hce.waiting_txn_fingerprint_id,
hce.contention_count,
ss.metadata ->> 'query' AS blocking_statement
FROM [SELECT
encode(hce.blocking_txn_fingerprint_id, 'hex') as blocking_txn_fingerprint_id,
encode(hce.waiting_txn_fingerprint_id, 'hex') as waiting_txn_fingerprint_id,
count(*) AS contention_count
FROM
crdb_internal.transaction_contention_events hce
GROUP BY
hce.blocking_txn_fingerprint_id, hce.waiting_txn_fingerprint_id
] hce,
crdb_internal.statement_statistics ss
WHERE
hce.blocking_txn_fingerprint_id = encode(ss.transaction_fingerprint_id, 'hex')] hce,
crdb_internal.statement_statistics ss2
WHERE
hce.blocking_txn_fingerprint_id != '0000000000000000' AND
hce.waiting_txn_fingerprint_id != '0000000000000000' AND
hce.waiting_txn_fingerprint_id = encode(ss2.transaction_fingerprint_id, 'hex')
ORDER BY
contention_count
DESC{noformat}