Re: Database tuning.. any hints?

>From: jockc@netcom.com (jockc) >Subject: Database tuning.. any hints? >Date: Sat, 3 Dec 1994 01:50:22 GMT >X-Informix-List-Id: <news.10099> >I'm very new to Informix and I'm trying to improve performance >for an Informix application we are running. The application is >Quintus and each process is consuming about 10 megs of virtual >memory on an HP 9000 server. Of the 10 megs about 6meg is private >data. This is almost certainly an issue of how (whether) the application releases cursors and prepared statements, and can probably only be fixed by modifying the source and recompiling. >Also the tbstat -p program reports about a 95-97% cache >hit rate on reads but less than 20% on writes. This is not necessarily bad, despite what the manuals imply. One of the systems I implemented was used very extensively for queries, with only occasional changes being made to the database, apart from a weekly batch update from an external data source (supplied by an independent company). This system would routinely run with >90% read cache and 0% write cache efficiency. I wasn't concerned because the ratio of reads to writes was over 100:1. So, when looking to tune your system, inspect the ratio of the reads and the writes: if it is of the order of 10:1 (or lower) then you have cause for concern, but if (as I suspect) it is nearer 100:1, then this is just a normal side-effect of having a low write rate on an efficiently tuned system. >I have BUFFERS >set at 1000, LOCKS is about the same, and the clean MIN/MAX values >(can't remember the parameter names) are 70 and 80. BUFFERS is obviously OK, though increasing it will give some improvement in the OnLine performance (possibly at the expense of the rest of the system if you don't have enough physical memory and the shared memory is permanently resident). LOCKS depends on the number of users -- if the ovlocks statistic is non-zero then increase the number of them. If these are LRU_MIN_DIRTY and LRU_MAX_DIRTY, I'd be inclined to lower them, perhaps as far as 10 and 20, but look at the output from tbstat -F to see when your write activity occurs and whether that matters. It probably doesn't. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>