Re: chekcpoints, BUFFER settings, and optimization

In article <881611278.1353908229@dejanews.com>, jneugebauer@ameritrade.com writes >I am trying to increase throughput and speed for an OLTP system using >Informix 7.22 UC1 and would like some advice. The platform is a SUN Ultra >Enterprise 5000 with 7 UltraSPARC 248 MHZ processors and 1 Gig of memory. > >I would like to minimize the length of checkpoints and maximize the >BUFFER usage but have found that increasing the BUFFERS results in longer >checkpoints. Checkpoints must remain in the 1-2 second range to avoid >problems with applications running against the database. Current >settings yield a 1-2 second checkpoint every 60 seconds. The database is >ANSI mode with Unbuffered logging. My read caching seems fine but the >write caching is not good. I have thought about increasing the buffers >to 7000 and decreasing LRU_MAX_DIRTY to 5 and LRU_MIN_DIRTY to 2. Is >this a good move? also going to increase SHMVIRTSIZE from 10MB to 100MB >and SHMADD from 8MB to 20MB. We have plenty of memory to give informix >(1 Gig physical memory on machine) but I am not sure how to get informix >to utilize it?? System is application driven and not connection >intensive. Most activity is generated by SQL calls and stored >procedures. Any other suggestions would be appreciated! > >Also, How can I tell if additional CPU's would benefit the database? I am >confused by the usercpu and syscpu numbers? What do they really mean in >terms of the total cpu time available? > They are the number of seconds Online processes spent in user mode i.e. outside the kernel, not in system calls system mode i.e. in kernel mode, executing system calls. >stats for 1 min interval: >Mon Dec 8 08:37:44 CST 1997 > >INFORMIX-OnLine Version 7.22.UC1 -- On-Line -- Up 49 days 04:48:56 -- >85264 Kbytes > >Profile >dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached >10363 10367 1405830 99.26 3848 4447 6678 42.38 > Why is write caching so poor? Try finding out which tables are being updated. select * from sysmaster:sysptntab? I can't remember the exact tables name but look in $INFORMIXDIR/etc/sysmaster.sql There is a table systab... or sysptn.... which gives more info on what is happening per table. writes,rewrites,seqscans. For each table with seqscans>0 check nrows in <your database>.systables. Any seqscans on tables with >200 rows need to be looked at. RUn onstat -u. Any users with >1000 reads in 5 minutes and you need to find what they are doing, "SET EXPLAIN ON" and look to see if indexes could help. >isamtot open start read write rewrite delete commit >rollbk >4584911 7764 24197 457872 768 3200 2 1556 0 > >ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes >0 0 0 106.20 4.00 1 2 > >bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans >2408 95 181379 0 0 6 48 1725 > >ixda-RA idx-RA da-RA RA-pgsused lchwaits >6837 0 0 6812 20296 > > >current onconfig file: >#************************************************************************ >** # # INFORMIX SOFTWARE, INC. # # Title: online_24hbConfig # >Description: INFORMIX-OnLine Configuration Parameters (PRODUCTION SYSTEM) ># >#************************************************************************ >** > ># Root Dbspace Configuration > >ROOTNAME rootdbs # Root dbspace name >ROOTPATH /dev/vx/rdsk/24hb/informix_d1_v1> # Path for device containing root dbspace >ROOTOFFSET 0 # Offset of root dbspace into device >(Kbytes) >ROOTSIZE 700000 # Size of root dbspace (Kbytes)> ># Disk Mirroring Configuration Parameters > >MIRROR 0 # Mirroring flag (Yes = 1, No = 0) >MIRRORPATH # Path for device containing mirrored root >MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> ># Physical Log Configuration > >PHYSDBS rootdbs # Location (dbspace) of physical log >PHYSFILE 140000 # Physical log file size (Kbytes)> ># Logical Log Configuration > >LOGFILES 13 # Number of logical log files >LOGSIZE 42000 # Logical log size (Kbytes)> ># Diagnostics > >MSGPATH /vrs/log/DBMessage # System message log file path >CONSOLE /vrs/log/DBConsole # System console message path >ALARMPROGRAM /opt/informix/etc/no_log.sh # Alarm program path ># note: set ALARMPROGRAM to no_log.sh to turn off alarms. > ># System Archive Tape Device > >TAPEDEV /dev/rmt/0mb # Tape device path >TAPEBLK 64 # Tape block size (Kbytes) >TAPESIZE 5000000 # Maximum amount of data to put on tape >(Kbytes)> ># Log Archive Tape Device > >LTAPEDEV /dev/null # Log tape device path >LTAPEBLK 16 # Log tape block size (Kbytes) >LTAPESIZE 10240 # Max amount of data to put on log tape >(Kbytes)> ># Optical > >STAGEBLOB # INFORMIX-OnLine/Optical staging area ># System Configuration > >SERVERNUM 1 # Unique id corresponding to a OnLine>instance >DBSERVERNAME online_24hb # Name of default database server >DBSERVERALIASES online_24hb_pc # List of alternate dbservernames >NETTYPE ipcshm,2,75,CPU # Override sqlhosts nettype parameters >NETTYPE tlitcp,1,50,NET # Override sqlhosts nettype parameters >DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed >env. >RESIDENT 1 # Forced residency flag (Yes = 1, No = 0)> Keep this set at 1, you don't want Online pages getting paged out. That will really slow down performance. >MULTIPROCESSOR 1 # 0 for single-processor, 1 for>multi-processor >NUMCPUVPS 5 # Number of user (cpu) vps Up to 6 >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 4 # Affinity number of processors >AFF_SPROC 1 # Affinity start processor >AFF_NPROCS 5 # Affinity number of processors Up to 6 as well.> ># Shared Memory Parameters > >LOCKS 100000 # Maximum number of locks BUFFERS 5000 # Maximum number BUFFERS 5000!! Run onstat -b and look at the last line it will end in page size either 2048 bytes or 4096 bytes (2 or 4K). I would up BUFFERS by 100Mb and run sar -p 1 1 / sar -q 1 1 make sure you are not getting paging/swapping. Keep on increasing by 100Mb unti paging starts then set it to the highest value which has no paging. Run onstat -g ath. Are there kaio threads running. If so set NUMAIOVPS=1. Otherwise increase NUMAIOVPS to the number of disks you have (up