Hi Mitko,
I executed the query many times with no notable improvement of response
time.
We are not using striped setup.
I rebooted Virtuoso, checked the stats, ran the query and re-checked the
stats. Onle the RDF_QUAD (primary key?) and RDF_QUAD_POGS indexes seem
to be used, see below.
Cheers,
Martin
--------------------
AFTER SERVER RESTART
--------------------
SQL> select * from sys_d_stat where INDEX_NAME like 'RDF_QUAD%';
KEY_TABLE
INDEX_NAME
TOUCHES READS READ_PCT N_DIRTY N_BUFFERS
VARCHAR NOT NULL
VARCHAR
INTEGER INTEGER INTEGER INTEGER INTEGER
_______________________________________________________________________________
DB.DBA.RDF_QUAD
RDF_QUAD
4505 612 13 0 612
DB.DBA.RDF_QUAD
RDF_QUAD_GS
0 0 0 0 0
DB.DBA.RDF_QUAD
RDF_QUAD_OP
0 0 0 0 0
DB.DBA.RDF_QUAD
RDF_QUAD_POGS
432 1657 382 0 1657
DB.DBA.RDF_QUAD
RDF_QUAD_SP
3634 3 0 0 3
SQL> status();
REPORT
VARCHAR
_______________________________________________________________________________
OpenLink Virtuoso Server
Version 06.01.3127-pthreads for Linux as of Apr 19 2010
Started on: 2010/04/22 18:18 GMT+120
Database Status:
File size 6257901568, 763904 pages, 407986 free.
2621440 buffers, 2870 used, 0 dirty 0 wired down, repl age 0 0 w. io 0
w/crsr.
Disk Usage: 2945 reads avg 0 msec, 0% r 0% w last 0 s, 134 writes,
23 read ahead, batch = 97. Autocompact 0 in 0 out, 0% saved.
Gate: 750 2nd in reads, 0 gate write waits, 0 in while read 0 busy scrap.
Log = data/narysubproperty.trx, 87 bytes
355829 pages have been changed since last backup (in checkpoint state)
Current backup timestamp: 0x0000-0x00-0x00
Last backup date: unknown
Clients: 1 connects, max 1 concurrent
RPC: 4 calls, 1 pending, 1 max until now, 0 queued, 0 burst reads (0%),
0 second brk=21519429632
Checkpoint Remap 38 pages, 0 mapped back. 0 s atomic time.
DB master 763904 total 407986 free 38 remap 0 mapped back
temp 256 total 251 free
Lock Status: 0 deadlocks of which 0 2r1w, 0 waits,
Currently 1 threads running 0 threads waiting 0 threads in vdb.
Pending:
Client 1111:1: Account: dba, 203 bytes in, 256 bytes out, 1 stmts.
PID: 6680, OS: unix, Application: unknown, IP#: 127.0.0.1
Transaction status: PENDING, 1 threads.
Locks:
Running Statements:
Time (msec) Text
83 status()
Replication Status: Server db-ALX-DEV03.
db-ALX-DEV03 db-ALX-DEV03 0 OFF.
Hash indexes
-----
QUERY
-----
SQL> sparql select ?conflict ?dateBegin where { ?conflict a
<http://dbpedia.org/ontology/MilitaryConflict> ; ?p
<http://dbpedia.org/ontology/Event> . ?p rdfs:subPropertyOf rdf:type ;
<http://www.w3.org/2006/time#hasBeginning> ?dateBegin . };
...
http://dbpedia.org/resource/Siege_of_Gezer
2007-08-26 18:33:33
http://dbpedia.org/resource/Siege_of_Tripoli_%281821%29
1821-09-23 00:00:00
http://dbpedia.org/resource/Submarine_Incident_off_Kildin_island
1992-02-11 00:00:00
http://dbpedia.org/resource/The_1793_Battle_of_Hightower
1793-10-17 00:00:00
http://dbpedia.org/resource/Second_Battle_of_Langensalza
1866-06-27 00:00:00
http://dbpedia.org/resource/Siege_of_Messolonghi_%281822%29
1822-10-25 00:00:00
4250 Rows. -- 9450 msec.
-----------
AFTER QUERY
-----------
SQL> select * from sys_d_stat where INDEX_NAME like 'RDF_QUAD%';
KEY_TABLE
INDEX_NAME
TOUCHES READS READ_PCT N_DIRTY N_BUFFERS
VARCHAR NOT NULL
VARCHAR
INTEGER INTEGER INTEGER INTEGER INTEGER
_______________________________________________________________________________
DB.DBA.RDF_QUAD
RDF_QUAD
17981 11245 62 0 11245
DB.DBA.RDF_QUAD
RDF_QUAD_GS
0 0 0 0 0
DB.DBA.RDF_QUAD
RDF_QUAD_OP
0 0 0 0 0
DB.DBA.RDF_QUAD
RDF_QUAD_POGS
3811901 30618 0 0 30618
DB.DBA.RDF_QUAD
RDF_QUAD_SP
3634 3 0 0 3
SQL> status();
REPORT
VARCHAR
_______________________________________________________________________________
OpenLink Virtuoso Server
Version 06.01.3127-pthreads for Linux as of Apr 19 2010
Started on: 2010/04/22 18:12 GMT+120
Database Status:
File size 6257901568, 763904 pages, 408005 free.
2621440 buffers, 44669 used, 0 dirty 0 wired down, repl age 0 0 w. io
0 w/crsr.
Disk Usage: 44744 reads avg 0 msec, 0% r 0% w last 0 s, 140 writes,
237 read ahead, batch = 179. Autocompact 0 in 0 out, 0% saved.
Gate: 676 2nd in reads, 0 gate write waits, 0 in while read 0 busy scrap.
Log = data/narysubproperty.trx, 87 bytes
355829 pages have been changed since last backup (in checkpoint state)
Current backup timestamp: 0x0000-0x00-0x00
Last backup date: unknown
Clients: 1 connects, max 1 concurrent
RPC: 52 calls, 1 pending, 1 max until now, 0 queued, 82 burst reads
(157%), 0 second brk=21596606464
Checkpoint Remap 19 pages, 0 mapped back. 0 s atomic time.
DB master 763904 total 408005 free 19 remap 0 mapped back
temp 256 total 251 free
Lock Status: 0 deadlocks of which 0 2r1w, 0 waits,
Currently 1 threads running 0 threads waiting 0 threads in vdb.
Pending:
Client 1111:1: Account: dba, 2171 bytes in, 357896 bytes out, 1 stmts.
PID: 6619, OS: unix, Application: unknown, IP#: 127.0.0.1
Transaction status: PENDING, 1 threads.
Locks:
Running Statements:
Time (msec) Text
536 status()
Replication Status: Server db-ALX-DEV03.
db-ALX-DEV03 db-ALX-DEV03 0 OFF.
Hash indexes
43 Rows. -- 86 msec.
> Hi Martin,
>
> From the statistics i seem to me the db cache is not warmed, the disk
> cache is cold as only couple of disk buffers are used. What happens
> if you run query few more times ? you should see difference with
> execution time, how is it compared agains first run. If still time
> large, can check the disk status by :
>
> select top 20 * from sys_d_stat order by reads desc; This could
> give some ideas which index is read so often.
>
> Another note: are you using striped setup re db files with separate
> IO queues ? Also check FDsPerFile in INI , Parameters section, could
> set it to 4 if you have many reads, but this should not affect one
> query as all should be in memory .when executed few times
>
> Best Regards, Mitko
