What is the partition/directory structure of your data. Is this by day? On Thu, Jun 23, 2016 at 1:48 PM, Tanmay Solanki <[email protected] > wrote:
> Yeah, so I tried to cache the metadata for 1 day earlier and saw that that > greatly improved the performance. Then I tried doing this for a full 1 > month of data but unfortunately that was not allowed. I had to cache run > "refresh table metadata" on the full iad folder since it does it by > directory and goes into each sub directory and caches the metadata there > and goes back to the top level directory and caches the metadata there. > However, since we are constantly ingesting metrics, it gets to the last day > and is not able to refresh metadata there and seems to be stuck in a loop > (I had it running for 15000 seconds before I killed it). Is there any way > to fix that or get around that without changing the structure of my data? > > On Thursday, 23 June 2016 1:42 PM, Neeraja Rentachintala < > [email protected]> wrote: > > > You might want to enable metadata caching and see if it helps. > > https://drill.apache.org/docs/optimizing-parquet-metadata-reading/ > > On Thu, Jun 23, 2016 at 1:36 PM, Tanmay Solanki > <[email protected] > > wrote: > > > Below is the plan. The amount of files is ~213000 files of parquet data. > > > > 0: jdbc:drill:> explain plan for select count(*) from > > s3.`tables/stats/iad/201604*/`; > > +------+------+ > > | > > > > > > > > > > > > > > text | json | > > +------+------+ > > | 00-00 Screen > > 00-01 Project(EXPR$0=[$0]) > > 00-02 Project(EXPR$0=[$0]) > > 00-03 > > > Scan(groupscan=[org.apache.drill.exec.store.pojo.PojoRecordReader@7cb1a0e8 > [columns > > = null, isStarQuery = false, isSkipQuery = false]]) > > | { > > "head" : { > > "version" : 1, > > "generator" : { > > "type" : "ExplainHandler", > > "info" : "" > > }, > > "type" : "APACHE_DRILL_PHYSICAL", > > "options" : [ ], > > "queue" : 0, > > "resultMode" : "EXEC" > > }, > > "graph" : [ { > > "pop" : "DirectGroupScan", > > "@id" : 3, > > "cost" : 20.0 > > }, { > > "pop" : "project", > > "@id" : 2, > > "exprs" : [ { > > "ref" : "`EXPR$0`", > > "expr" : "`count`" > > } ], > > "child" : 3, > > "initialAllocation" : 1000000, > > "maxAllocation" : 10000000000, > > "cost" : 20.0 > > }, { > > "pop" : "project", > > "@id" : 1, > > "exprs" : [ { > > "ref" : "`EXPR$0`", > > "expr" : "`EXPR$0`" > > } ], > > "child" : 2, > > "initialAllocation" : 1000000, > > "maxAllocation" : 10000000000, > > "cost" : 20.0 > > }, { > > "pop" : "screen", > > "@id" : 0, > > "child" : 1, > > "initialAllocation" : 1000000, > > "maxAllocation" : 10000000000, > > "cost" : 20.0 > > } ] > > } | > > +------+------+ > > 1 row selected (7493.869 seconds) > > Additionally I have the drillbit.log for this query which I will post > > below: > > 2016-06-23 18:25:16,417 [2893d673-3dad-dd21-d5e6-8ef28e0f81c9:foreman] > > INFO o.a.drill.exec.work.foreman.Foreman - Query text for query id > > 2893d673-3dad-dd21-d5e6-8ef28e0f81c9: explain plan for select count(*) > from > > s3.`tables/stats/iad/201604*/` > > 2016-06-23 20:29:45,446 [2893d673-3dad-dd21-d5e6-8ef28e0f81c9:foreman] > > INFO o.a.d.exec.store.parquet.Metadata - Fetch parquet metadata: > Executed > > 218474 out of 218474 using 16 threads. Time: 3474817ms total, > 254.452884ms > > avg, 50344ms max. > > 2016-06-23 20:29:45,446 [2893d673-3dad-dd21-d5e6-8ef28e0f81c9:foreman] > > INFO o.a.d.exec.store.parquet.Metadata - Fetch parquet metadata: > Executed > > 218474 out of 218474 using 16 threads. Earliest start: 431.101000 ?s, > > Latest start: 3474340355.187000 ?s, Average start: 1753982685.665761 ?s . > > 2016-06-23 20:30:10,211 [2893d673-3dad-dd21-d5e6-8ef28e0f81c9:frag:0:0] > > INFO o.a.d.e.w.fragment.FragmentExecutor - > > 2893d673-3dad-dd21-d5e6-8ef28e0f81c9:0:0: State change requested > > AWAITING_ALLOCATION --> RUNNING > > 2016-06-23 20:30:10,211 [2893d673-3dad-dd21-d5e6-8ef28e0f81c9:frag:0:0] > > INFO o.a.d.e.w.f.FragmentStatusReporter - > > 2893d673-3dad-dd21-d5e6-8ef28e0f81c9:0:0: State to report: RUNNING > > 2016-06-23 20:30:10,226 [2893d673-3dad-dd21-d5e6-8ef28e0f81c9:frag:0:0] > > INFO o.a.d.e.w.fragment.FragmentExecutor - > > 2893d673-3dad-dd21-d5e6-8ef28e0f81c9:0:0: State change requested RUNNING > > --> FINISHED > > 2016-06-23 20:30:10,226 [2893d673-3dad-dd21-d5e6-8ef28e0f81c9:frag:0:0] > > INFO o.a.d.e.w.f.FragmentStatusReporter - > > 2893d673-3dad-dd21-d5e6-8ef28e0f81c9:0:0: State to report: FINISHED > > > > > > > > On Thursday, 23 June 2016 11:22 AM, Ted Dunning < > [email protected]> > > wrote: > > > > > > Also, how many files? What format? > > > > Being so slow is an anomaly. > > > > > > > > > > On Thu, Jun 23, 2016 at 11:15 AM, Khurram Faraaz <[email protected]> > > wrote: > > > > > Can you please share the query plan for that long running query here ? > > > > > > On Thu, Jun 23, 2016 at 11:40 PM, Tanmay Solanki < > > > [email protected]> wrote: > > > > > > > I am trying to run a query on Apache drill to simply count the number > > of > > > > rows in a table stored in parquet format in S3. I am running this on > a > > 20 > > > > node r3.8xlarge EC2 instance cluster and I have my direct memory set > to > > > > 80GB, heap memory set to 32GB and set the > > > > planner.memory.max_memory_per_node to a very high value. However, > > > counting > > > > the rows in this table takes around 7662 seconds, or around 2 hours, > > for > > > > drill to finish the query on a 9.93TB, 56 billion rows, and 174 > column > > > > dataset.It seems like, from the logs and the web console that query > > > > planning itself is taking near 99% of the time and actual query > > execution > > > > is almost taking no time. I ran the same query on PrestoDB of a > similar > > > > setup (20 node r3.8xlarge) and found that it completed in 137 seconds > > or > > > > just over 2 minutes. Is there someting wrong with my configuration of > > > drill > > > > possibly or is this what is expected for drill. > > > > > > > > > > > > > > > > > > >
