Database tuning.. any hints?

In Message-Id: <jockcD07p3y.D0q@netcom.com> jockc@netcom.com (jockc) writes: > 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. Also the tbstat -p program reports about a 95-97% cache > hit rate on reads but less than 20% on writes.. 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.. I've made a > few changes as suggested by the ref manual tuning section and gotten > some improvement. Can anyone suggest anything I can do. Do I > need to provide more info about my tbconfig settings? > > Thanks in advance for any help. > > -------------------+---------------------------------------------------------- > finger for PGP key | "Thufir, old friend," Paul said, "as you can see my back > | is to no door." > jockc@netcom.com | "The universe is full of doors," Hawat said. Before you go very deep at all into the "tunable parameters" you should examine your application. The most important aspect of the application is the index strategy. Examine the source code and the user's habits, and ensure that the indexes meet all the heavy requirements, but not any more. Too many indexes will consume more time than they gain. The parameters you mention (and most of the others, too) are harder. To really, properly set them you must have a very good understanding of the application, the platform, and the users. Are your users mostly reading, mostly writing, half & half? This knowledge gives meaning to the cache statistics. How large is your largest block alter? This provides a key to the LOCKS question. ;) Also there is the hardware itself. How is your data spread across how many disks, on how many controllers? Where is your bottleneck, from a h/w systems standpoint: I/O (disk, controller), CPU, memory, virtual memory, network traffic, screen refresh rate, et cetera, et cetera, et cetera. (Quoth the King of Siam.) Perhaps you could add to the feature request list we're building for informix and request a WFO tunable parameter. It would come set to 0%, and be of integer size (to measure a *percent* of user's expected performance, of course!) Then as the users wanted more speed the DBA could just increment that variable! Really, though. Most of performance tuning is just plain work, to understand what your users are doing with the application on the machine. Good luck, Hi-Ho, Hi-Ho, ... __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|