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.

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

ui: later pages of results in table list lead to non-operative table details when there are many tables in db #94330

Closed
knz opened this issue Dec 27, 2022 · 2 comments
Assignees
Labels
A-sql-observability Related to observability of the SQL layer A-webui-databases A-webui-scaling C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.

Comments

@knz
Copy link
Contributor

knz commented Dec 27, 2022

(Found via #94027)

Describe the problem

When there are 1000+ tables in a single database, the initial table list loads fast, but trying to access table details in later pages breaks down.

To Reproduce

  1. start cockroach demo with the changes from sql: new functions crdb_internal.generate_test_objects and .gen_rand_ident #94027
  2. generate 1000 tables, e.g. via select crdb_internal.generate_test_objects('{"counts":[1000],"name_gen":{"noise":false}}'::jsonb);
  3. navigate to db console -> defaultdb eg. via http://127.0.0.1:8080/#/database/defaultdb
  4. click on the link to page 50 at the bottom
  5. click on a table name, e.g. table997

Observe:

  • the table details / CREATE statement are not displayed for at least 60 seconds
  • there is no warning that something is amiss (see note below)
  • eventually (multiple minutes), the results show ups

Note: if the page is loaded within 1-2 minutes of generating 1000 tables, there is a warning banner at the top that "some details could not be retrieved". This is a separate issue, and this banner does not happen if the repro steps are run again later. The delay still happens though.

Expected behavior

The table details view should load equally as fast for a table on page 50 of the list as a table on page 1.

Additional data / screenshots

image

Environment:

  • master branch towards v23.1

Epic: CRDB-20388

@knz knz added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. A-webui-databases A-webui-scaling A-sql-observability Related to observability of the SQL layer T-sql-observability labels Dec 27, 2022
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Jan 26, 2023
…tenant_ranges_per_table`

Part of: cockroachdb#94332
Part of: cockroachdb#94330

This PR introduces two new SRFs:
`crdb_internal.ranges_in_span(start_key, end_key)` - returns the set of ranges encompassing this span in the form of `(range_id, start_key, end_key)`
`crdb_internal.tenant_ranges_per_table()` - returns the tenant's tables, each table row contains the set of ranges encompassing the table, each row takes the form of `(database_name, database_id, table_name, table_id, range_count, range_ids)`

The former SRF is a QOL improvement.
The latter SRF is intended to be used as a method to retrieve a large number of tables' range data quickly, particularly for the database details page (where we surface the range count of each table).

The latter SRF is able to the range ids & range count for ~10,000 tables in ~1.5s.

Release note(sql change): introduce two SRFs `crdb_internal.ranges_in_span` and `crdb_internal.tenant_ranges_per_table`
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Jan 26, 2023
…tenant_ranges_per_table`

Part of: cockroachdb#94332
Part of: cockroachdb#94330

This PR introduces two new SRFs:
`crdb_internal.ranges_in_span(start_key, end_key)` - returns the set of ranges encompassing this span in the form of `(range_id, start_key, end_key)`
`crdb_internal.tenant_ranges_per_table()` - returns the tenant's tables, each table row contains the set of ranges encompassing the table, each row takes the form of `(database_name, database_id, table_name, table_id, range_count, range_ids)`

The former SRF is a QOL improvement.
The latter SRF is intended to be used as a method to retrieve a large number of tables' range data quickly, particularly for the database details page (where we surface the range count of each table).

The latter SRF is able to the range ids & range count for ~10,000 tables in ~1.5s.

Release note(sql change): introduce two SRFs `crdb_internal.ranges_in_span` and `crdb_internal.tenant_ranges_per_table`
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Jan 26, 2023
…tenant_ranges_per_table`

Part of: cockroachdb#94332
Part of: cockroachdb#94330

This PR introduces two new SRFs:
`crdb_internal.ranges_in_span(start_key, end_key)` - returns the set of ranges encompassing this span in the form of `(range_id, start_key, end_key)`
`crdb_internal.tenant_ranges_per_table()` - returns the tenant's tables, each table row contains the set of ranges encompassing the table, each row takes the form of `(database_name, database_id, table_name, table_id, range_count, range_ids)`

The former SRF is a QOL improvement.
The latter SRF is intended to be used as a method to retrieve a large number of tables' range data quickly, particularly for the database details page (where we surface the range count of each table).

The latter SRF is able to the range ids & range count for ~10,000 tables in ~1.5s.

Release note(sql change): introduce two SRFs `crdb_internal.ranges_in_span` and `crdb_internal.tenant_ranges_per_table`
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Jan 26, 2023
…tenant_ranges_per_table`

Part of: cockroachdb#94332
Part of: cockroachdb#94330

This PR introduces two new SRFs:
`crdb_internal.ranges_in_span(start_key, end_key)` - returns the set of ranges encompassing this span in the form of `(range_id, start_key, end_key)`
`crdb_internal.tenant_ranges_per_table()` - returns the tenant's tables, each table row contains the set of ranges encompassing the table, each row takes the form of `(database_name, database_id, table_name, table_id, range_count, range_ids)`

The former SRF is a QOL improvement.
The latter SRF is intended to be used as a method to retrieve a large number of tables' range data quickly, particularly for the database details page (where we surface the range count of each table).

The latter SRF is able to the range ids & range count for ~10,000 tables in ~1.5s.

Release note(sql change): introduce two SRFs `crdb_internal.ranges_in_span` and `crdb_internal.tenant_ranges_per_table`
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Jan 26, 2023
…tenant_ranges_per_table`

Part of: cockroachdb#94332
Part of: cockroachdb#94330

This PR introduces two new SRFs:
`crdb_internal.ranges_in_span(start_key, end_key)` - returns the set of ranges encompassing this span in the form of `(range_id, start_key, end_key)`
`crdb_internal.tenant_ranges_per_table()` - returns the tenant's tables, each table row contains the set of ranges encompassing the table, each row takes the form of `(database_name, database_id, table_name, table_id, range_count, range_ids)`

The former SRF is a QOL improvement.
The latter SRF is intended to be used as a method to retrieve a large number of tables' range data quickly, particularly for the database details page (where we surface the range count of each table).

The latter SRF is able to the range ids & range count for ~10,000 tables in ~1.5s.

Release note(sql change): introduce two SRFs `crdb_internal.ranges_in_span` and `crdb_internal.tenant_ranges_per_table`
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Jan 30, 2023
…tenant_ranges_per_table`

Part of: cockroachdb#94332
Part of: cockroachdb#94330

This PR introduces two new SRFs:
`crdb_internal.ranges_in_span(start_key, end_key)` - returns the set of ranges encompassing this span in the form of `(range_id, start_key, end_key)`
`crdb_internal.tenant_ranges_per_table()` - returns the tenant's tables, each table row contains the set of ranges encompassing the table, each row takes the form of `(database_name, database_id, table_name, table_id, range_count, range_ids)`

The former SRF is a QOL improvement.
The latter SRF is intended to be used as a method to retrieve a large number of tables' range data quickly, particularly for the database details page (where we surface the range count of each table).

The latter SRF is able to the range ids & range count for ~10,000 tables in ~1.5s.

Release note(sql change): introduce two SRFs `crdb_internal.ranges_in_span` and `crdb_internal.tenant_ranges_per_table`
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Feb 6, 2023
…tenant_ranges_per_table`

Part of: cockroachdb#94332
Part of: cockroachdb#94330

This PR introduces two new SRFs:
`crdb_internal.ranges_in_span(start_key, end_key)` - returns the set of ranges encompassing this span in the form of `(range_id, start_key, end_key)`
`crdb_internal.tenant_ranges_per_table()` - returns the tenant's tables, each table row contains the set of ranges encompassing the table, each row takes the form of `(database_name, database_id, table_name, table_id, range_count, range_ids)`

The former SRF is a QOL improvement.
The latter SRF is intended to be used as a method to retrieve a large number of tables' range data quickly, particularly for the database details page (where we surface the range count of each table).

The latter SRF is able to the range ids & range count for ~10,000 tables in ~1.5s.

Release note(sql change): introduce two SRFs `crdb_internal.ranges_in_span` and `crdb_internal.tenant_ranges_per_table`
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Feb 23, 2023
Part of: cockroachdb#94332
Part of: cockroachdb#94330

Added new builtin function `tenant_span_stats` that retrieves span
statistics for the current tenant. `tenant_span_stats` can be called as:
- `crdb_internal.tenant_span_stats()`: returns table span statistics for all of the tenants tables
- `crdb_internal.tenant_span_stats(database_id)`: returns table span statistics for the tenant's tables belonging to the specified database id
- `crdb_internal.tenant_span_stats(database_id, table_id)`: returns the tenant's table span statistics for the provided table id

Release note (sql change): new builtin function `tenants_span_stats`,
retrieves the span statistics for the current tenant.
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Feb 24, 2023
Part of: cockroachdb#94332
Part of: cockroachdb#94330

Added new builtin function `tenant_span_stats` that retrieves span
statistics for the current tenant. `tenant_span_stats` can be called as:
- `crdb_internal.tenant_span_stats()`: returns table span statistics for all of the tenants tables
- `crdb_internal.tenant_span_stats(database_id)`: returns table span statistics for the tenant's tables belonging to the specified database id
- `crdb_internal.tenant_span_stats(database_id, table_id)`: returns the tenant's table span statistics for the provided table id

Release note (sql change): new builtin function `tenants_span_stats`,
retrieves the span statistics for the current tenant.
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Feb 27, 2023
Part of: cockroachdb#94332
Part of: cockroachdb#94330

Added new builtin function `tenant_span_stats` that retrieves span
statistics for the current tenant. `tenant_span_stats` can be called as:
- `crdb_internal.tenant_span_stats()`: returns table span statistics for all of the tenants tables
- `crdb_internal.tenant_span_stats(database_id)`: returns table span statistics for the tenant's tables belonging to the specified database id
- `crdb_internal.tenant_span_stats(database_id, table_id)`: returns the tenant's table span statistics for the provided table id

Release note (sql change): new builtin function `tenants_span_stats`,
retrieves the span statistics for the current tenant.
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Feb 28, 2023
Part of: cockroachdb#94332
Part of: cockroachdb#94330

Added new builtin function `tenant_span_stats` that retrieves span
statistics for the current tenant. `tenant_span_stats` can be called as:
- `crdb_internal.tenant_span_stats()`: returns table span statistics for all of the tenants tables
- `crdb_internal.tenant_span_stats(database_id)`: returns table span statistics for the tenant's tables belonging to the specified database id
- `crdb_internal.tenant_span_stats(database_id, table_id)`: returns the tenant's table span statistics for the provided table id

Release note (sql change): new builtin function `tenants_span_stats`,
retrieves the span statistics for the current tenant.
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Mar 2, 2023
Part of: cockroachdb#94332
Part of: cockroachdb#94330

Added new builtin function `tenant_span_stats` that retrieves span
statistics for the current tenant. `tenant_span_stats` can be called as:
- `crdb_internal.tenant_span_stats()`: returns table span statistics for all of the tenants tables
- `crdb_internal.tenant_span_stats(database_id)`: returns table span statistics for the tenant's tables belonging to the specified database id
- `crdb_internal.tenant_span_stats(database_id, table_id)`: returns the tenant's table span statistics for the provided table id

Release note (sql change): new builtin function `tenants_span_stats`,
retrieves the span statistics for the current tenant.
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Mar 2, 2023
Part of: cockroachdb#94332
Part of: cockroachdb#94330

Added new builtin function `tenant_span_stats` that retrieves span
statistics for the current tenant. `tenant_span_stats` can be called as:
- `crdb_internal.tenant_span_stats()`: returns table span statistics for all of the tenants tables
- `crdb_internal.tenant_span_stats(database_id)`: returns table span statistics for the tenant's tables belonging to the specified database id
- `crdb_internal.tenant_span_stats(database_id, table_id)`: returns the tenant's table span statistics for the provided table id

Release note (sql change): new builtin function `tenants_span_stats`,
retrieves the span statistics for the current tenant.
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Mar 2, 2023
Part of: cockroachdb#94332
Part of: cockroachdb#94330

Added new builtin function `tenant_span_stats` that retrieves span
statistics for the current tenant. `tenant_span_stats` can be called as:
- `crdb_internal.tenant_span_stats()`: returns table span statistics for all of the tenants tables
- `crdb_internal.tenant_span_stats(database_id)`: returns table span statistics for the tenant's tables belonging to the specified database id
- `crdb_internal.tenant_span_stats(database_id, table_id)`: returns the tenant's table span statistics for the provided table id

Release note (sql change): new builtin function `tenants_span_stats`,
retrieves the span statistics for the current tenant.
THardy98 pushed a commit to THardy98/cockroach that referenced this issue Mar 2, 2023
Part of: cockroachdb#94332
Part of: cockroachdb#94330

Added new builtin function `tenant_span_stats` that retrieves span
statistics for the current tenant. `tenant_span_stats` can be called as:
- `crdb_internal.tenant_span_stats()`: returns table span statistics for all of the tenants tables
- `crdb_internal.tenant_span_stats(database_id)`: returns table span statistics for the tenant's tables belonging to the specified database id
- `crdb_internal.tenant_span_stats(database_id, table_id)`: returns the tenant's table span statistics for the provided table id

Release note (sql change): new builtin function `tenants_span_stats`,
retrieves the span statistics for the current tenant.
craig bot pushed a commit that referenced this issue Mar 2, 2023
97534: builtins: add `tenant_span_stats` generator function r=THardy98 a=THardy98

Part of: #94332
Part of: #94330

Added new builtin function `tenant_span_stats` that retrieves span
statistics for the current tenant. `tenant_span_stats` can be called as:
- `crdb_internal.tenant_span_stats()`: returns table span statistics for all of the tenants tables
- `crdb_internal.tenant_span_stats(database_id)`: returns table span statistics for the tenant's tables belonging to the specified database id
- `crdb_internal.tenant_span_stats(database_id, table_id)`: returns the tenant's table span statistics for the provided table id

Returned rows take the format:
```
[email protected]:26257/movr> select * from crdb_internal.tenant_span_stats(104);
  database_id | table_id | range_count | approximate_disk_bytes | live_bytes | total_bytes | live_percentage
--------------+----------+-------------+------------------------+------------+-------------+------------------
          104 |      106 |          27 |                  53866 |      16821 |       16821 |               1
          104 |      107 |          27 |                  45285 |      10932 |       10932 |               1
          104 |      108 |          27 |                 225645 |     528219 |      528219 |               1
          104 |      109 |           3 |                 130978 |     260826 |      260826 |               1
          104 |      110 |           3 |                 393815 |     708387 |      708387 |               1
          104 |      111 |           3 |                  30021 |       1413 |        1413 |               1
```

**Note**: This implementation is naive - it calls `SpanStats` for each table span. Future work will entail expanding the endpoint to be able to fetch span statistics for multiple spans at once.

Release note (sql change): new builtin function `tenants_span_stats`,
retrieves the span statistics for the current tenant.

Co-authored-by: Thomas Hardy <[email protected]>
@world2mark
Copy link

This comment is the same from (#94332): From a UI perspective, it's waiting on CRDB to return the actual result-set, resulting in a spinning hour-glass that ultimately leads to an error dialog-panel saying "unknown" (or something to that effect). If we have the potential to run a query that takes a long time, it should never be in a blocking-state. CRDB should send back a job-ID of this UI-request, and let the UI poll against this Job-ID to get a %completed result, allowing the user to continue operating in the UI.
Furthermore, massive lists of DBs or Tables is a reality, so maybe it's time to implement HTML pagination or other best-practices in UI development.

@THardy98
Copy link

THardy98 commented Jul 4, 2023

Addressed by: #103752

@THardy98 THardy98 closed this as completed Jul 4, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
A-sql-observability Related to observability of the SQL layer A-webui-databases A-webui-scaling C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.
Projects
None yet
Development

No branches or pull requests

3 participants