Bart, On 17 Apr 2014, at 15:42, Bart Vandewoestyne <bart.vandewoest...@telenet.be> wrote:
> On 2014-04-17 15:04, Hugh Williams wrote: >> Hi Bart, >> >> I note your Virtuoso version is reported as "Version >> 07.00.3203-pthreads for Linux as of Mar 26 2014" which despite having >> been be built in March 2014, a 3203 build is about the original 7.0 >> release code from a year ago. The latest builds are 07.10.3207+ >> available from git, thus I suggest you update your binary to the latest >> develop/7 archive, as the have been many many query optimisation >> improvements since then: >> >> http://virtuoso.openlinksw.com/dataspace/doc/dav/wiki/Main/VOSGitUsage >> >> Best Regards >> Hugh Williams > > Hugh (and list), > > The comparison between 7.0.0-rc2.3203-pthreads and > 7.1.1-dev.3208-pthreads (for the queries that I work with) is online at > > https://dl.dropboxusercontent.com/u/32340538/query_timings_version_comparison.png > > As you can see, no real improvement: > > * Query 1 and 6 are slower > * Only query 6 is significantly faster > * All other queries are about as fast as before > > I really would like to speed up query 1 and 4. In the meanwhile, I also > tried increasing FreeTextBatchSize from 100000 to its default 10000000 > but that didn't help either. > > What else could I try? Do you need any further info from my side? [Hugh] I note that you do not appear to have any of the parameters the new v7 vectored execution set in your INI file , these being: MaxQueryMem = 2G ; memory allocated to query processor VectorSize = 1000 ; initial parallel query vector (array of query operations) size MaxVectorSize = 1000000 ; query vector size threshold. AdjustVectorSize = 0 ThreadsPerQuery = 8 AsyncQueueMaxThreads = 10 See the following documentation for details on use: http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#confvectexec http://docs.openlinksw.com/virtuoso/vexqrparl.html These should improve query performance, although probably not the relative performance between the two builds. If the query response time are still not fast enough, are you able to provide a copy of the datasets such that we can load locally for recreation ? Regards Hugh > > Kind regards, > Bart > > >>> [...] >>> I'm confident that further tweaking my virtuoso.ini can still speed >>> things up. It is however difficult to know what parameters I can >>> further finetune. I already tried changing O_DIRECT=1 instead of >>> O_DIRECT=0 but that didn't help. My swapiness is set to 10 instead of >>> 60. What else could I try? Below, you can find more information on >>> my setup. If you need more info, just let me know! >>> >>> Kind regards, >>> Bart >>> >>> >>> ---------------- operating system ------------------------------ >>> Ubuntu 12.04.4 LTS >>> Linux hp-g7-02 3.2.0-59-generic #90-Ubuntu SMP Tue Jan 7 22:43:51 UTC >>> 2014 x86_64 x86_64 x86_64 GNU/Linux >>> >>> >>> ----------------- output of free -m ---------------------------- >>> bart@hp-g7-02:~$ free -m >>> total used free shared buffers cached >>> Mem: 24101 5306 18794 0 3 4882 >>> -/+ buffers/cache: 420 23681 >>> Swap: 529 24 505 >>> >>> ----------- output of status() while worst query is running ------------ >>> >>> SQL> status(); >>> REPORT >>> VARCHAR >>> _______________________________________________________________________________ >>> >>> OpenLink Virtuoso Server >>> Version 07.00.3203-pthreads for Linux as of Mar 26 2014 >>> Started on: 2014/04/17 14:12 GMT+120 >>> >>> Database Status: >>> File size 152433590272, 18607616 pages, 5854115 free. >>> 2000000 buffers, 242406 used, 0 dirty 0 wired down, repl age 0 0 w. >>> io 158 w/crsr. >>> Disk Usage: 243428 reads avg 0 msec, 0% r 0% w last 5 s, 1087 writes, >>> 1098 read ahead, batch = 218. Autocompact 0 in 0 out, 0% saved. >>> Gate: 179 2nd in reads, 0 gate write waits, 0 in while read 0 busy scrap. >>> Log = /data/virtuoso7/1114/virtuoso.trx, 185 bytes >>> 12753014 pages have been changed since last backup (in checkpoint state) >>> Current backup timestamp: 0x0000-0x00-0x00 >>> Last backup date: unknown >>> Clients: 2 connects, max 2 concurrent >>> RPC: 23 calls, 2 pending, 2 max until now, 0 queued, 0 burst reads >>> (0%), 0 second brk=16624304128 >>> Checkpoint Remap 38 pages, 0 mapped back. 0 s atomic time. >>> DB master 18607616 total 5854115 free 38 remap 0 mapped back >>> temp 256 total 251 free >>> >>> Lock Status: 0 deadlocks of which 0 2r1w, 0 waits, >>> Currently 2 threads running 0 threads waiting 0 threads in vdb. >>> Pending: >>> >>> Client 1111:1: Account: dba, 1167 bytes in, 754 bytes out, 1 stmts. >>> PID: 25685, OS: unix, Application: unknown, IP#: 127.0.0.1 >>> Transaction status: PENDING, 1 threads. >>> Locks: >>> >>> Client 1111:2: Account: dba, 1107 bytes in, 21127 bytes out, 1 stmts. >>> PID: 25689, OS: unix, Application: unknown, IP#: 127.0.0.1 >>> Transaction status: PENDING, 1 threads. >>> Locks: >>> >>> >>> Running Statements: >>> Time (msec) Text >>> 542 status() >>> 31685 #line 1 "temp.sparql" >>> sparql >>> # This query finds the first 15 items containing a >>> >>> >>> Hash indexes >>> >>> >>> 45 Rows. -- 542 msec. >>> >>> ----------------- virtuoso version ----------------------------- >>> Virtuoso Open Source Edition (Column Store) (multi threaded) >>> Version 7.0.0-rc2.3203-pthreads as of Mar 26 2014 >>> Compiled for Linux (x86_64-unknown-linux-gnu) >>> Copyright (C) 1998-2013 OpenLink Software >>> >>> ------------- current worst query ---------------------------- >>> PREFIX foo: <http://blabla_unimportant_blabla#> >>> SELECT ?id ?label ?term ?type >>> WHERE >>> { >>> { >>> SELECT ?id >>> WHERE >>> { >>> ?id foo:hasSearchTerm ?searchterm . >>> ?searchterm bif:contains '"bar*"' . >>> } >>> GROUP BY ?id >>> LIMIT 15 >>> } >>> >>> ?id foo:preferredLabel ?label ; >>> disq:hasSearchTerm ?term ; >>> a ?type >>> } >>> >>> -------- current second worst 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 "'foofoo'" . >>> >>> ?property_value2 bif:contains "'barbar'" . >>> } >>> GROUP BY ?val >>> ORDER BY DESC(?vc) >>> >>> ----------------------------- 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 = 2000 >>> Striping = 0 >>> TempStorage = TempDatabase >>> >>> [TempDatabase] >>> DatabaseFile = /data/virtuoso7/1114/virtuoso-temp.db >>> TransactionFile = /data/virtuoso7/1114/virtuoso-temp.trx >>> MaxCheckpointRemap = 2000 >>> Striping = 0 >>> >>> [Parameters] >>> ServerPort = 1111 >>> LiteMode = 0 >>> DisableUnixSocket = 1 >>> DisableTcpSocket = 0 >>> ServerThreads = 20 >>> CheckpointInterval = 60 >>> O_DIRECT = 0 >>> CaseMode = 2 >>> MaxStaticCursorRows = 5000 >>> CheckpointAuditTrail = 0 >>> AllowOSCalls = 0 >>> SchedulerInterval = 10 >>> DirsAllowed = ., /opt/virtuoso/share/virtuoso/vad >>> ThreadCleanupInterval = 0 >>> ThreadThreshold = 10 >>> ResourcesCleanupInterval = 0 >>> FreeTextBatchSize = 100000 >>> 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 >>> AdjustVectorSize = 1 >>> MaxVectorSize = 3000000 >>> >>> [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 >>> MaxQueryExecutionTime = 600; 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