Rebuilding TDB index and updating stats file

[Sarven Capadisli <in...@csarven.ca>]

Hi,

I was wondering if there is a way to rebuild the TDB index from 
command-line and have it consequently update the stats file?

Thanks,

-Sarven

Re: Rebuilding TDB index and updating stats file

[Andy Seaborne <an...@apache.org>]

On 30/06/12 21:04, Sarven Capadisli wrote:
> On 2012-06-30 12:48, Andy Seaborne wrote:
>> On 29/06/12 02:49, Sarven Capadisli wrote:
>>> On 2012-06-28 20:25, Andy Seaborne wrote:
>>>> On 28/06/12 10:11, Sarven Capadisli wrote:
>>>>> I was wondering if there is a way to rebuild the TDB index from
>>>>> command-line and have it consequently update the stats file?
>>>>
>>>> There isn't a way to rebuild just one of the indexes from another in
>>>> the
>>>> TDB distribution.  Is that you want to do?
>>>>
>>>> tdbstats calculates the stats.
>>>
>>> I want to optimize query response times.
>>>
>>> I can't get a satisfactory solution with tdbstats because it doesn't let
>>> me optimize for each named graph in the store.
>>
>> What sort of queries are you asking the store?
>
> For a store with 165 million triples, some real examples that's :
>
> SELECT DISTINCT ?o WHERE { ?s a ?o }
>
> Time: 159.359 sec (100 sec in second time round)
>
>
> SELECT DISTINCT ?o WHERE { GRAPH <http://worldbank.270a.info/graph/meta>
> { ?s a ?o } }
>
> Time: 0.394 sec
>
> SELECT DISTINCT ?o WHERE { GRAPH
> <http://worldbank.270a.info/graph/world-bank-finances> { ?s a ?o } }
>
> Time: 1.946 sec
>
> SELECT DISTINCT ?o WHERE { GRAPH
> <http://worldbank.270a.info/graph/world-bank-climates { ?s a ?o } }
>
> Time: 46.967 sec
>
> SELECT DISTINCT ?o WHERE { GRAPH
> <http://worldbank.270a.info/graph/world-development-indicators> { ?s a
> ?o } }
>
> Time: 61.323 sec
>
> SELECT DISTINCT ?o WHERE { GRAPH
> <http://worldbank.270a.info/graph/world-bank-projects-and-operations> {
> ?s a ?o } }
>
> Time: 0.559 sec
>
> A quick note on this: when I run the query where the default graph is
> the union of all graphs, it takes much longer in total than the total
> time for queries with different named graphs.
>
> Other examples:
>
> SELECT DISTINCT ?p ?o WHERE { GRAPH <g> ?s ?p ?o } --time=10m
>
> SELECT DISTINCT ?g WHERE { GRAPH ?g { } } --time=60s (49s in second
> round, 55s on third..)

The low-level optimizer, stats or otherwise, reorders the triples within 
a basic graph pattern.  In your example, there is only one triple 
pattern so there are no choices of ordering and the optimizer will make 
no difference.

SELECT DISTINCT ?o WHERE { ?s a ?o }

over the union default graph is an access to the POSG index.  P first 
because P = rdf:type is fixed.  TDB uses 3 indexes for the (real) 
default graph, 6 for named graphs, which means any access of G/S/P/O can 
be found from an index but not in every possible sort order (c.f. 
hexstore which has 6 indexes for the single graph) It would take 24 (= 
4*3*2*1) all possibilities of names graphs.

And when it is the union graph, the results have to be reduced to unique 
triples so { ?s a ?o } becomes what is effectively

DISTINCT ?s ?o { GRAPH ?g { ?s a ?o } }

Each triple pattern has to have the distinct-ness applied so it puts 
stress on memory as well.  If it were cleverer, it would know it could 
use a cheaper filter to calculate distinct-ness.

Also the system isn't smart enough to notice you have a DISTINCT of a 
unique expression and it does not need the outer DISTINCT.

Something similar happens for

  SELECT DISTINCT ?g WHERE { GRAPH ?g { } }

The thing that will most help performance is RAM.  How much RAM and on 
what sort of OS are you running?

	Andy

Re: Rebuilding TDB index and updating stats file

[Sarven Capadisli <in...@csarven.ca>]

On 2012-06-30 12:48, Andy Seaborne wrote:
> On 29/06/12 02:49, Sarven Capadisli wrote:
>> On 2012-06-28 20:25, Andy Seaborne wrote:
>>> On 28/06/12 10:11, Sarven Capadisli wrote:
>>>> I was wondering if there is a way to rebuild the TDB index from
>>>> command-line and have it consequently update the stats file?
>>>
>>> There isn't a way to rebuild just one of the indexes from another in the
>>> TDB distribution.  Is that you want to do?
>>>
>>> tdbstats calculates the stats.
>>
>> I want to optimize query response times.
>>
>> I can't get a satisfactory solution with tdbstats because it doesn't let
>> me optimize for each named graph in the store.
>
> What sort of queries are you asking the store?

For a store with 165 million triples, some real examples that's :

SELECT DISTINCT ?o WHERE { ?s a ?o }

Time: 159.359 sec (100 sec in second time round)


SELECT DISTINCT ?o WHERE { GRAPH <http://worldbank.270a.info/graph/meta> 
{ ?s a ?o } }

Time: 0.394 sec

SELECT DISTINCT ?o WHERE { GRAPH 
<http://worldbank.270a.info/graph/world-bank-finances> { ?s a ?o } }

Time: 1.946 sec

SELECT DISTINCT ?o WHERE { GRAPH 
<http://worldbank.270a.info/graph/world-bank-climates { ?s a ?o } }

Time: 46.967 sec

SELECT DISTINCT ?o WHERE { GRAPH 
<http://worldbank.270a.info/graph/world-development-indicators> { ?s a 
?o } }

Time: 61.323 sec

SELECT DISTINCT ?o WHERE { GRAPH 
<http://worldbank.270a.info/graph/world-bank-projects-and-operations> { 
?s a ?o } }

Time: 0.559 sec

A quick note on this: when I run the query where the default graph is 
the union of all graphs, it takes much longer in total than the total 
time for queries with different named graphs.

Other examples:

SELECT DISTINCT ?p ?o WHERE { GRAPH <g> ?s ?p ?o } --time=10m

SELECT DISTINCT ?g WHERE { GRAPH ?g { } } --time=60s (49s in second 
round, 55s on third..)


>> I thought rebuilding the indexes might create a new stats file. As I
>> understand it, the stats file is created after the initial import with
>> tdbloader, and subsequent imports don't update the stats file.
>
> True - but you just need to rebuild the stats, not the index itself. The
> stats file is separate from the index.

On a copy of the dataset, I ran:

java tdb.tdbstats -v --desc=/usr/lib/fuseki/tdb2.worldbank.bak.ttl 
--graph=urn:x-arq:UnionGraph

and replaced content of stats.opt with the output. I've placed the 
output here http://pastebin.com/2nt9jphE

I took the following query and compared the results in two stores:

SELECT DISTINCT ?o WHERE { ?s a ?o }

There was no notable difference.

Did I miss a step or make a wrong turn somewhere? Which type of queries 
would demonstrate the differences in stats?

>> I suppose at this point I need to compare the performances between the
>> stats that's created after importing incrementally (meanwhile initial
>> import being the largest), and the stats that's based on the union of
>> graphs.
>>
>> That's all in context of having the data dumps in N-Triples format where
>> each dump is assigned a named graph.
>>
>> Alternatively, I have to switch to using a single dump file in N-Quads,
>> but I'm thinking that the stats for that would get me at best the same
>> results as in the union of graphs approach.
>>
>> Does this line of thinking makes sense: Which state should the TDB
>> indexes be in such that I get the most preferable stats? Is there even a
>> need to rebuild the indexes?
>
> No point rebuilding the indexes.
>
> You can write a stats file by hand.
>
> But I think the first step is understand the queries.

Fair enough. Although the queries above are generally done by anyone 
trying to get an insight on the data in the store (although they are 
going at it via Fuseki), as opposed to some basic information retrieval, 
do you think the results are in any way returned in a reasonable amount 
of time? Is there a way to allocate more memory for TDB queries? If it 
matters in the end, what would be the ideal way to import the data into 
the store?

Thanks Andy,

-Sarven

Re: Rebuilding TDB index and updating stats file

[Andy Seaborne <an...@apache.org>]

On 29/06/12 02:49, Sarven Capadisli wrote:
> On 2012-06-28 20:25, Andy Seaborne wrote:
>> On 28/06/12 10:11, Sarven Capadisli wrote:
>>> I was wondering if there is a way to rebuild the TDB index from
>>> command-line and have it consequently update the stats file?
>>
>> There isn't a way to rebuild just one of the indexes from another in the
>> TDB distribution.  Is that you want to do?
>>
>> tdbstats calculates the stats.
>
> I want to optimize query response times.
>
> I can't get a satisfactory solution with tdbstats because it doesn't let
> me optimize for each named graph in the store.

What sort of queries are you asking the store?

> I thought rebuilding the indexes might create a new stats file. As I
> understand it, the stats file is created after the initial import with
> tdbloader, and subsequent imports don't update the stats file.

True - but you just need to rebuild the stats, not the index itself. 
The stats file is separate from the index.

> I suppose at this point I need to compare the performances between the
> stats that's created after importing incrementally (meanwhile initial
> import being the largest), and the stats that's based on the union of
> graphs.
>
> That's all in context of having the data dumps in N-Triples format where
> each dump is assigned a named graph.
>
> Alternatively, I have to switch to using a single dump file in N-Quads,
> but I'm thinking that the stats for that would get me at best the same
> results as in the union of graphs approach.
>
> Does this line of thinking makes sense: Which state should the TDB
> indexes be in such that I get the most preferable stats? Is there even a
> need to rebuild the indexes?

No point rebuilding the indexes.

You can write a stats file by hand.

But I think the first step is understand the queries.

	Andy

>
> -Sarven

Re: Rebuilding TDB index and updating stats file

[Sarven Capadisli <in...@csarven.ca>]

On 2012-06-28 20:25, Andy Seaborne wrote:
> On 28/06/12 10:11, Sarven Capadisli wrote:
>> I was wondering if there is a way to rebuild the TDB index from
>> command-line and have it consequently update the stats file?
>
> There isn't a way to rebuild just one of the indexes from another in the
> TDB distribution.  Is that you want to do?
>
> tdbstats calculates the stats.

I want to optimize query response times.

I can't get a satisfactory solution with tdbstats because it doesn't let 
me optimize for each named graph in the store.

I thought rebuilding the indexes might create a new stats file. As I 
understand it, the stats file is created after the initial import with 
tdbloader, and subsequent imports don't update the stats file.

I suppose at this point I need to compare the performances between the 
stats that's created after importing incrementally (meanwhile initial 
import being the largest), and the stats that's based on the union of 
graphs.

That's all in context of having the data dumps in N-Triples format where 
each dump is assigned a named graph.

Alternatively, I have to switch to using a single dump file in N-Quads, 
but I'm thinking that the stats for that would get me at best the same 
results as in the union of graphs approach.

Does this line of thinking makes sense: Which state should the TDB 
indexes be in such that I get the most preferable stats? Is there even a 
need to rebuild the indexes?

-Sarven

Re: Rebuilding TDB index and updating stats file

[Andy Seaborne <an...@apache.org>]

On 28/06/12 10:11, Sarven Capadisli wrote:
> Hi,
>
> I was wondering if there is a way to rebuild the TDB index from
> command-line and have it consequently update the stats file?


There isn't a way to rebuild just one of the indexes from another in the 
TDB distribution.  Is that you want to do?

tdbstats calculates the stats.

	Andy


>
> Thanks,
>
> -Sarven