Long checkpoint problem
Challenger212 Abderrafi reported long checkpoints on Informix 11.70 C5 despite lowering LRU dirty limits, with all chunks on one disk. Art Kagel said the single disk was the problem and advised more or faster disks, and isolating high-IO objects such as root, logical and physical logs, temp spaces and busy tables. Andrew Ford suggested checking onstat -D and -F and lowering CLEANERS. No outcome reported.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, we have from time to time long checkpoints in informix 11.70 C5, we have lowered the Min_drity and max_dirty to 2 and 1% but the same problem. we have 400000 bufferpool and Lrus=20 and cleaners=8, what should I do plz, we suspect a IO disk problem, we have one disk including all chunks andone dbspace for all tables.
Your problem is that you have only one disk for all chunks. Nothing to do but get a faster disk or more of them and spread the load. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Mon, May 16, 2016 at 2:26 AM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi, we have from time to time long checkpoints in informix 11.70 C5, > we have lowered the Min_drity and max_dirty to 2 and 1% but the same > problem. > > we have 400000 bufferpool and Lrus=20 and cleaners=8, what should I do > plz, we > suspect a IO disk problem, we have one disk including all chunks andone > dbspace for all tables. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c0031725b81270532f5ead7
Hi, Ok, if I dedicate to large tables their own dbspace, would this bring more performance and good checkpoints time?
If that dbspace is located on different disks than the rest, maybe. More important than relocating large tables is to isolate objects with high IO activity from each other. High activity objects includes: - ROOTDB dbspace - Logical log dbspace - Physical log dbspace - Temp dbspaces - tables that experience high insert/update/delete activity - indexes on these tables - tables that are naturally scanned over a large percentage of their rows (so the subject of large reports, etc.) Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Mon, May 16, 2016 at 3:49 PM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi, Ok, if I dedicate to large tables their own dbspace, would this bring > more > performance and good checkpoints time? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1143b232cdbcd10532fb06c0
Question, what's the problem with a long checkpoint now that checkpoints are
non-blocking (for the most part).
Are your users/applications experiencing any negative impacts from these
long checkpoints?
Either way, I/O is likely your bottleneck. What does onstat -r 1 -D and
onstat -r 1 -F look like during these long checkpoints?
Are you seeing multiple chunks being written to concurrently for extended
periods of time during the long checkpoint?
Maybe your I/O can't handle the 8 concurrent cleaner threads + the other I/O
the engine is doing to fulfill SQL requests.
You could try dropping CLEANERS down to 1 and see what impact that has on
checkpoint duration if you are seeing multiple chunks on the same physical
drive being written to concurrently during a checkpoint.
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
CHALLENGER212 ABDERRAFI
Sent: Monday, May 16, 2016 2:49 PM
To: ids@iiug.org
Subject: Re: Long checkpoint problem [37132]
Hi, Ok, if I dedicate to large tables their own dbspace, would this bring
more performance and good checkpoints time?
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.