Re: Phy. Log Overflow,CKPT Blocked

Physical log are critical to the database server's fast recovery. When checkpoint happens one job the database server tries to do is to flush all the physical buffer to physical log. But when the physical log is full the database sever can do nothing but shutdown. The causes of the physical log overflow might be large active blobpage update in a non-logging database or long transaction or the PHYSFILE is too small or your CKPTINTVL is too long for an active OLTP system. Fine tune these parameters might be the method to prevent overflow from happening again. Cheers Dong >From: "Sadagopan, Sathish, CFCTR" <sadagop0458@att.com> >To: informix-list@iiug.org >Subject: Phy. Log Overflow,CKPT Blocked >Date: Wed, 17 Mar 1999 13:18:14 -0500 > >Hi, >We run Online 7.14UD1 on MP-RAS. >The engine was hanging with the CKPT blocked message.The message log >indicated a physical log overflow. >The onstat -u output was explanatory >29abe784 Y--P--- 1287 >29abf420 --RPX-- 1370 >29ac118c C-BP--- 1281 >29ac19f4 Y--P--- 1262 >29ac332c ---P--- 1293 >29ac3760 Y--P--- 1154 >29ac5900 C--P--- 1377 >29ac5d34 ---P--- 1369 >So I inferred that the thread in Critical section caused the Phy. Log >overflow. >(Thanks to Art.S.Kagel's classic explanation of the Phy.Log overflow >recently). > >But why does the CKPT block, causing Online to hang while a Phy.Log overflow >occurs ? > >Has anyone seen this "mt.c", invariably this shows up whenever online >crashes. >TIA >Sathish Sadagopan > > > > >Contents of message log >------------------------------------ >11:04:43 Assert Failed: Physical log file overflow > >11:04:43 Who:Session(1370, sadagop@serv, 26115, 699268812)> > Thread(35335, sqlexec, 29abf420, 7) > >11:04:43 Results: OnLine must abort > >11:04:43 Action: Reinitialize shared memory > >11:04:43 See Also: /infxshm/af.8a07d29b, shmem.8a07d29b.0, >/usr/informix/core >11:14:20 mt.c, line 8994, thread 7, proc id 567, Unexpected virtual>processor t >ermination, pid = 573, exit = 0xb > >. >Contents of af.* file >---------------------------- > 11:04:43 Physical log file overflow > physlog_t > ........ >11:04:43 Assert Failed: Physical log file overflow > >11:04:43 Who:Session(1370, sadagop@serv, 26115, 699268812)> > Thread(35335, sqlexec, 29abf420, 7) > >11:04:43 Results: OnLine must abort > >11:04:43 Action: Reinitialize shared memory > >11:04:43 See Also: /infxshm/af.8a07d29b, shmem.8a07d29b.0, >/usr/informix/core >11:04:43 Stack for thread: 35335 sqlexec> > > > base: 0x2a260358 > > len: 36864 > > pc: 0x082e46fd > > tos: 0x2a268adc > > > > > > > > Get Your Private, Free Email at http://www.hotmail.com

I've found that the easiest way around this is just to make the physical log large enough that you'll never come close to filling it. In general, this is still fairly small compared to the rest of the installation. HTH... -Richard Dong Xiao wrote: > Physical log are critical to the database server's fast recovery. When > checkpoint happens one job the database server tries to do is to flush > all the physical buffer to physical log. But when the physical log is > full the database sever can do nothing but shutdown. > > The causes of the physical log overflow might be large active blobpage > update in a non-logging database or long transaction or the PHYSFILE is > too small or your CKPTINTVL is too long for an active OLTP system. Fine > tune these parameters might be the method to prevent overflow from > happening again. > > Cheers > Dong > > >From: "Sadagopan, Sathish, CFCTR" <sadagop0458@att.com> > >To: informix-list@iiug.org > >Subject: Phy. Log Overflow,CKPT Blocked > >Date: Wed, 17 Mar 1999 13:18:14 -0500 > > > >Hi, > >We run Online 7.14UD1 on MP-RAS. > >The engine was hanging with the CKPT blocked message.The message log > >indicated a physical log overflow. > >The onstat -u output was explanatory > >29abe784 Y--P--- 1287 > >29abf420 --RPX-- 1370 > >29ac118c C-BP--- 1281 > >29ac19f4 Y--P--- 1262 > >29ac332c ---P--- 1293 > >29ac3760 Y--P--- 1154 > >29ac5900 C--P--- 1377 > >29ac5d34 ---P--- 1369 > >So I inferred that the thread in Critical section caused the Phy. Log > >overflow. > >(Thanks to Art.S.Kagel's classic explanation of the Phy.Log overflow > >recently). > > > >But why does the CKPT block, causing Online to hang while a Phy.Log > overflow > >occurs ? > > > >Has anyone seen this "mt.c", invariably this shows up whenever online > >crashes. > >TIA > >Sathish Sadagopan > > > > > > > > > >Contents of message log > >------------------------------------ > >11:04:43 Assert Failed: Physical log file overflow > > > >11:04:43 Who:Session(1370, sadagop@serv, 26115, 699268812)> > > > Thread(35335, sqlexec, 29abf420, 7) > > > >11:04:43 Results: OnLine must abort > > > >11:04:43 Action: Reinitialize shared memory > > > >11:04:43 See Also: /infxshm/af.8a07d29b, shmem.8a07d29b.0, > >/usr/informix/core > >11:14:20 mt.c, line 8994, thread 7, proc id 567, Unexpected virtual> >processor t > >ermination, pid = 573, exit = 0xb > > > >. > >Contents of af.* file > >---------------------------- > > 11:04:43 Physical log file overflow > > physlog_t > > ........ > >11:04:43 Assert Failed: Physical log file overflow > > > >11:04:43 Who:Session(1370, sadagop@serv, 26115, 699268812)> > > > Thread(35335, sqlexec, 29abf420, 7) > > > >11:04:43 Results: OnLine must abort > > > >11:04:43 Action: Reinitialize shared memory > > > >11:04:43 See Also: /infxshm/af.8a07d29b, shmem.8a07d29b.0, > >/usr/informix/core > >11:04:43 Stack for thread: 35335 sqlexec> > > > > > > > base: 0x2a260358 > > > > len: 36864 > > > > pc: 0x082e46fd > > > > tos: 0x2a268adc > > > > > > > > > > > > > > > > > > Get Your Private, Free Email at http://www.hotmail.com