Hello list,
I have the following query:
SELECT ?val (COUNT(DISTINCT ?id) as ?vc)
WHERE
{
?id <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> ?val ;
?property1 ?property_value1 ;
?property2 ?property_value2 .
?property_value1 bif:contains "'foo'" .
?property_value2 bif:contains "'bar'" .
}
GROUP BY ?val
ORDER BY DESC(?vc)
Note that I am using bif:contains instead of REGEX or CONTAINS to make
things as fast as possible. Currently, the query takes about 140
seconds to execute on my machine. I am wondering how I can further
speed up this query. 140s is still way too much. I can currently only
think of two options:
1) Re-write the SPARQL query. However, with the current SPARQL
knowledge that I have, I think that it is already written in the most
optimal form, isn't it?
2) Tweak my virtuoso.ini file. The file that I am using right now is
given at the bottom of this mail. I think I've set the NumberOfBuffers
and MaxDirtyBuffers to the recommended values for my platform (see
output of free -m at the bottom of this mail). I also tried using
Direct I/O by enabling O_DIRECT, but this had no effect. I went through
the docs to see if I could find more parameters that I can tweak in
order to improve performance, but it is hard to figure out what
parameters could really lead to a significant query performance
improvement, and also to what values to set them.
I have also set the swappiness to 10 instead of the default 60:
bart@hp-g7-02:~$ /sbin/sysctl vm.swappiness
vm.swappiness = 10
Any help in performance tuning this would greatly appreciated. I am
using Virtuoso Version: 07.00.3203, Build: Mar 26 2014 on an Ubuntu
13.10 64 bit platform. It the output of any other command is required
in order to diagnose this any further, please let me know.
Kind regards,
Bart
------------------- output of free -m -------------------------
bart@hp-g7-02:~$ free -m
total used free shared buffers cached
Mem: 24101 19189 4912 0 57 18293
-/+ buffers/cache: 837 23264
Swap: 529 0 529
---------------- output of status() command in isql --------------
SQL> status();
REPORT
VARCHAR
_______________________________________________________________________________
OpenLink Virtuoso Server
Version 07.00.3203-pthreads for Linux as of Mar 26 2014
Started on: 2014/04/16 09:40 GMT+120
Database Status:
File size 152433590272, 18607616 pages, 5854086 free.
20000 buffers, 19878 used, 31 dirty 0 wired down, repl age 7316 0 w.
io 0 w/crsr.
Disk Usage: 35928 reads avg 0 msec, 0% r 0% w last 0 s, 1080 writes,
259 read ahead, batch = 127. Autocompact 0 in 0 out, 0% saved.
Gate: 171 2nd in reads, 0 gate write waits, 0 in while read 0 busy scrap.
Log = /data/virtuoso7/1114/virtuoso.trx, 2754 bytes
12753014 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: 6 calls, 1 pending, 1 max until now, 0 queued, 0 burst reads (0%),
0 second brk=175112192
Checkpoint Remap 38 pages, 0 mapped back. 0 s atomic time.
DB master 18607616 total 5854086 free 38 remap 2 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, 335 bytes in, 347 bytes out, 1 stmts.
PID: 11635, OS: unix, Application: unknown, IP#: 127.0.0.1
Transaction status: PENDING, 1 threads.
Locks:
Running Statements:
Time (msec) Text
513 status()
Hash indexes
37 Rows. -- 514 msec.
----------------- virtuoso.ini -----------------------------------
[Database]
DatabaseFile = /data/virtuoso7/1114/virtuoso.db
ErrorLogFile = /data/virtuoso7/1114/virtuoso.log
LockFile = /data/virtuoso7/1114/virtuoso.lck
TransactionFile = /data/virtuoso7/1114/virtuoso.trx
xa_persistent_file = /data/virtuoso7/1114/virtuoso.pxa
ErrorLogLevel = 7
FileExtend = 200
MaxCheckpointRemap = 4754243
Striping = 0
TempStorage = TempDatabase
[TempDatabase]
DatabaseFile = /data/virtuoso7/1114/virtuoso-temp.db
TransactionFile = /data/virtuoso7/1114/virtuoso-temp.trx
MaxCheckpointRemap = 3604
Striping = 0
[Parameters]
ServerPort = 1111
LiteMode = 0
DisableUnixSocket = 1
DisableTcpSocket = 0
ServerThreads = 20
CheckpointInterval = 60
O_DIRECT = 1
CaseMode = 2
MaxStaticCursorRows = 5000
CheckpointAuditTrail = 0
AllowOSCalls = 0
SchedulerInterval = 10
DirsAllowed = ., /opt/virtuoso/share/virtuoso/vad
ThreadCleanupInterval = 0
ThreadThreshold = 10
ResourcesCleanupInterval = 0
FreeTextBatchSize = 1000
SingleCPU = 0
VADInstallDir = /opt/virtuoso/share/virtuoso/vad/
PrefixResultNames = 0
RdfFreeTextRulesSize = 100
IndexTreeMaps = 256
MaxMemPoolSize = 200000000
PrefixResultNames = 0
MacSpotlight = 0
IndexTreeMaps = 64
NumberOfBuffers = 2000000
MaxDirtyBuffers = 1500000
[HTTPServer]
ServerPort = 8890
ServerRoot = /opt/virtuoso/var/lib/virtuoso/vsp
ServerThreads = 20
DavRoot = DAV
EnabledDavVSP = 0
HTTPProxyEnabled = 0
TempASPXDir = 0
DefaultMailServer = localhost:25
ServerThreads = 10
MaxKeepAlives = 10
KeepAliveTimeout = 10
MaxCachedProxyConnections = 10
ProxyConnectionCacheTimeout = 15
HTTPThreadSize = 280000
HttpPrintWarningsInOutput = 0
Charset = UTF-8
[AutoRepair]
BadParentLinks = 0
[Client]
SQL_PREFETCH_ROWS = 100
SQL_PREFETCH_BYTES = 16000
SQL_QUERY_TIMEOUT = 0
SQL_TXN_TIMEOUT = 0
[VDB]
ArrayOptimization = 0
NumArrayParameters = 10
VDBDisconnectTimeout = 1000
KeepConnectionOnFixedThread = 0
[Replication]
ServerName = db-HP-G7-02
ServerEnable = 1
QueueMax = 50000
[Striping]
Segment1 = 100M, db-seg1-1.db, db-seg1-2.db
Segment2 = 100M, db-seg2-1.db
[Zero Config]
ServerName = virtuoso (HP-G7-02)
[Mono]
[URIQA]
DynamicLocal = 0
DefaultHost = localhost:8890
[SPARQL]
ResultSetMaxRows = 10000
MaxQueryCostEstimationTime = 400 ; in seconds
MaxQueryExecutionTime = 60 ; in seconds
DefaultQuery = select distinct ?Concept where {[] a
?Concept} LIMIT 100
DeferInferenceRulesInit = 0 ; controls inference rules loading
[Plugins]
LoadPath = /opt/virtuoso/lib/virtuoso/hosting
Load1 = plain, wikiv
Load2 = plain, mediawiki
Load3 = plain, creolewiki
-----------------------------------------------------------------------------
------------------------------------------------------------------------------
Learn Graph Databases - Download FREE O'Reilly Book
"Graph Databases" is the definitive new guide to graph databases and their
applications. Written by three acclaimed leaders in the field,
this first edition is now available. Download your free book today!
http://p.sf.net/sfu/NeoTech
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
