Re: Performance & checkpoint

Here is my system configuration: SUN V480 900mhz and 8GB ram Solaris 5.8 IDS 9.21 RAID 10 Businesss application is Baan5c. If you have any input would be appreciated. Thank you in advance. --Wayne ******************************************************** onstat -d Chunks address chk/dbs offset size free bpages flags pathname 5310f918 1 1 0 200000 197720 PO- /dev/rawlinks/rootdbs 5310fb00 2 2 0 1000000 999897 PO- /dev/rawlinks/tempdbs 5310fc70 3 3 0 1000000 99947 PO- /dev/rawlinks/plogdbs 5310fde0 4 4 0 1000000 499947 PO- /dev/rawlinks/llogdbs 5313f830 5 5 0 1000000 221 PO- /dev/rawlinks/data01 5313f9a0 6 5 0 1000000 430 PO- /dev/rawlinks/data02 5313fb10 7 5 0 1000000 218 PO- /dev/rawlinks/data03 5313fc80 8 5 0 1000000 165 PO- /dev/rawlinks/data04 5313fdf0 9 5 0 1000000 770 PO- /dev/rawlinks/data05 539b4018 10 5 0 1000000 285 PO- /dev/rawlinks/data06 539b4188 11 5 0 1000000 267 PO- /dev/rawlinks/data07 539b42f8 12 5 0 1000000 369 PO- /dev/rawlinks/data08 539b4468 13 5 0 1000000 341 PO- /dev/rawlinks/data09 539b45d8 14 5 0 1000000 502 PO- /dev/rawlinks/data10 539b4748 15 5 0 1000000 87 PO- /dev/rawlinks/data11 539b48b8 16 5 0 1000000 28 PO- /dev/rawlinks/data12 539b4a28 17 5 0 1000000 470861 PO- /dev/rawlinks/data13 539b4b98 18 5 0 1000000 999997 PO- /dev/rawlinks/data14 539b4d08 19 5 0 1000000 999997 PO- /dev/rawlinks/data15 539b4e78 20 5 0 1000000 999997 PO- /dev/rawlinks/data16 539b5018 21 5 0 1000000 999997 PO- /dev/rawlinks/data17 539b5188 22 5 0 1000000 999997 PO- /dev/rawlinks/data18 22 active, 2047 maximum ************************************************************ $ onstat -g iov Informix Dynamic Server 2000 Version 9.21.UC4 -- On-Line -- Up 3 days 07:17:17 -- 1806336 Kbytes AIO I/O vps: class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors kio 0 i 23.6 6743404 5911155 832249 0 15142951 0.4 0 msc 0 i 0.3 83026 0 0 0 82978 1.0 0 aio 0 i 0.0 161 61 0 0 64 2.5 0 pio 0 i 0.0 0 0 0 0 1 0.0 0 lio 0 i 0.0 0 0 0 0 1 0.0 0 ***************************************************** Informix Dynamic Server 2000 Version 9.21.UC4 -- On-Line -- Up 3 days 07:17:46 -- 1806336 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 11775792 39362423 2251324629 99.48 971533 5778329 6053142 83.95 isamtot open start read write rewrite delete commit rollbk 1243684959 1781075 27883004 1068978860 352608 631757 199160 218938 176 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 0 0 0 42103.68 20251.59 1158 3166 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 1144801 13 231849680 0 0 423 162451 282224 ixda-RA idx-RA da-RA RA-pgsused lchwaits 9118728 14358 1134854 10041192 1355 ntrieu@yahoo.com (Wayne T) wrote in message news:<e6623a40.0305121517.15f36184@posting.google.com>... > Hi All, > My database has grown 3 times and the performance is degrading. During > the business hours my checkpoint duration is between 1-8 seconds. > About two months ago my checkpoint mostly is between 0-1 seconds. I > included my onconfig file for you to review. If you have any > suggestions I would be appreciated. > Thank you. --Wayne > > > # Physical Log Configuration > > PHYSDBS plogdbs # Location (dbspace) of physical log > PHYSFILE 1800000 # Physical log file size (Kbytes)> > > # Logical Log Configuration > > LOGFILES 50 # Number of logical log files > LOGSIZE 20000 # 20MB each Logical log size (Kbytes) > > NETTYPE tlitcp,1,100,NET # Configure poll thread(s) for> nettype > NETTYPE ipcstr,1,63,CPU # Configure poll thread(s) for nettype > #NETTYPE tlitcp,1,100,CPU # Configure poll thread(s) for> nettype > #NETTYPE ipcshm,1,63,CPU # Configure poll thread(s) for > nettype > # above never work > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in> distributed env. > RESIDENT 1 # Forced residency flag (Yes = 1, No = > 0) > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> multi-processor > NUMCPUVPS 1 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to one > > NOAGE 1 # Process aging > AFF_SPROC 1 # Affinity start processor > AFF_NPROCS 1 # Affinity number of processors> > # Shared Memory Parameters > > LOCKS 5000000 # Maximum number of locks > BUFFERS 358000 # Maximum number of shared buffers > NUMAIOVPS 1 # Number of IO vps > PHYSBUFF 256 # Physical log buffer size (Kbytes) > LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 200 # Maximum number of logical log files > CLEANERS 30 # Number of buffer cleaner processes > SHMBASE 0xa000000 # Shared memory base address > SHMVIRTSIZE 512000 # initial virtual shared memory> segment size > SHMADD 16384 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited > CKPTINTVL 300 # Check point interval (in sec) > LRUS 12 # Number of LRU queues > LRU_MAX_DIRTY 3 # LRU percent dirty begin cleaning> limit > LRU_MIN_DIRTY 2 # LRU percent dirty end cleaning limit > LTXHWM 50 # Long transaction high water mark> percentage > LTXEHWM 60 # Long transaction high water mark > (exclusive) > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 32 # Stack size (Kbytes) > > OFF_RECVRY_THREADS 10 # Default number of offline worker> threads > ON_RECVRY_THREADS 1 # Default number of online worker> threads > > # Data Replication Variables > DRINTERVAL 30 # DR max time between DR

Hey Wayne, Turn off Multiprocessing overhead if you just have the one cpu. Pump up your LRU queues to about 1/2000 buffers Match the number of cleaners to LRUs up to 128 Install ISA (on your IDS engine CD or download from IBM) and run the Update statistics script generator (SQL -> Schema -> <Database name>-> generate update statistics script) on your database to get a custom statistics sql and run it nightly if you can. Set NUMAIOVPS to 2 not 1. 1's too restrictive. Greg. On 14 May 2003 18:07:39 -0700, ntrieu@yahoo.com (Wayne T) wrote: >Here is my system configuration: >SUN V480 900mhz and 8GB ram >Solaris 5.8 >IDS 9.21 >RAID 10 >Businesss application is Baan5c. > >If you have any input would be appreciated. Thank you in advance. >--Wayne > >******************************************************** >onstat -d> >Chunks >address chk/dbs offset size free bpages flags pathname >5310f918 1 1 0 200000 197720 PO- >/dev/rawlinks/rootdbs >5310fb00 2 2 0 1000000 999897 PO- >/dev/rawlinks/tempdbs >5310fc70 3 3 0 1000000 99947 PO- >/dev/rawlinks/plogdbs >5310fde0 4 4 0 1000000 499947 PO- >/dev/rawlinks/llogdbs >5313f830 5 5 0 1000000 221 PO- >/dev/rawlinks/data01 >5313f9a0 6 5 0 1000000 430 PO- >/dev/rawlinks/data02 >5313fb10 7 5 0 1000000 218 PO- >/dev/rawlinks/data03 >5313fc80 8 5 0 1000000 165 PO- >/dev/rawlinks/data04 >5313fdf0 9 5 0 1000000 770 PO- >/dev/rawlinks/data05 >539b4018 10 5 0 1000000 285 PO- >/dev/rawlinks/data06 >539b4188 11 5 0 1000000 267 PO- >/dev/rawlinks/data07 >539b42f8 12 5 0 1000000 369 PO- >/dev/rawlinks/data08 >539b4468 13 5 0 1000000 341 PO- >/dev/rawlinks/data09 >539b45d8 14 5 0 1000000 502 PO- >/dev/rawlinks/data10 >539b4748 15 5 0 1000000 87 PO- >/dev/rawlinks/data11 >539b48b8 16 5 0 1000000 28 PO- >/dev/rawlinks/data12 >539b4a28 17 5 0 1000000 470861 PO- >/dev/rawlinks/data13 >539b4b98 18 5 0 1000000 999997 PO- >/dev/rawlinks/data14 >539b4d08 19 5 0 1000000 999997 PO- >/dev/rawlinks/data15 >539b4e78 20 5 0 1000000 999997 PO- >/dev/rawlinks/data16 >539b5018 21 5 0 1000000 999997 PO- >/dev/rawlinks/data17 >539b5188 22 5 0 1000000 999997 PO- >/dev/rawlinks/data18 > 22 active, 2047 maximum >************************************************************ > >$ onstat -g iov> >Informix Dynamic Server 2000 Version 9.21.UC4 -- On-Line -- Up 3 >days 07:17:17 -- 1806336 Kbytes > >AIO I/O vps: >class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup >errors > kio 0 i 23.6 6743404 5911155 832249 0 15142951 0.4 > 0 > msc 0 i 0.3 83026 0 0 0 82978 1.0 > 0 > aio 0 i 0.0 161 61 0 0 64 2.5 > 0 > pio 0 i 0.0 0 0 0 0 1 0.0 > 0 > lio 0 i 0.0 0 0 0 0 1 0.0 > 0 > >***************************************************** >Informix Dynamic Server 2000 Version 9.21.UC4 -- On-Line -- Up 3 >days 07:17:46 -- 1806336 Kbytes > >Profile >dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached >11775792 39362423 2251324629 99.48 971533 5778329 6053142 83.95 > >isamtot open start read write rewrite delete commit > rollbk >1243684959 1781075 27883004 1068978860 352608 631757 199160 >218938 176 > >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 >0 0 0 42103.68 20251.59 1158 3166 > >bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress >seqscans >1144801 13 231849680 0 0 423 162451 282224 > >ixda-RA idx-RA da-RA RA-pgsused lchwaits >9118728 14358 1134854 10041192 1355 > > >ntrieu@yahoo.com (Wayne T) wrote in message news:<e6623a40.0305121517.15f36184@posting.google.com>... >> Hi All, >> My database has grown 3 times and the performance is degrading. During >> the business hours my checkpoint duration is between 1-8 seconds. >> About two months ago my checkpoint mostly is between 0-1 seconds. I >> included my onconfig file for you to review. If you have any >> suggestions I would be appreciated. >> Thank you. --Wayne >> >> >> # Physical Log Configuration >> >> PHYSDBS plogdbs # Location (dbspace) of physical log >> PHYSFILE 1800000 # Physical log file size (Kbytes)>> >> >> # Logical Log Configuration >> >> LOGFILES 50 # Number of logical log files >> LOGSIZE 20000 # 20MB each Logical log size (Kbytes) >> >> NETTYPE tlitcp,1,100,NET # Configure poll thread(s) for>> nettype >> NETTYPE ipcstr,1,63,CPU # Configure poll thread(s) for nettype >> #NETTYPE tlitcp,1,100,CPU # Configure poll thread(s) for>> nettype >> #NETTYPE ipcshm,1,63,CPU # Configure poll thread(s) for >> nettype >> # above never work >> DEADLOCK_TIMEOUT 60 # Max time to wait of lock in>> distributed env. >> RESIDENT 1 # Forced residency flag (Yes = 1, No = >> 0) >> >> MULTIPROCESSOR 1 # 0 for single-processor, 1 for>> multi-processor >> NUMCPUVPS 1 # Number of user (cpu) vps >> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps>> to one >> >> NOAGE 1 # Process aging >> AFF_SPROC 1 # Affinity start processor >> AFF_NPROCS 1 # Affinity number of processors>> >> # Shared Memory Parameters >> >> LOCKS 5000000 # Maximum number of locks >> BUFFERS 358000 # Maximum number of shared buffers >> NUMAIOVPS 1 # Number of IO vps >> PHYSBUFF 256 # Physical log buffer size (Kbytes) >> LOGBUFF 32 # Logical log buffer size (Kbytes)>> LOGSMAX 200 # Maximum number of logical log files >> CLEANERS 30 # Number of buffer cleaner processes >> SHMBASE 0xa000000 # Shared memory base address >> SHMVIRTSIZE 512000 # initial virtual shared memory>> segment size >> SHMADD 16384 # Size of new shared memory segments >> (Kbytes) >> SHMTOTAL 0 # Total shared memory (Kbytes). >> 0=>unlimited >> CKPTINTVL 300 # Check point interval (in sec) >> LRUS 1