Hi Pantelis, OK, so by reducing the NumberOfBuffers from 2720000 to 1360000 and settingThreadCleanupInterval & ResourcesCleanupInterval = 1 making more system memory available, you have been able to perform the insert …
The "ISR NO OWNER …” messages indicate pending transactions probably on the
RDF_QUAD table perform the query to performed the triple pattern lookups and
then insertions into the table. You can query the sys_l_stat view to see which
tables have the most locks, waits and deadlocks on them with the query:
select top 10 * from sys_l_stat order by waits desc;
as detailed at:
http://docs.openlinksw.com/virtuoso/perfdiag/
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 13 Aug 2016, at 14:30, Pantelis Natsiavas <natsia...@gmail.com> wrote:
>
> Hi Hugh.
>
> Answering to your questions:
>
> 1. The total number of triples in virtuoso is 179.686.927, or at least this
> is the result of the count query against the default graph in the
> myURL:8890/sparql enpoint, without specifying a default graph IRI. I suppose
> that if I do not specify a default graph IRI, Virtuoso considers the union of
> all graphs as the default graph. Is this right?
>
> SELECT COUNT(*)
> WHERE {
> ?s ?p ?o
> }
>
> 2. I cannot really understand much of the status(); command, and therefore I
> write down the full response. Repeating the long-time transaction's
> execution, after 45 minutes passed (normally, after an hour or so the process
> breaks and the virtuoso restarts), the response is the following:
>
> Database Status:
> File size 0, 11663872 pages, 4599861 free.
> 2720000 buffers, 1201742 used, 2 dirty 0 wired down, repl age 0 0 w. io 1
> w/crsr.
> Disk Usage: 1202392 reads avg 0 msec, 16% r 0% w last 821 s, 731 writes
> flush 0 MB,
> 10509 read ahead, batch = 110. Autocompact 0 in 0 out, 0% saved.
> Gate: 42826 2nd in reads, 0 gate write waits, 0 in while read 0 busy scrap.
> Log = /usr/local/var/lib/virtuoso/db/virtuoso.trx, 4769 bytes
> 7063527 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: 17 calls, 2 pending, 2 max until now, 0 queued, 5 burst reads (29%), 0
> second 4057M large, 4057M max
> Checkpoint Remap 38 pages, 0 mapped back. 0 s atomic time.
> DB master 11663872 total 4599861 free 38 remap 2 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:
> 3574016: IER 1:-2
> 56: ISR NO OWNER
> 52: ISR NO OWNER
> 48: ISR NO OWNER
> ..... (Thousands of similar lines repeating) .......
> Client 1111:1:-2: Account: dba, 742 bytes in, 2516 bytes out, 1 stmts.
> PID: 2120, OS: unix, Application: unknown, IP#: 127.0.0.1
> Transaction status: PENDING, 1 threads.
> Locks: 34868: IE, 3574134: IE, 34971: IE, 3574137: IE, 3574173: IE, 3574080:
> IE, 3574072: IE, 3574068: IE, 3574172: IE, 3574103: IE, 34933: IE, ....
>
> Client 1111:3:-4: Account: dba, 471 bytes in, 547444 bytes out, 1 stmts.
> PID: 2268, OS: unix, Application: unknown, IP#: 127.0.0.1
> Transaction status: PENDING, 1 threads.
> Locks:
>
>
> Running Statements:
> Time (msec) Text
> 1707 status()
> 2732874 SPARQL DEFINE sql:log-enable 3 INSERT { GRAPH
> <http://bio2rdf.org/clinicaltrials
>
>
> Hash indexes
>
> I tried to ask the status() in a later stage, but the response was to large
> to fit (flooded by the repeating statements like the ones highlighted above)
> in the isql-v command line window
> The "lost connection" message came after an hour from the beginning of the
> whole transaction with the top command line utility showing virtuoso process
> consuming around 100% CPU and 98% memory.
>
> Therefore, I have set the parameters
> NumberOfBuffers = 1360000
> MaxDirtyBuffers = 1000000
> ThreadCleanupInterval = 1
> ResourcesCleanupInterval = 1
>
> I restarted and repeated the overall process and the result was successful.
> After 13 hours, the SPARUL operation had completed successfully.
>
> Thank you very much for your help.
>
> Kind regards,
> Pantelis Natsiavas
>
>
>
>
> 2016-08-11 15:56 GMT+03:00 Hugh Williams <hwilli...@openlinksw.com>:
> Hi Pantelis,
>
> 152M triple graphs is quite large to be performing such an operation on which
> will require a significant amount of memory to complete. What is the total
> number of triples in Virtuoso and how many of the buffers are in use when the
> database is in use, which can be seen by running the status(); command from
> isql. As you may be able to reduce the “NumberOfBuffers” param for database
> workig set to make more memory available to the system for performing such
> large insert queries.
>
> You should also ensure the following params are set in the INI file to ensure
> unused resource/threads are cleaned up immediately to maximise available
> memory:
>
> [Parameters]
> ....
> ThreadCleanupInterval = 1
> ResourcesCleanupInterval = 1
> ...
>
> See, http://docs.openlinksw.com/virtuoso/dbadm/
>
> The server log does not show anything useful …
>
> 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
>
smime.p7s
Description: S/MIME cryptographic signature
------------------------------------------------------------------------------ What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic patterns at an interface-level. Reveals which users, apps, and protocols are consuming the most bandwidth. Provides multi-vendor support for NetFlow, J-Flow, sFlow and other flows. Make informed decisions using capacity planning reports. http://sdm.link/zohodev2dev
_______________________________________________ Virtuoso-users mailing list Virtuoso-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/virtuoso-users
