I have an Elasticsearch 5.6 index with 112 million documents wth response times of about a second for different aggregate queries. I am trying to test Dremio and the idea is to use it to join the index with a lookup table with 77 values, stored in S3.
The situation is that after creating the reflection with a few fields, the response time is about 20 seconds which is better than not getting any results as it occurred initially without a reflection, but not nearly the response time that I need. Also, if I add a WHERE clause using a field that exists in the reflection, the reflection is not picked up and the query doesn’t return.
I am probably doing something wrong but I am not sure what it is.
My cluster is one c4.2xlarge instance for the coordinator and one m5.4xlarge instance for the executor.
Memory configuration for the executor is:
Memory configuration for the coordinator is:
Using EBS for both servers with no IOPS for now.
The Elasticsearch server, which is the underlying source, is a single c5.9xlarge node also using EBS with no IOPS.
The profile of the query that never returns is attached. Also the profile of the query that picks up the reflection but takes over 20 seconds to return is attached.
query_uses_reflection_but_takes_over_20s.zip (15.8 KB)
query_never_returns.zip (15.8 KB)
In fact thinking about it, try setting the heap size to say 4GB, and try rerunning the query taking 24 seconds. I want to see if that 20 second wait at the start is consistent. It’s possible you were unlucky and e.g. got stuck waiting on a GC process to finish.
Christy, thank you for your reply and directions. It made sense when you said that the 20 seconds was wait time. My application includes 19 widgets that can trigger simultaneous filters by selecting their visualization elements. The queries were all queued up. I increased the cluster resources and now they all execute within a couple of seconds.
The reflection is now also picked up. It turned out that if I don’t join with the S3 lookup table, Dremio picks up the reflection, providing that the WHERE clause uses a field included in the reflection. I can provide more details in this area if needed.
That’s great news
Is everything OK for you now, or are you still looking for further improvements?
Seems like you should still be able to pick up the reflection if you do a join. Maybe you can share profiles for both scenarios?
Kelly, Christy, thanks for following up. I was happy to see my initial queries accelerated and today I tried adding a raw reflection to the virtual data set as Christy suggested and I believe that made the difference for the join queries. The fused dataset is being accelerated as well as queries using DATE_TRUNC() which were not being picked up before. All this is great.
Queries that include a WHERE clause like the one below still take a long time (19s) even though they are accelerated. My goal is to reach 1-2 seconds. I wonder if I should add more processors, provision IOPS or both, or something else.
WHERE (“trip_minutes” BETWEEN 10 AND 1439.98)
I am attaching the profile of the query that takes 19s. On the cluster side, my executor node is a c5.9xlarge and the coordinator is a c4.2xlarge. I am using AWS EBS instances.
e6f70295-b383-46d3-9c16-a4bd9a4a37b2.zip (19.7 KB)
Thanks again for the help!
Christy, thanks for the directions. I created a quick video with the data application we built on top of Dremio: https://youtu.be/tnsuI8hqdM4.