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 frequently-used fields in system statistics tables #98624

Closed
ericharmeling opened this issue Mar 14, 2023 · 1 comment · Fixed by #99042
Closed

sql: index frequently-used fields in system statistics tables #98624

ericharmeling opened this issue Mar 14, 2023 · 1 comment · Fixed by #99042
Assignees
@ericharmeling
Labels
branch-master Failures and bugs on the master branch. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) GA-blocker

Comments

@ericharmeling
Copy link
Contributor

ericharmeling commented Mar 14, 2023 •
edited by cockroach-jira-scripts
Loading

We should add indexes on the following columns/fields in system.statement_statistics and system.transaction_statistics:

  • execution_statistics.cnt
  • statistics.svcLat
  • execution_statistics.cpuSQLNanos
  • execution_statistics.contentionTime

Background thread: https://cockroachlabs.slack.com/archives/G01Q9D01NTU/p1678388421700209

Follow up on #98261.

Jira issue: CRDB-25372
@ericharmeling ericharmeling added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) T-sql-observability labels Mar 14, 2023
@ericharmeling ericharmeling self-assigned this Mar 14, 2023
@blathers-crl
Copy link

blathers-crl bot commented Mar 15, 2023

Hi @maryliag, please add branch-* labels to identify which branch(es) this release-blocker affects.

🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.

@maryliag maryliag added the branch-master Failures and bugs on the master branch. label Mar 15, 2023
@ericharmeling ericharmeling mentioned this issue Mar 20, 2023
Merged
ericharmeling added a commit to ericharmeling/cockroach that referenced this issue Mar 20, 2023
@ericharmeling
sql: added indexes to system.statement_statistics, system.transaction…
d081e5a 
…_statistics

Fixes cockroachdb#98624.

This commit adds indexes on new computed columns to the
system.statement_statistics and system.transaction_statistics
tables.

Epic: none

Release note: None
ericharmeling added a commit to ericharmeling/cockroach that referenced this issue Mar 21, 2023
@ericharmeling
sql: added indexes to system.statement_statistics, system.transaction…
b9bca30 
…_statistics

Fixes cockroachdb#98624.

This commit adds indexes on new computed columns to the
system.statement_statistics and system.transaction_statistics
tables.

Epic: none

Release note: None
ericharmeling added a commit to ericharmeling/cockroach that referenced this issue Mar 21, 2023
@ericharmeling
sql: added indexes to system.statement_statistics, system.transaction…
d2eb362 
…_statistics

Fixes cockroachdb#98624.

This commit adds indexes on new computed columns to the
system.statement_statistics and system.transaction_statistics
tables.

Epic: none

Release note: None
ericharmeling added a commit to ericharmeling/cockroach that referenced this issue Mar 21, 2023
@ericharmeling
sql: add p99 computed column and index to system.statement_statistics…
22df5b4 
…, system.transaction_statistics

Part of cockroachdb#98624.
Follows cockroachdb#99042.

This commit adds a new computed column (p99_latency) and partial
index (p99_latency_idx) to the system.statement_statistics and
system.transaction_statistics tables.

Epic: none

Release note: None
craig bot pushed a commit that referenced this issue Mar 21, 2023
@dt @mgartner
@maryliag @ericharmeling @abarganier @pav-kv
Merge #98527 #98879 #98988 #99042 #99054 #99119
77029b1 
98527: sql: deprecate gossip-based physical planning r=dt a=dt

Previously system tenants and secondary tenants used differnt physical planning implementations, with the system tenant and only the system tenant using nodeIDs while other tenants used the instance table. This unifies those implementations such that all tenants use NodeIDs if running in mixed mode and use the instance table if not.

Release note: none.
Epic: CRDB-16910

98879: sql: support array-flatten subqueries within UDFs r=mgartner a=mgartner

Array-flatten subqueries (e.g., `ARRAY(SELECT a FROM t)`) are now
supported within UDFs. They are now converted to a normal subquery with
a ScalarGroupBy if they exist within a UDF, even if they are
uncorrelated. This allows them to be executed without any changes to the
execbuilder or the evaluation logic of `tree.Routine`.

Fixes #98738

Release note: None


98988: ui: add badges for filter elements r=maryliag a=maryliag

Adds badges for each of the selected filters on SQL Activity and
Insights pages.

Part Of #98891

https://www.loom.com/share/e7417209fc704d71b2733f58609fb4de

<img width="1160" alt="Screenshot 2023-03-19 at 1 30 49 PM" src="https://user-images.githubusercontent.com/1017486/226195412-03d3ef58-5d6d-4c24-84f1-6a55b952f5c0.png">

Release note (ui change): Adds badges for each selected
filter on SQL Activity and Insights pages.

99042: sql: added indexes to system.statement_statistics, system.transaction_statistics r=ericharmeling a=ericharmeling

Fixes #98624.

This commit adds indexes on new computed columns to the system.statement_statistics and system.transaction_statistics tables.

Epic: none

Release note: None

99054: pkg/ccl: Unskip TestTenantStatusAPI/tenant_ranges/pagination r=dhartunian a=abarganier

Fixes: #92979

Previously, in #97386, we skipped test_tenant_ranges_pagination because it was marked as flaky.

The test makes a request for a single range and expects an offset of `1` back. It then uses this offset to request a second range, and expects an offset of `2`. This means that the test requires at least 3 ranges to exist on the tenant.

The test was flaking on the assertion that the offset returned by the second request came back as `2`. Instead, it was flaking when the offset came back as `0`, which signifies that there are no more ranges to process.

We learned that the tenant create process has an asycnhronous splitting of ranges that occurs, which is what would lead to this sporadic scenario where not enough ranges existed (yet) for the test to succeed.

This patch updates the test with a `testutils.SucceedsSoon` clause that checks first that `crdb_internal.ranges` contains at least 3 ranges, prior to making the second request. This should provide sufficient time for the range split queue to be processed and eliminate the vast majority of these test flakes.

Release note: none

99119: kvserver: mark in-flight storage checkpoints r=tbg a=pavelkalinnikov

This commit makes it so that the consistency checker checkpoints are first created in a "_pending" folder, and only after completion are atomically renamed to the intended directory name. This is to help distinguish valid checkpoints from the ones that weren't finalized (for example, when the node crashed in the meantime).

Part of #81819
Epic: none
Release note (ops change): checkpoint directories (that can be created in the rare event of range inconsistency) are now clearly indicated as pending until they are fully populated. This is to help operators to distinguish valid checkpoints from corrupted ones.

Co-authored-by: David Taylor <[email protected]>
Co-authored-by: Marcus Gartner <[email protected]>
Co-authored-by: maryliag <[email protected]>
Co-authored-by: Eric Harmeling <[email protected]>
Co-authored-by: Alex Barganier <[email protected]>
Co-authored-by: Pavel Kalinnikov <[email protected]>
@ericharmeling ericharmeling mentioned this issue Mar 21, 2023
Merged
@craig craig bot closed this as completed in b6d5bb7 Mar 21, 2023
ericharmeling added a commit to ericharmeling/cockroach that referenced this issue Mar 21, 2023
@ericharmeling
sql: add p99 computed column and index to system.statement_statistics…
c4c5f10 
…, system.transaction_statistics

Part of cockroachdb#98624.
Follows cockroachdb#99042.

This commit adds a new computed column (p99_latency) and partial
index (p99_latency_idx) to the system.statement_statistics and
system.transaction_statistics tables.

Epic: none

Release note: None
ericharmeling added a commit to ericharmeling/cockroach that referenced this issue Mar 21, 2023
@ericharmeling
sql: add p99 computed column and index to system.statement_statistics…
6e4ca2d 
…, system.transaction_statistics

Part of cockroachdb#98624.
Follows cockroachdb#99042.

This commit adds a new computed column (p99_latency) and partial
index (p99_latency_idx) to the system.statement_statistics and
system.transaction_statistics tables.

The commit also increases the hard-coded fingerprint execution
count for the flush benchmark to the maximum for in-memory
statement stats that trigger a flush.

Epic: none

Release note: None
ericharmeling added a commit to ericharmeling/cockroach that referenced this issue Mar 22, 2023
@ericharmeling
sql: add p99 computed column and index to system.statement_statistics…
7cb8c19 
…, system.transaction_statistics

Part of cockroachdb#98624.
Follows cockroachdb#99042.

This commit adds a new computed column (p99_latency) and partial
index (p99_latency_idx) to the system.statement_statistics and
system.transaction_statistics tables.

The commit also increases the hard-coded fingerprint execution
count for the flush benchmark to the maximum for in-memory
statement stats that trigger a flush.

Epic: none

Release note: None
craig bot pushed a commit that referenced this issue Mar 22, 2023
@ericharmeling @mgartner
Merge #99148 #99231
a930643 
99148: sql: add p99 computed column and index to system.statement_statistics, system.transaction_statistics r=ericharmeling a=ericharmeling

Part of #98624.
Follows #99042.

This commit adds a new computed column (p99_latency) and partial
index (p99_latency_idx) to the system.statement_statistics and
system.transaction_statistics tables.

Epic: none

Release note: None

99231: sql: fix partitioned table format in statement bundle schema.sql r=mgartner a=mgartner

Fixes #99026

Release note: None

Co-authored-by: Eric Harmeling <[email protected]>
Co-authored-by: Marcus Gartner <[email protected]>
@blathers-crl blathers-crl bot mentioned this issue Mar 22, 2023
Merged
blathers-crl bot pushed a commit that referenced this issue Mar 22, 2023
@ericharmeling
sql: add p99 computed column and index to system.statement_statistics…
9dc146f 
…, system.transaction_statistics

Part of #98624.
Follows #99042.

This commit adds a new computed column (p99_latency) and partial
index (p99_latency_idx) to the system.statement_statistics and
system.transaction_statistics tables.

The commit also increases the hard-coded fingerprint execution
count for the flush benchmark to the maximum for in-memory
statement stats that trigger a flush.

Epic: none

Release note: None
maryliag added a commit to maryliag/cockroach that referenced this issue Mar 23, 2023
@maryliag
server: update sort of sql stats to new computed columns
2c81026 
Part of cockroachdb#98624
To take advantage of the actual indexes created on the new
persisted views, we need to use the computed columns.
This PR updates the sort by column to use the new columns
instead.

Release note: None
@maryliag maryliag mentioned this issue Mar 23, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@ericharmeling ericharmeling

Labels
branch-master Failures and bugs on the master branch. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) GA-blocker
Projects
None yet
Milestone
No milestone
2 participants
@maryliag @ericharmeling