Instance constantly checkpointing...
Tristan reported an IDS 11.50.FC1 instance checkpointing every few seconds, with onstat -g ckp showing the trigger as "Plog" and recommending implausibly huge physical (39GB) and logical (21GB) logs, even though physical log use never exceeded 0.1%. Suggestions included checking for online index builds (ruled out), reviewing checkpoint monitoring docs, posting the full onstat -g ckp output for analysis, turning off AUTO_CKPTS (which had fixed a similar case for another poster), and opening an IBM support case. No confirmed resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi Everyone.
I've got a server instance that is effectively constantly check pointing, (as
in once every couple of seconds)
Onstat -g ckp tells me "AUTO_CKPTS=On RTO_SERVER_RESTART=Off", and it also
tells me that the trigger for all these checkpoints is Plog.
Onstat -l shows that the phys size is 512000, but the % used never gets above
0.1.
I don't understand why it's checkpointing so often?
The onstat -g ckp tells me I need a larger physical log, specifically
39,160,208 KB. But really? 39Gig! :)
It's also telling me I need a larger logical log, 21,715,120 KB. But again,
21G seems on the large side. The whole database is only 80, and frankly the
write load isn't that high!
Any help much appreciated!
Thanks,
Tristan
[Pronto Software]<http://www.pronto.com.au>;
www.pronto.com.au<http://www.pronto.com.au>;
Tristan Ball - Hosted Services Manager VIC
Pronto Hosted Services
20 Lakeside Drive, Burwood East, VIC 3151, Australia
Phone: +61 3 9887 7770 | Email:
tristanb@pronto.com.au<mailto:tristanb@pronto.com.au>
Mobile: +61 408 397 473
[Follow us on Twitter]<http://www.twitter.com/ProntoSoftware>;
---Legal Notice---
The email message and any attachments are confidential and subject to
copyright. If you are not the intended recipient, any use, interference with,
disclosure or copying of this material is unauthorised and prohibited. No part
may be reproduced, adapted or transmitted without the written permission of
the copyright owner. If you have received this email in error, please
immediately advise the sender by return email and delete the message from your
system. Before opening or using attachments, check for viruses and defects.
Our liability is limited to re-supplying any affected attachments.
Hello Tristan.
You didn´t mention your engine version and architecture, but supposing
you´re running 11.50,
here´s a good link of Information Center to help you:
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?tab=search&searc
hWord=monitoring+checkpoints
Obs: is it a new machine, or an old one? If it suddently started to
happen may indicate some application change....
And your physical log size must be really slow, depending on the size of
you bufferpools.
I have small instances here, on linux_64 and IDS 11.70.FC2 of 40GB with
1GB on physical log, that´s running pretty smooth for us.
Best regards.
Em 09/06/2011 09:42, Tristan Ball escreveu:
> Hi Everyone.
>
> I've got a server instance that is effectively constantly check pointing, (as
> in once every couple of seconds)
>
> Onstat -g ckp tells me "AUTO_CKPTS=On RTO_SERVER_RESTART=Off", and it also
> tells me that the trigger for all these checkpoints is Plog.
> Onstat -l shows that the phys size is 512000, but the % used never gets above
> 0.1.
>
> I don't understand why it's checkpointing so often?
>
> The onstat -g ckp tells me I need a larger physical log, specifically
> 39,160,208 KB. But really? 39Gig! :)
> It's also telling me I need a larger logical log, 21,715,120 KB. But again,
> 21G seems on the large side. The whole database is only 80, and frankly the
> write load isn't that high!
>
> Any help much appreciated!
>
> Thanks,
>
> Tristan
>
> [Pronto Software]<http://www.pronto.com.au>;
> www.pronto.com.au<http://www.pronto.com.au>;
> Tristan Ball - Hosted Services Manager VIC
> Pronto Hosted Services
> 20 Lakeside Drive, Burwood East, VIC 3151, Australia
> Phone: +61 3 9887 7770 | Email:
> tristanb@pronto.com.au<mailto:tristanb@pronto.com.au>
> Mobile: +61 408 397 473
>
> [Follow us on Twitter]<http://www.twitter.com/ProntoSoftware>;
>
> ---Legal Notice---
> The email message and any attachments are confidential and subject to
> copyright. If you are not the intended recipient, any use, interference with,
> disclosure or copying of this material is unauthorised and prohibited. No
part
> may be reproduced, adapted or transmitted without the written permission of
> the copyright owner. If you have received this email in error, please
> immediately advise the sender by return email and delete the message from
your
> system. Before opening or using attachments, check for viruses and defects.
> Our liability is limited to re-supplying any affected attachments.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Alexandre Marini
Tecnologia da Informação - DBA
msn: alexandre_marini@hotmail.com
SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg>
IBM Certified System Administrator - Informix Dynamic Server V10 / V11
Are you creating online indexes? Either in normal or temp tables (in these
ones doesn't make any sense, but I've seen it)
Please state your version
On Thu, Jun 9, 2011 at 2:42 PM, Tristan Ball <tristanb@pronto.com.au> wrote:
> Hi Everyone.
>
> I've got a server instance that is effectively constantly check pointing,
> (as
> in once every couple of seconds)
>
> Onstat -g ckp tells me "AUTO_CKPTS=On RTO_SERVER_RESTART=Off", and it also
> tells me that the trigger for all these checkpoints is Plog.
> Onstat -l shows that the phys size is 512000, but the % used never gets
> above
> 0.1.
>
> I don't understand why it's checkpointing so often?
>
> The onstat -g ckp tells me I need a larger physical log, specifically
> 39,160,208 KB. But really? 39Gig! :)
> It's also telling me I need a larger logical log, 21,715,120 KB. But again,
> 21G seems on the large side. The whole database is only 80, and frankly the
> write load isn't that high!
>
> Any help much appreciated!
>
> Thanks,
>
> Tristan
>
> [Pronto Software]<http://www.pronto.com.au>;
> www.pronto.com.au<http://www.pronto.com.au>;
> Tristan Ball - Hosted Services Manager VIC
> Pronto Hosted Services
> 20 Lakeside Drive, Burwood East, VIC 3151, Australia
> Phone: +61 3 9887 7770 | Email:
> tristanb@pronto.com.au<mailto:tristanb@pronto.com.au>
> Mobile: +61 408 397 473
>
> [Follow us on Twitter]<http://www.twitter.com/ProntoSoftware>;
>
> ---Legal Notice---
> The email message and any attachments are confidential and subject to
> copyright. If you are not the intended recipient, any use, interference
> with,
> disclosure or copying of this material is unauthorised and prohibited. No
> part
> may be reproduced, adapted or transmitted without the written permission of
> the copyright owner. If you have received this email in error, please
> immediately advise the sender by return email and delete the message from
> your
> system. Before opening or using attachments, check for viruses and defects.
> Our liability is limited to re-supplying any affected attachments.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e6d273697d872e04a54a0a05
Not sure what version that you are on...I had a similar situation in early
v11.50 ... had to turn off the auto check pointing .. I had an instance
that would do exactly what you are mentioning ... and nothing else would
perform at the proper speed .. This was about a year ago ... Turning off
the auto checkpointing was the solution given by the IBM support line ...
I'm running higher 11.50 and 11.70, but haven't turned back on the auto
checkpointing yet ....
Peter
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "Fernando Nunes" <domusonline@gmail.com>
To: ids@iiug.org
Date: 06/09/2011 12:36 PM
Subject: Re: Instance constantly checkpointing... [23965]
Sent by: ids-bounces@iiug.org
Are you creating online indexes? Either in normal or temp tables (in these
ones doesn't make any sense, but I've seen it)
Please state your version
On Thu, Jun 9, 2011 at 2:42 PM, Tristan Ball <tristanb@pronto.com.au>
wrote:
> Hi Everyone.
>
> I've got a server instance that is effectively constantly check
pointing,
> (as
> in once every couple of seconds)
>
> Onstat -g ckp tells me "AUTO_CKPTS=On RTO_SERVER_RESTART=Off", and it
also
> tells me that the trigger for all these checkpoints is Plog.
> Onstat -l shows that the phys size is 512000, but the % used never gets
> above
> 0.1.
>
> I don't understand why it's checkpointing so often?
>
> The onstat -g ckp tells me I need a larger physical log, specifically
> 39,160,208 KB. But really? 39Gig! :)
> It's also telling me I need a larger logical log, 21,715,120 KB. But
again,
> 21G seems on the large side. The whole database is only 80, and frankly
the
> write load isn't that high!
>
> Any help much appreciated!
>
> Thanks,
>
> Tristan
>
> [Pronto Software]<http://www.pronto.com.au>;
> www.pronto.com.au<http://www.pronto.com.au>;
> Tristan Ball - Hosted Services Manager VIC
> Pronto Hosted Services
> 20 Lakeside Drive, Burwood East, VIC 3151, Australia
> Phone: +61 3 9887 7770 | Email:
> tristanb@pronto.com.au<mailto:tristanb@pronto.com.au>
> Mobile: +61 408 397 473
>
> [Follow us on Twitter]<http://www.twitter.com/ProntoSoftware>;
>
> ---Legal Notice---
> The email message and any attachments are confidential and subject to
> copyright. If you are not the intended recipient, any use, interference
> with,
> disclosure or copying of this material is unauthorised and prohibited.
No
> part
> may be reproduced, adapted or transmitted without the written permission
of
> the copyright owner. If you have received this email in error, please
> immediately advise the sender by return email and delete the message
from
> your
> system. Before opening or using attachments, check for viruses and
defects.
> Our liability is limited to re-supplying any affected attachments.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e6d273697d872e04a54a0a05
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
We're on 11.50.FC1, and no, I don't believe any indexes are being created. The
schema's are very static - the database is the backing store for a single
application that creates all tables/indexes as part of the install.
Regards,
Tristan
Tristan Ball - Hosted Services Manager VIC
Pronto Hosted Services
20 Lakeside Drive, Burwood East, VIC 3151, Australia
Phone: +61 3 9887 7770 | Email: tristanb@pronto.com.au
Mobile: +61 408 397 473
www.pronto.com.au
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando
Nunes
Sent: Friday, 10 June 2011 2:36 AM
To: ids@iiug.org
Subject: Re: Instance constantly checkpointing... [23965]
Are you creating online indexes? Either in normal or temp tables (in these
ones doesn't make any sense, but I've seen it) Please state your version
On Thu, Jun 9, 2011 at 2:42 PM, Tristan Ball <tristanb@pronto.com.au> wrote:
> Hi Everyone.
>
> I've got a server instance that is effectively constantly check
> pointing, (as in once every couple of seconds)
>
> Onstat -g ckp tells me "AUTO_CKPTS=On RTO_SERVER_RESTART=Off", and it
> also tells me that the trigger for all these checkpoints is Plog.
> Onstat -l shows that the phys size is 512000, but the % used never
> gets above 0.1.
>
> I don't understand why it's checkpointing so often?
>
> The onstat -g ckp tells me I need a larger physical log, specifically
> 39,160,208 KB. But really? 39Gig! :)
> It's also telling me I need a larger logical log, 21,715,120 KB. But
> again, 21G seems on the large side. The whole database is only 80, and
> frankly the write load isn't that high!
>
> Any help much appreciated!
>
> Thanks,
>
> Tristan
>
> [Pronto Software]<http://www.pronto.com.au>;
> www.pronto.com.au<http://www.pronto.com.au>;
> Tristan Ball - Hosted Services Manager VIC Pronto Hosted Services
> 20 Lakeside Drive, Burwood East, VIC 3151, Australia
> Phone: +61 3 9887 7770 | Email:
> tristanb@pronto.com.au<mailto:tristanb@pronto.com.au>
> Mobile: +61 408 397 473
>
> [Follow us on Twitter]<http://www.twitter.com/ProntoSoftware>;
>
> ---Legal Notice---
> The email message and any attachments are confidential and subject to
> copyright. If you are not the intended recipient, any use,
> interference with, disclosure or copying of this material is
> unauthorised and prohibited. No part may be reproduced, adapted or
> transmitted without the written permission of the copyright owner. If
> you have received this email in error, please immediately advise the
> sender by return email and delete the message from your system. Before
> opening or using attachments, check for viruses and defects.
> Our liability is limited to re-supplying any affected attachments.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e6d273697d872e04a54a0a05
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Original post:
We're on 11.50.FC1, and no, I don't believe any indexes are being created. The
schema's are very static - the database is the backing store for a single
application that creates all tables/indexes as part of the install.
Regards,
Tristan
Tristan Ball - Hosted Services Manager VIC
Pronto Hosted Services
20 Lakeside Drive, Burwood East, VIC 3151, Australia
Phone: +61 3 9887 7770 | Email: tristanb@pronto.com.au
Mobile: +61 408 397 473
www.pronto.com.au
Response:
The onstat -g ckp output shold have a trigger column, which should give a
pretty good indication what's triggering the checkpoint. Perhaps you could
post your onstat -g ckp and if it's one of the more cryptic ones I can help
out.
Jacques Renaut
IBM Informix Advanced Support
APD Team
Version details ALWAYS help, but my suggestions:
1. Turn AUTO_CKPT off for now, and
2. Open a support case with IBM, it's possible that this is a known
problem with a known fix
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Jun 9, 2011 at 9:42 AM, Tristan Ball <tristanb@pronto.com.au> wrote:
> Hi Everyone.
>
> I've got a server instance that is effectively constantly check pointing,
> (as
> in once every couple of seconds)
>
> Onstat -g ckp tells me "AUTO_CKPTS=On RTO_SERVER_RESTART=Off", and it also
> tells me that the trigger for all these checkpoints is Plog.
> Onstat -l shows that the phys size is 512000, but the % used never gets
> above
> 0.1.
>
> I don't understand why it's checkpointing so often?
>
> The onstat -g ckp tells me I need a larger physical log, specifically
> 39,160,208 KB. But really? 39Gig! :)
> It's also telling me I need a larger logical log, 21,715,120 KB. But again,
> 21G seems on the large side. The whole database is only 80, and frankly the
> write load isn't that high!
>
> Any help much appreciated!
>
> Thanks,
>
> Tristan
>
> [Pronto Software]<http://www.pronto.com.au>;
> www.pronto.com.au<http://www.pronto.com.au>;
> Tristan Ball - Hosted Services Manager VIC
> Pronto Hosted Services
> 20 Lakeside Drive, Burwood East, VIC 3151, Australia
> Phone: +61 3 9887 7770 | Email:
> tristanb@pronto.com.au<mailto:tristanb@pronto.com.au>
> Mobile: +61 408 397 473
>
> [Follow us on Twitter]<http://www.twitter.com/ProntoSoftware>;
>
> ---Legal Notice---
> The email message and any attachments are confidential and subject to
> copyright. If you are not the intended recipient, any use, interference
> with,
> disclosure or copying of this material is unauthorised and prohibited. No
> part
> may be reproduced, adapted or transmitted without the written permission of
> the copyright owner. If you have received this email in error, please
> immediately advise the sender by return email and delete the message from
> your
> system. Before opening or using attachments, check for viruses and defects.
> Our liability is limited to re-supplying any affected attachments.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec51ba4b1933acc04a55c94d2
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