Hi Andreas, Generally there should be no create additional indexes as the 2 Full & 3 partial indexes have been found to be sufficient for most use cases, with only one use case encountered where an addtional partial index was required as detailed at:
http://virtuoso.openlinksw.com/dataspace/doc/dav/wiki/Main/VirtRDFPerformanceTuning#Index%20Scheme%20Selection
Thus what indexes (STATISTICS DB.DBA.RDF_QUAD;) are created on these various
instances you have are they all the same or do they have different indexes as
the more indexes the more large the database and hence more memory required for
hosting it. Also are the triple counts the same on all these instances ?
Did you make any of the other INI file changes in my previous email, certainly
“AdjustVectorSize” should be set to 0 ?
You should also consider profiling the queries to determine if the best quey
plan is being used, which can be done with the “profile()” function as detailed
at:
http://virtuoso.openlinksw.com/dataspace/doc/dav/wiki/Main/VirtTipsAndTricksAanalyzingSPARQLQuery
http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#querylogging -
Query Logging
Note there is are also some INI file params that can be set to control query
optimisation ie plans as if a bad plan is being chosen then these options can
in some cases enable better plans to be chosen, see:
http://virtuoso.openlinksw.com/dataspace/doc/dav/wiki/Main/VirtQueryOptDiagnostic
If you can provide query plans, database statistics then these can be analysed
to trying and determine the cause of long running queries …
It certainly would be interesting to see how the query plans and database stats
vary being the instance where the query runs in msecs and the other were it
runs in 40+mins ...
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 28 Feb 2016, at 14:58, Nolle, Andreas <no...@hs-albsig.de> wrote:
>
> Dear Hugh,
>
> many thanks for your reply!
> Sorry, I forgot to mentioned that I increased up to 24 core and 228 GB memory.
>
> I'm still on trying to optimize the performance and to avoid transaction
> deadlocks... but unfortunately without any success so far...
>
> I've already tried to create some additional indexes, like PSOG, but the
> evaluation of some filter parts take in some cases really long (more than 40
> minutes)...
>
> For me it is also very strange why the evaluation of query
>
> define input:default-graph-uri <http://clashsniffer.hs-albsig.de/benchset>
> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
> PREFIX owl: <http://www.w3.org/2002/07/owl#>
> PREFIX quest: <http://obda.org/quest#>
> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
>
> SELECT ?x ?b
> WHERE {
> ?x <http://purl.org/ontology/bibo/volume> ?b .
> FILTER ( ?x >= <http://eprints.wmin.ac.uk/id/eprint/1620> ) .
> FILTER ( ?x < <http://pub.epsilon.slu.se/id/eprint/11557> ) .
> }
>
> at instance running at port 8895 will take only some ms, but executing query
>
> define input:default-graph-uri <http://clashsniffer.hs-albsig.de/benchset>
> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
> PREFIX owl: <http://www.w3.org/2002/07/owl#>
> PREFIX quest: <http://obda.org/quest#>
> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
>
> SELECT ?x ?b
> WHERE {
> SERVICE <http://141.87.4.8:8895/sparql> {
> ?x <http://purl.org/ontology/bibo/volume> ?b .
> FILTER ( ?x >= <http://eprints.wmin.ac.uk/id/eprint/1620> ) .
> FILTER ( ?x < <http://pub.epsilon.slu.se/id/eprint/11557> ) .
> } .
> }
>
> at another instance is running possibly indefinitely... (stopped the
> evaluation after 45 minutes).
>
> Best
> Andy
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: Hugh Williams [mailto:hwilli...@openlinksw.com]
> Gesendet: Sonntag, 28. Februar 2016 04:14
> An: Nolle, Andreas <no...@hs-albsig.de>
> Cc: virtuoso-users@lists.sourceforge.net
> Betreff: Re: [Virtuoso-users] infrequent errors on parallel querying
>
> Hi Andreas,
>
> In you INI file “MaxClientConnections” and “ServerThreads” are the same thing
> the latter being the original name which is retained for compatibility
> reasons. Why do you set these params to 1000 on both sections of the INI file
> ? I would reduce them to some thing like 200 as especially the HTTP Server
> ones are pre-allocated on server startup which will consume significant
> server resources for these threads.
>
> Looking at the Vectored execution INI file params you have set:
>
> MaxQueryMem = 32G ; memory allocated to query processor
> VectorSize = 1000 ; initial parallel query vector (array
> of query operations) size
> MaxVectorSize = 1000000 ; query vector size threshold.
> AdjustVectorSize = 1
> ThreadsPerQuery = 24
>
> Why is MaxQueryMem set so high ie 32G as this will all consume significant
> system memory when multiple queries are being run, thus I would set it to
> something like the 2G default to start off with. How much memory is available
> on the system ?
>
> AdjustVectorSize should be set to 0 in most use cases …
>
> I assume you machine has 24 cores which is why you set "ThreadsPerQuery
> = 24” ?
>
> See the following documentation on vectored query execution:
>
> http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#confvectexec -
> Configuring Vectored Execution
>
> http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#tunparamsmworkload -
> Tuning Parameters for Multiuser Workloads
>
> and the following on Transaction deadlocks and how to determine and avoid
> them:
>
> http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#perfdiag -
> Performance diagnostics
>
> http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#TRANSACTION_ISOLATION_LEVELS
> - Transaction Metrics, Diagnostics and Optimization
>
> 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 26 Feb 2016, at 08:03, Nolle, Andreas <no...@hs-albsig.de> wrote:
>>
>> The critical error is now that after some query evaluations I get a lot of
>> "Virtuoso 40001 Error SR172: Transaction deadlocked" errors. For me this is
>> really strange because I've set the DefaultIsolation = 2 and only SELECT
>> queries are executed. Please notice that I've slightly changed some
>> parameters in the ini files.
>>
>> It would be really nice if you could help me on that and give me some
>> suggestions how transaction errors can be avoided on evaluating SELECT
>> queries. Please find the current log and ini files of each Virtuoso instance
>> as well as the corresponding output of status() at
>> https://www.dropbox.com/s/v6hvw7j7uz9r9uq/virtuoso_files.zip?dl=0
>>
>> Best
>> Andy
>>
>>
>> -----Ursprüngliche Nachricht-----
>> Von: Nolle, Andreas [mailto:no...@hs-albsig.de]
>> Gesendet: Freitag, 26. Februar 2016 09:00
>> An: Hugh Williams <hwilli...@openlinksw.com>
>> Cc: virtuoso-users@lists.sourceforge.net
>> Betreff: Re: [Virtuoso-users] infrequent errors on parallel querying
>>
>> Hi Hugh,
>>
>> in the meantime I solved some of the errors. Assuming the following query is
>> evaluated at endpoint running at port 8893:
>>
>> SELECT ?x ?a ?b ?P0src ?P1src
>> WHERE {
>> {
>> SERVICE <http://141.87.4.8:8899/sparql> {
>> ?x <http://purl.org/dc/terms/partOf> ?a .
>> FILTER ( ?x >=
>> <http://bibsonomy.org/uri/bibtexkey/conf/icmcs/NagatomoYOIT02/dblp> ) .
>> FILTER ( ?x <
>> <http://bibsonomy.org/uri/bibtexkey/conf/wpmc/EndoMA14/dblp> ) .
>> } .
>> BIND (<http://141.87.4.8:8899/sparql> AS ?P0src)
>> }
>> UNION
>> {
>> SERVICE <http://141.87.4.8:8891/sparql> {
>> ?x <http://purl.org/dc/terms/partOf> ?a .
>> FILTER ( ?x >=
>> <http://bibsonomy.org/uri/bibtexkey/conf/icmcs/NagatomoYOIT02/dblp> ) .
>> FILTER ( ?x <
>> <http://bibsonomy.org/uri/bibtexkey/conf/wpmc/EndoMA14/dblp> ) .
>> } .
>> BIND (<http://141.87.4.8:8891/sparql> AS ?P0src)
>> }
>> UNION
>> {
>> ?x <http://purl.org/dc/terms/partOf> ?a .
>> FILTER ( ?x >=
>> <http://bibsonomy.org/uri/bibtexkey/conf/icmcs/NagatomoYOIT02/dblp> ) .
>> FILTER ( ?x < <http://bibsonomy.org/uri/bibtexkey/conf/wpmc/EndoMA14/dblp>
>> ) .
>> BIND (<http://141.87.4.8:8893/sparql> AS ?P0src)
>> }
>> UNION
>> {
>> SERVICE <http://141.87.4.8:8895/sparql> {
>> ?x <http://purl.org/dc/terms/partOf> ?a .
>> FILTER ( ?x >=
>> <http://bibsonomy.org/uri/bibtexkey/conf/icmcs/NagatomoYOIT02/dblp> ) .
>> FILTER ( ?x <
>> <http://bibsonomy.org/uri/bibtexkey/conf/wpmc/EndoMA14/dblp> ) .
>> } .
>> BIND (<http://141.87.4.8:8895/sparql> AS ?P0src)
>> BIND (<http://141.87.4.8:8895/sparql> AS ?P0src)
>> } .
>> ?b <http://www.aktors.org/ontology/portal#article-of-journal> ?x .
>> FILTER ( ?x >=
>> <http://bibsonomy.org/uri/bibtexkey/conf/icmcs/NagatomoYOIT02/dblp> ) .
>> FILTER ( ?x < <http://bibsonomy.org/uri/bibtexkey/conf/wpmc/EndoMA14/dblp> )
>> .
>> BIND (<http://141.87.4.8:8893/sparql> AS ?P1src) }
>>
>> If this endpoint has no ?x matching pattern at line 22 of this query, an
>> exception is thrown. It is possible to avoid that by replacing line 20 to 26
>> by
>>
>> UNION
>> {
>> {
>> ?x <http://purl.org/dc/terms/partOf> ?a .
>> FILTER ( ?x >=
>> <http://bibsonomy.org/uri/bibtexkey/conf/icmcs/NagatomoYOIT02/dblp> ) .
>> FILTER ( ?x <
>> <http://bibsonomy.org/uri/bibtexkey/conf/wpmc/EndoMA14/dblp> ) .
>> }
>> BIND (<http://141.87.4.8:8893/sparql> AS ?P0src)
>> }
>>
>> Best
>> Andy
>>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> Virtuoso-users mailing list
> Virtuoso-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/virtuoso-users
smime.p7s
Description: S/MIME cryptographic signature
------------------------------------------------------------------------------ Site24x7 APM Insight: Get Deep Visibility into Application Performance APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month Monitor end-to-end web transactions and take corrective actions now Troubleshoot faster and improve end-user experience. Signup Now! http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
_______________________________________________ Virtuoso-users mailing list Virtuoso-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/virtuoso-users
