Below is a iiprtqry.log fragment from eod processing:

Log file:
Query Response Time: Thu Mar 3 21:27:29 2005 time=54 seconds
Query text:
commit
Query Send Time: Thu Mar 3 21:26:35 2005
Query Response Time: Thu Mar 3 21:27:29 2005
================================
Log file:
Query Response Time: Thu Mar 3 21:28:58 2005 time=81 seconds
Query text:
commit
Query Send Time: Thu Mar 3 21:27:37 2005
Query Response Time: Thu Mar 3 21:28:58 2005


What can be the cause of the commit statement taking so much time?

<Piotr.Wisniewski (AT) breat (DOT) com.pl> wrote

Beats me. Is this being logged by a remote client, and is your network
busy/saturated?

This is WAY beyond what you'd see if it was something like a lack of log
writer threads or long locklists.

If your network is not busy I'd have to say this looks like a bug. Version?

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"

"Roy Hann" <specially (AT) processed (DOT) almost.meat> wrote


Do you know if you had a log-full stall going on at about that time?

Roy

Hi Piotr,

When you commit the system will not respond until everything is written
into the log file (for fast commit servers) or until everything is back on
the data disks (non fast commit servers)

Given that you are probably running fast commit servers, I would
suggest that what you are seeing is a problem with log buffers. ie you
need *LOTS* more. And possibly the number of log writer threads
needs to be upped substantailly.

Do a logstat -header -statistics and post the output.

Martin Bowes
--
Random Farscape Quote #6:
Ka'Dargo - You have no idea where we are.
Pilot - Of course I do... We are someplace else, I'll get back to you
when
more details become apparent.

On 4 Mar 2005 at 10:52, Piotr.Wisniewski (AT) breat (DOT) com.pl wrote:

Need to know about the installation you are connected to...

1. Ingres Version / platform

2. Database Type (eg INGRES / STAR / GATEWAY)

I could perhaps understand this if you have some log problems, or this
was an Ingres Star database and your previous transactions were
invoking 2 phase commit..

Have you checked for log problems in your errlog.log ?? As Marty says,
need logstat figures to understand more what is going on...

Gary

There are 4 dbms servers running (one for every db)

Fast_commit=ON
Log_writer=16

Ingress version:
valid 9915 1 II 2.6/0305 (su4.us5/00)

the session runs on the same machine as the server.

Logststat:
Database add 34128 Database removes 33966
Transaction begins 1102643 Transaction ends 1102419
Log read i/o's 554407 Log write i/o's 560558
Log writes 9391333 Log forces 69833
Log waits 209995 Log split waits 0
Log free waits 0 Log stall waits 19
Log BCP waits 19 Logfull stall waits 0
Log group commit 103039 Log group count 117938
Check commit timer 91182 Timer write 91000
Timer write, time 105 Timer write, idle 90895
Inconsistent db 0 Kbytes written 853651
ii_log_file read 554407 ii_dual_log read 0
write complete 560561 dual write complete 0
All logwriters busy 31101 Max write queue len 3584
Max write queue cnt 1

-----Original Message-----
From: Roy Hann [mailto:specially (AT) processed (DOT) almost.meat]
Sent: Friday, March 04, 2005 2:42 PM
To: info-ingres (AT) cariboulake (DOT) com
Subject: Re: [Info-ingres] commit takes very long time

<Piotr.Wisniewski (AT) breat (DOT) com.pl> wrote

Beats me. Is this being logged by a remote client, and is your network
busy/saturated?

This is WAY beyond what you'd see if it was something like a lack of log
writer threads or long locklists.

If your network is not busy I'd have to say this looks like a bug. Version?

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"


_______________________________________________
Info-ingres mailing list
Info-ingres (AT) cariboulake (DOT) com
http://mailman.cariboulake.com/mailm...py/info-ingres

<Piotr.Wisniewski (AT) breat (DOT) com.pl> wrote

16 log writer threads should not have taken more than a couple of seconds to
write even 3,584 log buffers. And it doesn't look like you've had a log
full stall which I was secretly confident you had.

Thinking about it a bit harder it occurs to me that a long-running rollback
by another session could delay a commit.

Roy

Hi Piotr,

That looks pretty good. You clearly have a lot of buffers
configured. At least 3584 and have only managed to use that many
once.

16 log writer threads may be a bit small for that number of
buffers. It might be worthwhile trying to increase that value substantially
given the number of times we see 'All logwriters busy'. You might like to
check how many threads your allowed to start first.

Also is the log file coooked or raw? Have you checked for any
errrors on the log disk itself?

Can you tie the long commit to a specific query or is it general?

Marty

On 4 Mar 2005 at 16:20, Piotr.Wisniewski (AT) breat (DOT) com.pl wrote:

Random Duckman Quote #83:
Cornfed: It's my fault. I was attempting an actual conversation.

At 4:20 PM +0100 3/4/2005, Piotr.Wisniewski (AT) breat (DOT) com.pl wrote:
[re commit stalling]


That's an awful lot of log buffers. I question whether that many buffers
are doing you any good. (It's not impossible, if you have really large
updating transactions, just unlikely.)

Starting with 2.6 (I think), multiple rollbacks can proceed concurrently,
and in any case I don't think that rollback would stall a commit.

I suspect that you ran into either some sort of hardware hiccup, or
a consistency-point wait (BCP wait) that took a really long time for
some unknown reason. But I don't really know.

Karl

Hi Dudes,

I'll take that back now. I just tried setting the number of
log_writer_threads up substantially (to 150) on a Linux IngresII2.6 and
promptlly put the server into a spin. 100% cpu! So if you do raise the
number of threads - don't go crazy.
Marty
--
Random Duckman Quote #102:
Cornfed: Uh Oh.
Duckman: 'Uh Oh' as in 'Nice Work?'
Cornfed: No, as in 'You're screwed.'