Sure, please find them attached Boris:
Audit-LookupForLinetag.sparql - corresponding to the first of query
plans (cantor-linetag-lookup-with-binding)
ClassViewQuery.sparql - corresponding to the second of query plans
(cantor-class-view)
Kind Regards,
Anthony
On Fri, 2019-04-26 at 18:06 +0000, Boris Pelakh wrote:
> Can you send me the text of the actual SPARQL query?
>
> Boris Pelakh
> Ontologist, Developer, Software Architect
> boris.pel...@semanticarts.com
> +1-321-243-3804
>
>
> -----Original Message-----
> From: Anthony Schiller <anthony.schil...@exfo.com>
> Sent: Thursday, April 25, 2019 5:15 PM
> To: dev@rya.incubator.apache.org
> Cc: Boris Pelakh <boris.pel...@semanticarts.com>
> Subject: RE: improving query performance
>
> Thanks for offering to take a look Boris. The query plans don’t look
> a too different before- and after- optimisation, which in itself may
> indicate that something is missing as I would have thought the joins
> would be subject to re-ordering in view of cardinalities. I've
> attached query plans for two queries separated by headers "BEFORE"
> and "AFTER", with query plans marked out by header "
> =================== Rya Query ===================".
>
> -----Original Message-----
> From: Boris Pelakh <boris.pel...@semanticarts.com>
> Sent: 25 April 2019 18:51
> To: dev@rya.incubator.apache.org
> Subject: RE: improving query performance
>
> I am not very familiar with the join optimizer, but I have decent
> idea of what optimal query evaluation strategy should be. Can you
> provide the query and the before- and after-optimizer query plans?
>
> Boris Pelakh
> Ontologist, Developer, Software Architect
> boris.pel...@semanticarts.com
> +1-321-243-3804
>
>
> -----Original Message-----
> From: Anthony Schiller <anthony.schil...@exfo.com>
> Sent: Thursday, April 25, 2019 12:36 PM
> To: dev@rya.incubator.apache.org
> Subject: Re: improving query performance
>
> Additional investigation into the performance of joins revealed that
> queries are better served by setting the configuration parameter
> “setUseJoinSelectivity” on
> org.apache.rya.accumulo.AccumuloRdfConfigurationBuilder to true. To
> work against this configuration I needed to setup the selectivity
> table (as per the documentation here
> https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fapache%2Fincubator-rya%2Fblob%2Fmaster%2Fdao%2Faccumulo.rya%2Fsrc%2Fmain%2Fjava%2Forg%2Fapache%2Frya%2Faccumulo%2FAbstractAccumuloRdfConfigurationBuilder.java&data=02%7C01%7Canthony.schiller%40exfo.com%7Ce350a0538c3749807c3f08d6ca71f195%7C1c75be0f25694bcc95f73ad9d904f42a%7C0%7C0%7C636918988073343904&sdata=9yHIUzjoRxeJXEGPtRnjvD7Rug258VvD49yKHQ1wavg%3D&reserved=0
> ). The selectivity table in turn requires a prospects table to have
> been generated, which I managed using the guidelines supplied here
> https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fapache%2Fincubator-rya%2Fblob%2Fmaster%2Fextras%2Frya.manual%2Fsrc%2Fsite%2Fmarkdown%2Feval.md&data=02%7C01%7Canthony.schiller%40exfo.com%7Ce350a0538c3749807c3f08d6ca71f195%7C1c75be0f25694bcc95f73ad9d904f42a%7C0%7C0%7C636918988073343904&sdata=ruGDrohQDJD7tfHZQteQGM9XyJevCgDIgetS4JggS0I%3D&reserved=0
> .
>
>
> For 9.8 million entries to each triple table, I generated a prospects
> table with approximately 22.5 million entries and a selectivity table
> with 93.2 million entries.
>
> With the above in place, when running a query I get the following
> logged just ahead of the query plan being printed “Entering join
> optimizer!”. This is being reported out of
> org.apache.rya.rdftriplestore.evaluation.QueryJoinSelectOptimizer and
> is perhaps where the magic happens. Sadly though the response times
> to my queries are not demonstrating this, on the contrary the queries
> are taking far longer to run. For example, a query that used to run
> in c. 6 minutes has yet to complete after c. 50 minutes.
>
> I am happy to supply any further details/ information to help
> identify why things are not performing as one might expect. Please
> let me know what would help in this process.
>
> Thanks for you support,
> Anthony
>
> On Thu, 2019-04-18 at 19:06 +0100, Anthony Schiller wrote:
> > Hi, I'm working through an evaluation of Rya for my team here at
> > Exfo.
> > As part of this we are running some benchmarks to compare Rya to
> > other
> > graph stores -
> >
https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fmerck.github.io%2FHalyard%2F&data=02%7C01%7Canthony.schiller%40exfo.com%7Ce350a0538c3749807c3f08d6ca71f195%7C1c75be0f25694bcc95f73ad9d904f42a%7C0%7C0%7C636918988073353913&sdata=nbjkMSAJKLYe75P2%2BTNB8DX3lgw6rEKO9MLdz2znHwM%3D&reserved=0
> > and
> > https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.stardog.com%2F&data=02%7C01%7Canthony.schiller%40exfo.com%7Ce350a0538c3749807c3f08d6ca71f195%7C1c75be0f25694bcc95f73ad9d904f42a%7C0%7C0%7C636918988073353913&sdata=Dsbh2faz4EK%2B9UZf%2B5liaTCH9srVFre7bKNcx31iDQc%3D&reserved=0
> > .
> >
> > When it comes the querying performance we have a range of queries
> > that
> > vary in complexity. For some Rya seems to return the correct
> > result
> > quickly but for others it takes far longer we might have expected,
> > far
> > longer than Halyward and Stardog. It is certainly possible we are
> > not
> > making best use of the tools that Rya offers to demonstrate the
> > performance it is capable of providing to these queries. There are
> > certainly a lot of variables at play, we could well be making
> > heavy
> > work of some aspects to servicing these queries. One concern I have
> > is
> > the level of traffic running between my test node (NOT part of the
> > accumulo cluster) from which I run a query through the Rya Sail
> > across
> > to the (accumulo) data nodes - this I infer from the following
> > log
> > output being busy reported on data nodes:
> >
> > 2019-04-18 16:03:45,419 [tserver.TabletServer] DEBUG: MultiScanSess
> > 192.168.X.YYY:44646 0 entries in 0.00 secs (lookup_time:0.00 secs
> > tablets:1 ranges:1,000)
> >
> > where 192.168.X.YYY is my test node and observe using iftop (
> >
https://nam05.safelinks.protection.outlook.com/?url=http:%2F%2Fwww.ex-parrot.com%2F~pdw%2Fiftop%2F&data=02%7C01%7Canthony.schiller%40exfo.com%7Ce350a0538c3749807c3f08d6ca71f195%7C1c75be0f25694bcc95f73ad9d904f42a%7C0%7C0%7C636918988073353913&sdata=OuJ9z6P6%2FporNRLxknw46cfR9u%2BY5AmuD%2Be3qA8P9xE%3D&reserved=0
> > ).
> >
> > I thought that the query would largely be serviced locally on the
> > data
> > nodes, with some result merging happening as a finally step as
> > results
> > are returned from the data nodes. I was anticipating the queries
> > would
> > return quickly for the 9.8 million quad-statements we have loaded
> > into
> > the data nodes with it being possible to perform full scans of
> > this
> > volume of data in short-order. Though understandably if the data
> > is
> > being pushed from the data node to my test node for query
> > evaluation
> > this overhead will impact performance.
> >
> > I attach the following:
> > 1) Ambari blueprint cluster configurations
> > 2) Screenshots from the Accumulo web-ui for some insight into how
> > this
> > data resides across 4 data nodes
> > 3) A query plan that takes c. 7 minutes to return results.
> >
> > Please let me know of other information that would help to
> > investigate
> > this further.
> >
> > Many thanks for your direction and help in advance, Anthony
>
> Le contenu de ce courriel et de toute pièce jointe est destiné à
> l’usage exclusif de son destinataire. Il contient des renseignements
> exclusifs, privilégiés, confidentiels ou assujettis au droit
> d’auteur. Toute divulgation, distribution ou reproduction non
> autorisée est strictement interdite. Si vous n’êtes pas le
> destinataire prévu, veuillez-nous en aviser immédiatement et
> supprimer toutes les copies de ce courriel et des pièces jointes. Les
> courriels sont susceptibles d’altération. EXFO Inc. et ses sociétés
> affiliées ne seront pas tenues responsables du message s’il a été
> contrefait, modifié ou falsifié.
>
> The content of this email and any of its attachments is intended for
> the exclusive use of its recipient. It contains information that is
> proprietary, privileged, confidential and/or subject to copyright.
> Any unauthorized disclosure, distribution or reproduction is strictly
> prohibited. If you are not the intended recipient, please notify us
> immediately and delete all copies of this email and any attachments.
> E-mails are susceptible to alteration. EXFO Inc. and its affiliates
> shall not be liable for the message if altered, changed or falsified.
> Le contenu de ce courriel et de toute pièce jointe est destiné à
> l’usage exclusif de son destinataire. Il contient des renseignements
> exclusifs, privilégiés, confidentiels ou assujettis au droit
> d’auteur. Toute divulgation, distribution ou reproduction non
> autorisée est strictement interdite. Si vous n’êtes pas le
> destinataire prévu, veuillez-nous en aviser immédiatement et
> supprimer toutes les copies de ce courriel et des pièces jointes. Les
> courriels sont susceptibles d’altération. EXFO Inc. et ses sociétés
> affiliées ne seront pas tenues responsables du message s’il a été
> contrefait, modifié ou falsifié.
>
> The content of this email and any of its attachments is intended for
> the exclusive use of its recipient. It contains information that is
> proprietary, privileged, confidential and/or subject to copyright.
> Any unauthorized disclosure, distribution or reproduction is strictly
> prohibited. If you are not the intended recipient, please notify us
> immediately and delete all copies of this email and any attachments.
> E-mails are susceptible to alteration. EXFO Inc. and its affiliates
> shall not be liable for the message if altered, changed or falsified.
Le contenu de ce courriel et de toute pièce jointe est destiné à l’usage
exclusif de son destinataire. Il contient des renseignements exclusifs,
privilégiés, confidentiels ou assujettis au droit d’auteur. Toute divulgation,
distribution ou reproduction non autorisée est strictement interdite. Si vous
n’êtes pas le destinataire prévu, veuillez-nous en aviser immédiatement et
supprimer toutes les copies de ce courriel et des pièces jointes. Les courriels
sont susceptibles d’altération. EXFO Inc. et ses sociétés affiliées ne seront
pas tenues responsables du message s’il a été contrefait, modifié ou falsifié.
The content of this email and any of its attachments is intended for the
exclusive use of its recipient. It contains information that is proprietary,
privileged, confidential and/or subject to copyright. Any unauthorized
disclosure, distribution or reproduction is strictly prohibited. If you are not
the intended recipient, please notify us immediately and delete all copies of
this email and any attachments. E-mails are susceptible to alteration. EXFO
Inc. and its affiliates shall not be liable for the message if altered, changed
or falsified.
prefix cantor: <http://ontology.com/voc/cust/cantor#>
prefix siebel: <http://ontology.com/voc/sys/siebel#>
prefix core: <http://ontology.com/voc/cust/core#>
prefix isc: <http://ontology.com/voc/sys/iscircuits#>
prefix net: <http://ontology.com/voc/sdm/net#>
PREFIX tms:<http://ontology.com/voc/sys/tms#>
prefix ran: <http://ontology.com/voc/cust/rancid#>
select distinct
?tag
?linetagHasQos
?solutionId
?solutionName
?smartsAdminStatus
?siteNameFromSiebel
?siteNameFromIsCircuits
?siteNameFromTms
?serviceAccountNameFromSiebel
?serviceAccountNameFromIsCircuits
?addressFromSiebel
?addressFromTms
?serviceAccountIdFromSiebel
?serviceAccountIdFromIsCircuits
?serviceAccountNmsIdFromIsCircuits
?interfaceNameFromSmarts
?interfaceNameFromIsCircuits
?interfaceNameFromRancid
?routerNameFromSmarts
?routerNameFromIsCircuits
?routerNameFromRancid
?circuitIdFromSmarts
?circuitIdFromIsCircuits
?circuitIdFromSiebel
?linespeedFromIsCircuits
?linespeedFromSmarts
?linespeedFromSiebel
?linespeedFromTms
?linespeedFromRancid
?vrfNameFromSmarts
?vrfNameFromIsCircuits
?martisNodeFromSmarts
?martisNodeFromIsCircuits
?martisNodeFromTms
?remoteRouterFromIsCircuits
?notesFromIsCircuits
?interfaceAliasFromSmarts
?QosConfigHasErrors
?QosConfigHasErrorsMatch
?rancidSumQosSpeed
?siebelSumQosSpeed
?isCircuitsSumQosSpeed
?interfaceHasRateLimit
?platinum
?platinumIsCircuitsSpeed
?platinumSiebelSpeed
?platinumRancidSpeed
?gold
?goldIsCircuitsSpeed
?goldSiebelSpeed
?goldRancidSpeed
?silver
?silverIsCircuitsSpeed
?silverSiebelSpeed
?silverRancidSpeed
?vvcc
?vvccIsCircuitsSpeed
?vvccSiebelSpeed
?vvccRancidSpeed
?vc
?vcIsCircuitsSpeed
?vcSiebelSpeed
?vcRancidSpeed
?voice
?voiceIsCircuitsSpeed
?voiceSiebelSpeed
?voiceRancidSpeed
{
#! require (?tag) {
?linetag cantor:linetagValue ?tag .
optional {
?linetag cantor:linetagHasQosLinetag [].
ran:charCheckMark ran:charLooksLike ?linetagHasQos.
ran:charCheckMark ran:charHasName ?linetagHasQosMatch.
}
optional{
?linetag a cantor:LinetagWithQosLinetagInErrorQos.
ran:charHeavyBallotX ran:charLooksLike ?QosConfigHasErrors.
# allows you to compare to a string "heavyBallotX" in the decorators.
ran:charHeavyBallotX ran:charHasName ?QosConfigHasErrorsMatch.
}
optional {
?linetag cantor:linetagHasSolution ?solution .
?solution a cantor:ActiveSolution;
siebel:solutionServiceAccount ?serviceAccount ;
siebel:solutionId ?solutionId ;
siebel:solutionHasRootProduct ?rootProduct .
?rootProduct siebel:productName ?solutionName .
?serviceAccount siebel:siebelAccountName ?serviceAccountNameFromSiebel ;
core:accountId ?serviceAccountIdFromSiebel .
# if the customer is bound, lets limit results to just their siebel
linetags
?customer cantor:customerHasAccount ?serviceAccount ;
core:customerId ?customerId .
optional {
?serviceAccount siebel:accountHasAddress [
core:addressLine1 ?addressFromSiebel
]
}
optional {
?solution siebel:solutionCircuit ?circuitIdFromSiebel .
}
optional {
#[] cantor:circuitSpeedHasSolution ?solution ;
# cantor:circuitSpeedValue ?linespeedFromSiebel .
?solution cantor:solutionHasSpeed ?linespeedFromSiebel .
}
optional {
?rootProduct siebel:productDeliveredToSite [
core:siteName ?siteNameFromSiebel
]
}
}
optional {
?linetag cantor:linetagHasIsCircuitRecord ?isc .
optional {
?isc isc:isCircuitsNmsAccountId ?serviceAccountNmsIdFromIsCircuits
}
optional {
?isc isc:isCircuitsAccountId ?serviceAccountIdFromIsCircuits
}
optional {
?isc isc:orgName ?serviceAccountNameFromIsCircuits
}
optional {
?isc isc:circuitDetails ?circuitIdFromIsCircuits .
}
optional {
?isc isc:speed ?linespeedFromIsCircuits .
}
optional {
?isc isc:physicalLocation ?martisNodeFromIsCircuits .
}
optional {
?isc isc:circuitLocation ?popFromIsCircuits .
}
optional {
?isc isc:isVrfName ?vrfNameFromIsCircuits .
}
optional {
?isc isc:ifDescription ?interfaceNameFromIsCircuits .
}
optional {
?isc isc:nmsName ?routerNameFromIsCircuits .
}
optional {
?isc isc:notes ?notesFromIsCircuits
}
optional {
?isc isc:isRemoteRouter ?remoteRouterFromIsCircuits
}
optional {
?isc isc:describesSite [
core:siteName ?siteNameFromIsCircuits
]
}
}
# linetag to smarts
optional {
?linetag cantor:linetagHasInterface ?interface ;
cantor:linetagHasDevice [
net:deviceName ?routerNameFromSmarts
] .
?interface net:ifAlias ?interfaceAnnotation ;
net:ifAdminStatus ?smartsAdminStatus ;
net:ifDescr ?interfaceNameFromSmarts ;
net:ifAlias ?interfaceAliasFromSmarts .
?smartsCircuitSpeed cantor:circuitSpeedHasInterface ?interface ;
cantor:circuitSpeedValue ?linespeedFromSmarts .
optional {
?interface net:interfaceIsInVrf [
net:vrfName ?vrfNameFromSmarts
]
}
optional {
?linetag cantor:linetagHasAccessConnection _:circuitIdAccess .
[] cantor:circuitIdHasAccessConnection _:circuitIdAccess ;
cantor:circuitIdValue ?circuitIdFromSmarts .
}
optional {
?linetag cantor:linetagHasAccessConnection _:martisAccess .
[] cantor:martisNodeHasAccessConnection _:martisAccess ;
cantor:martisNodeValue ?martisNodeFromSmarts .
}
}
optional {
?linetag cantor:linetagHasCircuitId [
cantor:circuitIdHasTmsRecord _:tmsRecord
] .
_:tmsRecord tms:tmsRecordHasASide [
tms:lineStatus "Live Install" ;
tms:martisNode ?martisNodeFromTms
] ;
tms:tmsRecordConnectsCustomerSite [
core:siteName ?siteNameFromTms ;
core:siteAddress [
core:addressLine1 ?addressFromTms
]
] .
[] cantor:circuitSpeedHasTmsRecord _:tmsRecord ;
cantor:circuitSpeedValue ?linespeedFromTms
}
# RANCID Data
optional{
?linetag cantor:linetagHasRancidInterface ?rancidInterface;
cantor:linetagHasRancidDevice ?rancidDevice.
?rancidInterface ran:linespeedFromRancid
?linespeedFromRancid.
?rancidDevice ran:rancidDeviceName ?routerNameFromRancid.
?rancidInterface ran:interfaceName ?interfaceNameFromRancid.
optional {
?rancidInterface ran:interfaceRateLimit
?interfaceHasRateLimit.
}
optional {
?linetag cantor:linetagHasQosLinetag-platinum
?platQosLinetag.
optional {
?platQosLinetag
cantor:qosLinetagHasIsCircuitsBandwidth ?platinumIsCircuitsSpeed.
}
optional {
?platQosLinetag cantor:qosLinetagHasProductBandwidth
?platinumSiebelSpeed.
}
optional {
?platQosLinetag cantor:qosLinetagRancidBandwidth
?platinumRancidSpeed.
}
ran:platinumCos ran:cosType ?platinum.
}
optional {
?linetag cantor:linetagHasQosLinetag-gold
?goldQosLinetag.
optional {
?goldQosLinetag cantor:qosLinetagHasIsCircuitsBandwidth
?goldIsCircuitsSpeed.
}
optional {
?goldQosLinetag cantor:qosLinetagHasProductBandwidth
?goldSiebelSpeed.
}
optional {
?goldQosLinetag cantor:qosLinetagRancidBandwidth
?goldRancidSpeed.
}
ran:goldCos ran:cosType ?gold.
}
optional {
?linetag cantor:linetagHasQosLinetag-silver
?silverQosLinetag.
optional {
?silverQosLinetag cantor:qosLinetagHasIsCircuitsBandwidth
?silverIsCircuitsSpeed.
}
optional {
?silverQosLinetag cantor:qosLinetagHasProductBandwidth
?silverSiebelSpeed.
}
optional {
?silverQosLinetag cantor:qosLinetagRancidBandwidth
?silverRancidSpeed.
}
ran:silverCos ran:cosType ?silver.
}
optional {
?linetag cantor:linetagHasQosLinetag-vvcc
?vvccQosLinetag.
optional {
?vvccQosLinetag cantor:qosLinetagHasIsCircuitsBandwidth
?vvccIsCircuitsSpeed.
}
optional {
?vvccQosLinetag cantor:qosLinetagHasProductBandwidth
?vvccSiebelSpeed.
}
optional {
?vvccQosLinetag cantor:qosLinetagRancidBandwidth
?vvccRancidSpeed.
}
ran:vvccCos ran:cosType ?vvcc.
}
optional {
?linetag cantor:linetagHasQosLinetag-vc ?vcQosLinetag.
optional {
?vcQosLinetag cantor:qosLinetagHasIsCircuitsBandwidth
?vcIsCircuitsSpeed.
}
optional {
?vcQosLinetag cantor:qosLinetagHasProductBandwidth
?vcSiebelSpeed.
}
optional {
?vcQosLinetag cantor:qosLinetagRancidBandwidth
?vcRancidSpeed.
}
ran:vcCos ran:cosType ?vc.
}
optional {
?linetag cantor:linetagHasQosLinetag-voice ?voiceQosLinetag.
optional {
?voiceQosLinetag cantor:qosLinetagHasIsCircuitsBandwidth
?voiceIsCircuitsSpeed.
}
optional {
?voiceQosLinetag cantor:qosLinetagHasProductBandwidth
?voiceSiebelSpeed.
}
optional {
?voiceQosLinetag cantor:qosLinetagRancidBandwidth
?voiceRancidSpeed.
}
ran:voiceCos ran:cosType ?voice.
}
optional {
?linetag cantor:qosLinetagRancidSpeedSum
?rancidSumQosSpeed.
}
optional {
?linetag cantor:qosLinetagSiebelSpeedSum
?siebelSumQosSpeed.
}
optional {
?linetag cantor:qosLinetagIscSpeedSum
?isCircuitsSumQosSpeed.
}
}
#! }
}PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
select distinct ?class ?parent
where {
{ ?class rdf:type owl:Class . }
union { ?class rdf:type rdfs:Class . }
union { ?class rdfs:subClassOf+ owl:Thing . }
OPTIONAL {
?class rdfs:subClassOf ?parent
FILTER(?parent != owl:Thing && ?parent!=?class)
FILTER not exists
{
?class rdfs:subClassOf+ ?intermediate .
filter( ?intermediate != owl:Thing && ?intermediate != ?class &&
?intermediate != ?parent )
?intermediate rdfs:subClassOf ?parent .
}
}
}
