Re: Application performance

With just 56000 odd page writes against 4million odd page reads in 16 hours(?), excessive reads could be causing your problems. Try changing the onconfig parameter OPTCOMPIND to 0. Under OLTP (especially with MEDIUM stats), this could help by pointing queries towards nested-loop joins (i.e index usage). You will have to bounce your engine for the parameter to take effect. The fact that you have FG Writes (onstat -F) is not a good sign. It could be related to read inefficiency because of OPTCOMPIND. On the other hand, it could mean that you need to increase BUFFERS. How much memory does your box have? Also, could you post onstat -p, onstat -u (in full), onstat -g ses (in full). Rudy bwhite@deroyal.com wrote: > Hello > > I upgraded from SE to IDS 7.31.UC5 at the end of June. I have four > large applications that run at certain times during the month. These > applications take twice as long to run on IDS than SE! I did not want > to post until I did some performance tuning with the help of various > resources & I wanted to get more comfortable with IDS. I have made many > changes, and feel good about my monitoring statistics (Bufwait ratio, > Buffer Turnover, IO, Checkpoint duration (around 5 during peak, as high > as 8, but not very often), etc). I have not mastered the art of update > statistics (high on specific columns etc), so I do an update statistics > medium on the entire database nightly. I have included various onstats > (although I'm sure I left some important ones out). I reset stats > nightly. I've tried many of the suggestions of this group, and they've > helped my onstats, but not my applications. Nothing jumps out in the > set explain of the applications. I am new to IDS in production, so> please be gentle. > > ...

In article <398743A9.901FC298@americasm01.nt.com>, Rudy Fernandes <rferdy@americasm01.nt.com> wrote: > With just 56000 odd page writes against 4million odd page reads in 16 > hours(?), excessive reads could be causing your problems. > > Try changing the onconfig parameter OPTCOMPIND to 0. Under OLTP (especially > with MEDIUM stats), this could help by pointing queries towards nested-loop > joins (i.e index usage). You will have to bounce your engine for the > parameter to take effect. > > The fact that you have FG Writes (onstat -F) is not a good sign. It could > be related to read inefficiency because of OPTCOMPIND. On the other hand, > it could mean that you need to increase BUFFERS. How much memory does your > box have? 3 G memory. I considered the FG writes relatively low (I've read messages in this group before that said sometimes it's virtually impossible to completely eliminate FG writes. My FG writes are .02 % of my total writes, is this too high? > > Also, could you post onstat -p, onstat -u (in full), onstat -g ses (in > full). Sorry, I missed the paste... onstat -p Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 1 days 01:40:19 -- 336448 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 4998586 5317163 216746561 97.69 400311 538439 4911166 91.85 isamtot open start read write rewrite delete commit rollbk 227288286 748153 27239316 67637713 3669800 86745 77100 275969 4 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 58 16970.52 7824.27 77 170 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 809483 0 151762735 0 0 52 24342 105517 ixda-RA idx-RA da-RA RA-pgsused lchwaits 945343 25131 897353 1866197 7052 > > Rudy > > bwhite@deroyal.com wrote: > > > Hello > > > > I upgraded from SE to IDS 7.31.UC5 at the end of June. I have four > > large applications that run at certain times during the month. These > > applications take twice as long to run on IDS than SE! I did not want > > to post until I did some performance tuning with the help of various > > resources & I wanted to get more comfortable with IDS. I have made many > > changes, and feel good about my monitoring statistics (Bufwait ratio, > > Buffer Turnover, IO, Checkpoint duration (around 5 during peak, as high > > as 8, but not very often), etc). I have not mastered the art of update > > statistics (high on specific columns etc), so I do an update statistics > > medium on the entire database nightly. I have included various onstats > > (although I'm sure I left some important ones out). I reset stats > > nightly. I've tried many of the suggestions of this group, and they've > > helped my onstats, but not my applications. Nothing jumps out in the > > set explain of the applications. I am new to IDS in production, so> > please be gentle. > > > > ... > > Sent via Deja.com http://www.deja.com/ Before you buy.

bwhite@deroyal.com wrote: > > it could mean that you need to increase BUFFERS. How much memory > does your > > box have? > > 3 G memory. > I considered the FG writes relatively low (I've read messages in this > group before that said sometimes it's virtually impossible to > completely eliminate FG writes. My FG writes are .02 % of my total > writes, is this too high? I don't think that's quite right. FG Writes can, and must, be eliminated. They are an indication of one or more of the following 1) Extreme shortage of BUFFERS 2) A poorly performing I/O sub-system With 3GB floating around, I think that you must consider doubling your BUFFERS (at least, for now). But your problems may be related to your Optimizer, as Zandy suggested. The number of seqscans in your "onstat -p" output lends weight to that. As a first step, I'd suggest the following : 1. Set OPTCOMPIND=0 in your onconfig file. 2. Double your BUFFERS from (the measly 120Mb) to 120000, at least. If you are worried about checkpoint duration becoming too long, halve the value of LRU_MAX_DIRTY and LRU_MIN_DIRTY. All the best. Rudy

In article <3988638B.65569683@americasm01.nt.com>, Rudy Fernandes <rferdy@americasm01.nt.com> wrote: > bwhite@deroyal.com wrote: > > > > it could mean that you need to increase BUFFERS. How much memory > > does your > > > box have? > > > > 3 G memory. > > > I considered the FG writes relatively low (I've read messages in this > > group before that said sometimes it's virtually impossible to > > completely eliminate FG writes. My FG writes are .02 % of my total > > writes, is this too high? > > I don't think that's quite right. FG Writes can, and must, be eliminated. > They are an indication of one or more of the following > 1) Extreme shortage of BUFFERS > 2) A poorly performing I/O sub-system > > With 3GB floating around, I think that you must consider doubling your > BUFFERS (at least, for now). > > But your problems may be related to your Optimizer, as Zandy suggested. > The number of seqscans in your "onstat -p" output lends weight to that. As > a first step, I'd suggest the following : > > 1. Set OPTCOMPIND=0 in your onconfig file. I set OPTCOMPIND=0 and, my application hasn't improved. > 2. Double your BUFFERS from (the measly 120Mb) to 120000, at least. I'm AIX, so my BUFFERS are actually 240M, but I'll still double them. If you > are worried about checkpoint duration becoming too long, halve the value of > LRU_MAX_DIRTY and LRU_MIN_DIRTY. Informix always suggests only changing one parameter at a time (which obviously if I was relying just on Informix, I wouldn't be seeking help from the group). I consider 4 or so parameter changes to related items pretty safe. (I'm getting braver every day). Next downtime I plan the following changes... Double Buffers. Raise LRU & CLEANERS from 60 to 128. Lower MAX & MIN to 1 & 0 Increase the size of my V portion of shared memory by about 25% or so. Is this too much at one time? (I also plan an upgrade to 7.31.UC6 ASAP, but after these parameter changes) Thanks Bryan > > All the best. > > Rudy > > Sent via Deja.com http://www.deja.com/ Before you buy.

bwhite@deroyal.com wrote: > > 2. Double your BUFFERS from (the measly 120Mb) to 120000, at least. > > I'm AIX, so my BUFFERS are actually 240M, but I'll still double them. > > If you > > are worried about checkpoint duration becoming too long, halve the > value of > > LRU_MAX_DIRTY and LRU_MIN_DIRTY. > > Informix always suggests only changing one parameter at a time (which > obviously if I was relying just on Informix, I wouldn't be seeking help > from the group). I consider 4 or so parameter changes to related items > pretty safe. (I'm getting braver every day). > > Next downtime I plan the following changes... > > Double Buffers. > Raise LRU & CLEANERS from 60 to 128. > Lower MAX & MIN to 1 & 0 > Increase the size of my V portion of shared memory by about 25% or so. > > Is this too much at one time? Seems reasonable. Buffers and LRU_MAX/MIN go hand in hand (because MAX & MIN are percentages). Equivalence is maintained by changing them in inverse proportion of each other. If Buffers double, halve LRU_MAX/MIN to 2 and 1, unless you want to improve your checkpoint times further. For the batch processes that you talk about (read 9m, write 5m), it could be beneficial to increase your writes at checkpoint - i.e leave LRU_MAX/MIN at 4/2 (although your I/O sub-system may be poor enough for LRU_MAX/MIN to be inconsequential) You still have to get to the bottom of your I/O sub-system problems (FG Writes). Try the following : 1. onstat -R | tail -3. During a busy time, check if cleaning is keeping up. 2. Investigate the table(s) that are high-hit. (you do need TBLSPACE_STATS to be set to 1 for this). Initialize your stats (onstat -z), wait for a few "busy" hours to pass, then run the following SQL. select tabname[1,18], ti_nrows Rows, bufreads, bufwrites, seqscans, round((bufreads/ti_nrows),2) HitRatio from sysptprof a, systabinfo b where ti_partnum = partnum and dbsname = "<your_db>" and ti_nrows > 50 order by 6 desc; Update Statistics high on small, high-hit tables.If a table with a significant number of rows has a large number of sequential scans, you will need to talk to your developers about access methods & indexes. 3. Table being inserted into. Is it being recreated from scratch everytime? If not, make sure that the Indexes, at least, are recreated (disable/enable). If it is being recreated, configure extent sizes appropriately. Have any new indexes been added to this table recently? If possible, disable all indexes during the "load", enabling them at the end. Slog on. Rudy