After finishing the rewrite rule to merge the child path steps, I ran a few
tests. The results of the query's and plans are attached.
First I noted when the following group of operators were added to the plan,
the time changed by 4 minutes (from 35s to 4m27s).
subplan {
aggregate [$$19] <- [function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}sequence,
Args:[function-call: vxquery:{urn:org.apache.vxquery.operators-ext}child,
Args:[function-call: vxquery:{urn:org.apache.vxquery.operators-ext}treat,
Args:[%0->$$17, {http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] =
[1d000000ee])], {http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] =
[1d0000010b])]]]
-- AGGREGATE |LOCAL|
unnest $$17 <- function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}iterate, Args:[%0->$$15]
-- UNNEST |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
The above query plan section appears twice in the original query. If each
takes 4 minutes that would account for most of the time. My test with the
original query has a time of 9m16.336s.
I suggest a rewrite rule that could change this plan section to a single
assign.
Does anything in this plan section stand out as being slow? Is it just the
number of operators? The child path step function is fairly fast.
On Tue, Dec 3, 2013 at 3:48 PM, Eldon Carman <[email protected]> wrote:
> The first query (q00.xq) was executed 10 times on the 10 stations of data.
> The data contains 6,827 files (/dataCollection) with 206,686 sensor
> readings (/dataCollection/data) amounting to ~55 MB. The query was executed
> 10 times to remove the overhead of starting and stoping the cluster and
> node controllers in VXQuery.
>
> (: XQuery Filter Query :)
> (: See historical data for Riverside, CA (ASN00008113) station by
> selecting :)
> (: the weather readings for December 25 over the last 10 years.
> :)
> let $collection := "/tmp/1.0_partition_ghcnd_all_xml/sensors"
> for $r in collection($collection)/dataCollection/data
> let $date := xs:date(fn:substring(xs:string(fn:data($r/date)), 0, 11))
> where $r/station eq "GHCND:ASN00008113"
> and fn:year-from-date($date) >= (2003)
> and fn:month-from-date($date) eq 12
> and fn:day-from-date($date) eq 25
> return $r
>
> Saxon processed this query 10 times in 35.936s with an average of 3.5936s
> per query.
> VXQuery processed this query 10 times in 504.715s with an average
> of 50.4715s per query.
>
> I ran the query again with out the date filter options. The query returns
> all data from station GHCND:ASN00008113.
> Saxon processed this query 10 times in 35.953s with an average of 3.5953s
> per query.
> VXQuery processed this query 10 times in 376.325s with an average
> of 37.6325s per query.
>
> The below modified query takes an average of 4.0028s. The query basically
> touches each sensor reading but does nothing. The select is much simpler
> and the plan does not have two subplans for paths steps used in the select.
>
> let $collection := "/tmp/1.0_partition_ghcnd_all_xml/sensors/ASN"
> for $r in collection($collection)/dataCollection/data
> where empty($r)
> return $r
>
> The process seems to take a lot of time to prepare data and then execute
> the select for the where clause.
>
> Notes on VXQuery performance:
> ========================
> The frame size was set to 1 MB.
> The cpu is at 100% to 260% on a 8 core machine. (100% is one core is being
> fully used)
> The disk has sporadic activity.
> The system has one cluster controller and one node controller set up from
> inside the CLI script.
>
> Suggested Options:
> 1. Remove the subplans for path steps going into the select.
> * The subplan iterates over a field created by an unnest operator. The
> unnest operator is guaranteed to produce single value items. The subplan is
> not required when the input is a single item that gets iterated over then
> result aggregated back together. The process could be a simple assign for
> the value inside the aggregate (including the rest of the nested plan
> operators minus the unnest).
> 2. Project unused variables out of the tuple during local execution.
> * Depends on how the tuples are being passes between operators. Right
> now a lot of information is stored in the tuple (XML file, all path steps,
> etc.). Reducing the size could help with coping less information during
> each new path step.
>
> Questions?
> * Can you track to see which operators are taking the longest?
> * Can you explain the tuple stream and how it interacts with each
> operator? Is there one stream? Does it only grow or change size at each
> operator?
>
>
> On Mon, Dec 2, 2013 at 8:14 PM, Vinayak Borkar <[email protected]> wrote:
>
>> Preston,
>>
>> Let me suggest a way to track down our performance issues in VXQuery.
>> Let's approach our queries one at a time. First, let's start with the
>> single collection, scan-based queries and reason about their performance in
>> comparison to Saxon. As an even smaller goal, can you take your first query
>> and report running times on the 250MB of data alongwith Saxon's running
>> times?
>>
>> Thanks,
>> Vinayak
>>
>>
>>
>>
>> On 11/29/13, 12:48 PM, Eldon Carman wrote:
>>
>>> The query plans are so big, I attached a document with the queries and
>>> plans.
>>>
>>>
>>> On Wed, Nov 27, 2013 at 8:53 PM, Vinayak Borkar <[email protected]
>>> <mailto:[email protected]>> wrote:
>>>
>>> Preston,
>>>
>>> For each query, please send the following:
>>>
>>> 1. The query
>>> 2. The translated logical plan
>>> 3. The optimized physical plan
>>>
>>> Thanks,
>>> Vinayak
>>>
>>>
>>>
>>> On 11/27/13, 8:16 PM, Eldon Carman wrote:
>>>
>>> It appears that our query process is taking longer than
>>> expected. I have
>>> created a small set of sensors to test our benchmark queries.
>>> The data set
>>> is about 250 MB and the queries execute in 10 to 20 seconds with
>>> the SAXON
>>> XSLT processor. When I tried a few of the queries on VXQuery,
>>> the process
>>> ran for one hour and still did not complete. I am now looking
>>> into where
>>> the time is being spent for our query and see why its taking so
>>> long.
>>>
>>>
>>>
>>>
>>
>
>
Test for q00 (filter) query
(: XQuery Filter Query :)
(: See historical data for Riverside, CA (ASN00008113) station by selecting :)
(: the weather readings for December 25 over the last 10 years. :)
let $collection := "/tmp/1.0_partition_ghcnd_all_xml/sensors/ASN/"
for $r in collection($collection)/dataCollection/data
let $date := xs:date(fn:substring(xs:string(fn:data($r/date)), 0, 11))
where $r/station eq "GHCND:ASN00008113"
and fn:year-from-date($date) >= (2003)
and fn:month-from-date($date) eq 12
and fn:day-from-date($date) eq 25
return $r
Saxon Execution time: 0m36.009s
VXQuery Execution time: 9m16.336s
Below are the simplified queries, runtimes, and optimized plans.
The goal was to start with a base query that did not produce output
and slowly add items to the plan to find the piece that is slow.
Times are based on 10 queries.
—————————————————————
let $collection := "/tmp/1.0_partition_ghcnd_all_xml/sensors/ASN/"
for $r in collection($collection)/dataCollection/data
where fn:false()
return $r
VXQuery Execution time: 0m35.042s
distribute result [%0->$$18]
-- DISTRIBUTE_RESULT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest $$18 <- function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}iterate, Args:[%0->$$16]
-- UNNEST |PARTITIONED|
assign [$$16] <- [function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}treat, Args:[%0->$$5,
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d000000e7])]]
-- ASSIGN |PARTITIONED|
select (function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}boolean, Args:[function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}false, Args:[]])
-- STREAM_SELECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$5] <-
org.apache.vxquery.metadata.VXQueryCollectionDataSource@245215e7
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
let $collection := "/tmp/1.0_partition_ghcnd_all_xml/sensors/ASN/"
for $r in collection($collection)/dataCollection/data
where $r/station eq "GHCND:ASN00008113"
and fn:false()
return $r
VXQuery Execution time: 4m38.490s
distribute result [%0->$$27]
-- DISTRIBUTE_RESULT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest $$27 <- function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}iterate, Args:[%0->$$25]
-- UNNEST |PARTITIONED|
assign [$$25] <- [function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}treat, Args:[%0->$$5,
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d000000e7])]]
-- ASSIGN |PARTITIONED|
select (function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}boolean, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}and, Args:[function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}boolean, Args:[function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}data, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}value-eq, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}promote, Args:[function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}data, Args:[%0->$$19],
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d00000002])],
function-call: vxquery:{urn:org.apache.vxquery.operators-ext}promote,
Args:[function-call: vxquery:{http://www.w3.org/2005/xpath-functions}data,
Args:[{http://www.w3.org/2001/XMLSchema}string QUANT_ONE(bytes[20] =
[0400114748434e443a41534e3030303038313133])],
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d00000002])]]]],
function-call: vxquery:{http://www.w3.org/2005/xpath-functions}boolean,
Args:[function-call: vxquery:{http://www.w3.org/2005/xpath-functions}data,
Args:[function-call: vxquery:{http://www.w3.org/2005/xpath-functions}false,
Args:[]]]]])
-- STREAM_SELECT |PARTITIONED|
subplan {
aggregate [$$19] <- [function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}sequence, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}child, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}treat, Args:[%0->$$17,
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d000000ee])],
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d0000010b])]]]
-- AGGREGATE |LOCAL|
unnest $$17 <- function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}iterate, Args:[%0->$$15]
-- UNNEST |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
assign [$$15] <- [function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}treat, Args:[%0->$$5,
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d000000e7])]]
-- ASSIGN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$5] <-
org.apache.vxquery.metadata.VXQueryCollectionDataSource@4ceee548
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
*** I removed the PushSelectDownRule and ran the following query.
let $collection := "/tmp/1.0_partition_ghcnd_all_xml/sensors/ASN/"
for $r in collection($collection)/dataCollection/data
let $station := $r/station
where fn:false()
return $station
VXQuery Execution time: 4m27.124s
-- DISTRIBUTE_RESULT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest $$24 <- function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}iterate, Args:[%0->$$22]
-- UNNEST |PARTITIONED|
assign [$$22] <- [function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}treat, Args:[%0->$$19,
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d000000e7])]]
-- ASSIGN |PARTITIONED|
select (function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}boolean, Args:[function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}false, Args:[]])
-- STREAM_SELECT |PARTITIONED|
subplan {
aggregate [$$19] <- [function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}sequence, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}child, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}treat, Args:[%0->$$17,
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d000000ee])],
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d0000010b])]]]
-- AGGREGATE |LOCAL|
unnest $$17 <- function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}iterate, Args:[%0->$$15]
-- UNNEST |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
assign [$$15] <- [function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}treat, Args:[%0->$$5,
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d000000e7])]]
-- ASSIGN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$5] <-
org.apache.vxquery.metadata.VXQueryCollectionDataSource@6a109ac
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
*** I removed the PushSelectDownRule and ran the following query.
let $collection := "/tmp/1.0_partition_ghcnd_all_xml/sensors/ASN/"
for $r in collection($collection)/dataCollection/data
let $date := xs:date(fn:substring(xs:string(fn:data($r/date)), 0, 11))
where fn:false()
return $date
VXQuery Execution time: 4m27.290s
-- DISTRIBUTE_RESULT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
unnest $$30 <- function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}iterate, Args:[%0->$$28]
-- UNNEST |PARTITIONED|
assign [$$28] <- [function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}treat, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}cast, Args:[function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}substring, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}promote, Args:[function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}data, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}cast, Args:[function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}data, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}treat, Args:[%0->$$19,
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d000000e9])]],
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d00000007])]],
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d00000007])],
function-call: vxquery:{urn:org.apache.vxquery.operators-ext}promote,
Args:[function-call: vxquery:{http://www.w3.org/2005/xpath-functions}data,
Args:[{http://www.w3.org/2001/XMLSchema}integer QUANT_ONE(bytes[9] =
[190000000000000000])], {http://www.w3.org/2001/XMLSchema}int
QUANT_ONE(bytes[5] = [1d00000065])], function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}promote, Args:[function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}data,
Args:[{http://www.w3.org/2001/XMLSchema}integer QUANT_ONE(bytes[9] =
[19000000000000000b])], {http://www.w3.org/2001/XMLSchema}int
QUANT_ONE(bytes[5] = [1d00000065])]], {http://www.w3.org/2001/XMLSchema}int
QUANT_ONE(bytes[5] = [1d00000043])], {http://www.w3.org/2001/XMLSchema}int
QUANT_ONE(bytes[5] = [1d000000e7])]]
-- ASSIGN |PARTITIONED|
select (function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}boolean, Args:[function-call:
vxquery:{http://www.w3.org/2005/xpath-functions}false, Args:[]])
-- STREAM_SELECT |PARTITIONED|
subplan {
aggregate [$$19] <- [function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}sequence, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}child, Args:[function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}treat, Args:[%0->$$17,
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d000000ee])],
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d0000010b])]]]
-- AGGREGATE |LOCAL|
unnest $$17 <- function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}iterate, Args:[%0->$$15]
-- UNNEST |LOCAL|
nested tuple source
-- NESTED_TUPLE_SOURCE |LOCAL|
}
-- SUBPLAN |PARTITIONED|
assign [$$15] <- [function-call:
vxquery:{urn:org.apache.vxquery.operators-ext}treat, Args:[%0->$$5,
{http://www.w3.org/2001/XMLSchema}int QUANT_ONE(bytes[5] = [1d000000e7])]]
-- ASSIGN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
data-scan []<-[$$5] <-
org.apache.vxquery.metadata.VXQueryCollectionDataSource@2b64ff3b
-- DATASOURCE_SCAN |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
