Informix Database and Performance

What are people doing for optimizing the layouts of their Informix Database instances. How have people addressed factors as disk layouts, Shared Memorey and Table Layouts? We are currently in an effort to try and optimize our Informix instance given the current hardware we are useing. AIX 4.3.2 IBM RS6000 - S70 8-way binding 7 processors to DB 5Gb memory. Disk - SSA D-40, 4 and 9Gb disks. We have 19 disks to play with over 15 Logical Volumes to be used by Informix. What works the best, small pools of disk with data over multiple disks for r/w heads, isolating logical volumes to individual disks, one big pool of disks with data spead over all disks? How about shared memory usage, what works best here? I am monitoring disk and system performance on the unix side with commands like, vmstat, iostat, monitor, what else is there? How can we get to the table level and monitor more on the DB side? What are the best monitoring tools? I ask a wide range of questions here, but want to know what is working well for others so we can all opitimize and learn from each other. Thanks Bryan Manthei Bryan Manthei, Unix System Administrator The Musicland Group, bryan@musicland.com ============================================================= +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

In article <3895C505.411B82D8@musicland.com>, Bryan Manthei <bryan@musicland.com> wrote: > What are people doing for optimizing the layouts of their Informix > Database instances. > How have people addressed factors as disk layouts, Shared Memorey and > Table Layouts? > > We are currently in an effort to try and optimize our Informix instance > given the current hardware we are useing. > > AIX 4.3.2 > IBM RS6000 - S70 8-way binding 7 processors to DB 5Gb memory. > Disk - SSA D-40, 4 and 9Gb disks. We have 19 disks to play with over 15 > Logical Volumes to > be used by Informix. Ooooooo, ahhhhhhh..... Sorry, is the drool apparent? > > What works the best, small pools of disk with data over multiple disks > for r/w heads, isolating logical volumes to individual disks, one big > pool of disks with data spead over all disks? > Hard to say without knowing how the data is to be accessed. In general, for OLTP systems, it's best to fragment the data by expression over several physical disks. Another option is to use RAID and put the data in a black box. I've never actually done it, but I've seen reports that say that performance can be quite a bit better if it's configured correctly. It is more difficult to get it right, though. I used to have a link to a document done by someone at Informix on the subject, but I've since lost it. Perhaps someone else in this group knows what I'm talking about and can supply the doc. > How about shared memory usage, what works best here? Give it all you can! I usually shoot for about 75 - 90% of what's available on the box, if it's dedicated as an Informix server with no other major apps running on it. > > I am monitoring disk and system performance on the unix side with > commands like, vmstat, iostat, monitor, what else is there? How can we > get to the table level and monitor more on the DB side? What are the > best monitoring tools? I don't know, nobody ever give me the budget for tools... grrr. I've always used the onstat commands to gather information like that. There are too many options to go into, but it's in you databse admin's manual. > > I ask a wide range of questions here, but want to know what is working > well for others so we can all opitimize and learn from each other. > > Thanks > Bryan Manthei > > Bryan Manthei, Unix System Administrator > The Musicland Group, bryan@musicland.com > > ============================================================= > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

Bryan Manthei wrote: Hi, Bryan, check out the Informix Performance Guide, my article on tuning in Informix TechNotes (vol 8, issue 3), Joe Lumbley's book Informix DBA Survival Guide, 2nd ed., and any of several other good books on performance tuning. > What are people doing for optimizing the layouts of their Informix > Database instances. > How have people addressed factors as disk layouts, Shared Memorey and > Table Layouts? Peruse the archives of this newsgroup, found at the IIUG WEB SITE (www.iiug.org) there have been many posts and responses to similar questions. > We are currently in an effort to try and optimize our Informix instance > given the current hardware we are useing. > > AIX 4.3.2 > IBM RS6000 - S70 8-way binding 7 processors to DB 5Gb memory. > Disk - SSA D-40, 4 and 9Gb disks. We have 19 disks to play with over 15 > Logical Volumes to > be used by Informix. > > What works the best, small pools of disk with data over multiple disks > for r/w heads, isolating logical volumes to individual disks, one big > pool of disks with data spead over all disks? What generally works best is to set up at least 3 independent disk pools (ie separate sets of spindles) one small stripe for rootdbs and logical and physical logs, one wider stripe for high activity tables and a third for low activity tables. Lookups and other very low activity tables can usually live anywhere. I always recommend setting the disks up as RAID10 arrays so all data is fully mirrored and performance is maximized. > How about shared memory usage, what works best here? More. I don't understand the question. > I am monitoring disk and system performance on the unix side with > commands like, vmstat, iostat, monitor, what else is there? How can we > get to the table level and monitor more on the DB side? What are the > best monitoring tools? You can turn on table level I/O stats in the engine with the TBLSPACE_STATS ONCONFIG parameter, then use onstat -g ppf to monitor. This is expensive, however, so you will have to turn it off most of the time and use it for occassional monitoring. > I ask a wide range of questions here, but want to know what is working > well for others so we can all opitimize and learn from each other. Art S. Kagel