Just make sure this email has been heard properly... I don't have too much expertise on nested field access, but I think this is a fair comparison excluding the effects of storage layer (both datasets are cached). Moreover, it's also highly unlikely that MongoDB cheats by building some key index, as Rana is not accessing the primary key here...
Best regards, Chen Luo On Sat, Jan 27, 2018 at 9:15 PM, Rana Alotaibi <[email protected]> wrote: > Hi all, > > I have a follow-up issue using the same query. Let's forget about the join > and selection predicates. I have the following query (same as previous > one), but without the join and selection predicates: > > *AsterixDB Query: * > USE mimiciii; > SET `compiler.parallelism` "5"; > SET `compiler.sortmemory` "128MB"; > SET `compiler.joinmemory` "265MB"; > SELECT COUNT(*) AS cnt > FROM PATIENTS P, P.ADMISSIONS A, A.LABEVENTS E > > Result : {cnt:22237108} > > Please note I have changed the page-size default configuration from 128 KB > to 1MB, and I have the buffercache stays that same ( 57GB) > > *MongoDB Equivalent Query:* > db.patients.aggregate( > [ > { > "$unwind":"$ADMISSIONS", > > }, > { > "$unwind":"$ADMISSIONS.LABEVENTS", > > }, > > { $count: "cnt } > > ] > ) > > Result : {cnt:22237108} > > The query takes ~7min in AsterixDB, and 30sec in MongoDB given that > MongoDB is running on a single core. I don't think that MongoDB storage > compression techniques play some factor here (All the data is cached in > memory) unless MongoDB does some in-memory compression (Which I need to > investigate more about that). > > Does this explain that navigating deeply into nested fields is an > expensive operation in AsterixDB? or I do still have some issues with > AsterixDB configuration parameters?. > > Thanks > Rana > > > On Sat, Jan 27, 2018 at 3:45 PM, Rana Alotaibi <[email protected]> > wrote: > >> Hi Mike, >> >> Here is some results: >> 1) Non-reordered FROM clause+ no bcast added ~12mins >> 2) Reordered FROM clause + no bcast added ~12mins (same as (1) ) >> 3) Non-reordered FROM clause+ bcast added ~6mins >> 4) Reordered FROM clause+bacst added ~6mins >> >> It seems the FROM clause datasets order has no impact. But in both cases, >> the bcast reduced the execution time. >> >> As for querying MongoDB, I'm almost writing a "logical" plan for that >> query (It took me days to understand MongoDB query operators). I totally >> prefer SQL++ using hints. However, think about data scientists who are >> mostly familiar with SQL queries, I don't expect them to spend time and >> determine for example the predicates selectivity and accordingly decide >> what's the appropriate join algorithms to use and specify this in their >> query (i.e /*indexnl*/) (Basically they end-up doing the cost-based >> optimizer job :) ). >> >> Thanks, >> --Rana >> >> On Fri, Jan 26, 2018 at 2:15 PM, Mike Carey <[email protected]> wrote: >> >>> Rana, >>> >>> We need the physical hints because we have a conservative cost-minded >>> rule set rather than an actual cost-based optimizer - so it always picks >>> partitioned hash joins when doing joins. (I am curious as to how much the >>> bcast hint helps vs. the reordered from clause - what fraction does each >>> contribute to the win? - it would be cool to have the numbers without and >>> with that hint if you felt like trying that - but don't feel obligated). >>> >>> Question: In MongoDB, didn't you end up essentially writing a >>> query-plan-like program to solve this query - and isn't the SQL++ with >>> hints a lot smaller/simpler? (Just asking - I'm curious as to your >>> feedback on that.) We'd argue that you can write a mostly declarative >>> familiar query and then mess with it and annotate it a little to tune it - >>> which isn't as good as a great cost-based optimizer, but is better than >>> writing/maintaining a program. Thoughts? >>> >>> In terms of how good we can get - the size answer is telling. In past >>> days, when we were normally either on par with (smaller things) or beating >>> (larger things) MongoDB, they hadn't yet acquired their new storage engine >>> company (WiredTiger) with its compression. Now they're running 3x+ >>> smaller, I would not be surprised if that's now the explanation for the >>> remaining difference, which is indeed about 3x. (We are going to >>> experiment with compression as well.) >>> >>> Cheers, >>> >>> Mike >>> >>> On 1/26/18 1:58 PM, Rana Alotaibi wrote: >>> >>> Hi all, >>> Thanks for your immediate and quick reply. >>> >>> @Chen Luo : Yes. I have crated an index on FLAG in MongoDB. However, in >>> AsterixDB setting, it seems that navigating deeply in the nested fields and >>> create an index is not supported. >>> @Taewoo Adding /*+indexnl */ didn't change the plan. >>> @Wail : Adding /*+bcast/ did the magic. Now, it takes roughly ~6.34 mins >>> on average without including the warm-up time. The plan has changed and it >>> is attached. >>> >>> Can you please points me where I can find this in the documentation? And >>> here is my question: Why do I need to add some physical details in the >>> query like adding /*+indexnl */ index-nested-loop-join or /*+bcast/ >>> broadcast_exchange? >>> @Michael: The data size is ~7GB for PATEINTS dataset and 1MB for >>> LABITEMS dataset (Both datasets have an open schema). After data ingestion, >>> in my setting the data in MongoDB is ~3GB(It seems MongoDB does some >>> compression) and in AsterixDB is ~10GB. (I have 4 partitions,and I checked >>> the size of files in each partition and the total is ~10GB!) >>> >>> Thanks! >>> --Rana >>> >>> >>> >>> >>> On Fri, Jan 26, 2018 at 12:52 PM, Wail Alkowaileet <[email protected]> >>> wrote: >>> >>>> One thing I noticed is that the "large" unnested arrays are hash >>>> partitioned to the probably "small" index-filtered dataset. >>>> Since the data can fit in memory (7 GB in total), I think >>>> broadcast_exchange may do better in this particular case. >>>> >>>> USE mimiciii; >>>> SET `compiler.parallelism` "5"; >>>> SET `compiler.sortmemory` "128MB"; >>>> SET `compiler.joinmemory` "265MB"; >>>> SELECT P.SUBJECT_ID >>>> FROM PATIENTS P, P.ADMISSIONS A, A.LABEVENTS E, LABITEMS I >>>> WHERE E.ITEMID/*+bcast*/ = I.ITEMID AND >>>> E.FLAG = 'abnormal' AND >>>> I.FLUID='Blood' AND >>>> I.LABEL='Haptoglobin' >>>> >>>> Note: I reordered the FROM clause... >>>> >>>> Another thing is that I think it's a CPU bound query ... and I'm not >>>> sure how MongoDB utilizes CPU resources compared with AsterixDB. >>>> >>>> >>>> >>>> On Fri, Jan 26, 2018 at 10:36 AM, Taewoo Kim <[email protected]> >>>> wrote: >>>> >>>>> PS: UNNEST doc >>>>> https://ci.apache.org/projects/asterixdb/sqlpp/manual.html#U >>>>> nnest_clauses >>>>> >>>>> Best, >>>>> Taewoo >>>>> >>>>> On Fri, Jan 26, 2018 at 10:00 AM, Taewoo Kim <[email protected]> >>>>> wrote: >>>>> >>>>>> Hi Rana, >>>>>> >>>>>> Thank you for attaching your plan. It seems that the selections are >>>>>> correctly made before each join. If your query predicate is selective >>>>>> enough (e.g., I.LABEL = 'Haptoglobin' generates less than 1% of records >>>>>> as >>>>>> the result), I suggest you could try an index-nested-loop-join. Changes >>>>>> are >>>>>> highlighted. And one more question: if LABEVENTS.FLAG is an array, you >>>>>> can't just use "E.FLAG="abnormal". I think you need to use UNNEST. >>>>>> >>>>>> USE mimiciii; >>>>>> SET `compiler.parallelism` "5"; >>>>>> SET `compiler.sortmemory` "128MB"; >>>>>> SET `compiler.joinmemory` "265MB"; >>>>>> SELECT P.SUBJECT_ID >>>>>> FROM LABITEMS I, PATIENTS P, P.ADMISSIONS A, A.LABEVENTS E >>>>>> WHERE I.ITEMID */* +indexnl */ *=E.ITEMID AND >>>>>> E.FLAG = 'abnormal' AND >>>>>> I.FLUID='Blood' AND >>>>>> I.LABEL='Haptoglobin' >>>>>> >>>>>> Best, >>>>>> Taewoo >>>>>> >>>>>> On Fri, Jan 26, 2018 at 9:16 AM, Chen Luo <[email protected]> wrote: >>>>>> >>>>>>> Hi Rana, >>>>>>> >>>>>>> I think the performance issue might related to the access of nested >>>>>>> fields, since the rest performance hot spots (index search, hash join >>>>>>> etc >>>>>>> looks normal to me), and I assume " I.FLUID='Blood' AND >>>>>>> I.LABEL='Haptoglobin'" should be very selective. Since MongoDB >>>>>>> supports array index, did you build an index on L.FLAG using MongoDB? >>>>>>> >>>>>>> @Wail, do you have any clue on nested fields access? >>>>>>> >>>>>>> Best regards, >>>>>>> Chen Luo >>>>>>> >>>>>>> On Fri, Jan 26, 2018 at 1:47 AM, Rana Alotaibi < >>>>>>> [email protected]> wrote: >>>>>>> >>>>>>>> Hi Taewoo, >>>>>>>> >>>>>>>> - >>>>>>>> >>>>>>>> Can you paste the optimized plan? -- Attached the plan (Plan_01.txt) >>>>>>>> >>>>>>>> - >>>>>>>> >>>>>>>> Can you create an index on LABEVENTS.FLAG? -- I couldn't create an >>>>>>>> index on LABEVENTS.FLAG since LABEVENTS is of type array. I got this >>>>>>>> message when I tried to create the index : "msg": "ASX0001: Field type >>>>>>>> array can't be promoted to type object" >>>>>>>> >>>>>>>> - Can you switch the predicate order? -- It seems for me that >>>>>>>> the plan remains the same even if I changed the order of the >>>>>>>> predicates. >>>>>>>> (Attached the plan after changing the order of the predicates >>>>>>>> Plan_02.txt) >>>>>>>> >>>>>>>> Thanks >>>>>>>> >>>>>>>> Rana >>>>>>>> On Thu, Jan 25, 2018 at 11:24 PM, Rana Alotaibi < >>>>>>>> [email protected]> wrote: >>>>>>>> >>>>>>>>> Hi Chen, >>>>>>>>> >>>>>>>>> *How did you import data into the dataset? using "load" or "feed"?* >>>>>>>>> I used "LOAD" (i.e USE mimiciii; LOAD DATASET PATIENTS USING >>>>>>>>> localfs ((\"path\"=\"127.0.0.1:///data/ralotaib/patients.json\"), >>>>>>>>> (\"format\"=\"json\"))). >>>>>>>>> >>>>>>>>> >>>>>>>>> *Which version of AsterixDB are you using? * >>>>>>>>> AsterixDB Master (0.9.3-SNAPSHOT) >>>>>>>>> >>>>>>>>> Thanks! >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> On Thu, Jan 25, 2018 at 10:39 PM, Chen Luo <[email protected]> wrote: >>>>>>>>> >>>>>>>>>> Hi Rana, >>>>>>>>>> >>>>>>>>>> Nice to see you again! You may post to [email protected] >>>>>>>>>> as well to get more feedbacks from our developers. >>>>>>>>>> >>>>>>>>>> Just clarify two things: how did you import data into the >>>>>>>>>> dataset? using "load" or "feed"? And which version of AsterixDB are >>>>>>>>>> you >>>>>>>>>> using? But any way in your case it seems the join takes a lot of >>>>>>>>>> time, and >>>>>>>>>> your data is pretty much cached into the memory... >>>>>>>>>> >>>>>>>>>> Best regards, >>>>>>>>>> Chen Luo >>>>>>>>>> >>>>>>>>>> On Thu, Jan 25, 2018 at 8:46 PM, Rana Alotaibi < >>>>>>>>>> [email protected]> wrote: >>>>>>>>>> >>>>>>>>>>> Hi there, >>>>>>>>>>> >>>>>>>>>>> I have a query that takes ~12.7mins on average (I have excluded >>>>>>>>>>> the warm-up time which was 30mins)!, and I would like to make sure >>>>>>>>>>> that I >>>>>>>>>>> didn't miss any performance tuning parameters ( I have run the same >>>>>>>>>>> query >>>>>>>>>>> on MongoDB, and it took ~2mins). >>>>>>>>>>> >>>>>>>>>>> The query asks to find all patients that have 'abnormal' >>>>>>>>>>> haptoglobin blood test result. (The query result can have duplicate >>>>>>>>>>> values). >>>>>>>>>>> >>>>>>>>>>> *Query:* >>>>>>>>>>> USE mimiciii; >>>>>>>>>>> SET `compiler.parallelism` "5"; >>>>>>>>>>> SET `compiler.sortmemory` "128MB"; >>>>>>>>>>> SET `compiler.joinmemory` "265MB"; >>>>>>>>>>> SELECT P.SUBJECT_ID >>>>>>>>>>> FROM LABITEMS I, PATIENTS P, P.ADMISSIONS A, A.LABEVENTS E >>>>>>>>>>> WHERE I.ITEMID=E.ITEMID AND >>>>>>>>>>> E.FLAG = 'abnormal' AND >>>>>>>>>>> I.FLUID='Blood' AND >>>>>>>>>>> I.LABEL='Haptoglobin' >>>>>>>>>>> >>>>>>>>>>> *Datasets Schema:* >>>>>>>>>>> - PATIENTS and LABITEMS datasets have an open schema. >>>>>>>>>>> - LABITEMS's primary key is ITEMID >>>>>>>>>>> - PATIENTS 's primary key is SUBJECT_ID >>>>>>>>>>> - The JSON schema for both datasets is attached. >>>>>>>>>>> - The DDL for both datasets is attached >>>>>>>>>>> >>>>>>>>>>> *Performance Tuning Parameters:* >>>>>>>>>>> - 4 partitions (iodevices) >>>>>>>>>>> - The total memory size is : 125GB, and I have assigned ~ 57GB >>>>>>>>>>> to the buffercache (storage.buffercache.size). >>>>>>>>>>> - As you can see from the query, I set the parallelism to 5, >>>>>>>>>>> sort-memory to 128MB, join-memory to 265MB. >>>>>>>>>>> - The data size is 7GB >>>>>>>>>>> >>>>>>>>>>> Your feedback is highly appreciated! >>>>>>>>>>> >>>>>>>>>>> --Rana >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>>> >>>> -- >>>> >>>> *Regards,* >>>> Wail Alkowaileet >>>> >>> >>> >>> >> >
