Expensive queries are causing unnecessary load and delays on Elasticsearch

[rudolf]

Until https://github.com/elastic/kibana/pull/89915 (v7.12.0) saved objects didn't support paging through large result sets. Now that we have _search_after support, plugins who previously paged through "all" results by setting size: 10000 should be refactored to use search after instead.

The problem with creating searches with large batches of 10000 is that it blocks the Elasticsearch thread pool for a long time which negatively impacts the performance of other search queries. Since Kibana started using system indices for the saved objects index in 7.11, this has had a much bigger impact because these searches share a thread pool with the security index. Paging with smaller batches means faster responses per request, allowing the thread pool to interleave Kibana searches with other requests.

In addition to the performance impact on Elasticsearch, large searches also mean large response payloads which blocks the Kibana thread for an extended amount of time. This causes spikes in the event loop delay which impacts the performance of all plugins.

Short term: fix all 10k searches against the saved object indices

The following is a list of plugins performing searches with perPage: 10000. Please audit each occurrence and mark the task as complete with a link to the PR once it has been resolved. These links are based on a quick search, if the linked code isn't searching against a saved objects index with size > 1000 please mark the item as done.

Blocked on https://github.com/elastic/kibana/issues/91175 because that will make it significantly easier for teams to address these issues. Done. Here are docs on the new point-in-time finder.

• [x] @elastic/kibana-app https://github.com/elastic/kibana/blob/master/src/plugins/vis_type_vega/server/usage_collector/get_usage_collector.ts#L70
• [x] @elastic/kibana-app https://github.com/elastic/kibana/blob/master/src/plugins/visualizations/server/usage_collector/get_usage_collector.ts#L42
• [x] https://github.com/elastic/kibana/blob/master/x-pack/plugins/apm/scripts/aggregate-latency-metrics/index.ts#L239
• [x] https://github.com/elastic/kibana/blob/master/x-pack/plugins/apm/server/lib/apm_telemetry/collect_data_telemetry/tasks.ts#L100
• [x] https://github.com/elastic/kibana/blob/master/x-pack/plugins/apm/server/lib/service_nodes/index.ts#L40
• [ ] @elastic/kibana-presentation https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/canvas/server/collectors/custom_element_collector.ts#L147-L153
• [ ] @elastic/kibana-presentation https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/canvas/server/collectors/workpad_collector.ts#L381-L387
• [ ] @elastic/kibana-presentation https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/canvas/public/services/kibana/custom_element.ts#L36
• [ ] @elastic/kibana-presentation https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/canvas/public/services/kibana/workpad.ts#L108
• [ ] @elastic/security-detections-response https://github.com/elastic/kibana/blob/master/x-pack/plugins/lists/server/services/items/get_list_item_by_values.ts#L38
  • https://github.com/elastic/kibana/issues/127688
• [ ] @elastic/security-detections-response https://github.com/elastic/kibana/blob/master/x-pack/plugins/lists/server/services/items/search_list_item_by_values.ts#L38
  • https://github.com/elastic/kibana/issues/127689
• [ ] @elastic/beats https://github.com/elastic/kibana/blob/master/x-pack/plugins/beats_management/server/lib/adapters/beats/elasticsearch_beats_adapter.ts#L142 (and many more in beats_management)
• [ ] @elastic/security-solution https://github.com/elastic/kibana/blob/master/x-pack/plugins/security_solution/public/cases/components/case_view/helpers.ts#L39
• [ ] @elastic/security-solution https://github.com/elastic/kibana/blob/main/x-pack/plugins/security_solution/server/lib/detection_engine/signals/threshold/find_previous_threshold_signals.ts#L82
  • https://github.com/elastic/kibana/issues/127690
• [ ] @elastic/security-solution https://github.com/elastic/kibana/blob/master/x-pack/plugins/security_solution/server/usage/detections/detections_helpers.ts#L166
• [ ] @ml-ui https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/ml/server/usage/collector.ts#L137
• [ ] @ml-ui https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/ml/public/application/util/index_utils.ts#L30
• [ ] @ml-ui https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/ml/server/models/data_recognizer/data_recognizer.ts#L222
• [ ] @ml-ui https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/ml/server/saved_objects/service.ts#L58
• [ ] @ml-ui https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/ml/server/saved_objects/service.ts#L199
• [ ] @ml-ui https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/transform/public/app/hooks/use_search_items/common.ts#L39
• [x] @elastic/kibana-app-services https://github.com/elastic/kibana/blob/8.0/src/plugins/data/public/query/saved_query/saved_query_service.ts#L31
• [ ] @elastic/kibana-app-services https://github.com/elastic/kibana/blob/8.0/src/plugins/data_views/common/data_views/data_views.ts#L115
• [x] @elastic/kibana-telemetry https://github.com/elastic/kibana/blob/8.0/src/plugins/kibana_usage_collection/server/collectors/application_usage/telemetry_application_usage_collector.ts#L88-L95
• [x] @elastic/kibana-telemetry https://github.com/elastic/kibana/blob/8.0/src/plugins/kibana_usage_collection/server/collectors/application_usage/rollups/total.ts#L35-L43
• [x] @elastic/kibana-telemetry https://github.com/elastic/kibana/blob/8.0/src/plugins/kibana_usage_collection/server/collectors/ui_counters/register_ui_counters_collector.ts#L103
• [x] @elastic/kibana-telemetry https://github.com/elastic/kibana/blob/8.0/src/plugins/kibana_usage_collection/server/collectors/ui_counters/register_ui_counters_collector.ts#L112
• [x] @elastic/kibana-telemetry https://github.com/elastic/kibana/blob/8.0/src/plugins/kibana_usage_collection/server/collectors/ui_metric/telemetry_ui_metric_collector.ts#L58
• [x] @elastic/kibana-telemetry https://github.com/elastic/kibana/blob/8.0/src/plugins/kibana_usage_collection/server/collectors/usage_counters/register_usage_counters_collector.ts#L94
• [x] @elastic/kibana-data-discovery https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/graph/public/helpers/use_workspace_loader.ts#L82
• [ ] @elastic/security-detections-response https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/lists/server/routes/delete_list_route.ts#L53
• [ ] @elastic/security-detections-response https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/lists/server/routes/delete_list_route.ts#L156
• [ ] @elastic/security-detections-response https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/lists/server/services/exception_lists/export_exception_list_and_items.ts#L62
• [ ] @elastic/security-asset-management https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/osquery/server/routes/status/create_status_route.ts#L47
• [x] @elastic/kibana-core https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/saved_objects_tagging/server/routes/lib/get_connection_count.ts#L28
• [x] @elastic/kibana-core https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/saved_objects_tagging/server/services/tags/tags_client.ts#L53
• [ ] @security-solution https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/security_solution/server/lib/detection_engine/rule_actions/legacy_get_bulk_rule_actions_saved_object.ts#L47
• [ ] @security-solution https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/security_solution/server/lib/detection_engine/rules/get_export_by_object_ids.ts#L106
• [ ] @elastic/kibana-alerting-services https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/triggers_actions_ui/public/common/lib/data_apis.ts#L66
• [x] @elastic/kibana-stack-management https://github.com/elastic/kibana/blob/8.0/x-pack/plugins/watcher/public/application/lib/api.ts#L157

Medium term

• [ ] Introduce a soft-limit that throws if Kibana searches with a size > 1000 in development mode
• [ ] Introduce better traceability
  • [ ] Stack traces to locate the exact line that created a request which exceeded the soft-limit
  • [ ] Identify the plugin that initiated a request https://github.com/elastic/kibana/issues/77214
• [ ] Replace savedObjects:listingLimit advanced setting with a better UI pattern since users sometimes set this to 10k (some more context in https://github.com/elastic/kibana/issues/46435)

[elasticmachine]

Pinging @elastic/kibana-core (Team:Core)

[lukeelmers]

https://github.com/elastic/kibana/issues/91175 has been addressed, so teams should now be unblocked on moving forward with the short-term fixes outlined here.

[smith]

None of the APM items listed are querying saved object indices. Checked them off.

[alexwizp]

changes related to the KibanaApp team are ready (#99031, #99023, #98914, #98903) but blocked by #99044

[afharo]

All the items for @elastic/kibana-telemetry are listed in #96715. We'll try to prioritize them.

[yctercero]

Just FYI, security solution platform has this ticket we've been trying to get to of moving over to PIT - https://github.com/elastic/kibana/issues/103944

[afharo]

All the @elastic/kibana-telemetry items are handled in https://github.com/elastic/kibana/pull/135689