Re: Gradual Performance Drop on 7.23.UC1 x86

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. 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 corresponding to a OnLine instance } DBSERVERNAME nndb # Name of default database server } DBSERVERALIASES # List of alternate dbservernames } NETTYPE tlitcp,1,75,CPU # Override sqlhosts nettype parameters } DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. } RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) } } MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor } NUMCPUVPS 4 # Number of user (cpu) vps } SINGLE_CPU_VP