Re: Arghh! Please Help.
>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?
I don't really know Sun, but it doesn't sound right.
>4) Would having the physical and logical logs residing in the root
dbspace effect query performance?
Depends on what else is happening on the box. But move them out,
anyway.
>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?
Normally, I would say OPTCOMPIND=0, but since you've already done
that, may I say UPDATE STATISTICS?
>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.
1. How many users?
2. Is it just queries that are suffering?
>---------------------------------------------------------------------
>
>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 rootdbspace
Is this a raw device?
>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)
I'd move this.
># 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 aDynamic Server 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 formulti-processor
>NUMCPUVPS 3 # Number of user (cpu) vps
>SINGLE_CPU_VP 0 # If non-zero, limit number of cpuvps to one
>
>NOAGE 1 # Process aging
>AFF_SPROC 1 # Affinity start processor
>AFF_NPROCS 3 # Affinity number of processors>
># Shared Memory Parameters
>
>LOCKS 20000 # Maximum number of locks
>BUFFERS 128000 # Maximum number of shared buffers
>NUMAIOVPS 1 # Number of IO vps
>PHYSBUFF 32 # Physical log buffer size (Kbytes)
You might double this.
>LOGBUFF 32 # Logical log buffer size (Kbytes)
You might double this.
>LOGSMAX 100 # Maximum number of logical log files
>CLEANERS 8 # Number of buffer cleaner processes
>SHMBASE 0xa000000 # Shared memory base address
>SHMVIRTSIZE 32768 # initial virtual shared memorysegment size
>SHMADD 16384 # Size of new shared memory segments
(Kbytes)
>SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
>CKPTINTVL 300 # Check point interval (in sec)
>LRUS 8 # Number of LRU queues
>LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaninglimit
>LRU_MIN_DIRTY 50 # LRU percent dirty end cleaninglimit
>LTXHWM 50 # Long transaction high water markpercentage
>LTXEHWM 60 # Long transaction high water mark
(exclusive)
>TXTIMEOUT 0x12c # Transaction timeout (in sec)
>STACKSIZE 32 # Stack size (Kbytes)>
># System Page Size
># BUFFSIZE - Dynamic Server no longer supports this configuration
parameter.
># To determine the page size used by Dynamic Server on
your platform
># see the last line of output from the command, 'onstat
-b'.
>
>
># Recovery Variables
># OFF_RECVRY_THREADS:
># Number of parallel worker threads during fast recovery or an
offline restore.
># ON_RECVRY_THREADS:
># Number of parallel worker threads during an online restore.
>
>OFF_RECVRY_THREADS 10