SMI to Detect a Stalled Checkpoint
(Re-Send - first post seems to not have taken)
Hi Family.
I have been following some threads regarding checkpoints taking as long
as *20 SECONDS* (gasp). OK, the guy with the 80 second checkpoint had
a real beef. In my environment, we have enormous physical logs so I
don't get too surprised if a checkpoint takes 120 seconds to complete
flushing close to 150,000 buffers. (Sigh.. sometimes low LRU_MAX_DIRTY
simply can't keep up with the demands..)
My manager called me today and asked me to have a look at the
online.log from the middle of the night:
02:03:55 Checkpoint Completed: duration was 2 seconds.
02:09:00 Checkpoint Completed: duration was 1 seconds.
03:01:16 Checkpoint Completed: duration was 2831 seconds. **YIKES**
03:06:21 Checkpoint Completed: duration was 2 seconds.
03:11:27 Checkpoint Completed: duration was 2 seconds.
For the night jobs, the norm goes up to 10 seconds also. No sweat. But
I really did a double-take on that 2831-second (47-minute) checkpoint.
Obviously, it didn't spend that time flushing. Most likely, it was in a
state of CKPT REQ for about 45 minutes before proceeding. It is
reasonable (IMO) to conjecture that someone was in a critical section
blocking the checkpoint from actually getting started. Had someone
been hanging around to monitor for this condition, said someone would
have run onstat -u, looked for someone in a critical section (X in
position 5) and .. well, I don't know what to do with that user. But we
would have known about it.
I'd like to write a script or C program to monitor for this kind of
nonsense. I'd could use a 5-minute (or so) wake-upper that runs any
onstat command and looks for CKPT REQ to be repeated too mantimes in a
row but this seems clumsy. In order to finesse this monitor, I need to
know:
- Is there an SMI table that tells me the current state of the
system - like CKPT or CKPT REQ ?
- If a checkpoint is currently pending or in progress, is there an
SMI table that will contain a time stamp telling me when the
checkpoint started (or tried to start?)
- Is there another tool I can use to find out how long the current
CKPT [REQ] has been the state of the system?
These are toughies but goodies to know about. Yeah, I figure whatever
it is, it is probably undocumented..
I am investigating the use of sysmaster:flags_txt as a key into other
stuff. Observe:
select tabname, flags, txt[1,20] from flags_text order by tabname, txt;
Among the other data, I got:
tabname flags txt
systwaits 7 checkpoint
systwaits 17 ckpt mutex
I just don't know if there is something about this discovery I can use
in my quest.
Any ideas out there?
Thanks.
--
+----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+
|------------------- Bulletin Board Announcement ----------------------|
| Congregants will please note that the bowl at the back of the church |
| bearing the sign "For the Sick" is for monetary contributions only. |
+----------------------------------------------------------------------+
Sent via Deja.com http://www.deja.com/
Before you buy.
In article <7uq7d7$7fp$1@nnrp1.deja.com>, Jacob Salomon
<JSalomon@bn.com> writes
>(Re-Send - first post seems to not have taken)
>Hi Family.
>
>I have been following some threads regarding checkpoints taking as long
>as *20 SECONDS* (gasp). OK, the guy with the 80 second checkpoint had
>a real beef. In my environment, we have enormous physical logs so I
>don't get too surprised if a checkpoint takes 120 seconds to complete
>flushing close to 150,000 buffers. (Sigh.. sometimes low LRU_MAX_DIRTY
>simply can't keep up with the demands..)
>
>My manager called me today and asked me to have a look at the
>online.log from the middle of the night:
>
>02:03:55 Checkpoint Completed: duration was 2 seconds.
>02:09:00 Checkpoint Completed: duration was 1 seconds.
>03:01:16 Checkpoint Completed: duration was 2831 seconds. **YIKES**
>03:06:21 Checkpoint Completed: duration was 2 seconds.
>03:11:27 Checkpoint Completed: duration was 2 seconds.>
>For the night jobs, the norm goes up to 10 seconds also. No sweat. But
>I really did a double-take on that 2831-second (47-minute) checkpoint.
>
>Obviously, it didn't spend that time flushing. Most likely, it was in a
>state of CKPT REQ for about 45 minutes before proceeding. It is
>reasonable (IMO) to conjecture that someone was in a critical section
>blocking the checkpoint from actually getting started. Had someone
Also on 5.x I've seen tbtape wait for a hung tape drive and hold the
mutex!
>been hanging around to monitor for this condition, said someone would
>have run onstat -u, looked for someone in a critical section (X in
>position 5) and .. well, I don't know what to do with that user. But we
>would have known about it.
>
>I'd like to write a script or C program to monitor for this kind of
>nonsense. I'd could use a 5-minute (or so) wake-upper that runs any
>onstat command and looks for CKPT REQ to be repeated too mantimes in a
>row but this seems clumsy. In order to finesse this monitor, I need to
>know:
>
> - Is there an SMI table that tells me the current state of the
> system - like CKPT or CKPT REQ ?
> - If a checkpoint is currently pending or in progress, is there an
> SMI table that will contain a time stamp telling me when the
> checkpoint started (or tried to start?)
> - Is there another tool I can use to find out how long the current
> CKPT [REQ] has been the state of the system?
>
>These are toughies but goodies to know about. Yeah, I figure whatever
>it is, it is probably undocumented..
>
>I am investigating the use of sysmaster:flags_txt as a key into other
>stuff. Observe:
>select tabname, flags, txt[1,20] from flags_text order by tabname, txt;>
>Among the other data, I got:
> tabname flags txt
> systwaits 7 checkpoint
> systwaits 17 ckpt mutex
>
>I just don't know if there is something about this discovery I can use
>in my quest.
>
Look at $INFORMIXDIR/etc/sysmaster.sql
Look at systwaits table. This sound like
SYS Thread WAITS
^^^ ^ ^^^^^
SYS-T------WAITS
You might need to add
WSTATS 1
to onconfig and reboot online to enable this. Also it there a
sysseswaits, might help you link to waiting sessions.
Does anyone know what type of queries stay in critical sections?
Art, any examples? (I meant 'that Kagel boy' not the Mona Lisa and
Venus de Milo!)
We have seen long checkpoint of several minutes but onstat -s
to look at latches never helped.
>Any ideas out there?
>
>Thanks.
>--
>+----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+
>|------------------- Bulletin Board Announcement ----------------------|
>| Congregants will please note that the bowl at the back of the church |
>| bearing the sign "For the Sick" is for monetary contributions only. |
>+----------------------------------------------------------------------+
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
--
David Williams