Yes, for this one I think the problem is our "storage-wide parallelism"
(i.e., only using as many cores as storage partitions for a query like
this - I suspect the plan is all 1:1 connectors until the very top
aggregation step, the combine step for the counts).
On 1/29/18 9:06 AM, Chen Luo wrote:
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
