Re: Gradual Performance Drop on 7.23.UC1 x86

In article <68u944$kng@cssun.mathcs.emory.edu>, Mark Collins <mcollins@us.dhl.com> writes >I know it's been a while since the original posting, but the holidays and all >sort of got in >the way. > >Anyway, I see that someone has recommended setting OPTCOMPIND=0, which is >usually a good >idea. Another thing that I see from your onconfig is that DBSPACETEMP points to >your root >dbspace. Try creating another dbspace, with no mirroring and with the temp flag >set so that >no logging is performed. As it currently stands, the temporary tables are stuck >in with some >heavily used stuff in the rootdbs, and all I/O against the temporary tables is >logged because >of the dbspace. > Try increasing CLEANERS to 8 to even 10. Check the FAQ for why this should be done. Also try decrasing LRU_MIN_DIRTY and LRU_MAX_DIRTY to 1 and 2 (or even 0 and 1). Any chance you could e-mail us the onstat -a output? >I see that the physical log is in its own dbspace (good). Are the logical logs >also in a >space of their own? > >Since the size of one of the tables "grows and shrinks (60K rows a day)", how >large was the >table when UPDATE STATISTICS was run? If the table was small at that time, then >it could be >using an access path that is not appropriate for larger volumes. > >How are your LRU writes vs. chunk writes? Run "onstat -F" to check. If most of >the writes >are chunk writes, you may want to reduce your LRU_MAX_DIRTY and LRU_MIN_DIRTY so >that more >I/O is performed between checkpoints. > > > > >Mark Collins >mcollins@us.dhl.com > >The problem lies in how easily and dangerously we forget that >manipulating things is not the same as understanding them. > >------------------------ > From: The Dad <scottlee@mindspring.com> > Subject: Gradual Performance Drop on 7.23.UC1 x86 > Date: 18 Dec 1997 12:20:47 -0500 > To: informix-list@rmy.emory.edu > > >} We are having some performance problems that increase gradually over time. >} >} We just recently upgrade to 7.23.UC1 for x86 that solved the majority of >} our problems, but this one remains. As such, I believe that it is a >} configuration problem but I can't figure out where it is. >} >} We are running 7.23.UC1 for x86 on Solaris 2.5.1 256Meg, 4 Seagate 9Gig >} drives that are mirrored. This system stores the data for a communications >} platform (fax, audio, email), including scheduling data, client >} information, configuration parameters, image and audio BLOBS, etc... >} There are 3 tables that are heavily accessed; one that grows slowly (500 >} rows a day) and is updated 60K times a day, one that grows and shrinks (60K >} rows a day) and is updated 100K times a day, and one that grows quickly >} (about 100K rows a day). All of these tables have their extents sized >} appropriately. The second table is sized so that it is never larger than a >} single extent. Almost all access to the database is made through programs; >} this is an automated system. There are also only a few programs connecting >} to the system (maybe 12-15 connections). >} >} The second table is where the problems occur. When Informix is >} started all is well. Queries are fast and checkpoints are in the >} 0-2 second range, whether things are busy or idle. As time goes on and the >} table expands and then finally empties, things are a bit slower. When it's >} busy, the checkpoints are taking in the 4-5 second range, but when the >} table empties, it's again down in the 0 range. If allowed to run long >} enough, though, it really starts to bog down. Checkpoints, when busy, will >} 10 seconds. When the system is idle, it will take 2 seconds to run a >} "SELECT * from the-table" query. >} >} We found that we could get some relief from this by idling our client >} processes and creating and dropping a clustered index on the table. This >} improved performance somewhat, as did running an "UPDATE STATISTICS" on the >} table, but while this helps for a while (or seems to) it will eventually >} slow down. Ultimately what fixes things for another 5-7 days is to bring >} Informix down, start it back up, create the cluster index and then drop it. >} It is now up to its normal, speedy, self. >} >} I have looked at various results from onstat and they seem reasonable. It >} is the gradual degradation that puzzles me. If my tables were changing >} dramatically I could also understand, but other than some days being >} heavier than others, the table access is pretty much the same every day. >} Oninit is not allocating additional memory, there are no extents being >} added, and there are no additional file descriptors or items that are OS >} resources being exhausted. >} >} I would appreciate any pointers that you can give me. I am including our >} onconfig file in case I have done something REALLY obvious. >} >} Thanks, >} >} >} >} #************************************************************************** >} # >} # INFORMIX SOFTWARE, INC. >} # >} # Title: onconfig.std >} # Description: INFORMIX-OnLine Configuration Parameters >} # >} #************************************************************************** >} >} # Root Dbspace Configuration >} >} ROOTNAME nikoroot # Root dbspace name >} ROOTPATH /nn/db/dev/disk100 # Path for device containing root dbspace >} ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) >} ROOTSIZE 502784 # Size of root dbspace (Kbytes) >} >} # Disk Mirroring Configuration Parameters >} >} MIRROR 1 # Mirroring flag (Yes = 1, No = 0) >} MIRRORPATH /nn/db/dev/disk110 # Path for device containing mirrored root >} MIRROROFFSET 0 # Offset into mirrored device (Kbytes) >} >} # Physical Log Configuration >} >} PHYSDBS log0 # Location (dbspace) of physical log >} PHYSFILE 4800 # Physical log file size (Kbytes) >} >} # Logical Log Configuration >} >} LOGFILES 4084 # Number of logical log files >} LOGSIZE 512 # Logical log size (Kbytes) >} >} # Diagnostics >} >} MSGPATH /nn/db/Log # System message log file path >} CONSOLE /dev/console # System console message path >} ALARMPROGRAM /nn/db/alarm # Alarm program path >} >} # System Archive Tape Device >} >} TAPEDEV /nn/db/dev/tapeb # Tape device path >} TAPEBLK 32 # Tape block size (Kbytes) >} TAPESIZE 5000000 # Maximum amount of data to put on tape >(Kbytes) >} >} # Log Archive Tape Device >} >} #LTAPEDEV /nn/db/dev/tapel # Log tape device path >} LTAPEDEV /nn/db/dev/tapel # Log tape device path >} LTAPEBLK 32 # Log tape block size (Kbytes) >} LTAPESIZE 5000000 # Max amount of data to put on log tape >(Kbytes) >} >} # Optical >} >} STAGEBLOB # INFORMIX-OnLine/Optical staging area >} >} # System Configuration >} >} SERVERNUM 0 # Unique id