Re: Arghh! Please Help.

rico@wsx.wsex.com wrote: First, if you post from a browser please manually wrap your lines for those who do not read the newsgroup from a browser that auto-wraps. > 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) IDS 7.3 is SLIGHTLY slower that 5.xx on the same hardware for simple queries and few users. Increase query complexity, database size, and/or number of users and IDS7.3x will blow OL5.xx away. That still may not explain your results. First thing, did you update statistics according to the recommendations given in the release notes for 7.2x or did you just do "UPDATE STATISTICS" as you did in 5.xx or worst yet nothing at all? IDS is VERY dependent on statistics for the optimizer to make good decisions. GIGO definitely applies. You can get my dostats.ec utility which automatically implements the optimal set of statistics commands. This is available from the IIUG Software Repository as part of the package named utils2_ak (a VERY early version is in utils_ak get the latest from utils2_ak). > 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? Sounds like filesystem performance problems writing the sqexplain.out file to me. > 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? Is it possible you are missing some kernel patches? > 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?) ? Not at all. If you are using mirroring that should improve performance. > 4) Would having the physical and logical logs residing in the root dbspace effect query performance? YES definitely. Rootdbs, LLogdbs, and PLogdbs should ideally be on separate physical disks and the logical logs should ideally be on a separate controller for safety and speed. Otherwise if you loose a controller you cannot write out either the data pages or the log entries, you are hosed. > 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? There is OPTCOMPIND=0 which you already have. I think this gets back to UPDATE STATISTICS again. Post some of the offending queries and sqexplain output if update stats does not help. > 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. Did you have 128000 buffers configured in 5.xx on SCO? Your engine is using >310MB of memory for resident and virtual shared segments alone! Have you checked for system swap activity? > 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 ^^^^^^^^^^^^^AHHHH!!!!! NEVER USE a real device/file name for chunks ALWAYS use symbolic links placed in a directory/FS OTHER than /dev! Many many future headaches will be prevented if you do this. > 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 mirrored ^^^^^^^^^^^^^^Same comment as for ROOTPATH. > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> > # Physical Log Configuration > > PHYSDBS dbroot # Location (dbspace) of physical log As above this should be another dbspace not dbroot. > 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 Looks like you installed the software in /usr/local/informix may I suggest that you move it to a subdirectory named by version, ie /usr/local/informix/ifmx7.30UC2, so that you can more easily upgrade when the time comes and easily be able to rollback if there is a problem with the upgrade. > 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 Please do not displose of your logfiles! You will surely be sorry later. You are running log_full.sh as the Alarm Program this will backup the logs using On_Bar but the /dev/null here is disposing of them before that can happen. > LTAPEBLK 32 # Log tape block size (Kbytes) > LTAPESIZE 12000000 # Max amount of data to put on log tape (Kbytes)> >