large chkpoints with update program running

The system is going v v slow and it is most likely due to an update progrm than has been running for over 2 hours. The following is the results from onstat -u | grep paladm, every 10 secs which shows it is doing a lot of writes. This has increased the checkpoint interval. Is this normal? Would you expect the checkpoint interval to go from 0 or 1 secs all the way up? I understand that it depends on how much data is being updates etc etc. Just looking for clues or anything I can change/tune to speed things up a bit. We are on IDS 7.30, 3 processors and Unixware 7.0.1 1d64e2c0 ---P--- 17540 paladm pts013 0 0 1 1220166 915652 1d64e2c0 ---P--- 17540 paladm pts013 0 0 1 1222050 917476 1d64e2c0 ---P--- 17540 paladm pts013 0 0 1 1224718 920012 1d64e2c0 ---P--- 17540 paladm pts013 0 0 1 1226598 921768 1d64e2c0 ---P--- 17540 paladm pts013 0 0 1 1228150 923252 1d64e2c0 ---P--- 17540 paladm pts013 0 0 1 1230662 925572 1d64e2c0 ---P--- 17540 paladm pts013 0 0 1 1233723 928312 1d64e2c0 ---P--- 17540 paladm pts013 0 0 1 1235543 929660 onstat -m 10:07:39 Checkpoint Completed: duration was 3 seconds. 10:17:46 Dynamically added 1 cpu VP 10:18:09 Checkpoint Completed: duration was 6 seconds. 10:22:56 Checkpoint Completed: duration was 13 seconds. 10:25:16 Logical Log 5136 Complete. 10:25:20 Logical Log 5136 - Backup Started 10:25:27 Logical Log 5136 - Backup Completed 10:26:25 Checkpoint Completed: duration was 15 seconds. 10:29:38 Checkpoint Completed: duration was 20 seconds. 10:32:46 Checkpoint Completed: duration was 16 seconds. 10:35:43 Checkpoint Completed: duration was 12 seconds. 10:40:24 Logical Log 5137 - Backup Started 10:40:24 Logical Log 5137 Complete. 10:40:33 Logical Log 5137 - Backup Completed 10:45:54 Checkpoint Completed: duration was 7 seconds. onstat -p Informix Dynamic Server Version 7.30.UC10X2 -- On-Line -- Up 9 days 20:17:39 -- 437432 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 150524382 14181166 3479905643 95.67 3995912 3283893 92954833 95.70 isamtot open start read write rewrite delete commit rollbk 2137261542 35594822 133115757 1587923637 42933432 1135739 223641 331363 25597 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 6 0 0 241667.47 150249.09 1143 2838 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 9731674 79 2044529143 4 0 604 215290 2455026 ixda-RA idx-RA da-RA RA-pgsused lchwaits 65678222 72603 49220912 114938522 561723 onstat -c (the parameters that are relevant) Informix Dynamic Server Version 7.30.UC10X2 -- On-Line -- Up 9 days 20:18:16 -- 437432 Kbytes PHYSDBS physspace PHYSFILE 10000 LOGFILES 30 LOGSIZE 5000 TBLSPACE_STATS 0 SERVERNUM 0 DBSERVERNAME pluto DBSERVERALIASES pluto_net NETTYPE ipcshm,2,64,CPU NETTYPE tlitcp,2,8,NET DEADLOCK_TIMEOUT 60 RESIDENT 1 MULTIPROCESSOR 1 NUMCPUVPS 2 SINGLE_CPU_VP 0 LOCKS 20000 BUFFERS 150000 NUMAIOVPS 40 PHYSBUFF 124 LOGBUFF 22LOGSMAX 60 CLEANERS 20 SHMVIRTSIZE 120000 SHMADD 8000 SHMTOTAL 0 CKPTINTVL 600 LRUS 93 LRU_MAX_DIRTY 4 LRU_MIN_DIRTY 2 LTXHWM 40 LTXEHWM 45 RA_PAGES 48 RA_THRESHOLD 42 DBSPACETEMP tempspace:tempspace2 OPTCOMPIND 0

In the year of Our Lord Thu, 15 Feb 2001 11:47:32 -0000, "Tam McLaughlin" <tamm@scotlegal.com> spake, saying: >The system is going v v slow and it is most likely due to an update >progrm than has been running for over 2 hours. The following is the results >from onstat -u | grep paladm, every 10 secs which shows it is doing a lot of >writes. This has increased the checkpoint interval. Is this normal? Would >you expect the checkpoint interval to go from 0 or 1 secs all the way up? >I understand that it depends on how much data is being updates etc etc. >Just looking for clues or anything I can change/tune to speed things up a >bit. > >We are on IDS 7.30, 3 processors and Unixware 7.0.1 >onstat -m> >10:07:39 Checkpoint Completed: duration was 3 seconds. >10:17:46 Dynamically added 1 cpu VP >10:18:09 Checkpoint Completed: duration was 6 seconds. >10:22:56 Checkpoint Completed: duration was 13 seconds. >10:25:16 Logical Log 5136 Complete. >10:25:20 Logical Log 5136 - Backup Started >10:25:27 Logical Log 5136 - Backup Completed >10:26:25 Checkpoint Completed: duration was 15 seconds. >10:29:38 Checkpoint Completed: duration was 20 seconds. >10:32:46 Checkpoint Completed: duration was 16 seconds. >10:35:43 Checkpoint Completed: duration was 12 seconds. >10:40:24 Logical Log 5137 - Backup Started >10:40:24 Logical Log 5137 Complete. >10:40:33 Logical Log 5137 - Backup Completed >10:45:54 Checkpoint Completed: duration was 7 seconds.> > >onstat -p> > >Informix Dynamic Server Version 7.30.UC10X2 -- On-Line -- Up 9 days >20:17:39 -- 437432 Kbytes > >Profile >dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached >150524382 14181166 3479905643 95.67 3995912 3283893 92954833 95.70 > >isamtot open start read write rewrite delete commit >rollbk >2137261542 35594822 133115757 1587923637 42933432 1135739 223641 331363 >25597 > >gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs >0 0 0 0 0 0 0 > >ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes >6 0 0 241667.47 150249.09 1143 2838 > >bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans >9731674 79 2044529143 4 0 604 215290 2455026 > >ixda-RA idx-RA da-RA RA-pgsused lchwaits >65678222 72603 49220912 114938522 561723 > >onstat -c (the parameters that are relevant)> >Informix Dynamic Server Version 7.30.UC10X2 -- On-Line -- Up 9 days >20:18:16 -- 437432 Kbytes > >PHYSDBS physspace >PHYSFILE 10000 Way too small. You're checkpointing more quickly than every 10 minutes because your PHYSLOG is too small. Based on what I can see in the message log, you need at least 50000. >LOGFILES 30 >LOGSIZE 5000 >TBLSPACE_STATS 0 >SERVERNUM 0 >DBSERVERNAME pluto >DBSERVERALIASES pluto_net >NETTYPE ipcshm,2,64,CPU >NETTYPE tlitcp,2,8,NET This isn't germane, but these are pretty daft settings. Try: NETTYPE ipcshm,2,200,CPU NETTYPE tlitcp,2,200,NET >DEADLOCK_TIMEOUT 60 >RESIDENT 1 >MULTIPROCESSOR 1 >NUMCPUVPS 2 >SINGLE_CPU_VP 0 >LOCKS 20000 I generally have more locks than buffers, this seems odd. I'd go for at least one lock per buffer. >BUFFERS 150000 >NUMAIOVPS 40 I take it you don't have KAIO? If you do, use it, it makes a big difference. >PHYSBUFF 124 >LOGBUFF 22>LOGSMAX 60 >CLEANERS 20 >SHMVIRTSIZE 120000 >SHMADD 8000 >SHMTOTAL 0 >CKPTINTVL 600 >LRUS 93 Eh? 93? >LRU_MAX_DIRTY 4 >LRU_MIN_DIRTY 2 >LTXHWM 40 >LTXEHWM 45 >RA_PAGES 48 >RA_THRESHOLD 42 >DBSPACETEMP tempspace:tempspace2 >OPTCOMPIND 0 There are basically two approaches you can take: 1. reduce individual checkpoint time * DECREASE LRU_MIN/MAX_DIRTY (0/1), PHYSFILE and CKPTINTVL 2. reduce total checkpoint time * INCREASE LRU_MIN/MAX_DIRTY (80/90), PHYSFILE and CKPTINTVL Which way you go depends on you. Maybe you can also up LRUS and CLEANERS to 127 and monitor onstat -u to see how many cleaners are used -- maybe 20 isn't enough.

>>PHYSDBS physspace >>PHYSFILE 10000> >Way too small. You're checkpointing more quickly than every 10 minutes because >your PHYSLOG is too small. Based on what I can see in the message log, you need >at least 50000. ok, our checkpoints are almost always every 10 mins, but I guess the physlog should be large enough to cope for these situatons. >>NETTYPE ipcshm,2,64,CPU >>NETTYPE tlitcp,2,8,NET> >This isn't germane, but these are pretty daft settings. Try: >NETTYPE ipcshm,2,200,CPU >NETTYPE tlitcp,2,200,NET I will try this but was basing this on having around 100 connections incl. oninits and having only a few people connecting via tcp ip as most connect thru shared memory. >>DEADLOCK_TIMEOUT 60 >>RESIDENT 1 >>MULTIPROCESSOR 1 >>NUMCPUVPS 2 >>SINGLE_CPU_VP 0 >>LOCKS 20000 >I generally have more locks than buffers, this seems odd. I'd go for at least >one lock per buffer. I thought locks were purely to do with rows/pages/tables. We try to have programs that either lock the table and/or updat around a begin/commit work rather than try and update every row. So are you saying that a lock is used when a buffer is used? if there are not enough locks, then why are there no lock table overflows? > >>BUFFERS 150000 >>NUMAIOVPS 40> >I take it you don't have KAIO? If you do, use it, it makes a big difference. unixware does not support KAIO > >>PHYSBUFF 124 >>LOGBUFF 22>>LOGSMAX 60 >>CLEANERS 20 >>SHMVIRTSIZE 120000 >>SHMADD 8000 >>SHMTOTAL 0 >>CKPTINTVL 600 >>LRUS 93> >Eh? 93? > >>LRU_MAX_DIRTY 4 >>LRU_MIN_DIRTY 2 >>LTXHWM 40 >>LTXEHWM 45 >>RA_PAGES 48 >>RA_THRESHOLD 42 >>DBSPACETEMP tempspace:tempspace2 >>OPTCOMPIND 0> >There are basically two approaches you can take: > >1. reduce individual checkpoint time >* DECREASE LRU_MIN/MAX_DIRTY (0/1), PHYSFILE and CKPTINTVL > >2. reduce total checkpoint time >* INCREASE LRU_MIN/MAX_DIRTY (80/90), PHYSFILE and CKPTINTVL > >Which way you go depends on you. Maybe you can also up LRUS and CLEANERS to 127 >and monitor onstat -u to see how many cleaners are used -- maybe 20 isn't >enough.

In the year of Our Lord Thu, 15 Feb 2001 13:38:21 -0000, "Tam McLaughlin" <tamm@scotlegal.com> spake, saying: >>I generally have more locks than buffers, this seems odd. I'd go for at >least >>one lock per buffer. > >I thought locks were purely to do with rows/pages/tables. We try to have >programs >that either lock the table and/or updat around a begin/commit work rather >than >try and update every row. So are you saying that a lock is used when a >buffer >is used? if there are not enough locks, then why are there no lock table >overflows? But there were: 6 in the onstat -p you provided. :)

Obnoxio The Clown wrote in message <3a8bdebc.21323922@130.133.1.4>... >In the year of Our Lord Thu, 15 Feb 2001 13:38:21 -0000, "Tam McLaughlin" ><tamm@scotlegal.com> spake, saying: > >>>I generally have more locks than buffers, this seems odd. I'd go for at >>least >>>one lock per buffer. >> >>I thought locks were purely to do with rows/pages/tables. We try to have >>programs >>that either lock the table and/or updat around a begin/commit work rather >>than >>try and update every row. So are you saying that a lock is used when a >>buffer >>is used? if there are not enough locks, then why are there no lock table >>overflows? > >But there were: 6 in the onstat -p you provided. :) True, that was due to a badly designed query that was eventually sorted, but I guess there should be enough locks in case this happens again. I have not wanted to increase the locks in the past to try and make developers use "lock table" properly, but the developers we now have understand locking enough that we do not generraly have any problems

In the year of Our Lord Thu, 15 Feb 2001 16:00:47 -0000, "Tam McLaughlin" <tamm@scotlegal.com> spake, saying: >Obnoxio The Clown wrote in message <3a8bdebc.21323922@130.133.1.4>... >>In the year of Our Lord Thu, 15 Feb 2001 13:38:21 -0000, "Tam McLaughlin" >><tamm@scotlegal.com> spake, saying: >> >>>>I generally have more locks than buffers, this seems odd. I'd go for at >>>least >>>>one lock per buffer. >>> >>>I thought locks were purely to do with rows/pages/tables. We try to have >>>programs >>>that either lock the table and/or updat around a begin/commit work rather >>>than >>>try and update every row. So are you saying that a lock is used when a >>>buffer >>>is used? if there are not enough locks, then why are there no lock table >>>overflows? >> >>But there were: 6 in the onstat -p you provided. :) > >True, that was due to a badly designed query that was eventually sorted, but Jeez, like I'm supposed to guess... :-) >I guess >there should be enough locks in case this happens again. I have not wanted >to increase >the locks in the past to try and make developers use "lock table" properly, >but the developers >we now have understand locking enough that we do not generraly have any >problems What's this table locking stuff? Don't you lock at row-level?

(replying from home address now - yes that's sad i know) Obnoxio The Clown wrote: > > In the year of Our Lord Thu, 15 Feb 2001 16:00:47 -0000, "Tam McLaughlin" > <tamm@scotlegal.com> spake, saying: > > >Obnoxio The Clown wrote in message <3a8bdebc.21323922@130.133.1.4>... > >>In the year of Our Lord Thu, 15 Feb 2001 13:38:21 -0000, "Tam McLaughlin" > >><tamm@scotlegal.com> spake, saying: > >> > >>>>I generally have more locks than buffers, this seems odd. I'd go for at > >>>least > >>>>one lock per buffer. > >>> > >>>I thought locks were purely to do with rows/pages/tables. We try to have > >>>programs > >>>that either lock the table and/or updat around a begin/commit work rather > >>>than > >>>try and update every row. So are you saying that a lock is used when a > >>>buffer > >>>is used? if there are not enough locks, then why are there no lock table > >>>overflows? > >> > >>But there were: 6 in the onstat -p you provided. :) > > > >True, that was due to a badly designed query that was eventually sorted, but > > Jeez, like I'm supposed to guess... :-) > > >I guess > >there should be enough locks in case this happens again. I have not wanted > >to increase > >the locks in the past to try and make developers use "lock table" properly, > >but the developers > >we now have understand locking enough that we do not generraly have any > >problems > > What's this table locking stuff? Don't you lock at row-level? Yes we do use row level locking but sometimes developers will just do update table tablename where ...... without using "begin work ; lock table in exclusive mode". I always try to check their queries before I run them on the live system but if it's a large table and it's not always possible for me to find out how many rows will be updated. There are also some old programs that update rows out with a transaction. Basically, I had not wanted to increase the number of locks just to compinsate for badly written programs, but now that the developers are a bit more responsible, I think I should increase LOCKS to make surewe don't run out of any locks when odd things happen. Also I know that increasing LOCKS does not have much of an overhead (if any) on IDS.

Tam McLaughlin wrote in message <5lug69.gvf.ln@mail.scotlegal.com>... > >True, that was due to a badly designed query that was eventually sorted, but >I guess >there should be enough locks in case this happens again. I have not wanted >to increase >the locks in the past to try and make developers use "lock table" properly, >but the developers >we now have understand locking enough that we do not generraly have any >problems > Emmm, why would you want your developers to prefer the use of lock table? Am I understanding you properly? Total number of locks has sod-all effect on performance and also memory use when compared to other greedy data structures in the engine. I routinely set 100k or 200k before I even blink. Then the monitoring starts...