RE: Write cache rate

I have often wondered if there are any detrimental effects (other than excessive OS paging and/or swapping) of having "too many buffers". I have many machines that need a large number of buffers overnight(batch operations, largre reports etc) and then use a very small number during the day(OLTP and small precise? Queries). I have therefore been monitoring cache rates,BR, BTR, RAU over 20 minute periods for long period. I have also looked at other OS parameters but as yet I don't have a common system for both types of monitoring. And so far I have looked at two different application software packages. I can see no evidence that having too many buffers has a detrimental effect. I will be extending my research into this in due course. The figures I have collected include all sysmaster:sysprofile measurements so I have the ability to look at comparisons. I also cope with any of the stats going negative. When I get some more time I will be looking more closely at the impact of read/write ratio on caching rates, Art's figures, and other performance measurements. Regards Malcolm -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Christopher Coleman Sent: 01 December 2005 21:33 To: jleffler@earthlink.net; Doug Conrey Cc: informix-list@iiug.org Subject: RE: Write cache rate Doug Conrey wrote: > I have a question about tuning the write cache %. I have an OLTP > database running IDS 9.40 on AIX 5.2. There are 300,000 buffers > configured (about 30% of physical memory -- 4 GB), and the read cache > rate is pretty consistently 98.5 - 99.0%, but the write cache rate > hovers between 70 and 75%, which seems low to me. Jonathan Leffler replied: > Not everybody agrees with me, but in my view, if the total number of > writes is much less than the total number of reads - which is usually > the case - the write hit ratio isn't all that important. The only > issue is 'how much less' - and it depends on your system. I frequently agree with Jonathan. In this case, he makes the key comment, "it depends on your system." My company provides an application with Informix embedded to about 300 customers. The app is primarily OLTP but with some DSS components in the same database. We have done quite a bit of tuning at various sites, because each has unique requirements. Most of our customers, though, find that, by the time they have finished tuning their system to correctly get the read cache rate high, the write cache rate is down where yours is. Trying to tune the write cache rate is usually futile. Generally, it takes too much DBA effort for too little performance gain, not to mention the frustration of starting and stopping IDS. So, your instinct was right that you "don't want to twiddle settings that are unlikely to help." However, it is this whole symptom which may be a non-issue for you. The read cache rate has far greater user impact than the write cache rate, especially on a system which does a lot of inserts. That said, Malcolm made a good recommendation with Art Kagel's calculations. Having your RA_PAGES or BUFFERS miscalculated could have other performance impact which your users may be feeling, besides the read cache rate. The nice thing is that these tools can help you make the decision, yourself. As I said, our application is mixed OLTP and DSS. This is why some of our customers frequently need BUFFERS set to 25-30% of the physical memory in order for nightly reports and data mining type operations, though, during the day, when more OLTP activity is going on, this is too much. Since the instance is 24*7, they use the same onconfig all day long. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc. sending to informix-list sending to informix-list

The only things I will say are a) is that you should fill the larger buffer cache with data that can be re-read. With a very large buffer cache clearly some of this will be re-read rarely so the performance increase from more caching tails off. b) IDS 9.40 and 10 allow fractional LRU_MIN/MAX_DIRTY to ensure that at checkpoint time the maximum amount of data to write can be controlled better. Calculate how large you want the maximum checkpoint duration will to be (figure a) Work out how much disk i/o you can sustain per second (figure b). Then use LRU_MAX_DIRTY to ensure that BUFFERS*buffer size * LRU_MAX_DIRTY i.e. max dirty buffer memory < a*b. b) IDS 10 has shorter code paths and i would assume better the best buffer management code of all the releases. So consider IDS 10. c) Make sure that you have enough LRUs to reduce contention on the spin locks on the LRU queues. Monitor with onstat -g spi (redirect to a file). You do not want any of the the lru-NN spin locks in the top 20-30 in terms of num loops. d) For IDS 9 check the new b-tree cleaner stuff and tune appropriately. I believe www.ardenta.com have a white paper on this (www.ardenta.com) but I can't find it right now....Neil? Any chance of a URL? Obviously as the buffer cache gets truly huge the increase in performance from better disk caching per gigabyte of memory added go down and the cost of buffer management go up. Eventually they must cross somewhere but I doubt you will be paying for that much memory. Very few people are going to cache a >10Gb database completely in memory! Obviously the increase in buffer cache size does nothing until the cache is filled. A cold cache will still do lots of disk i/o until you get everything in memory! David.

<david@smooth1.co.uk> wrote in message news:1133738969.219926.44970@f14g2000cwb.googlegroups.com... > > > d) For IDS 9 check the new b-tree cleaner stuff and tune appropriately. > I believe www.ardenta.com have a white paper on this (www.ardenta.com) > but I can't find it right now....Neil? Any chance of a URL? http://www.informix-support.co.uk/btscanner.htm