Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: index usage metrics for unused indexes (reads) #64740

Closed
Azhng opened this issue May 5, 2021 · 0 comments
Closed

sql: index usage metrics for unused indexes (reads) #64740

Azhng opened this issue May 5, 2021 · 0 comments
Assignees
@Azhng
Labels
A-sql-cli-observability Issues related to surfacing SQL observability in SHOW, CRDB_INTERNAL, SYSTEM, etc. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)

Comments

@Azhng
Copy link
Contributor

Azhng commented May 5, 2021 •
edited by kevin-v-ngo
Loading

Feature brief: Index usage statistics
Summary: Capture metrics for when indexes are used for reads

This issue captures V1 index usage metrics (reads) for the unused index user story.

Epic: CRDB-6570
@Azhng Azhng added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label May 5, 2021
@Azhng Azhng self-assigned this May 5, 2021
@kevin-v-ngo kevin-v-ngo mentioned this issue May 20, 2021
Closed
@kevin-v-ngo kevin-v-ngo added the A-sql-cli-observability Issues related to surfacing SQL observability in SHOW, CRDB_INTERNAL, SYSTEM, etc. label May 26, 2021
@Azhng Azhng mentioned this issue Jun 14, 2021
Merged
Azhng added a commit to Azhng/cockroach that referenced this issue Jul 14, 2021
@Azhng
sql,server: change indexusagestats subsystem to issue cluster RPC fanout
5692451 
Previously, indexusagestats subsystem can only read node-local index usage
stats. This commit introduces clusterindexusagestats package within the
indexusagestats subsystem to use RPC fanout infrastructure inside
statusServer to fetch cluster-wide index usage statistics.

Related issue: cockroachdb#64740

Release note: None
@kevin-v-ngo kevin-v-ngo mentioned this issue Jul 15, 2021
Closed
Azhng added a commit to Azhng/cockroach that referenced this issue Jul 16, 2021
@Azhng
sql,server: change indexusagestats subsystem to issue cluster RPC fanout
9c627f0 
Previously, indexusagestats subsystem can only read node-local index usage
stats. This commit introduces clusterindexusagestats package within the
indexusagestats subsystem to use RPC fanout infrastructure inside
statusServer to fetch cluster-wide index usage statistics.

Related issue: cockroachdb#64740

Release note: None
Azhng added a commit to Azhng/cockroach that referenced this issue Jul 19, 2021
@Azhng
sql,server: change indexusagestats subsystem to issue cluster RPC fanout
897bc93 
Previously, indexusagestats subsystem can only read node-local index usage
stats. This commit introduces clusterindexusagestats package within the
indexusagestats subsystem to use RPC fanout infrastructure inside
statusServer to fetch cluster-wide index usage statistics.

Related issue: cockroachdb#64740

Release note: None
Azhng added a commit to Azhng/cockroach that referenced this issue Jul 21, 2021
@Azhng
sql,server: introduce RPC fanout for index usage stats
3437ee3 
Related issue: cockroachdb#64740

Release note: None
Azhng added a commit to Azhng/cockroach that referenced this issue Jul 21, 2021
@Azhng
sql: introduce crdb_internal.index_usage_stats virtual table
cbfb348 
This commit introduce crdb_internal.index_usage_stats virtual
table that is backed by new clusterindexusagestats package. This
new package implements a variant of the indexusagestats interface
and serves the data by issuing cluster RPC fanout.

Addresses cockroachdb#64740

Followup to cockroachdb#66639

Release note (sql change): introduce crdb_internal.index_usage_statistics
virtual table to surface index usage statistics.
sql.metrics.index_usage_stats.enabled cluster setting can be used to
turn on/off the subsystem. It is default to true.
pawalt pushed a commit to pawalt/cockroach that referenced this issue Jul 22, 2021
@Azhng @pawalt
sql: introduce index usage stats subsystem
65b8712 
This commit introduce a new index usage stats subsystem living inside
the sql package. This subsystem is responsible for asynchronously
collecting index usage statistics from the planner and its is created
within the sql.Server.

Related issue cockroachdb#64740

Release note: None
Azhng added a commit to Azhng/cockroach that referenced this issue Jul 22, 2021
@Azhng
sql,server: introduce RPC fanout for index usage stats
0a95d40 
Related issue: cockroachdb#64740

Release note: None
craig bot pushed a commit that referenced this issue Jul 26, 2021
@Azhng @nvanbenschoten
Merge #66639 #68045
82b32cf 
66639: sql,server: change indexusagestats subsystem to issue cluster RPC fanout r=Azhng a=Azhng

sql: introduce crdb_internal.index_usage_stats virtual table

This commit introduce crdb_internal.index_usage_stats virtual
table that is backed by new clusterindexusagestats package. This
new package implements a variant of the indexusagestats interface
and serves the data by issuing cluster RPC fanout.

Addresses #64740

Followup to #66451

Release note (sql change): introduce crdb_internal.index_usage_statistics
virtual table to surface index usage statistics.
sql.metrics.index_usage_stats.enabled cluster setting can be used to
turn on/off the subsystem. It is default to true.

68045: kv: grab raftMu during no-op writes with local gossip triggers r=nvanbenschoten a=nvanbenschoten

Fixes #68011.

As of 9f8c019, it is now possible to have no-op writes that do not go through
Raft but do set one of the gossip triggers. These gossip triggers require the
raftMu to be held, so we were running into trouble when handling the local
eval results above Raft.

For instance, we see this case when a transaction sets the system config
trigger and then performs a delete range over an empty span before
committing. In this case, the transaction will have no intents to
remove, so it can auto-GC its record during an EndTxn. If its record was
never written in the first place, this is a no-op (as of 9f8c019).

There appear to be three ways we could solve this:
1. we can avoid setting gossip triggers on transactions that don't perform
   any writes.
2. we can force EndTxn requests with gossip triggers to go through Raft even
   if they are otherwise no-ops.
3. we can properly handle gossip triggers on the above Raft local eval result
   path.

This commit opts for the third option.

Co-authored-by: Azhng <[email protected]>
Co-authored-by: Nathan VanBenschoten <[email protected]>
Azhng added a commit to Azhng/cockroach that referenced this issue Aug 3, 2021
@Azhng
sql: introduce crdb_internal.index_usage_stats virtual table
52fa667 
This commit introduce crdb_internal.index_usage_stats virtual
table that is backed by new clusterindexusagestats package. This
new package implements a variant of the indexusagestats interface
and serves the data by issuing cluster RPC fanout.

Addresses cockroachdb#64740

Followup to cockroachdb#66639

Release note (sql change): introduce crdb_internal.index_usage_statistics
virtual table to surface index usage statistics.
sql.metrics.index_usage_stats.enabled cluster setting can be used to
turn on/off the subsystem. It is default to true.
Azhng added a commit to Azhng/cockroach that referenced this issue Aug 4, 2021
@Azhng
sql: introduce crdb_internal.index_usage_stats virtual table
7fd0548 
This commit introduce crdb_internal.index_usage_stats virtual
table that is backed by new clusterindexusagestats package. This
new package implements a variant of the indexusagestats interface
and serves the data by issuing cluster RPC fanout.

Addresses cockroachdb#64740

Followup to cockroachdb#66639

Release note (sql change): introduce crdb_internal.index_usage_statistics
virtual table to surface index usage statistics.
sql.metrics.index_usage_stats.enabled cluster setting can be used to
turn on/off the subsystem. It is default to true.
Azhng added a commit to Azhng/cockroach that referenced this issue Aug 4, 2021
@Azhng
sql: introduce crdb_internal.index_usage_stats virtual table
4e36b23 
This commit introduce crdb_internal.index_usage_stats virtual
table that is backed by new clusterindexusagestats package. This
new package implements a variant of the indexusagestats interface
and serves the data by issuing cluster RPC fanout.

Addresses cockroachdb#64740

Followup to cockroachdb#66639

Release note (sql change): introduce crdb_internal.index_usage_statistics
virtual table to surface index usage statistics.
sql.metrics.index_usage_stats.enabled cluster setting can be used to
turn on/off the subsystem. It is default to true.
Azhng added a commit to Azhng/cockroach that referenced this issue Aug 5, 2021
@Azhng
sql: introduce crdb_internal.index_usage_stats virtual table
47459d6 
This commit introduce crdb_internal.index_usage_stats virtual
table that is backed by new clusterindexusagestats package. This
new package implements a variant of the indexusagestats interface
and serves the data by issuing cluster RPC fanout.

Addresses cockroachdb#64740

Followup to cockroachdb#66639

Release note (sql change): introduce crdb_internal.index_usage_statistics
virtual table to surface index usage statistics.
sql.metrics.index_usage_stats.enabled cluster setting can be used to
turn on/off the subsystem. It is default to true.
craig bot pushed a commit that referenced this issue Aug 5, 2021
@Azhng
Merge #66640
d33af93 
66640: sql: introduce crdb_internal.index_usage_stats virtual table r=Azhng a=Azhng

This commit introduce crdb_internal.index_usage_stats virtual
table that is backed by new clusterindexusagestats package. This
new package implements a variant of the indexusagestats interface
and serves the data by issuing cluster RPC fanout.

Release note (sql change): introduce crdb_internal.index_usage_statistics
virtual table to surface index usage statistics.
sql.metrics.index_usage_stats.enabled cluster setting can be used to
turn on/off the subsystem. It is default to true.
sql.metrics.index_usage_stats.reset_interval can change the reset
interval of the collected statistics. It is default to 1 hour.

Addresses #64740

Followup to #66639

Co-authored-by: Azhng <[email protected]>
@kevin-v-ngo kevin-v-ngo changed the title sql: index usage metrics sql: index usage metrics for unused indexes (reads) Aug 6, 2021
sajjadrizvi pushed a commit to sajjadrizvi/cockroach that referenced this issue Aug 10, 2021
@Azhng
sql: introduce crdb_internal.index_usage_stats virtual table
835fb93 
This commit introduce crdb_internal.index_usage_stats virtual
table that is backed by new clusterindexusagestats package. This
new package implements a variant of the indexusagestats interface
and serves the data by issuing cluster RPC fanout.

Addresses cockroachdb#64740

Followup to cockroachdb#66639

Release note (sql change): introduce crdb_internal.index_usage_statistics
virtual table to surface index usage statistics.
sql.metrics.index_usage_stats.enabled cluster setting can be used to
turn on/off the subsystem. It is default to true.
This was referenced Oct 29, 2021
Closed
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@Azhng Azhng

Labels
A-sql-cli-observability Issues related to surfacing SQL observability in SHOW, CRDB_INTERNAL, SYSTEM, etc. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@Azhng @kevin-v-ngo