Hi Bart,
Your status(''); output shows that the default Buffers size of 20000 is being
allocated on server startup and all quickly used due to the size of your
dataset, with the "NumberOfBuffers" setting of 2000000 in the INI file not
being pickup due to those leading spaces which should be removed and the server
restarted:
> 20000 buffers, 19878 used,
> IndexTreeMaps = 64
> NumberOfBuffers = 2000000
> MaxDirtyBuffers = 1500000
>
> [HTTPServer]
Best Regards
Hugh Williams
Professional Services
OpenLink Software, Inc. // http://www.openlinksw.com/
Weblog -- http://www.openlinksw.com/blogs/
LinkedIn -- http://www.linkedin.com/company/openlink-software/
Twitter -- http://twitter.com/OpenLink
Google+ -- http://plus.google.com/100570109519069333827/
Facebook -- http://www.facebook.com/OpenLinkSoftware
Universal Data Access, Integration, and Management Technology Providers
On 16 Apr 2014, at 09:11, Bart Vandewoestyne <bart.vandewoest...@telenet.be>
wrote:
> 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
------------------------------------------------------------------------------
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
