Increase in checkpoint time
A user on IDS 7.31 saw checkpoints jump from ~10s to over 100s after loading a million rows, and wondered whether the table's growth to 60 extents was to blame and if a rebuild was needed. Suggestions included rebuilding with a bigger next-extent size, running UPDATE STATISTICS (which indirectly cuts physical I/O and dirty pages), raising LRUS/CLEANERS to 127, and checking dbspace/chunk layout and disk contention. The 'very old page' bug and CCFLAGS were raised but dismissed as affecting archives, not checkpoints. No confirmed fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi We are using IDS 7.31. DB was running smoothly till last week. But last week we have inserted 10Lakh records after that extent size of that table increased to 60 from 1, because of this (i think) checkpoint is taking more than 100 seconds, earlier it was around 10secs. what could be the problem? Should i Rebuild the table? Pls suggest Thanks Suresh
S SURESH said: > Hi > > We are using IDS 7.31. DB was running smoothly till last week. But last > week > we have inserted 10Lakh records after that extent size of that table What is a Lakh? > increased > to 60 from 1, because of this (i think) checkpoint is taking more than 100 > seconds, earlier it was around 10secs. > > what could be the problem? > Should i Rebuild the table? You *could* increase the next extent size and rebuild the table ... are all checkpoints taking this long, or was it just while you were loading the data? Did you UPDATE STATISTICS? -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
1 Lakh equals one hundred thousand ... -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Obnoxio The Clown Sent: Tuesday, February 27, 2007 01:37 PM To: ids@iiug.org Subject: Re: Increase in checkpoint time [8516] S SURESH said: > Hi > > We are using IDS 7.31. DB was running smoothly till last week. But > last week we have inserted 10Lakh records after that extent size of > that table What is a Lakh? > increased > to 60 from 1, because of this (i think) checkpoint is taking more than > 100 seconds, earlier it was around 10secs. > > what could be the problem? > Should i Rebuild the table? You *could* increase the next extent size and rebuild the table ... are all checkpoints taking this long, or was it just while you were loading the data? Did you UPDATE STATISTICS? -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
You might just be running into the 'very old page' bug (search previous posts
for details). If you contact tech support they can determine if this is
happening and they have a utility they can run, if you can let them log into
your system, to update the page timestamps on all old pages to prevent the slow
archives. If not you just have to live with slow archives for a while until
ontape and restamped all of you very old pages with new timestamps. Upgrading
to 9.4 or later will alleviate the problem in several ways.
Art S. Kagel
----- Original Message -----
From: S Suresh <ids@iiug.org>
At: 2/27 2:45:26
Hi
We are using IDS 7.31. DB was running smoothly till last week. But last week
we have inserted 10Lakh records after that extent size of that table increased
to 60 from 1, because of this (i think) checkpoint is taking more than 100
seconds, earlier it was around 10secs.
what could be the problem?
Should i Rebuild the table?
Pls suggest
Thanks
Suresh
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
On 27/02/07, ART KAGEL, BLOOMBERG/ 731 LEXIN <kagel@bloomberg.net> wrote:
> You might just be running into the 'very old page' bug (search previous posts
> for details). If you contact tech support they can determine if this is
> happening and they have a utility they can run, if you can let them log into
> your system, to update the page timestamps on all old pages to prevent the
> slow
> archives. If not you just have to live with slow archives for a while until
> ontape and restamped all of you very old pages with new timestamps. Upgrading
> to 9.4 or later will alleviate the problem in several ways.
>
> Art S. Kagel
>
> ----- Original Message -----
> From: S Suresh <ids@iiug.org>
> At: 2/27 2:45:26
>
> Hi
>
> We are using IDS 7.31. DB was running smoothly till last week. But last week
> we have inserted 10Lakh records after that extent size of that table
increased
> to 60 from 1, because of this (i think) checkpoint is taking more than 100
> seconds, earlier it was around 10secs.
>
> what could be the problem?
> Should i Rebuild the table?
>
> Pls suggest
> Thanks
> Suresh
>
No, Very Old Page Bug (which can be fixed by starting the engine with
a CCLAGS setting) only affects archives not checkpoints.
What are the settings in your onconfig file? BUFFERS, LRU, CLEANERS,
LRU_MAX, LRU_MIN, onstat -g seg, onstat -m and other good things.
Keith
Answered in a rush. I thought Suresh was having long archive problems. OOps.
Art S. Kagel
----- Original Message -----
From: Keith Simmons <ids@iiug.org>
At: 2/27 9:49:24
On 27/02/07, ART KAGEL, BLOOMBERG/ 731 LEXIN <kagel@bloomberg.net> wrote:
> You might just be running into the 'very old page' bug (search previous
posts
> for details). If you contact tech support they can determine if this is
> happening and they have a utility they can run, if you can let them log into
> your system, to update the page timestamps on all old pages to prevent the
> slow
> archives. If not you just have to live with slow archives for a while until
> ontape and restamped all of you very old pages with new timestamps.
Upgrading
> to 9.4 or later will alleviate the problem in several ways.
>
> Art S. Kagel
>
> ----- Original Message -----
> From: S Suresh <ids@iiug.org>
> At: 2/27 2:45:26
>
> Hi
>
> We are using IDS 7.31. DB was running smoothly till last week. But last week
> we have inserted 10Lakh records after that extent size of that table
increased
> to 60 from 1, because of this (i think) checkpoint is taking more than 100
> seconds, earlier it was around 10secs.
>
> what could be the problem?
> Should i Rebuild the table?
>
> Pls suggest
> Thanks
> Suresh
>
No, Very Old Page Bug (which can be fixed by starting the engine with
a CCLAGS setting) only affects archives not checkpoints.
What are the settings in your onconfig file? BUFFERS, LRU, CLEANERS,
LRU_MAX, LRU_MIN, onstat -g seg, onstat -m and other good things.
Keith
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Please run update statistics high on the database.I think that will resolve both backup and response problem. Thanks, Amitava "S SURESH" <suresh.sambana@r il.com> To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject Increase in checkpoint time [8515] 27/02/2007 13:14 Please respond to ids@iiug.org Hi We are using IDS 7.31. DB was running smoothly till last week. But last week we have inserted 10Lakh records after that extent size of that table increased to 60 from 1, because of this (i think) checkpoint is taking more than 100 seconds, earlier it was around 10secs. what could be the problem? Should i Rebuild the table? Pls suggest Thanks Suresh ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
How can 'update statistics high' will help reduce checkpoint time ??? - Nilesh - "Amitava Chakraborty" <amitacha@in.ibm.com> Sent by: ids-bounces@iiug.org 02/27/2007 10:49 PM Please respond to ids@iiug.org To ids@iiug.org cc Subject Re: Increase in checkpoint time [8533] Please run update statistics high on the database.I think that will resolve both backup and response problem. Thanks, Amitava "S SURESH" <suresh.sambana@r il.com> To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject Increase in checkpoint time [8515] 27/02/2007 13:14 Please respond to ids@iiug.org Hi We are using IDS 7.31. DB was running smoothly till last week. But last week we have inserted 10Lakh records after that extent size of that table increased to 60 from 1, because of this (i think) checkpoint is taking more than 100 seconds, earlier it was around 10secs. what could be the problem? Should i Rebuild the table? Pls suggest Thanks Suresh ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
What is CCLAGS? where/How can we set this ?
Can you give me the procedure?
these are my onconfig settings
BUFFERS 400000
CLEANERS 40
LRUS 24
LRU_MAX_DIRTY 2
LRU_MIN_DIRTY 1
On 28/02/07, S SURESH <suresh.sambana@ril.com> wrote:
> What is CCLAGS? where/How can we set this ?
> Can you give me the procedure?
>
> these are my onconfig settings
>
> BUFFERS 400000
> CLEANERS 40
> LRUS 24
> LRU_MAX_DIRTY 2
> LRU_MIN_DIRTY 1>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
You do NOT need CCFLAGS, it is only required if you have archive
problems, your issue is with long checkpoints.
How many dbspaces and chunks do you have? Raw or cooked space?
Any disk contention (sar -d or sar and check wio).
Could try LRUS and CLEANERS at 127, won't hurt and may help.
Keith
Suresh,
the CCFLAGS (not CCLAGS) is actually related to archive problems descri=
bed
also in IBM Technote T1000184.
See URL
http://www.ibm.com/support/docview.wss?&uid=3Disg3T1000184
for further information.
But didn't also also report a backup related problem 'Problem while tak=
ing
backup'?
Unfortunately I don't know your current IDS version in use.
But probably TRACECKPT is already implemented in your version to get mo=
re
details.
See IBM Technote 1250366 at URL
http://www.ibm.com/support/docview.wss?&uid=3Dswg21250366
However, the IBM Technote at URL
http://www.ibm.com/support/docview.wss?&uid=3Dswg2125084
may give you some brief description about the checkpoint background and=
bottlenecks.
HTH,
Robert
Robert Seifert
_______________________________________________________________________=
_____
IBM Deutschland GmbH, Software, Information Management,
Informix Products, Advanced Support
Elite VAR support engineer
Development Support for IBM Informix Products on-site @ SAP
c/o SAP AG, -PS03-,Neurottstrasse 16, D-69190 Walldorf, Germany
_______________________________________________________________________=
_____
_______________________________________________________________________=
_____
Dieses Mail stammt von Robert Seifert, Mitarbeiter der IBM Deutschland
GmbH,
handelnd im Auftrag (i.A.) von:
This is a mail from Robert Seifert, employee of IBM Deutschland GmbH,
on behalf of:
IBM Deutschland GmbH
Vorsitzender des Aufsichtsrats: Hans Ulrich Maerki
Gesch=E4ftsf=FChrung: Martin Jetter (Vorsitzender), Rudol=
f Bauer,
Christian Diedrich, Christoph Grandpier=
re,
Matthias Hartmann, Andreas Kerstan
Sitz der Gesellschaft: Stuttgart
Registergericht: Amtsgericht Stuttgart, HRB 14562
WEEE-Reg.-Nr.: DE 99369940
ids-bounces@iiug.org wrote on 28.02.2007 11:35:44:
> What is CCLAGS? where/How can we set this ?
> Can you give me the procedure?
>
> these are my onconfig settings
>
> BUFFERS 400000
> CLEANERS 40
> LRUS 24
> LRU_MAX_DIRTY 2
> LRU_MIN_DIRTY 1>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.=
>=
By making the queries more efficient, you can reduce the number of sequential scans and thus reduce the total amount of physical IO that t= he system is having to do. Which in turn will make if more easy for the L= RU page cleaners to perform their work - which will make it easier to main= tain a lower percentage of dirty pages in the buffer. Which will make chekcpoints shorter..... ;-) = Nilesh = Ozarkar/Lenexa/IB = M@IBMUS = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Re: Increase in checkpoint time = 02/27/2007 10:57 [8534] = PM = = = Please respond to = ids@iiug.org = = = How can 'update statistics high' will help reduce checkpoint time ??? - Nilesh - "Amitava Chakraborty" <amitacha@in.ibm.com> Sent by: ids-bounces@iiug.org 02/27/2007 10:49 PM Please respond to ids@iiug.org To ids@iiug.org cc Subject Re: Increase in checkpoint time [8533] Please run update statistics high on the database.I think that will resolve both backup and response problem. Thanks, Amitava "S SURESH" <suresh.sambana@r il.com> To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject Increase in checkpoint time [8515] 27/02/2007 13:14 Please respond to ids@iiug.org Hi We are using IDS 7.31. DB was running smoothly till last week. But last= week we have inserted 10Lakh records after that extent size of that table increased to 60 from 1, because of this (i think) checkpoint is taking more than = 100 seconds, earlier it was around 10secs. what could be the problem? Should i Rebuild the table? Pls suggest Thanks Suresh ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g