Re: MS SQL vs Informix Online Workgroup (7.12) on NT observations
In article <3311D294.75F8@thetsn.com>, Gregory Trubetskoy
<grisha@thetsn.com> writes
>We are trying to compare performance of SQL server vs Informix for our
>application, which mostly do querying rather than updating.
>
>Both SQL Server and Informix are on the same machine, dual P133 with 224
>M. MS SQL Server has 80 M allocated to it, Informix's BUFFERS is set to
>15000 (x4K per page = 60M).
>
>A query against the same table, using the same index according to the
>query plan (SET EXPLAIN ON/SET SHOWPLAN ON on Online/MSSQL) gives the
>following:
>
>(1st time means right after starting the server, i.e. nothing is cached)
>
>1st time MSSQL - 117 sec Online - 10 sec
>2nd time MSSQL - 3 sec Online - 8 sec
>
>Clearly Informix beats SQL Server first time around hands down, but the
>second time, when most (probably all - a reasonable guess based on the
>approximate size of the result) of the data is cached it is nearly 3
>times slower.
>
>I am no guru on configuring Informix, but here is the Oncinfig file I
>use. (With MS SQL server you don't get too many options, basically you
>have one parameter - memory - and that's all). I'd apreciate any advice
>on what I should try changing:
>
>(This is a dual P133, 224M RAM, PCI, data is stored on a stripe set made
>of 2 SCSI 4G drives, striping accomplished using NT, OS is NT 4.0...
>can't think of anything else relevant)
>
>#**************************************************************************
># Description: INFORMIX-OnLine Configuration Parameters
>#**************************************************************************
>
># Root Dbspace Configuration
>
>ROOTNAME rootdbs # Root dbspace name
>ROOTPATH I:\\IFMXDATA\\mysrv_informix\\rootdbs_dat.000
>ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
>ROOTSIZE 20480 # Size of root dbspace (Kbytes)>
># Disk Mirroring Configuration Parameters
>
>MIRROR 1 # Mirroring flag (Yes = 1, No = 0)
>MIRRORPATH # Path for device containing mirrored root
>MIRROROFFSET 0 # Offset into mirrored device (Kbytes)>
># Physical Log Configuration
>
>PHYSDBS rootdbs # Location (dbspace) of physical log
>PHYSFILE 3000 # Physical log file size (Kbytes)>
># Logical Log Configuration
>
>LOGFILES 15 # Number of logical log files
>LOGSIZE 500 # Logical log size (Kbytes)>
># Diagnostics
>
>MSGPATH F:\\Informix\\online.log # System message log file path
>CONSOLE F:\\Informix\\console.log # System console message path
>ALARMPROGRAM # Alarm program path>
Are these on the network? That could slow down Online.
># System Archive Tape Device
>
>TAPEDEV NUL # Tape device path
>TAPEBLK 16 # Tape block size (Kbytes)
>TAPESIZE 1363148 # Maximum amount of data to put on tape (Kbytes)>
># Log Archive Tape Device
>
>LTAPEDEV F:\\IFMXBKUP\\IFMXBKLG.BAK # Log tape device path
>LTAPEBLK 16 # Log tape block size (Kbytes)
>LTAPESIZE 1686824 # 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 mysrv_informix # Name of default database server
>DBSERVERALIASES # List of alternate dbservernames
>NETTYPE onsoctcp,1,50,NET # 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 2 # Number of user (cpu) vps
>SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps>to one
Set MULTIPROCESSOR=0, NUMCPVPS=1,SINGLE_CPU_VP=1.
When this is all set there is less overhead as Online can assume that
data structures in memory handled by the 1 CPU VP will not be accessed
by another CPU VP. This means that certain mutex calls can be eliminated
resulting in faster access to beffered data.
>
>NOAGE 0 # Process aging
>AFF_SPROC 0 # Affinity start processor
>AFF_NPROCS 0 # Affinity number of processors>
Setup these as well.
># Shared Memory Parameters
>
>LOCKS 2000 # Maximum number of locks
>BUFFERS 15000 # Maximum number of shared buffers>TBLSPACES 200 # Maximum number of open tblspaces
>CHUNKS 8 # Maximum number of chunks
>NUMAIOVPS 3 # Number of IO vps Why 3 when you only have 2 disks? Reduce to 2.
>DBSPACES 8 # Maximum number of dbspaces
>PHYSBUFF 32 # Physical log buffer size (Kbytes)
>LOGBUFF 32 # Logical log buffer size (Kbytes)>LOGSMAX 20 # Maximum number of logical log files
>CLEANERS 1 # Number of buffer cleaner processes Increase to 2 as have 2 disk and 2 CPUs.
>SHMBASE 0xc000000L # Shared memory base address
>SHMVIRTSIZE 16348 # initial virtual shared memory segment size
>SHMADD 8192 # Size of new shared memory segments
>(Kbytes)
>SHMTOTAL 0 # Total shared memory (Kbytes).
>0=>unlimited
>CKPTINTVL 300 # Check point interval (in sec)
>LRUS 4 # Number of LRU queues
>LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
>LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
>LTXHWM 50 # Long transaction high water mark percentage
>LTXEHWM 60 # Long transaction high water mark (exclusive)
>TXTIMEOUT 300 # Transaction timeout (in sec)
>STACKSIZE 32 # Stack size (Kbytes)>
># System Page Size
># BUFFSIZE - OnLine no longer supports this configuration parameter.
># To determine the page size used by OnLine 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 # Default number of offline worker threads
>ON_RECVRY_THREADS 1 # Default number of online worker threads>
># Data Replication Variables
># DRAUTO: 0 manual, 1 retain type, 2 reverse type
>DRAUTO 0 # DR automatic switchover
>DRINTERVAL 30 # DR max time between DR buffer flushes (in sec)
>DRTIMEOUT 30 # DR network timeout (in sec)@@NL@