Failing to retrieve results from ES when the timezone is UTC+1, UTC+2 - Problem started in indexes opened since 1/1/2019

We are seeing very odd behavior when trying to query ES via Kibana or directly (using Postman). If the timezone is set to UTC or a timezone that is UTC+3 and above/UTC-3 and below, queries return fast, while queries ran with UTC+1/UTC+2/UTC-1/UTC-2 have poor performance and get timed out.
We see that when running the exact same query directly to ES with different time zones.
ES version 6.3.1

Thanks in advance!

1 Like

Could you show an example of such query with the mapping?

Thanks for the prompt reply.
Here it is (its a copied Kibana request) :
{"index":"2_newlogs*","ignore_unavailable":true,"timeout":3000000,"preference":1546442297305}
{"version":true,"size":500,"sort":[{"timestamp":{"order":"desc","unmapped_type":"boolean"}}],"_source":{"excludes":},"aggs":{"2":{"date_histogram":{"field":"timestamp","interval":"30m","time_zone":"Asia/Jerusalem","min_doc_count":1}}},"stored_fields":[""],"script_fields":{},"docvalue_fields":["timestamp"],"query":{"bool":{"must":[{"match_all":{}},{"range":{"timestamp":{"gte":1546356433787,"lte":1546442833788,"format":"epoch_millis"}}}],"filter":[],"should":[],"must_not":[]}},"highlight":{"pre_tags":["@kibana-highlighted-field@"],"post_tags":["@/kibana-highlighted-field@"],"fields":{"":{}},"fragment_size":2147483647}}

BTW, when we remove "time_zone":"Asia/Jerusalem" or the histogram query (or both) we get results.

@amnons @iamredlus @farin99

Hey Igor,

I've sent you directly the mapping of one of the indices queried in @Ariel_Assaraf's reply (we wish not to put it in the public domain :slight_smile: ).

Let me know if you need anything else.

Lior.

And you can reproduce this by querying ES directly without kibana? When you get the result after it takes the long time, does it seem correct? Could you possibly execute hot_threads request while running this slow request directly against elasticsearch? It would be interesting to see where it spends this time.

Yes, it is reproduced directly against elasticsearch (using Postman for querying). Removing the time_zone from the date_histogram aggregation (or plainly using "UTC" as the value) eliminates the problem.

Yes, we are reproducing this with/without Kibana while changing the timezone to UTC works both in Kibana and directly.
@iamredlus or @amnons can probably help with the hot_threads req

Can you try running it with Etc/GMT+2instead of Asia/Jerusalem? I am starting to suspect that it might be Mitigate date histogram slowdowns with non-fixed timezones. by jpountz · Pull Request #30534 · elastic/elasticsearch · GitHub, but I am not really sure why it started to happen only after jan 1 though.

I've run a hot_threads request while the query was running. Around 10 seconds have passed from when I sent the query until the hot_threads has been sent. Query finally returned after 49.827 seconds.
Sent you the hot_threads pastebin in a DM (filtered in only the 6 nodes the index resides on).

Running the query with "UTC" - query returns within 1.220 seconds.
Running the query with "Etc/GMT+2" - query returns within 1.341 seconds.

BTW this worked (and still works) on indexes up to 31/12/2018 and isn't working on 01/01/2019, 02/01/2019.

So https://github.com/elastic/elasticsearch/pull/30534 doesn't seem relevant :expressionless:

Hmm this seems to point towards the issue that I have linked.

Do you create indices daily? Did you upgrade elasticsearch recently?

@jpountz, any thoughts on this?

We are running elasticsearch 6.3.2 and create indices daily. Does this have any effect on querying?

Hey @Igor_Motov and @jpountz
Any updates on the issue?

Thanks!

@iamredlus Sorry for the lag, would you mind sharing hot threads again?

@jpountz we no longer have these available for the circumstances described.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.