SMI to Detect a Stalled Checkpoint
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. |
+----------------------------------------------------------------------
+
--
+---- Jacob Salomon -- Obligatory sesquipedalian
obfuscation: ---------+
| An object of igneous, sedimentary or
metamorphic mineral in combined |
Sent via Deja.com http://www.deja.com/
Before you buy.
In article <7uo7c1$r0a$1@nnrp1.deja.com>, Jacob Salomon
<JSalomon@bn.com> writes
>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;>
Check syssesswaits (?).
Look in $INFORMIXDIR/etc/sysmaster.sql and look for tables/views
containing the word waits.
>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. |
>+----------------------------------------------------------------------
>+
>
>--
>+---- Jacob Salomon -- Obligatory sesquipedalian
>obfuscation: ---------+
>| An object of igneous, sedimentary or
>metamorphic mineral in combined |
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
--
David Williams