ILM policies retrieval API is taking a long time in a large cluster

[nielsbauman]

We've had reports of users running into issues while trying to load their ILM policies through Kibana, as the GET /_ilm/policy request is taking a long time when there are a lot of indices that have an ILM policy configured.

The reason that this call is taking so long, is that it's computing the in_use_by response field. However, currently, Kibana shows the Linked Indices column which is based on that response field. Therefore, we'll need to come up with a solution together with the Kibana team.

Possible solutions discussed thus far are:

1. Optimizing the in_use_by computation in ES itself. This would benefit everyone (Kibana users and direct ES users) and would not require any changes in Kibana. The downside is that we're unsure how much we're going to be able to shave off this response time.
2. Making the in_use_by field opt-out which would allow Kibana to still use that endpoint to show the list of ILM policies, but in a much quicker way. The downside is that we'll have to come up with another way of showing the number of indices that are using a specific ILM policy. (it might be worth checking if people actually use this information currently)
3. Adding a request parameter along the lines of max_resources which would limit the number of linked indices that are returned. This would require some more investigation to assess whether this would actually improve performance on the ES side or not. Additionally, this would require Kibana to make it clear to users that there are, in fact, more indices.

[elasticsearchmachine]

Pinging @elastic/es-data-management (Team:Data Management)

[nielsbauman]

While discussing this internally, we have decided to start by giving option 1 a go (optimizing the endpoint itself). If that (potential) speed-up turns out to be insufficient, we can always reconsider the other options.

[nielsbauman]

I just ran another benchmark and with the setup I created, a call to GET /_ilm/policy on main takes roughly 2 minutes, whereas on my branch it takes less than 0.5 seconds. I'm good to go on this performance-wise.

#106953 (comment)

With this massive speedup, I'll consider this issue to be fixed. In the future, we can improve even more by running this on the local (coordinating) node instead of always on the master node (i.e. #101805).