ui: initial load of table list takes minutes (!) when there are 10000+ tables in db #94332

knz · 2022-12-27T18:15:48Z

(Found via #94027)

Describe the problem

When there are 10000+ tables in a single db, the initial display of the table list takes 30+ seconds, leaving the user to wonder if something is broken.

To Reproduce

start cockroach demo with the changes from sql: new functions crdb_internal.generate_test_objects and .gen_rand_ident #94027
generate 10000 tables, e.g. via select crdb_internal.generate_test_objects('{"counts":[10000],"name_gen":{"noise":false}}'::jsonb);
navigate to db console -> defaultdb eg. via http://127.0.0.1:8080/#/database/defaultdb

Observe: the page remains blank for very long.

Expected behavior

The first page of results should display immediately.

Additional data / screenshots

Environment:

master towards v23.1

Epic: CRDB-20388

Jira issue: CRDB-22835

The text was updated successfully, but these errors were encountered:

…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`

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.

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 · 2023-04-05T18:43:54Z

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.

The databases page displays partial results instead of just showing an error message. Sorting is disabled if there are more than 2 pages of results which is currently configured to 40dbs. This still allows most user to use sort functionality, but prevents large customers from breaking when it would need to do a network call per a database. The database details are now loaded on demand for the first page only. Previously a network call was done for all databases which would result in 2k network calls. It now only loads the page of details the user is looking at. part of: cockroachdb#94332 Release note: none

THardy98 · 2023-07-04T14:27:06Z

Addressed by: #103752

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

knz mentioned this issue Dec 27, 2022

ui: initial load of table list takes minutes (!) when there are 10000+ tables in DB #94331

Closed

maryliag assigned THardy98 Jan 10, 2023

THardy98 mentioned this issue Jan 26, 2023

sql: new functions crdb_internal.ranges_in_span and crdb_internal.tenant_ranges_per_table #96011

Closed

THardy98 mentioned this issue Feb 22, 2023

builtins: add tenant_span_stats generator function #97534

Merged

cockroach-teamcity mentioned this issue Mar 3, 2023

PR #97534 - builtins: add tenant_span_stats generator function cockroachdb/docs#16409

Open

world2mark mentioned this issue Apr 5, 2023

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

Closed

THardy98 closed this as completed Jul 4, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ui: initial load of table list takes minutes (!) when there are 10000+ tables in db #94332

ui: initial load of table list takes minutes (!) when there are 10000+ tables in db #94332

knz commented Dec 27, 2022 •

edited by cockroach-jira-scripts

Loading

world2mark commented Apr 5, 2023

THardy98 commented Jul 4, 2023

ui: initial load of table list takes minutes (!) when there are 10000+ tables in db #94332

ui: initial load of table list takes minutes (!) when there are 10000+ tables in db #94332

Comments

knz commented Dec 27, 2022 • edited by cockroach-jira-scripts Loading

world2mark commented Apr 5, 2023

THardy98 commented Jul 4, 2023

knz commented Dec 27, 2022 •

edited by cockroach-jira-scripts

Loading