You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@rya.apache.org by "pranav.puri" <pr...@orkash.com> on 2016/09/05 07:37:55 UTC
regarding slow query execution
Dear All
The query execution time for sparql queries with regex filters is much
more(10-12 minutes) as compared to queries with no filters applied.
The queries are being run on tables containing triples from dbpedia dataset.
Each table(ie Spo,osp,po) contains 110 million entries.I am currently
using a three node accumulo cluster.
Please suggest some ways to improve the query execution time .
Regards
Pranav
RE: regarding slow query execution
Posted by "Meier, Caleb" <Ca...@parsons.com>.
Hello Pranav,
Are you using the freetext index or are your regex filters just normal open RDF filters? The freetext index has the added benefit that filtering is pushed to the server, whereas in the
case of the normal, open RDF filters, all of the filtering happens client side.
-----Original Message-----
From: pranav.puri [mailto:pranav.puri@orkash.com]
Sent: Monday, September 05, 2016 3:38 AM
To: dev@rya.incubator.apache.org
Subject: regarding slow query execution
Dear All
The query execution time for sparql queries with regex filters is much
more(10-12 minutes) as compared to queries with no filters applied.
The queries are being run on tables containing triples from dbpedia dataset.
Each table(ie Spo,osp,po) contains 110 million entries.I am currently using a three node accumulo cluster.
Please suggest some ways to improve the query execution time .
Regards
Pranav
RE: regarding slow query execution
Posted by Mark Wallace <mw...@modusoperandi.com>.
I'm not a Rya expert, but generally speaking, for large data in any RDF/SPARQL system, string comparisons on parts of IRIs is a bad idea because It requires all of the pre-filter data to be processed (string conversion) and then regex compared. Very expensive over large data.
A good data model usually avoids the need to do string search on parts of resource IRIs.
Does the dbpedia data model provide any help in this area? E.g. does it declare lists as being of type list? And categories as type category? If so, this would create a direct equal/not-equal resource comparison vs. a string/regex conversion and comparison, which is usually much more efficient.
E.g.
prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT * WHERE {
?pages <http://www.w3.org/2000/01/rdf-schema#label> ?labels .
?pages rdf:type ?type .
FILTER (?type != <http://dbpedia.org/ontology/Category>
&& ?type != <http://dbpedia.org/ontology/List>).
?pages ?property ?objects .
}
--
Mark Wallace
-----Original Message-----
From: Puja Valiyil [mailto:pujav65@gmail.com]
Sent: Wednesday, September 07, 2016 4:00 PM
To: dev@rya.incubator.apache.org
Subject: Re: regarding slow query execution
Hi Pranav,
I'm not sure that the free text index would be useful in your case -- we assume that regexes would be on string literals (not uris converted to strings).
I think you would probably have the best luck with breaking your query into a few queries, since that would avoid doing a complex join client side, but I'm not sure if anything would really make this query perform in sub-second/minutes range for total execution time. The last statement in your query is particularly expensive, so if you could move that into a separate query logically, you'd probably see a performance improvement.
Someone who is more familiar with SPARQL may be able to suggest a way of doing that as a single query, but I'm not sure off hand how to (maybe using describe or construct keywords?).
On Wed, Sep 7, 2016 at 8:22 AM, pranav.puri <pr...@orkash.com> wrote:
> Hi
>
> I am using simple rdf regex filters .
>
> The query being evaluated is this:
>
> SELECT * WHERE {
> ?pages <http://www.w3.org/2000/01/rdf-schema#label> ?labels .
> FILTER (!regex(str(?pages),'^http://dbpedia.org/resource/Category:')).
> FILTER (!regex(str(?pages), '^http://dbpedia.org/resource/List')).
> ?pages ?property ?objects .
> }
>
> Can you please provide me with some direction for using free text
> index or pre-computed joins to perform free text search .
>
>
>
> On Tuesday 06 September 2016 06:58 PM, Puja Valiyil wrote:
>
>> Hi Pranav,
>> I think this is likely due to the fact that filters are being
>> evaluated client side (so it's possible you are bringing back all 110
>> million triples multiple times in your query). Can you send us your query so we can verify?
>>
>> Sent from my iPhone
>>
>> On Sep 6, 2016, at 8:49 AM, Meier, Caleb <Ca...@parsons.com> wrote:
>>>
>>> Hey Pranav,
>>>
>>> Even if the filtering is occurring client side, that is still
>>> strange behavior. What does your query look like?
>>>
>>>
>>> -----Original Message-----
>>> From: pranav.puri [mailto:pranav.puri@orkash.com]
>>> Sent: Monday, September 05, 2016 3:38 AM
>>> To: dev@rya.incubator.apache.org
>>> Subject: regarding slow query execution
>>>
>>> Dear All
>>>
>>> The query execution time for sparql queries with regex filters is
>>> much
>>> more(10-12 minutes) as compared to queries with no filters applied.
>>>
>>> The queries are being run on tables containing triples from dbpedia
>>> dataset.
>>> Each table(ie Spo,osp,po) contains 110 million entries.I am
>>> currently using a three node accumulo cluster.
>>>
>>> Please suggest some ways to improve the query execution time .
>>>
>>> Regards
>>> Pranav
>>>
>>
>
Re: regarding slow query execution
Posted by Puja Valiyil <pu...@gmail.com>.
Hi Pranav,
I'm not sure that the free text index would be useful in your case -- we
assume that regexes would be on string literals (not uris converted to
strings).
I think you would probably have the best luck with breaking your query into
a few queries, since that would avoid doing a complex join client side, but
I'm not sure if anything would really make this query perform in
sub-second/minutes range for total execution time. The last statement in
your query is particularly expensive, so if you could move that into a
separate query logically, you'd probably see a performance improvement.
Someone who is more familiar with SPARQL may be able to suggest a way of
doing that as a single query, but I'm not sure off hand how to (maybe using
describe or construct keywords?).
On Wed, Sep 7, 2016 at 8:22 AM, pranav.puri <pr...@orkash.com> wrote:
> Hi
>
> I am using simple rdf regex filters .
>
> The query being evaluated is this:
>
> SELECT * WHERE {
> ?pages <http://www.w3.org/2000/01/rdf-schema#label> ?labels .
> FILTER (!regex(str(?pages),'^http://dbpedia.org/resource/Category:')).
> FILTER (!regex(str(?pages), '^http://dbpedia.org/resource/List')).
> ?pages ?property ?objects .
> }
>
> Can you please provide me with some direction for using free text index or
> pre-computed joins to perform free text search .
>
>
>
> On Tuesday 06 September 2016 06:58 PM, Puja Valiyil wrote:
>
>> Hi Pranav,
>> I think this is likely due to the fact that filters are being evaluated
>> client side (so it's possible you are bringing back all 110 million triples
>> multiple times in your query). Can you send us your query so we can verify?
>>
>> Sent from my iPhone
>>
>> On Sep 6, 2016, at 8:49 AM, Meier, Caleb <Ca...@parsons.com> wrote:
>>>
>>> Hey Pranav,
>>>
>>> Even if the filtering is occurring client side, that is still strange
>>> behavior. What does your query look like?
>>>
>>>
>>> -----Original Message-----
>>> From: pranav.puri [mailto:pranav.puri@orkash.com]
>>> Sent: Monday, September 05, 2016 3:38 AM
>>> To: dev@rya.incubator.apache.org
>>> Subject: regarding slow query execution
>>>
>>> Dear All
>>>
>>> The query execution time for sparql queries with regex filters is much
>>> more(10-12 minutes) as compared to queries with no filters applied.
>>>
>>> The queries are being run on tables containing triples from dbpedia
>>> dataset.
>>> Each table(ie Spo,osp,po) contains 110 million entries.I am currently
>>> using a three node accumulo cluster.
>>>
>>> Please suggest some ways to improve the query execution time .
>>>
>>> Regards
>>> Pranav
>>>
>>
>
Re: regarding slow query execution
Posted by "pranav.puri" <pr...@orkash.com>.
Hi
I am using simple rdf regex filters .
The query being evaluated is this:
SELECT * WHERE {
?pages <http://www.w3.org/2000/01/rdf-schema#label> ?labels .
FILTER (!regex(str(?pages),'^http://dbpedia.org/resource/Category:')).
FILTER (!regex(str(?pages), '^http://dbpedia.org/resource/List')).
?pages ?property ?objects .
}
Can you please provide me with some direction for using free text index
or pre-computed joins to perform free text search .
On Tuesday 06 September 2016 06:58 PM, Puja Valiyil wrote:
> Hi Pranav,
> I think this is likely due to the fact that filters are being evaluated client side (so it's possible you are bringing back all 110 million triples multiple times in your query). Can you send us your query so we can verify?
>
> Sent from my iPhone
>
>> On Sep 6, 2016, at 8:49 AM, Meier, Caleb <Ca...@parsons.com> wrote:
>>
>> Hey Pranav,
>>
>> Even if the filtering is occurring client side, that is still strange behavior. What does your query look like?
>>
>>
>> -----Original Message-----
>> From: pranav.puri [mailto:pranav.puri@orkash.com]
>> Sent: Monday, September 05, 2016 3:38 AM
>> To: dev@rya.incubator.apache.org
>> Subject: regarding slow query execution
>>
>> Dear All
>>
>> The query execution time for sparql queries with regex filters is much
>> more(10-12 minutes) as compared to queries with no filters applied.
>>
>> The queries are being run on tables containing triples from dbpedia dataset.
>> Each table(ie Spo,osp,po) contains 110 million entries.I am currently using a three node accumulo cluster.
>>
>> Please suggest some ways to improve the query execution time .
>>
>> Regards
>> Pranav
Re: regarding slow query execution
Posted by Puja Valiyil <pu...@gmail.com>.
Hi Pranav,
I think this is likely due to the fact that filters are being evaluated client side (so it's possible you are bringing back all 110 million triples multiple times in your query). Can you send us your query so we can verify?
Sent from my iPhone
> On Sep 6, 2016, at 8:49 AM, Meier, Caleb <Ca...@parsons.com> wrote:
>
> Hey Pranav,
>
> Even if the filtering is occurring client side, that is still strange behavior. What does your query look like?
>
>
> -----Original Message-----
> From: pranav.puri [mailto:pranav.puri@orkash.com]
> Sent: Monday, September 05, 2016 3:38 AM
> To: dev@rya.incubator.apache.org
> Subject: regarding slow query execution
>
> Dear All
>
> The query execution time for sparql queries with regex filters is much
> more(10-12 minutes) as compared to queries with no filters applied.
>
> The queries are being run on tables containing triples from dbpedia dataset.
> Each table(ie Spo,osp,po) contains 110 million entries.I am currently using a three node accumulo cluster.
>
> Please suggest some ways to improve the query execution time .
>
> Regards
> Pranav
RE: regarding slow query execution
Posted by "Meier, Caleb" <Ca...@parsons.com>.
Hey Pranav,
Even if the filtering is occurring client side, that is still strange behavior. What does your query look like?
-----Original Message-----
From: pranav.puri [mailto:pranav.puri@orkash.com]
Sent: Monday, September 05, 2016 3:38 AM
To: dev@rya.incubator.apache.org
Subject: regarding slow query execution
Dear All
The query execution time for sparql queries with regex filters is much
more(10-12 minutes) as compared to queries with no filters applied.
The queries are being run on tables containing triples from dbpedia dataset.
Each table(ie Spo,osp,po) contains 110 million entries.I am currently using a three node accumulo cluster.
Please suggest some ways to improve the query execution time .
Regards
Pranav
Re: regarding the ingestion of ttl files
Posted by "Aaron D. Mihalik" <aa...@gmail.com>.
I'm sorry, but bulk ingest won't help. The issue occurs when Rya persists
data to the datastore. This mechanism is the same for both bulk ingest and
the simple Sail Java client.
You probably want the Distribution Zip. It's kinda large, but it should
have everything you need.
Since we don't have an official release, try downloading a snapshot from
[1] or our latest release candidate from [2]. Let us know if you run into
any issues or if you find any strange behavior along the way.
--Aaron
[1]
https://repository.apache.org/content/repositories/snapshots/org/apache/rya/rya.indexing.example/3.2.10-incubating-SNAPSHOT/rya.indexing.example-3.2.10-incubating-20161013.201206-1-distribution.zip
[2]
https://repository.apache.org/content/groups/staging/org/apache/rya/rya.indexing.example/3.2.10-incubating/rya.indexing.example-3.2.10-incubating-distribution.zip
On Wed, Oct 19, 2016 at 8:24 AM pranav.puri <pr...@orkash.com> wrote:
Thanks for filling the ticket .
Will i be able to overcome this problem if i ingest this file using bulk
ingest jar using map reduce?
and i was also trying to run the map reduce job using the example given
in the git documentation but
on building the project i could not find
accumulo.rya-3.0.4-SNAPSHOT-shaded.jar in the target folder.
Where can i find this jar?
Regards
Pranav
On Monday 17 October 2016 07:16 PM, Aaron D. Mihalik wrote:
> Thanks Pranav.
>
> I verified that this is an issue with Rya, and filed a ticket here [1].
> The ticket includes some very simple example code that reproduces the
issue.
>
> Basically, Rya is calling Java's Integer.parseInt("1000000000000"), and
> that is throwing an exception.
>
> A workaround would be to remove the data from dbpedia dataset or modify
> AccumuloRyaDAO.commit(AccumuloRyaDAO.java:291) to discard that exception
an
> move on with the ingest. Both workarounds will result in data loss.
>
> --Aaron
>
>
>
> On Mon, Oct 17, 2016 at 6:43 AM pranav.puri <pr...@orkash.com>
wrote:
>
> Hi everyone
>
> i am getting an exception while ingesting infobox properties turtle file
> from dbpedia dataset into rya .
> The file size is 9.8 gb.
> Here is the exception :-
> Caused by: org.openrdf.repository.RepositoryException:
> org.openrdf.sail.SailException: mvm.rya.api.persist.RyaDAOException:
> java.io.IOException:
> mvm.rya.api.resolver.triple.TripleRowResolverException:
> mvm.rya.api.resolver.RyaTypeResolverException: Exception occurred
> serializing data[1000000000000]
> at
>
org.openrdf.repository.sail.SailRepositoryConnection.addWithoutCommit(SailRepositoryConnection.java:287)
> at
>
org.openrdf.repository.base.RepositoryConnectionBase.add(RepositoryConnectionBase.java:469)
> at
>
mvm.rya.rdftriplestore.utils.CombineContextsRdfInserter.handleStatement(CombineContextsRdfInserter.java:137)
> at
>
org.openrdf.rio.turtle.TurtleParser.reportStatement(TurtleParser.java:1081)
> at
> org.openrdf.rio.turtle.TurtleParser.parseObject(TurtleParser.java:482)
> at
> org.openrdf.rio.turtle.TurtleParser.parseObjectList(TurtleParser.java:405)
> at
>
org.openrdf.rio.turtle.TurtleParser.parsePredicateObjectList(TurtleParser.java:377)
> at
> org.openrdf.rio.turtle.TurtleParser.parseTriples(TurtleParser.java:362)
> at
> org.openrdf.rio.turtle.TurtleParser.parseStatement(TurtleParser.java:250)
> at
org.openrdf.rio.turtle.TurtleParser.parse(TurtleParser.java:205)
> at
org.openrdf.rio.turtle.TurtleParser.parse(TurtleParser.java:148)
> at
>
org.openrdf.repository.util.RDFLoader.loadInputStreamOrReader(RDFLoader.java:325)
> at org.openrdf.repository.util.RDFLoader.load(RDFLoader.java:222)
> at
>
mvm.rya.rdftriplestore.RyaSailRepositoryConnection.add(RyaSailRepositoryConnection.java:61)
>
> Regards
> Pranav
>
Re: regarding the ingestion of ttl files
Posted by "pranav.puri" <pr...@orkash.com>.
Thanks for filling the ticket .
Will i be able to overcome this problem if i ingest this file using bulk
ingest jar using map reduce?
and i was also trying to run the map reduce job using the example given
in the git documentation but
on building the project i could not find
accumulo.rya-3.0.4-SNAPSHOT-shaded.jar in the target folder.
Where can i find this jar?
Regards
Pranav
On Monday 17 October 2016 07:16 PM, Aaron D. Mihalik wrote:
> Thanks Pranav.
>
> I verified that this is an issue with Rya, and filed a ticket here [1].
> The ticket includes some very simple example code that reproduces the issue.
>
> Basically, Rya is calling Java's Integer.parseInt("1000000000000"), and
> that is throwing an exception.
>
> A workaround would be to remove the data from dbpedia dataset or modify
> AccumuloRyaDAO.commit(AccumuloRyaDAO.java:291) to discard that exception an
> move on with the ingest. Both workarounds will result in data loss.
>
> --Aaron
>
>
>
> On Mon, Oct 17, 2016 at 6:43 AM pranav.puri <pr...@orkash.com> wrote:
>
> Hi everyone
>
> i am getting an exception while ingesting infobox properties turtle file
> from dbpedia dataset into rya .
> The file size is 9.8 gb.
> Here is the exception :-
> Caused by: org.openrdf.repository.RepositoryException:
> org.openrdf.sail.SailException: mvm.rya.api.persist.RyaDAOException:
> java.io.IOException:
> mvm.rya.api.resolver.triple.TripleRowResolverException:
> mvm.rya.api.resolver.RyaTypeResolverException: Exception occurred
> serializing data[1000000000000]
> at
> org.openrdf.repository.sail.SailRepositoryConnection.addWithoutCommit(SailRepositoryConnection.java:287)
> at
> org.openrdf.repository.base.RepositoryConnectionBase.add(RepositoryConnectionBase.java:469)
> at
> mvm.rya.rdftriplestore.utils.CombineContextsRdfInserter.handleStatement(CombineContextsRdfInserter.java:137)
> at
> org.openrdf.rio.turtle.TurtleParser.reportStatement(TurtleParser.java:1081)
> at
> org.openrdf.rio.turtle.TurtleParser.parseObject(TurtleParser.java:482)
> at
> org.openrdf.rio.turtle.TurtleParser.parseObjectList(TurtleParser.java:405)
> at
> org.openrdf.rio.turtle.TurtleParser.parsePredicateObjectList(TurtleParser.java:377)
> at
> org.openrdf.rio.turtle.TurtleParser.parseTriples(TurtleParser.java:362)
> at
> org.openrdf.rio.turtle.TurtleParser.parseStatement(TurtleParser.java:250)
> at org.openrdf.rio.turtle.TurtleParser.parse(TurtleParser.java:205)
> at org.openrdf.rio.turtle.TurtleParser.parse(TurtleParser.java:148)
> at
> org.openrdf.repository.util.RDFLoader.loadInputStreamOrReader(RDFLoader.java:325)
> at org.openrdf.repository.util.RDFLoader.load(RDFLoader.java:222)
> at
> mvm.rya.rdftriplestore.RyaSailRepositoryConnection.add(RyaSailRepositoryConnection.java:61)
>
> Regards
> Pranav
>
Re: regarding the ingestion of ttl files
Posted by "Aaron D. Mihalik" <aa...@gmail.com>.
Thanks Pranav.
I verified that this is an issue with Rya, and filed a ticket here [1].
The ticket includes some very simple example code that reproduces the issue.
Basically, Rya is calling Java's Integer.parseInt("1000000000000"), and
that is throwing an exception.
A workaround would be to remove the data from dbpedia dataset or modify
AccumuloRyaDAO.commit(AccumuloRyaDAO.java:291) to discard that exception an
move on with the ingest. Both workarounds will result in data loss.
--Aaron
On Mon, Oct 17, 2016 at 6:43 AM pranav.puri <pr...@orkash.com> wrote:
Hi everyone
i am getting an exception while ingesting infobox properties turtle file
from dbpedia dataset into rya .
The file size is 9.8 gb.
Here is the exception :-
Caused by: org.openrdf.repository.RepositoryException:
org.openrdf.sail.SailException: mvm.rya.api.persist.RyaDAOException:
java.io.IOException:
mvm.rya.api.resolver.triple.TripleRowResolverException:
mvm.rya.api.resolver.RyaTypeResolverException: Exception occurred
serializing data[1000000000000]
at
org.openrdf.repository.sail.SailRepositoryConnection.addWithoutCommit(SailRepositoryConnection.java:287)
at
org.openrdf.repository.base.RepositoryConnectionBase.add(RepositoryConnectionBase.java:469)
at
mvm.rya.rdftriplestore.utils.CombineContextsRdfInserter.handleStatement(CombineContextsRdfInserter.java:137)
at
org.openrdf.rio.turtle.TurtleParser.reportStatement(TurtleParser.java:1081)
at
org.openrdf.rio.turtle.TurtleParser.parseObject(TurtleParser.java:482)
at
org.openrdf.rio.turtle.TurtleParser.parseObjectList(TurtleParser.java:405)
at
org.openrdf.rio.turtle.TurtleParser.parsePredicateObjectList(TurtleParser.java:377)
at
org.openrdf.rio.turtle.TurtleParser.parseTriples(TurtleParser.java:362)
at
org.openrdf.rio.turtle.TurtleParser.parseStatement(TurtleParser.java:250)
at org.openrdf.rio.turtle.TurtleParser.parse(TurtleParser.java:205)
at org.openrdf.rio.turtle.TurtleParser.parse(TurtleParser.java:148)
at
org.openrdf.repository.util.RDFLoader.loadInputStreamOrReader(RDFLoader.java:325)
at org.openrdf.repository.util.RDFLoader.load(RDFLoader.java:222)
at
mvm.rya.rdftriplestore.RyaSailRepositoryConnection.add(RyaSailRepositoryConnection.java:61)
Regards
Pranav
regarding the ingestion of ttl files
Posted by "pranav.puri" <pr...@orkash.com>.
Hi everyone
i am getting an exception while ingesting infobox properties turtle file from dbpedia dataset into rya .
The file size is 9.8 gb.
Here is the exception :-
Caused by: org.openrdf.repository.RepositoryException: org.openrdf.sail.SailException: mvm.rya.api.persist.RyaDAOException: java.io.IOException: mvm.rya.api.resolver.triple.TripleRowResolverException: mvm.rya.api.resolver.RyaTypeResolverException: Exception occurred serializing data[1000000000000]
at org.openrdf.repository.sail.SailRepositoryConnection.addWithoutCommit(SailRepositoryConnection.java:287)
at org.openrdf.repository.base.RepositoryConnectionBase.add(RepositoryConnectionBase.java:469)
at mvm.rya.rdftriplestore.utils.CombineContextsRdfInserter.handleStatement(CombineContextsRdfInserter.java:137)
at org.openrdf.rio.turtle.TurtleParser.reportStatement(TurtleParser.java:1081)
at org.openrdf.rio.turtle.TurtleParser.parseObject(TurtleParser.java:482)
at org.openrdf.rio.turtle.TurtleParser.parseObjectList(TurtleParser.java:405)
at org.openrdf.rio.turtle.TurtleParser.parsePredicateObjectList(TurtleParser.java:377)
at org.openrdf.rio.turtle.TurtleParser.parseTriples(TurtleParser.java:362)
at org.openrdf.rio.turtle.TurtleParser.parseStatement(TurtleParser.java:250)
at org.openrdf.rio.turtle.TurtleParser.parse(TurtleParser.java:205)
at org.openrdf.rio.turtle.TurtleParser.parse(TurtleParser.java:148)
at org.openrdf.repository.util.RDFLoader.loadInputStreamOrReader(RDFLoader.java:325)
at org.openrdf.repository.util.RDFLoader.load(RDFLoader.java:222)
at mvm.rya.rdftriplestore.RyaSailRepositoryConnection.add(RyaSailRepositoryConnection.java:61)
Regards
Pranav