Checkpoint causes Db to "freeze"
A user on IDS 9.40 under AIX 5 reported the server hanging 1–3 minutes at every checkpoint, with a session blocked on an INSERT. Keith Simmons suggested checking the disk subsystem (e.g. a failed write-cache battery on the array forcing direct writes), via array lights or OS errpt. Art Kagel explained checkpoint blocking (critical sections plus flushing dirty pages) and advised lowering LRU_MIN/MAX_DIRTY to around 0.5/1, avoiding hard checkpoints, looking for what had changed in workload (heavy insert/delete churn on one table), and upgrading to 11.x/12.10 for non-blocking checkpoints. Another poster mentioned btscanner issues in 9.40. No confirmed resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All,
Has anyone encountered this issue? i'm running OLTP on an IDS 9.4 and AIX 5.
I'm having problem on isolating the problem. When a checkpoint is being
performed, the system hangs for 1-3 minutes.
PHYSDBS physdbs0000 # Location (dbspace) of physical log
PHYSFILE 100000 # Physical log file size (Kbytes)
PHYSBUFF 64 # Physical log buffer size (Kbytes)
LRUS 64
LRU_MAX_DIRTY 2.000000 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 1.000000 # LRU percent dirty end cleaning limit
LOGBUFF 64
CKPTINTVL 180 # Check point interval (in sec)
RA_PAGES 32 # Number of pages to attempt to read ahead
RA_THRESHOLD 16 # Number of pages left before next group
====================
Prior to the "freeze", i'm seeing this large user process.
=> onstat -u|grep G-
a5fb0308 G-BPX-- 1509649 user02 - a0ccdea0 0 1 734922 6256025
=> onstat -g sql 1509649
IBM Informix Dynamic Server Version 9.40.UC5W2 -- On-Line -- Up 16 days
02:34:12 -- 2072192 Kbytes
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
1509649 INSERT prd_db DR Not Wait 0 0 9.03 Off
Current SQL statement :
INSERT INTO table1 (col1, col2_num, col3_data) VALUES (?,?,?)
Host variables :
address type flags value
---------------------------------
0xa33b9020 INT 0x000 9587639
0xa33b9084 INT 0x000 1080
0xa33b90e8 CHAR 0x000 Travel constant 0.0000 19.4157 0.0374
Last parsed SQL statement :
SELECT * FROM tab2 WHERE tab2_id = ?
FYI, DB is in "unbuf" and Fuzzy checkpoint ON.
How can i make the DB eliminate the "Freeze". Should i decrease the LOGBUFF to
32? or should i set the CKPINTVL to 60 seconds?
Please need your help.
Thanks,
Jack
Is this an issue that has just started occuring or have you always had
longish checkpoints.
How are your disks connected, what sort of cache do they have ?
A few weeks ago my checkpoints went from 0-1 seconds to 30-50 with
no change in work-load. Investigation showed the cache battery on the
disk array was dead so the server was sriting directly to disk and
suffering for it.
If your issue is a recent occurrence it might be worth checking the
performance of your disk sub-system.
Keiht
On 22 October 2013 15:21, JACK PAPA <informix2009@gmail.com> wrote:
> Hi All,
>
> Has anyone encountered this issue? i'm running OLTP on an IDS 9.4 and AIX
> 5.
> I'm having problem on isolating the problem. When a checkpoint is being
> performed, the system hangs for 1-3 minutes.
>
> PHYSDBS physdbs0000 # Location (dbspace) of physical log
> PHYSFILE 100000 # Physical log file size (Kbytes)
> PHYSBUFF 64 # Physical log buffer size (Kbytes)
>
> LRUS 64
> LRU_MAX_DIRTY 2.000000 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 1.000000 # LRU percent dirty end cleaning limit
> LOGBUFF 64
> CKPTINTVL 180 # Check point interval (in sec)
> RA_PAGES 32 # Number of pages to attempt to read ahead
> RA_THRESHOLD 16 # Number of pages left before next group>
> ====================
>
> Prior to the "freeze", i'm seeing this large user process.
>
> => onstat -u|grep G-
> a5fb0308 G-BPX-- 1509649 user02 - a0ccdea0 0 1 734922 6256025
>
> => onstat -g sql 1509649
>
> IBM Informix Dynamic Server Version 9.40.UC5W2 -- On-Line -- Up 16 days
> 02:34:12 -- 2072192 Kbytes>
> Sess SQL Current Iso Lock SQL ISAM F.E.
> Id Stmt type Database Lvl Mode ERR ERR Vers Explain
> 1509649 INSERT prd_db DR Not Wait 0 0 9.03 Off
>
> Current SQL statement :
> INSERT INTO table1 (col1, col2_num, col3_data) VALUES (?,?,?)>
> Host variables :
>
> address type flags value
>
> ---------------------------------
>
> 0xa33b9020 INT 0x000 9587639
>
> 0xa33b9084 INT 0x000 1080
>
> 0xa33b90e8 CHAR 0x000 Travel constant 0.0000 19.4157 0.0374
>
> Last parsed SQL statement :
> SELECT * FROM tab2 WHERE tab2_id = ?>
> FYI, DB is in "unbuf" and Fuzzy checkpoint ON.
>
> How can i make the DB eliminate the "Freeze". Should i decrease the
> LOGBUFF to> 32? or should i set the CKPINTVL to 60 seconds?
>
> Please need your help.
>
> Thanks,
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7b66f2f7596cb804e9554c43
There are two possibilities:
1) At the time the checkpoint starts there are sessions that are in a
critical code section and before the checkpoint can enter its critical
section. The engine will block all other sessions that request a critical
section until it is finished with that portion of the checkpoint setup.
This will, in turn, have to wait until any processing already in a
critical section have exited the protected code block in the engine and
released their critical section(s).
2) After the critical section of the checkpoint begins a hard checkpoint
will begin flushing dirty pages to disk using chunk writes. A "fuzzy"
checkpoint also has some mandatory IO and housekeeping to do at this point.
Once these operations are complete, the checkpoint releases its critical
section and the block on other sessions and everything goes back to normal
for the second half of the checkpoint. If these IO operations take a long
time, that will cause the engine to seem to pause or hang. Only read-only
queries can progress under these conditions.
There isn't much you can do about #1 except to examine your application
code to minimize the operations that may cause its server threads to
require a critical section. To minimize the length of the blocked portion
of a checkpoint, #2 above, you need to decrease the LRU_MIN/MAX_DIRTY
levels from 1,2 to say 0.5,1 or lower so that there isn't much IO required
at checkpoint time. You can also look for operations and conditions that
will required a hard checkpoint rather than a fuzzy one. (See the 9.40
manuals.)
Art
Art S. Kagel, Principal Consultant
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 Tue, Oct 22, 2013 at 10:21 AM, JACK PAPA <informix2009@gmail.com> wrote:
> Hi All,
>
> Has anyone encountered this issue? i'm running OLTP on an IDS 9.4 and AIX
> 5.
> I'm having problem on isolating the problem. When a checkpoint is being
> performed, the system hangs for 1-3 minutes.
>
> PHYSDBS physdbs0000 # Location (dbspace) of physical log
> PHYSFILE 100000 # Physical log file size (Kbytes)
> PHYSBUFF 64 # Physical log buffer size (Kbytes)
>
> LRUS 64
> LRU_MAX_DIRTY 2.000000 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 1.000000 # LRU percent dirty end cleaning limit
> LOGBUFF 64
> CKPTINTVL 180 # Check point interval (in sec)
> RA_PAGES 32 # Number of pages to attempt to read ahead
> RA_THRESHOLD 16 # Number of pages left before next group>
> ====================
>
> Prior to the "freeze", i'm seeing this large user process.
>
> => onstat -u|grep G-
> a5fb0308 G-BPX-- 1509649 user02 - a0ccdea0 0 1 734922 6256025
>
> => onstat -g sql 1509649
>
> IBM Informix Dynamic Server Version 9.40.UC5W2 -- On-Line -- Up 16 days
> 02:34:12 -- 2072192 Kbytes>
> Sess SQL Current Iso Lock SQL ISAM F.E.
> Id Stmt type Database Lvl Mode ERR ERR Vers Explain
> 1509649 INSERT prd_db DR Not Wait 0 0 9.03 Off
>
> Current SQL statement :
> INSERT INTO table1 (col1, col2_num, col3_data) VALUES (?,?,?)>
> Host variables :
>
> address type flags value
>
> ---------------------------------
>
> 0xa33b9020 INT 0x000 9587639
>
> 0xa33b9084 INT 0x000 1080
>
> 0xa33b90e8 CHAR 0x000 Travel constant 0.0000 19.4157 0.0374
>
> Last parsed SQL statement :
> SELECT * FROM tab2 WHERE tab2_id = ?>
> FYI, DB is in "unbuf" and Fuzzy checkpoint ON.
>
> How can i make the DB eliminate the "Freeze". Should i decrease the
> LOGBUFF to> 32? or should i set the CKPINTVL to 60 seconds?
>
> Please need your help.
>
> Thanks,
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3327a52882d04e955524f
Hi Keith, this happened about 2 months ago and until now, my users are experiencing the same issue even i've already twicked the LRUs,CKPINTVL. Would you know how should i check the battery from the disk array? I'm unable to find any issue from the online.log.
Hi Keith, this happened about 2 months ago and until now, my users are experiencing the same issue even i've already twicked the LRUs,CKPINTVL. Would you know how should i check the battery from the disk array? I'm unable to find any issue from the online.log.
Hi Art, Thank you once again on replying on this post. Actually, the DB is originally on a hard checkpoint, however, it takes too long when it's in a hard checkpoint mode. That's the reason why i enabled the fuzzy checkpoint. This DB works fine since a long time ago, but all of a sudden it went run out to some checkpoint issue. What is see is, when the DB performs an insert into that table, if i select count(*) from the same table, there's also a process that deletes record from the same table because the record counts changes up and down to 0 and up again. do you think that's the reason why? Thanks again.
That's a lot of IO all on a single table, yes it could be one reason. I had a colleague who was a fanatic about "find what's different" to solve problems. While it's not always "what's different" that is the source of every problem, I learned much from this attitude and became a better debugger and DBA. I know everything seems to be the same as it was four months ago before this problem reared up, but SOMETHING has changed. It might be as subtle as a 5% increase in the volume of those deletes and inserts that pushed the server over some threshold and created this bottleneck. I know it's been said before, but the non-blocking checkpoints in version 11.xx/12.10 are vastly better than fuzzy checkpoints were, and safer too. Plus, semi-temporary data stores like this table, that are obviously just being used as a staging area for data being processed into the engine are a good candidate to be traded for external tables. Yet another reason to get this system upgraded - even if a 40% average performance gain over 9.40 isn't enough. Art Art S. Kagel, Principal Consultant 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 Tue, Oct 22, 2013 at 2:01 PM, JACK PAPA <informix2009@gmail.com> wrote: > Hi Art, > > Thank you once again on replying on this post. Actually, the DB is > originally > on a hard checkpoint, however, it takes too long when it's in a hard > checkpoint mode. That's the reason why i enabled the fuzzy checkpoint. > > This DB works fine since a long time ago, but all of a sudden it went run > out > to some checkpoint issue. What is see is, when the DB performs an insert > into > that table, if i select count(*) from the same table, there's also a > process > that deletes record from the same table because the record counts changes > up > and down to 0 and up again. do you think that's the reason why? > > Thanks again. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0112c13639323804e958bc15
The most obvious way is to look at the disk array and see if any lights flashing/not flashing when they shouldn't or should be :-) It al depends the type of your disk array and what access you have to it. errpt from the O/S may also reveal something. Keith On 22 October 2013 18:52, JACK PAPA <informix2009@gmail.com> wrote: > Hi Keith, > > this happened about 2 months ago and until now, my users are experiencing > the > same issue even i've already twicked the LRUs,CKPINTVL. > > Would you know how should i check the battery from the disk array? I'm > unable > to find any issue from the online.log. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bdc051efddf5204e964ffae
Only a thought: On 9.40 we had some issues with btscanners, though not sure if the caused long running checkpoints. It came to my mind only because you mentioned the process that deletes record from the same table ... HTH, Reinhard. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Tuesday, October 22, 2013 8:41 PM > To: ids@iiug.org > Subject: Re: Checkpoint causes Db to "freeze" [31776] > > That's a lot of IO all on a single table, yes it could be one reason. I had a > colleague who was a fanatic about "find what's different" to solve problems. While > it's not always "what's different" that is the source of every problem, I learned > much from this attitude and became a better debugger and DBA. I know > everything seems to be the same as it was four months ago before this problem > reared up, but SOMETHING has changed. It might be as subtle as a 5% increase > in the volume of those deletes and inserts that pushed the server over some > threshold and created this bottleneck. > > I know it's been said before, but the non-blocking checkpoints in version > 11.xx/12.10 are vastly better than fuzzy checkpoints were, and safer too. > Plus, semi-temporary data stores like this table, that are obviously just being used > as a staging area for data being processed into the engine are a good candidate > to be traded for external tables. Yet another reason to get this system upgraded - > even if a 40% average performance gain over 9.40 isn't enough. > > Art > > Art S. Kagel, Principal Consultant > > 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 Tue, Oct 22, 2013 at 2:01 PM, JACK PAPA <informix2009@gmail.com> wrote: > > > Hi Art, > > > > Thank you once again on replying on this post. Actually, the DB is > > originally > > on a hard checkpoint, however, it takes too long when it's in a hard > > checkpoint mode. That's the reason why i enabled the fuzzy checkpoint. > > > > This DB works fine since a long time ago, but all of a sudden it went run > > out > > to some checkpoint issue. What is see is, when the DB performs an insert > > into > > that table, if i select count(*) from the same table, there's also a > > process > > that deletes record from the same table because the record counts changes > > up > > and down to 0 and up again. do you think that's the reason why? > > > > Thanks again. > > > > > > > > > ************************************************************************ ******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e0112c13639323804e958bc15 > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum.