Hi Hugh.
Here is a "status();" output when the database has been used for some
time already under quite high work load:
# isql-vt 1111 dba dba exec="status();"
Connected to OpenLink Virtuoso
Driver: 07.20.3212 OpenLink Virtuoso ODBC Driver
OpenLink Interactive SQL (Virtuoso), version 0.9849b.
Type HELP; for help and EXIT; to exit.
REPORT
VARCHAR
_______________________________________________________________________________
OpenLink Virtuoso Server
Version 07.20.3212-pthreads for Linux as of Apr 8 2015
Started on: 2015-10-22 05:15 GMT+0
Database Status:
File size 0, 53782528 pages, 13376185 free.
5000000 buffers, 4999093 used, 364851 dirty 0 wired down, repl age
2824259 0 w. io 0 w/crsr.
Disk Usage: 5512682 reads avg 0 msec, 37% r 0% w last 2 s, 628181
writes flush 59.98 MB,
28545 read ahead, batch = 184. Autocompact 44190 in 29737 out, 32%
saved col ac: 298285 in 7% saved.
Gate: 50366 2nd in reads, 0 gate write waits, 0 in while read 0 busy
scrap.
Log = /var/lib/virtuoso-opensource-7/db/virtuoso.trx, 1504439166 bytes
576438 pages have been changed since last backup (in checkpoint state)
Current backup timestamp: 0x3EF5-0xD4-0x1A
Last backup date: Thu Oct 22 05:25:15 2015
Clients: 21 connects, max 11 concurrent
RPC: 295859 calls, -424 pending, 10 max until now, 0 queued, 196 burst
reads (0%), 0 second 2M large, 134M max
Checkpoint Remap 486989 pages, 0 mapped back. 29 s atomic time.
DB master 53782528 total 13376040 free 486989 remap 3716 mapped back
temp 1280 total 1275 free
Lock Status: 0 deadlocks of which 0 2r1w, 132 waits,
Currently 2 threads running 0 threads waiting 0 threads in vdb.
Yesterday I executed the same update query, but with lower limit and it
run successfully. I kept monitoring status(); output and noticed that
with every run the used memory increased. Is there a way to flush the
memory after we do a checkpoint, when running the script? Is there a way
to execute some sort of garbage collection to free up unused memory?
Best Regards.
Piret Lattikas
On 20.10.15 17:00, Hugh Williams wrote:
Hi Piret,
By “warm” I mean when running the typical work load when the necessary
data of servicing the requests would have been loaded into memory, as
if there has been no activity then the server would not load data into
memory which is the case from the status() output below ie 5000000
buffers, 524723 used indicating 10% of the available buffers
configured in the INI file are in use …
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 20 Oct 2015, at 14:15, Piret Lattikas <piret.latti...@zerotech.ee
<mailto:piret.latti...@zerotech.ee>> wrote:
Hi, Hugh.
This is the output of running the "status();" command after the databse
has been up for ~20 minutes, but without any process using it:
SQL> status();
REPORT
VARCHAR
_______________________________________________________________________________
OpenLink Virtuoso Server
Version 07.20.3212-pthreads for Linux as of Apr 8 2015
Started on: 2015-10-20 12:41 GMT+0
Database Status:
File size 0, 53782528 pages, 14282765 free.
5000000 buffers, 524723 used, 2 dirty 0 wired down, repl age 0 0 w.
io 0 w/crsr.
Disk Usage: 529409 reads avg 0 msec, 0% r 0% w last 1 s, 97613
writes flush 0 MB,
1353 read ahead, batch = 315. Autocompact 0 in 0 out, 0% saved.
Gate: 1501 2nd in reads, 0 gate write waits, 0 in while read 0 busy
scrap.
Log = /var/lib/virtuoso-opensource-7/db/virtuoso.trx, 2062 bytes
1727723 pages have been changed since last backup (in checkpoint state)
Current backup timestamp: 0x3EF5-0xD4-0x1A
Last backup date: Mon Oct 19 23:17:46 2015
Clients: 2 connects, max 1 concurrent
RPC: 16 calls, 0 pending, 1 max until now, 0 queued, 4 burst reads
(25%), 0 second 0M large, 12M max
Checkpoint Remap 0 pages, 0 mapped back. 33 s atomic time.
DB master 53782528 total 14282765 free 0 remap 0 mapped back
temp 1280 total 1275 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:2: Account: dba, 653 bytes in, 7041 bytes out, 1 stmts.
PID: 7440, OS: unix, Application: unknown, IP#: 127.0.0.1
Transaction status: PENDING, 1 threads.
Locks:
Running Statements:
Time (msec) Text
868 status()
Hash indexes
Best Regards.
Piret Lattikas
On 20.10.15 15:59, Hugh Williams wrote:
Hi Piret,
If you have 10.6 billion trips and only 599GB RAM, then given that
Virtuoso 7 typically requires 10GB RAM per billion triples, I
suspect all the memory is being consumed and there will be a lot of
swapping to disk, which is why I wanted to see the output of running
the “status();” command when the database is “warm” which will show
resource usage. The output of the Linux “top” command would also be
useful to see.
I see you use the “isql-vt” command which implies you are using a
Virtuoso installation supplied with one of the Linux distro’s and as
said perviously is rather old (3212) and thus would recommend
updating to a latest 3214 stable or develop 7 build from git as
there have been a number of memory related and other fixes in these
later versions.
If your database integrity check is failing on this other instance
then you must to a crash-dump and recovery or a crash recovery when
the normal crash dump recovery fails, all of which are detailed at:
http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#backup
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 20 Oct 2015, at 12:44, Piret Lattikas
<piret.latti...@zerotech.ee> wrote:
Hi, Hugh.
To answer some of your questions. The total count of triples in our
Quad Store is around 10.6billion. The server machine has 6 cores
and 59GB RAM.
The actual .sql script run is this:
log_enable(2);
SPARQL DELETE { GRAPH ?graph { ?st
<http://www.w3.org/1999/02/22-rdf-syntax-ns#subject> ?subject . }}
INSERT { GRAPH ?graph { ?st
<http://purl.org/vocab/changeset/schema#subject> ?subject . }}
WHERE { GRAPH ?graph { ?st
<http://www.w3.org/1999/02/22-rdf-syntax-ns#subject> ?subject . }}
LIMIT 10000000;
checkpoint;
log_enable(1);
And the script is called out like this:
isql-vt 1111 dba dba update.sql
The script was called out 5 times in a row and then another
checkpoint was made like this:
isql-vt 1111 dba dba exec="checkpoint;"
No other process was using virtuoso when we were executing this
script and when the crash happened.
In addition we have a second instance of virtuoso, which often is
making many concurrent insertions. We noticed that making integrity
check in that instance fails after some time with the following error:
...
09:42:19 /usr/bin/virtuoso-t() [0x5db66c]
09:42:19 /usr/bin/virtuoso-t(sf_sql_execute_w+0x74) [0x5db884]
09:42:19 /usr/bin/virtuoso-t() [0x8d8f38]
09:42:19 /usr/bin/virtuoso-t() [0x8df6fc]
09:42:19 /lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)
[0x7ff65fd23182]
09:42:19 /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7ff65f32d47d]
09:42:19 GPF: page.c:899 out of order
Is it a problem if we keep using that instance, when the integrity
check fails with this error? Should we definitely do a crash dump
and restore from that? The reason I am asking is that this problem
occurs almost every week and restoring from crash dump takes some
time. So processes using that virtuoso instance are down during
that time.
Best Regards.
Piret Lattikas
On 19.10.15 17:41, Hugh Williams wrote:
Hi Piret,
I note you are using an older Virtuoso 7.2.3212 build from Apr
2015 where as the current stable release build from git or
source-forge is 7.2.3214, thus I would suggest updating to this
later release or even the current git develop/7 head to get all
the latest fixes and see if the problem persists.
The “??”’s in gdb back trace output would indicate the Virtuoso
binary is stripped of symbols thus to get a meaningful back trace
a debug build with symbols intact needs to be compiled as follows:
1. Configure using the —with debug option:
./configure —with-debug
2. In the Makefile, check to ensure CFLAGS has the “-g” option set
which generates debug information:
CFLAGS = -g -O2
3. Then do "make clean all deinstall reinstall” to build a new
debug unstripped binary (virtuoso-t)
4. Start database with this new binary and force the crash
condition again to generate a new core file
5. Use gdb to load core file
gdb virtuoso-t core
6. At the prompt, type "bt" or “backtrace” to backtrace through
stack and provide the output when top of stack is reached.
You indicate having 1.7billion triples to be updated of which
about 500 million are still to be updated, but what is the total
count of triples in the Quad Store ( select * where {?s ?p ?o} ) ?
Also what are the specs of the server machine being used in terms
of number of cores and memory in particular and what does the
output of the following command run from the “isql” command line
tool, report when the database is “warm” :
status();
Can you provide the actual content of the SQL script being run ?
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 19 Oct 2015, at 11:37, Piret Lattikas
<piret.latti...@zerotech.ee> wrote:
Hi,
We were running a simple update SPARQL query for a large set of
data. The update query for replacing one predicate with another,
itself is this:
SPARQL DELETE { GRAPH ?graph { ?st
<http://www.w3.org/1999/02/22-rdf-syntax-ns#subject> ?subject .
}} INSERT { GRAPH ?graph { ?st
<http://purl.org/vocab/changeset/schema#subject> ?subject . }}
WHERE { GRAPH ?graph { ?st
<http://www.w3.org/1999/02/22-rdf-syntax-ns#subject> ?subject .
}} LIMIT 10000000;
The initial count of triples that needed to be updated was ~1 700
000 000. The .sql script used for update set the log_enable(2) at
the beginning, before executing the actual update. And a
checkpoint was called out in the same .sql script after the
update query finished. We were able to run that script for quite
a while without any problems. Suddenly running the script made
virtuoso crash, the remaining triples count that needed updating
was around 500 000 000. The error in virtuoso.log file shows the
following:
09:13:37 In delete or rb of insert on RDF_QUAD dp 38144031 seg 6,
col 3 is 10909 values and next 8276
09:13:37 /usr/bin/virtuoso-t() [0x8d4afd]
...
09:13:37 /usr/bin/virtuoso-t() [0x53f19b]
09:13:37 /usr/bin/virtuoso-t(delete_node_input+0x13b) [0x5cbb3b]
09:13:37 /usr/bin/virtuoso-t() [0x5c7fed]
09:13:37 /usr/bin/virtuoso-t() [0x5c8416]
09:13:37 /usr/bin/virtuoso-t(table_source_input_unique+0x2a9)
[0x5cfe69]
09:13:37 /usr/bin/virtuoso-t() [0x5c7fed]
...
09:13:37 /usr/bin/virtuoso-t() [0x5c826e]
09:13:37 /usr/bin/virtuoso-t(skip_node_input+0x466) [0x5cc9e6]
09:13:37 /usr/bin/virtuoso-t() [0x5c7fed]
...
09:13:37 /usr/bin/virtuoso-t() [0x5db26a]
09:13:37 /usr/bin/virtuoso-t(sf_sql_execute_w+0x74) [0x5db884]
09:13:37 /usr/bin/virtuoso-t() [0x8d8f38]
09:13:37 /usr/bin/virtuoso-t() [0x8df6fc]
09:13:37 /lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)
[0x7f69a6f65182]
09:13:37 /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7f69a656f47d]
09:13:37 GPF: collock.c:1410 uneven length cols after delete,
update or rollback of insert
Do get the core dump of the crash set 'ulimit -c unlimited' and
started the script again. The crash is reproducable with every
run. The core dump shows the following:
# gdb virtuoso-t core
...
[New LWP 15882]
[New LWP 15879]
[Thread debugging using libthread_db enabled]
Using host libthread_db library
"/lib/x86_64-linux-gnu/libthread_db.so.1".
Core was generated by `/usr/bin/virtuoso-t +wait +configfile
/etc/virtuoso-opensource-7/virtuoso.ini'.
Program terminated with signal SIGSEGV, Segmentation fault.
(gdb) bt
#0 0x00000000008d4bde in ?? ()
...
#6 0x000000000053f19b in ?? ()
#7 0x00000000005cbb3b in delete_node_input ()
#8 0x00000000005c7fed in ?? ()
#9 0x00000000005c8416 in ?? ()
#10 0x00000000005cfe69 in table_source_input_unique ()
#11 0x00000000005c7fed in ?? ()
...
#30 0x00000000005c826e in ?? ()
#31 0x00000000005cc9e6 in skip_node_input ()
#32 0x00000000005c7fed in ?? ()
...
#43 0x00000000005db26a in ?? ()
#44 0x00000000005db884 in sf_sql_execute_w ()
#45 0x00000000008d8f38 in ?? ()
#46 0x00000000008df6fc in ?? ()
#47 0x00007f69a6f65182 in start_thread (arg=0x7f691a3bd700) at
pthread_create.c:312
#48 0x00007f69a656f47d in clone () at
../sysdeps/unix/sysv/linux/x86_64/clone.S:111
We run the integrity check of virtuoso with "backup '/dev/null'"
and that passed successfully, so this indicated for us that the
database itself is intact.
The version of virtuoso we are using is:
Virtuoso Open Source Edition (Column Store) (multi threaded)
Version 7.2.0.3212-pthreads as of Apr 8 2015
Compiled for Linux (x86_64-pc-linux-gnu)
Copyright (C) 1998-2015 OpenLink Software
Do you have any suggestions why this happened and how can it be
fixed?
Best regards,
Piret Lattikas
------------------------------------------------------------------------------
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
------------------------------------------------------------------------------
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
<mailto:Virtuoso-users@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
------------------------------------------------------------------------------
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
