Hi Martin,
To eliminate the time to retrieve the result set do a 'sparql select count(*)
where ( ... ) ' , this would give us the time to execute query server side. The
rest is transmitting results to client, should see difference.
Best Regards,
Mitko
On Apr 22, 2010, at 7:24 PM, Martin Gerlach wrote:
> 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
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Virtuoso-users mailing list
> Virtuoso-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/virtuoso-users
--
Mitko Iliev
Developer Virtuoso Team
OpenLink Software
http://www.openlinksw.com/virtuoso
Cross Platform Web Services Middleware
