Write cache help

Hi all, Can some one please help me how to increase write cache, right now my cache is about 73% it seem low. I've tried all source recommend I see on this group, but did not help. May be I done something wrong. I hope some one can look at my config file and give me some hits. Thanks in advance for any input and very appreciated. My system config as follow: Harware: SUN E450 CPU: 4 Memory: 3.5gig OS: Solaris 2.6 DB: Informix 7.30.uc5.-1 Veritas Software RAID 5 here are some onstat output: onstat -p Informix Dynamic Server Version 7.30.UC5 -- On-Line -- Up 1 days 04:14:28 -- 2078720 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 5521041 13946279 556670414 99.01 708844 1704464 2649529 73.25 isamtot open start read write rewrite delete commit rollbk 366078353 7624212 17380578 294565447 253420 1705444 9880 297253 7 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 22586.93 2681.95 702 2226 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 257477 12 96781517 0 0 307 5403 205816 ixda-RA idx-RA da-RA RA-pgsused lchwaits 1249775 1175 93800 1339070 65176 onstat -m Informix Dynamic Server Version 7.30.UC5 -- On-Line -- Up 1 days 04:15:03 -- 2078720 Kbytes Message Log File: /usr2/informix/online.log 16:49:57 Checkpoint Completed: duration was 0 seconds. 16:51:27 Checkpoint Completed: duration was 0 seconds. 16:52:57 Checkpoint Completed: duration was 0 seconds. 16:54:27 Checkpoint Completed: duration was 0 seconds. 16:55:59 Checkpoint Completed: duration was 2 seconds. 16:56:32 Logical Log 60732 Complete. 16:56:33 Logical Log 60732 - Backup Started 16:56:36 Logical Log 60732 - Backup Completed 16:57:36 Checkpoint Completed: duration was 7 seconds. 16:59:08 Checkpoint Completed: duration was 2 seconds. 17:00:39 Checkpoint Completed: duration was 1 seconds. 17:02:11 Checkpoint Completed: duration was 2 seconds. 17:03:42 Checkpoint Completed: duration was 1 seconds. 17:05:14 Checkpoint Completed: duration was 2 seconds. onstat -l Informix Dynamic Server Version 7.30.UC5 -- On-Line -- Up 1 days 04:16:14 -- 2078720 Kbytes Physical Logging Buffer bufused bufsize numpages numwrits pages/io P-1 6 16 42208 2875 14.68 phybegin physize phypos phyused %used 700035 210000 29331 6 0.00 Logical Logging Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io L-3 0 16 2358251 370326 301539 6.4 1.2 Subsystem numrecs Log Space used OLDRSAM 2358251 200376220 address number flags uniqid begin size used %used c9c8138 1 U-B---- 60721 401d81 2500 2500 100.00 c9c8154 2 U-B---- 60722 402745 2500 2500 100.00 c9c818c 4 U-B---- 60723 403109 2500 2500 100.00 c9c81a8 5 U-B---- 60724 403acd 2500 2500 100.00 onconfig file # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /dev/rawlinks/rootdbs1 # Path for device containing root dbspace ROOTOFFSET 10 # Offset of root dbspace into device (Kbytes) ROOTSIZE 100000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirroredroot MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS plogdbs # Location (dbspace) of physical log PHYSFILE 420000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 65 # Number of logical log files LOGSIZE 5000 # Logical log size (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server/Optical staging area # System Configuration SERVERNUM 1 # Unique id corresponding to a DynamicServer in stance DBSERVERNAME on_titan # Name of default database server DBSERVERALIASES on_titan2 # List of alternate dbservernames NETTYPE tlitcp,3,50,NET # Configure poll thread(s) for nettype NETTYPE sqlmux,3,50,NET # Configure poll thread(s) for nettype NETTYPE ipcstr,3,50,CPU # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 3 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 1 # Affinity start processor AFF_NPROCS 3 # Affinity number of processorsPSORT_NPROCS 2 # better performance for sort # Shared Memory Parameters LOCKS 800000 # Maximum number of locks BUFFERS 600000 # Maximum number of shared buffers NUMAIOVPS 1 # Number of IO vps PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 230 # Maximum number of logical log files CLEANERS 16 # Number of buffer cleaner processes SHMBASE 0xa000000 # Shared memory base address SHMVIRTSIZE 768000 # initial virtual shared memory segmentsize SHMADD 16384 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 90 # Check point interval (in sec) LRUS 12 # Number of LRU queues LRU_MAX_DIRTY 3 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water markpercentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) DBSPACETEMP tempdbs2 # Default temp dbspaces # DUMP*: # The following parameters control the type of diagnostics information which # is preserved when an unanticipated error condition (assertion failure) occurs # during Dynamic Server operations. # For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No. DUMPDIR /tmp # Preserve diagnostics in

Wayne Trieu <trieu@ti.L-3Com.com> wrote in message news:807t74$ufk$1@nnrp1.deja.com... > Hi all, Hi ! > Can some one please help me how to increase write cache, right > now my cache is about 73% it seem low. I've tried all source recommend I > see on this group, but did not help. May be I done something wrong. I > hope some one can look at my config file and give me some hits. Thanks > in advance for any input and very appreciated. Let me try. Some things in the ONCONFIG can be changed. This changes takes only to params related to write cache. See below... > # Shared Memory Parameters > > LOCKS 800000 # Maximum number of locks > BUFFERS 600000 # Maximum number of shared buffers As far as I know, in 7.30 number of buffers restricted by 500000. > NUMAIOVPS 1 # Number of IO vps If KAIO not used increase this. > PHYSBUFF 32 # Physical log buffer size (Kbytes) > LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 230 # Maximum number of logical log files > CLEANERS 16 # Number of buffer cleaner processes > SHMBASE 0xa000000 # Shared memory base address > SHMVIRTSIZE 768000 # initial virtual shared memory segment> size > SHMADD 16384 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited > CKPTINTVL 90 # Check point interval (in sec) > LRUS 12 # Number of LRU queues Oh! 12 LRUS for 600000 buffers is poor. Set something about 100. Respectively increase CLEANERS to 50 at minimum. > LRU_MAX_DIRTY 3 # LRU percent dirty begin cleaning limit > LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit Increase this. Set 20 and 15 for start. Remember, that LRUs writes decreases write caching. Also remember, that this will cause longer checkpoints. > LTXHWM 50 # Long transaction high water mark> percentage > LTXEHWM 60 # Long transaction high water mark > (exclusive) One more thing. Monitor tables, that have highest number of write operations. You can set TBLSPACE_STATS=1 in your ONCONFIG, found tables with 'onstat -g ppf', set them resident, then turn this param off. Remember, this param decreases your performance on 5-10%. HTH. ------------------------------------------------- With best regards, Yuri Dovgart SAP R/3, Informix technical consultant, Informix Certified Professional, Senior System Consultant System Architecture and High Availability Systems, 'Telecominvest' company Email y_dovgart@tci.ukrtel.net ICQ 39284285

Wayne Trieu wrote: > Can some one please help me how to increase write cache, right > now my cache is about 73% it seem low. I've tried all source recommend I > see on this group, but did not help. May be I done something wrong. I > hope some one can look at my config file and give me some hits. Thanks > in advance for any input and very appreciated. > > My system config as follow: > > Harware: SUN E450 > CPU: 4 > Memory: 3.5gig > OS: Solaris 2.6 > DB: Informix 7.30.uc5.-1 > Veritas Software RAID 5 > > here are some onstat output: > onstat -p > Informix Dynamic Server Version 7.30.UC5 -- On-Line -- Up 1 days > 04:14:28 -- 2078720 Kbytes> > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 5521041 13946279 556670414 99.01 708844 1704464 2649529 73.25 Other people may disagree with me, but FWIW, I don't think you need to worry too much. You are doing 556 M reads for 2.5 M writes, so the read performance dominates. Your read cache is just over 99%, which is excellent. You have your LRU_{MIN,MAX}_DIRTY values set to 1 and 3, which is sensible, too. I'd say you had a reasonably well tuned system unless you perceive a performance problem. Or, in other words, don't take the numbers in the too literally (but don't completely ignore them either!). -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h> PS: No, LRU_{MIN,MAX}_DIRTY have little effect on the cache percentages. Your check points were about 10 seconds, which is a bit long. If necessary, cut those figures from 1,3 to 0, 1.

Wayne Trieu wrote: > > Hi all, > Can some one please help me how to increase write cache, right > now my cache is about 73% it seem low. I've tried all source recommend I > see on this group, but did not help. May be I done something wrong. I > hope some one can look at my config file and give me some hits. Thanks > in advance for any input and very appreciated. I like the comments by Yuri, Jonathan, and Rudy. Jonathan's comment about reads dominating is VERY on target. However, there are still things you can do. Read on. > My system config as follow: > > Harware: SUN E450 > CPU: 4 > Memory: 3.5gig > OS: Solaris 2.6 > DB: Informix 7.30.uc5.-1 > Veritas Software RAID 5 AAAAAAHHHHHHHHHHHH. RAID10 RAID10 RAID10 RAID10 Sorry, but RAID 5 and databases should NEVER be used together in my opinion. If you are seeing performance problems this is most likely the BIGGEST culprit and you data IS NOT SAFE!!!!!!!!!!! See my many posts on why not. The problem is not Veritas but the design of RAID 5. > here are some onstat output: [SNIP] > Message Log File: /usr2/informix/online.log > 16:49:57 Checkpoint Completed: duration was 0 seconds. > 16:51:27 Checkpoint Completed: duration was 0 seconds. > 16:52:57 Checkpoint Completed: duration was 0 seconds. > 16:54:27 Checkpoint Completed: duration was 0 seconds. > 16:55:59 Checkpoint Completed: duration was 2 seconds. > 16:56:32 Logical Log 60732 Complete. > 16:56:33 Logical Log 60732 - Backup Started > 16:56:36 Logical Log 60732 - Backup Completed > 16:57:36 Checkpoint Completed: duration was 7 seconds. > 16:59:08 Checkpoint Completed: duration was 2 seconds. > 17:00:39 Checkpoint Completed: duration was 1 seconds. > 17:02:11 Checkpoint Completed: duration was 2 seconds. > 17:03:42 Checkpoint Completed: duration was 1 seconds. > 17:05:14 Checkpoint Completed: duration was 2 seconds. Despite using RAID5 your checkpoint durations are fine. I don't see where the problem lay except as Jonathan pointed out, don't let the numbers fool you. Most cache applications are happy with ~75% write cache, Informix recommends 85% as a goal, and while Informix has a very good cache and write caches in the mid 90s are certainly possible, 73% is not bad. Continue though, let's see what we can do. [SNIP] > address number flags uniqid begin size used %used > c9c8138 1 U-B---- 60721 401d81 2500 2500 100.00 > c9c8154 2 U-B---- 60722 402745 2500 2500 100.00 > c9c818c 4 U-B---- 60723 403109 2500 2500 100.00 > c9c81a8 5 U-B---- 60724 403acd 2500 2500 100.00 You really should have more logical log space, FWIW. > onconfig file > # Root Dbspace Configuration > > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /dev/rawlinks/rootdbs1 > # Path for device containing root > dbspace > ROOTOFFSET 10 # Offset of root dbspace into device If this offset is because of the Solaris VTOC problem, note that you are using Veritas to partition the logical drives so this offset is not needed. No harm done, just FYI. [SNIP] > # Physical Log Configuration > > PHYSDBS plogdbs # Location (dbspace) of physical log > PHYSFILE 420000 # Physical log file size (Kbytes) Hefty physical log. I guess I will not have to recommend increasing this to support longer checkpoints, see below. > # Logical Log Configuration > > LOGFILES 65 # Number of logical log files AHHH! You truncated the onstat -l output to save our precious bandwidth. Sorry, and thanks. > LOGSIZE 5000 # Logical log size (Kbytes)> > # Optical > > STAGEBLOB # Informix Dynamic Server/Optical > staging area > > # System Configuration > [SNIP] > PSORT_NPROCS 2 # better performance for sort This is a shell environment variable NOT an ONCONFIG parameter. For it to take effect you must set it in the user's environment NOT the server's. I like export PSORT_NPROCS=12 myself, woosh! > # Shared Memory Parameters > > LOCKS 800000 # Maximum number of locks > BUFFERS 600000 # Maximum number of shared buffers Got enough buffers! You are cycling the buffer cache about every 72 minutes which is pretty good. > NUMAIOVPS 1 # Number of IO vps With KAIO and RAW chunks use 2-6 AIO VPs for log and message output (2 are normally enough but monitor onstat -g iov). > PHYSBUFF 32 # Physical log buffer size (Kbytes) > LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 230 # Maximum number of logical log files > CLEANERS 16 # Number of buffer cleaner processes Despite Yuri's recommendation I think your CLEANERS is fine for your LRUS value. Now the LRUS value is another story. [SNIP] > CKPTINTVL 90 # Check point interval (in sec) Here is the REAL culprit. You are checkpointing too often! Set CKPTINTVL between 300 and 600 and you will see write cache in the 90s. > LRUS 12 # Number of LRU queues Informix makes a recommendation about the ideal number of buffers per LRU to improve performance. As Yuri states you don't have enough. See the Performance Guide and Administrators Guide. However, the real problem with too few LRUS is LRU contention between large numbers of concurrent users which will show up in a high bufwaits ratio. Yours is appox. 0.5% which is well below my own threshold of 7% and ceratinly below my panic level of 10% so no worries, just watch it. > LRU_MAX_DIRTY 3 # LRU percent dirty begin cleaning limit > LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit As pointed out, these parameters will also severely limit your write cache percent WITHOUT impacting your actual cache performance AT ALL! Huh? How is that? Because although low LRU_MIN/MAX_DIRTY values WILL cause dirty pages to be flushed more often and reduce the average number of writes between flushes, the flushed pages are STILL in cache SO the actual cache performance is unaffected. Unaffected that is UNLESS there is so much read activity that the need for buffers for reads causes the recently flushed buffer pages to be reused for other data pages before they can be written to again. This is what Yuri seems to be worried about and the only thing that Jonathan missed. However, with 600,000 buffers and a cache turnover rate of 72 minutes this is unlikely so Jonathan's analysis holds. You can mostly ignore these parameters, I do not think upping them will help the cache ratio much and it will not make a performance difference anyway. [SNIP] > # The following are undocumented and needed to increase size of the data > # dictionary cache. These lines must be added manually to the onconfig > file > DD_HASHSIZE 511 # Length of DD Hash The value 5