RE: Serious performance issues

There appears to be HUGE contention with this application. This is an application issue. large number of latch waits (wait for OS resource - see admin guide on latches) large number of lock waits Do you have lock mode row? (not the default) You have a large number of sequential scans. What are these? Either you have alot of small tables (with no indexes) which are regularly accessed, you are missing indexes, or you have not run update statistics correctly. If you look in the sysdistrib table, select a table (tabid from systables) and are there current distribution stats for each column on that table in sysdistrib? Read cache looks low (mine are in 98 and 99%). Your number of buffers looks low in proportion to the volume of data. (Should be increased if you have memory available). How bug do you need SHMVIRTSIZE? does the onstat -g seg type "V" ever reduce its blkfree to a small value? At the time of the sample you were only using half the 200Mb SHMVIRTSIZE. If you can, lower it to allow more buffers. I suggest you turn OFF read aheads - as this consumes buffers - until the application is running reasonably well. The referential integrity will have an effect only when inserting rows into a referencing table, updating columns, and I would expect it to be small in comparison to inserting a row. Post also onstat -g iof section of informix messages log You can monitor slou sessions from onstat -u with onstat -r 1 -g ses {session number} > outputfile Select slow sqls and put them through set explain on to determine the cost, the cause and the solution. But I think you need to look closer at the cause of sequential scans!! Regards Murray Wood -----Original Message----- From: Bill Weaver [SMTP:billw@fscorp.com] Sent: Saturday, June 05, 1999 4:06 AM To: informix-list@iiug.org; Murray Wood Subject: RE: Serious performance issues Database is more relational and table driven than before. It was quite an extensive redesign BUT the actual tables are similiar to the previous design so logically there wasn't much that changed. We have a Sequent Symmetry SE40 with 8 processors and 1 Gig of memory. There are lots of disk which are mirrored stripped pairs - 43 of these stripes are used for Informix (all 2 Gig stripes so there are 43 dbspaces with 1 chunk per space). OS is Dynix/ptx 4.4.2. IDS 7.30.UC3, 4GL 7.20.UD1X1. We've done some pretty extensive analysis on indicies, especially on problem programs, and everything looks good there. I won't say it is perfect yet, but most queries are using an index path. Right now, there are 252 users - 149 using 4gl applications over shared memory connections and 103 using a client/server application over network connections. This can be as much as the low 300's for total users. Yes we are using KAIO. Yes we've updated statistics (I use Art Kagel's dostats program to update statistics on a regular basis). Attached are all the onstats yourequested. As an example, we rebooted the box last night to try and help things out. After the reboot, there were 5 major processes that we started up to run overnight. Those 5 processes brought the system to 0% idle time which NEVER happened before. One thing I forgot to mention in the last email, the only other major change was that we introduced referrential integrity via primary/foreign key constraints which was not on the previous database. We've had several issues with that already (mostly locking related issues where the application couldn't get a lock to verify referrential integriy in time - we had to introduce set lock mode to wait X in many of our programs to get around this) and I'm still wondering if the overhead this generates is the source of our performance problems --- On Fri, 4 Jun 1999 10:49:26 +1200 Murray Wood <murray@quanta.co.nz> wrote: What is the effect of the application / database redesign? You dont say what hardware configuration, OS, Informix versions .... If IDS, have you run update statistics medium? Do you now have the right indexes for the new application? Can you trace a slow sql from onstat -g ses {number} and put it / them through set explain on? Number of users? KAIO? Post: onstat -p onstat -g seg onstat -g ioq onstat -F onstat -Ronconfig onstat -d Regards Murray Wood -----Original Message----- From: Bill Weaver [SMTP:billw@fscorp.com] Sent: Friday, June 04, 1999 3:54 AM To: informix-list@iiug.org Subject: Serious performance issues In the past few weeks, we went through a major upgrade of our database where we completely redesigned a large portion of the database structure, migrated the data, and updated the applications. Since that period, we've had serious performance problems. We went from a system that averaged 30-40% cpu idle time to one that stays 0-5% idle (more often at 0% idle during the day). I have to believe the problem is inefficencies in the new database structure as that is what changed. However, I'm at a total loss as to how to find where these inefficencies are! I/O is more evenly spread out on this new system than on the old so it isn't an I/O problem (which is also supported by the sudden increase in cpu utilization). The applications (4GL code) are the same applications, the only changes made to them were to support the new structure - no major logic changes were made - yet they themselves perform worse under the new system. Anybody have any advice as to how to track down what's causing this and eliminate it? Any areas that I need to be looking at? One addition we made to this new structure that wasn't in the old was that we added referrential integrity utilizing foreign keys. Can the additional overhead from referrential integrity checks cause or contribute to our performance woes? I've considered eliminating the foreign keys (one key table is referenced by almost every other table in the system for example) and replacing them with regular indexes. Would this help? One interesting thing I've noticed is that even during periods of 0% idle time, one of my cpuvps is still performing busy waits and semops. There is stuff sitting on the ready queue (onstat -g rea) but for some reason it isn't picking it up. And, it is always the same processor that has these busy waits/semops. The other 5 cpuvps have 0 in busy waits and semops so they are staying fulling utilized (I have an 8 processor system - 6 are dedicated and affinitied to a production instance of Informix, 1 to a development instance of Informix, and the last one is left free for OS - it actually is the first physical processor). Following is the output of onstat -g sch: vp pid class semops busy waits spins/wait 1 22836 cpu 0 0 0 2 22937 adm 0 0 0 3 22938 cpu 0 0 0 4 22939 cpu