On Mon, Oct 26, 2009 at 18:42, Hugh Williams <[email protected]>wrote:
> Indicating their are 5 deadlocks in the database. Can you try running the > following command form the isql command line too to try and forcibly kill > and release all transactions and locks: > > txn_killall(); > > As detailed at: > > http://docs.openlinksw.com/virtuoso/fn_txn_killall.html > > Hope fully the server will then start responding again ... > > Thanks Hugh - I'm aware of txn_killall() and have tried that at various times and it has helped temporarily in the past, though the server soon returned to that same state. What do all the IER, ISR, etc entries mean in status() ? Also, is there a way to identify what is causing the continuous activity? It's been happening for days (the checkpointed database file has grown by at least 20GB since the indexes were made and no more data has been sponged or explicitly imported). Following your email I killed all the transactions again, ran status() and the IER, ISR entries had gone but shortly after isql lost its connection with the server. The server process was still alive, but not responding to new connections or the web interface. The file "checkpoint_in_progress" existed (and the file time was 30 minutes previous) but there was no disk IO and no CPU activity and the load avg was 0. Just as I was about to click send, the server output "Checkpoint made, log reused" and woke up. Running status() now shows over 1000 IER type entries again but no deadlocks this time: Interactive SQL Basic Advanced Query result: REPORT VARCHAR OpenLink Virtuoso Server Version 06.00.3123-pthreads for Linux as of Oct 24 2009 Started on: 2009/10/26 17:53 GMT+00 Database Status: File size 102588481536, 12523008 pages, 5380833 free. 400000 buffers, 399948 used, 48987 dirty 3 wired down, repl age 91428 0 w. io 2 w/crsr. Disk Usage: 862821 reads avg 11 msec, 98% r 0% w last 29 s, 1232834 writes, 77 read ahead, batch = 131. Autocompact 73419 in 60199 out, 18% saved. Gate: 89 2nd in reads, 0 gate write waits, 0 in while read 0 busy scrap. Log = utopia-database.trx, 1368566 bytes 1313084 pages have been changed since last backup (in checkpoint state) Current backup timestamp: 0x8223-0x20-0xEE Last backup date: Thu Oct 22 11:59:12 2009 Clients: 5 connects, max 3 concurrent RPC: 42 calls, -3 pending, 1 max until now, 0 queued, 0 burst reads (0%), 0 second brk=434733056 Checkpoint Remap 300000 pages, 0 mapped back. 2381 s atomic time. DB master 12523008 total 5380804 free 300000 remap 21368 mapped back temp 256 total 250 free Lock Status: 0 deadlocks of which 0 2r1w, 2 waits, Currently 2 threads running 0 threads waiting 0 threads in vdb. Pending: 10095561: IER 0 92: IER 0 88: IER 0 84: IER 0 80: IER 0 76: IER 0 72: IER 0 68: IER 0 64: IER 0 60: IER 0 56: IER 0 52: IER 0 48: IER 0 44: IER 0 [ ... ] Client 1111:11:-12: Account: dba, 368 bytes in, 67867 bytes out, 1 stmts. PID: 3056, OS: unix, Application: unknown, IP#: 127.0.0.1 Transaction status: PENDING, 0 threads. Locks: Client 1111:5:-6: Account: kend, 955 bytes in, 1374 bytes out, 2 stmts. PID: 20332, OS: unix, Application: unknown, IP#: 192.168.122.1 Transaction status: PENDING, 0 threads. Locks: Client 1111:7:-8: Account: dba, 237 bytes in, 2984 bytes out, 1 stmts. PID: 20348, OS: unix, Application: unknown, IP#: 192.168.122.1 Transaction status: PENDING, 0 threads. Locks: Running Statements: Time (msec) Text Replication Status: Server anonymous. anonymous anonymous 0 OFF. Hash indexes No. of rows in result: *1536 * Incidentally the host machine is not swapping or otherwise stressed: top - 21:37:03 up 1 day, 5:57, 4 users, load average: 1.00, 0.79, 0.51 Tasks: 103 total, 2 running, 101 sleeping, 0 stopped, 0 zombie Cpu(s): 0.9%us, 0.1%sy, 0.0%ni, 85.6%id, 13.4%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 5609208k total, 5562180k used, 47028k free, 43944k buffers Swap: 6747384k total, 16244k used, 6731140k free, 1501944k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2794 marshj 20 0 3849m 3.6g 9292 D 3 67.4 5:14.50 virtuoso-t Kind regards, James.
