RE: Arghh! Please Help.

Have you run update statistics medium on your database(s)? Do you get may sequential scans? - problem! Run update statistics medium every day. Look at the slow sqls and run them with set explain on and interrogate the output. The optimiser is completely different and you probably need to alter you database, such as adding appropriate indexes. What is busy in the system? Locate and address this problem. Your root chunk looks to be oversize. I think 2048000Kb - 1 million pages - is the largest chunk size allowed. But this has no effect on performance. IDS 7.3 runs well with plenty of memory - far more than 5.0 ever required. Murray Wood (also see below) -----Original Message----- From: ktroxel@my-dejanews.com [SMTP:ktroxel@my-dejanews.com] Sent: Thursday, April 29, 1999 9:18 AM To: informix-list@iiug.org Subject: Re: Arghh! Please Help. From what you've showed - it doesn't look that bad. I'd say that maybe your LRU queues are a little lean - might also try setting down your min/max dirty and adding more cleaners. Some things that I'd check if you haven't aleady . check system cpu/disk (sar, vmstat, iostat) . check system memory ustilization - look for swapping activity (po) . Look at your checkpoint times in the log . check for foreground writes - increase buffers/decrease lru_max_dirty if needed. good luck - hope this was helpful - kt In article <7f0aei$41g$1@news.xmission.com>, rico@wsx.wsex.com wrote: > > Hello All, > > I have upgraded Online 5.0 to IDS 7.3 while also upgrading from a ALR 6xPentiumPro 200 to a Sun E3500 w/4x336Mhz UltraSparc. The new setup is overall a tiny bit faster for some things, or about twice as slow for a lot of things. > > I have spent a lot of time trying to tune IDS 7.3, but still cannot get the results I would expect. Simple things on the SCO/Online5.0 are about 1.5 to 2 times slower on the SUN/IDS7.3 box. (Before you yell at me OPTCOMPIND=0) > > Here are a few questions I was hoping some of you might be able to help me with: > > 1) On the SCO/Online5.0 box, when I turn on explain processing, a query takes around 2 to 3 times longer, on the SUN/IDS box, a query with explain processing turned on takes around 40 - 50 times as long. Is this normal? [Murray Wood] Update stats medium not run or index problem. > > 2) In the release notes for IDS 7.3 on Solaris 2.6, the kernel parameter "enable_sm_wa = 1" should be set, however my Solaris 2.6 box complains that the parameter "enable_sm_wa" is not defined within the kernel. Could this affect anything? [Murray Wood] No > > 3) I am using Disksuite on the E3500 to software mirror my root disk and my /usr partition which is where the informix installation is located, could this adversely affect performance (I am sure it would a little, but how much?) ? > [Murray Wood] No. Are the chunks on raw devices? > 4) Would having the physical and logical logs residing in the root dbspace effect query performance? > [Murray Wood] Not unless you had a performance issue with that disk(s). > 5) Last question is I have noticed on some queries, IDS7.3 does a dynamic hash join where Online 5.0 does a sort merge. Is there any way for me to force IDS 7.3 to do a sort merge? > > Some final notes, both systems are configured with 512MB main memory, the Sparc processors have a 4MB L2 cache as opposed to the P-Pro's which have a 256k L2. The Intel box uses Fast-UW-SCSI and the E3500 uses internal FC-AL. Both DB's use buffered logging. > > Included below is my IDS 7.3 onstat -c and -p. Thanks in advance. > > Olaf > > PS - I really hope someone can help me because I am going to puke if I cannot get a E3500 w/IDS 7.3 to run faster than 6 Pentium Pros w/Online5.0. > > ------------------------------------------------------------------------ -------- > ------------------------------------------------------------------------ -------- > > Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 00:05:30 -- 311296 Kbytes > > Configuration File: /usr/local/informix/etc/onconfig.use > #************************************************************************** > # > # INFORMIX SOFTWARE, INC. > # > # Title: onconfig.std > # Description: Informix Dynamic Server Configuration Parameters > # > #************************************************************************** > > # Root Dbspace Configuration > > ROOTNAME dbroot # Root dbspace name > ROOTPATH /dev/lrdbroot # Path for device containing root dbspace > ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) > ROOTSIZE 2090000 # Size of root dbspace (Kbytes)> > # Disk Mirroring Configuration Parameters > > MIRROR 1 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH /dev/lrdbroot2 # Path for device containing mirroredroot > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> > # Physical Log Configuration > > PHYSDBS dbroot # Location (dbspace) of physical log > PHYSFILE 8000 # Physical log file size (Kbytes)> > # Logical Log Configuration > > LOGFILES 55 # Number of logical log files > LOGSIZE 12500 # Logical log size (Kbytes)> > # Diagnostics > > MSGPATH /usr/local/informix/online.log # System message log file path > CONSOLE /usr/local/informix/console.log # System console message path > ALARMPROGRAM /usr/local/informix/etc/log_full.sh # Alarm program path > SYSALARMPROGRAM /usr/local/informix/etc/evidence.sh # System Alarm program path > TBLSPACE_STATS 1> > # System Archive Tape Device > > TAPEDEV /dev/rmt/0 # Tape device path > TAPEBLK 32 # Tape block size (Kbytes) > TAPESIZE 12000000 # Maximum amount of data to put on tape (Kbytes)> > # Log Archive Tape Device > > LTAPEDEV /dev/null # Log tape device path > LTAPEBLK 32 # Log tape block size (Kbytes) > LTAPESIZE 12000000 # Max amount of data to put on log tape (Kbytes)> > # Optical > > STAGEBLOB # Informix Dynamic Server/Optical staging area > > # System Configuration > > SERVERNUM 0 # Unique id corresponding to a DynamicServer instance > DBSERVERNAME world # Name of default database server > DBSERVERALIASES # List of alternate dbservernames > DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env. > RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor > NUMCPUVPS 3 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps toone > > NOAGE 1 # Process aging > AFF_SPROC 1 # Affinity start processor