Hi,

i'm just starting in an enterprise that has BaaN IV c with the
following specifications:

Server:
3 RAID5 (with 4 disks each one)
4 processors
4Gb Ram

OS: NT4

DataBase: Informix 9.2x (i don't remeber the exact number right now

The enterprise has a dbspace called baandbs and all the Informix
objects created in that dbspace, the size of the extents for all the
objects is default (16k) and about 50 chunks (the chunks are created
almost in a random fashion between 2 of the RAID's)

they have a dbspace rootdbs, one for physcal logs, one for logical
logs, and one temp...

they have about 11 tables in the range of 1 to 4 millions rows, and 28
table between 500000 and 999999 rows.

Total they has 12900 tables in use and the total instalation is about
119000 tables big

Obviously the performance is not just bad... it really sucks...

one of the things i tought of was create more dbspaces and ALTER
FRAGMENT some tables
to put some tables in one dbspaces some tables in other dbspace in
other RAID and FRAGMENT BY EXPRESSION (or ROUND ROBIN) some big
tables...

But from BaaN's distributor says that BaaN will scream if i do
something like that... they give to me a procedure to defragment the
databases but because of the size of the database here it seems very
impractical or at least a procedure that cannot be done once a month,
not even once every six months...


so, please give me some advices in how to optimize this monster....

regards,
Jaime Casanova


PS: here i provide my actual ONCONFIG.. i haven't touched it, yet...


# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace name
ROOTPATH C:\IFMXDATA\ol_server\rootdbs_dat.000 # Path for device
containing root dbspace
# Path for device containing root
dbspace
# Path for device containing root
dbspace
# Path for device containing root
dbspace
ROOTOFFSET 0 # Offset of root dbspace into device
(Kbytes)
ROOTSIZE 20480 # Size of root dbspace (Kbytes)
# Disk Mirroring Configuration Parameters
MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing mirrored root
MIRROROFFSET 0 # Offset into mirrored device (Kbytes)
# Physical Log Configuration
PHYSDBS baandb_plog # Location (dbspace) of physical log
PHYSFILE 409600 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 20 # Number of logical log files
LOGSIZE 102400 # Logical log size (Kbytes)
# Diagnostics
MSGPATH C:\Informix\online.log # System message log file path
CONSOLE C:\Informix\console.log # System console message path
ALARMPROGRAM C:\Informix\etc\log_full.bat # Alarm program path
#ALARMPROGRAM NUL # Alarm program path
TBLSPACE_STATS 1 # Maintain tblspace statistics ***

# System Archive Tape Device
#TAPEDEV NUL # Tape device path
TAPEDEV \\.\TAPE0 # Tape device path
TAPEBLK 512 # Tape block size (Kbytes)
TAPESIZE 24000000 # Maximum amount of data to put on tape
(Kbytes)
# Log Archive Tape Device
LTAPEDEV NUL # Log tape device path
LTAPEBLK 16 # Log tape block size (Kbytes)
LTAPESIZE 1363148 # 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 ol_server # Name of default Dynamic Server
DBSERVERALIASES # List of alternate dbservernames
#07sep03 NETTYPE soctcp,3,100,NET # Override sqlhosts nettype
parameters
NETTYPE soctcp,4,100,NET # Override sqlhosts nettype parameters
#07sep03
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in
distributed env.
RESIDENT 0 # Forced residency flag (Yes = 1, No =
0)
MULTIPROCESSOR 1 # 0 for single-processor, 1 for
multi-processor
#07sep03 NUMCPUVPS 3 # Number of user (cpu) vps
NUMCPUVPS 4 # Number of user (cpu) vps #07sep03
#VPCLASS CPU,NUM=3 #Recom.Rob.Machare
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps
to one
NOAGE 0 # Process aging
AFF_SPROC 0 # Affinity start processor
#07sep03 AFF_NPROCS 3 # Affinity number of
processors
AFF_NPROCS 4 # Affinity number of processors
#07sep03
# Shared Memory Parameters
LOCKS 600000 # Maximum number of locks
#14sep03BUFFERS 52413 # Maximum number of shared
buffers
BUFFERS 90000 # Maximum number of shared buffers
#14sep03 Recom.F.Sliva
#NUMAIOVPS 1 # Number of IO vps
#07sep03 NUMAIOVPS 20 #4X#DISCOS
#NUMAIOVPS 48 #4X#DISCOS #07sep03
NUMAIOVPS 4 #14sep03 Recom.F.Sliva
#VPCLASS AIO,NUM=20 #Recom.Rob.Machare
#PHYSBUFF 256 # Physical log buffer size (Kbytes)
#14sep03PHYSBUFF 384 # Physical log buffer size
(Kbytes) #FSILVA08nov02
PHYSBUFF 512 # Physical log buffer size (Kbytes)
#14sep03 Recom.F.Sliva
LOGBUFF 32 # Logical log buffer size (Kbytes)
LOGSMAX 235 # Maximum number of logical log files
#CLEANERS 4 # Number of buffer cleaner processes
#CLEANERS 5 # NUMERO DE DISCOS EN EL RAID
#07sep03 CLEANERS 6 # NUMERO DE DISCOS EN EL RAID #CFAB17feb03
CLEANERS 12 # NUMERO DE DISCOS EN EL RAID #07sep03
SHMBASE 0xc000000 # Shared memory base address
#SHMVIRTSIZE 327680 # initial virtual shared memory
segment size
SHMVIRTSIZE 655360 # initial virtual shared memory segment
size (CFAB08abr02ara asignar inicialmente la memoria requerida en un
dia)
SHMADD 163840 # Size of new shared memory segments
(Kbytes) (CFAB08abr02:como antes de 20ago01)
#SHMADD 327680 # 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 12 # LRU percent dirty begin cleaning
limit
LRU_MIN_DIRTY 8 # LRU percent dirty end cleaning limit
#LTXHWM 50 # Long transaction high water mark
percentage
#LTXEHWM 60 # Long transaction high water mark
(exclusive)
LTXHWM 40 # Long transaction high water mark
percentage
LTXEHWM 70 # Long transaction high water mark
(exclusive)
TXTIMEOUT 0x12c # 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)
DRLOSTFOUND \tmp # DR lost+found file path
# CDR Variables
#CDR_LOGBUFFERS 2048 # size of log reading buffer pool
(Kbytes)
CDR_LOGBUFFERS 4096 # size of log reading buffer pool
(Kbytes)
CDR_EVALTHREADS 1,2 # evaluator threads
(per-cpu-vp,additional)
CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
#CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR
queue (Kbytes)
CDR_QUEUEMEM 8192 # Maximum amount of memory for any CDR
queue (Kbytes)
# Backup/Restore variables
BAR_ACT_LOG C:\Informix\bar_act.log #Path of log file for onbar.exe
BAR_MAX_BACKUP 1
BAR_RETRY 1
BAR_NB_XPORT_COUNT 10
BAR_XFER_BUF_SIZE 15
# Read Ahead Variables
RA_PAGES 4 # Number of pages to attempt to read
ahead
RA_THRESHOLD # Number of pages left before next
group
# DBSPACETEMP:
# OnLine equivalent of DBTEMP for SE. This is the list of dbspaces
# that the OnLine SQL Engine will use to create temp tables etc.
# If specified it must be a colon separated list of dbspaces that exist
# when the OnLine system is brought online. If not specified, or if
# all dbspaces specified are invalid, various ad hoc queries will
create
# temporary files in /tmp instead.

#CFAB11jul04 DBSPACETEMP rootdbs # Default temp dbspaces
DBSPACETEMP baandb_tmp # Default temp dbspaces
#CFAB11jul04

# DUMP*:
# The following parameters control the type of diagnostics information
which
# is preserved when an unanticipated error condition (assertion
failure) occurs
# during OnLine operations.
# For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.
#DUMPDIR C:\tmp # Preserve diagnostics in this directory
DUMPDIR F:\tmp # Preserve diagnostics in this directory #CFAB19feb03
DUMPSHMEM 1 # Dump a copy of shared memory
DUMPGCORE 0 # Dump a core image using 'gcore'
DUMPCORE 0 # Dump a core image (Warning:this
aborts OnLine)
DUMPCNT 1 # Number of shared memory or gcore
dumps for
# a single user's session
# ADT*
# ADT* parameters moved to adtcfg file
FILLFACTOR 60 # Fill factor for building indexes
# method for OnLine to use when determining current time
USEOSTIME 0 # 0: use internal time(fast), 1: get
time from OS(slow)
# Parallel Database Queries (pdq)
# OFF => 0, LOW => 1, HIGH => 100
MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
DS_MAX_QUERIES 1 # Maximum number of decision support
queries
DS_TOTAL_MEMORY 2048 # Decision support memory (Kbytes)
DS_MAX_SCANS 1048576 # Maximum number of decision support
scans
DATASKIP off # List of dbspaces to skip
# OPTCOMPIND
# 0 => Nested loop joins will be preferred (where
# possible) over sortmerge joins and hash joins.
# 1 => If the transaction isolation mode is not
# "repeatable read", optimizer behaves as in (2)
# below. Otherwise it behaves as in (0) above.
# 2 => Use costs regardless of the transaction isolation
# mode. Nested loop joins are not necessarily
# preferred. Optimizer bases its decision purely
# on costs.
OPTCOMPIND 0 # To hint the optimizer
ONDBSPACEDOWN 0 # Dbspace down option: 0 = CONTINUE, 1
= ABORT, 2 = WAIT
LBU_PRESERVE 0 # Preserve last log for log backup
BAR_BSALIB_PATH C:\ISM\1.00\bin\libbsa.dll # Location of ISM XBSA DLL
LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL,
CONT)
OPTICAL_LIB_PATH # Location of Optical Subsystem driver DLL

#OPTICAL_LIB_PATH  # Location of Optical Subsystem
driver DLL
CDR_LOGDELTA 30 # % of log space allowed in queue
memory
CDR_NUMCONNECT 16 # Expected connections per server
CDR_NIFRETRY 300 # Connection retry (seconds)
CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0
none, 9 max)
RESTARTABLE_RESTORE off #To support restartable
restore..values on/off
ISM_DATA_POOL ISMData
ISM_LOG_POOL ISMLogs
OPCACHEMAX 128 # Maximum optical cache size (Kbytes)
HETERO_COMMIT 0
OPT_GOAL -1
DIRECTIVES 1

BLOCKTIMEOUT 3600 # Default timeout for system block ***
ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or anything
but 1)

Jaime Casanova wrote:
> Hi,
>
> i'm just starting in an enterprise that has BaaN IV c with the
> following specifications:
>
> Server:
> 3 RAID5 (with 4 disks each one)
> 4 processors
> 4Gb Ram
>
> OS: NT4
>
> DataBase: Informix 9.2x (i don't remeber the exact number right now
>
> The enterprise has a dbspace called baandbs and all the Informix
> objects created in that dbspace, the size of the extents for all the
> objects is default (16k) and about 50 chunks (the chunks are created
> almost in a random fashion between 2 of the RAID's)
>
> they have a dbspace rootdbs, one for physcal logs, one for logical
> logs, and one temp...
>

One quick and innocent way to get SOME performance back would be to add
a couple more temporary dbspaces.

> they have about 11 tables in the range of 1 to 4 millions rows, and 28
> table between 500000 and 999999 rows.
>
> Total they has 12900 tables in use and the total instalation is about
> 119000 tables big
>
> Obviously the performance is not just bad... it really sucks...
>
> one of the things i tought of was create more dbspaces and ALTER
> FRAGMENT some tables
> to put some tables in one dbspaces some tables in other dbspace in
> other RAID and FRAGMENT BY EXPRESSION (or ROUND ROBIN) some big
> tables...
>
> But from BaaN's distributor says that BaaN will scream if i do
> something like that... they give to me a procedure to defragment the
> databases but because of the size of the database here it seems very
> impractical or at least a procedure that cannot be done once a month,
> not even once every six months...
>
>

Can you do a table or two each weekend?

> so, please give me some advices in how to optimize this monster....
>
> regards,
> Jaime Casanova
>
>
> PS: here i provide my actual ONCONFIG.. i haven't touched it, yet...
>
>
> # Root Dbspace Configuration
> ROOTNAME rootdbs # Root dbspace name
> ROOTPATH C:\IFMXDATA\ol_server\rootdbs_dat.000 # Path for device
> containing root dbspace
> # Path for device containing root
> dbspace
> # Path for device containing root
> dbspace
> # Path for device containing root
> dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device
> (Kbytes)
> ROOTSIZE 20480 # Size of root dbspace (Kbytes)
> # Disk Mirroring Configuration Parameters
> MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
> MIRRORPATH # Path for device containing mirrored root
> MIRROROFFSET 0 # Offset into mirrored device (Kbytes)
> # Physical Log Configuration
> PHYSDBS baandb_plog # Location (dbspace) of physical log
> PHYSFILE 409600 # Physical log file size (Kbytes)
> # Logical Log Configuration
> LOGFILES 20 # Number of logical log files
> LOGSIZE 102400 # Logical log size (Kbytes)
> # Diagnostics
> MSGPATH C:\Informix\online.log # System message log file path
> CONSOLE C:\Informix\console.log # System console message path
> ALARMPROGRAM C:\Informix\etc\log_full.bat # Alarm program path
> #ALARMPROGRAM NUL # Alarm program path
> TBLSPACE_STATS 1 # Maintain tblspace statistics ***
>
> # System Archive Tape Device
> #TAPEDEV NUL # Tape device path
> TAPEDEV \\.\TAPE0 # Tape device path
> TAPEBLK 512 # Tape block size (Kbytes)
> TAPESIZE 24000000 # Maximum amount of data to put on tape
> (Kbytes)
> # Log Archive Tape Device
> LTAPEDEV NUL # Log tape device path
> LTAPEBLK 16 # Log tape block size (Kbytes)
> LTAPESIZE 1363148 # 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 ol_server # Name of default Dynamic Server
> DBSERVERALIASES # List of alternate dbservernames
> #07sep03 NETTYPE soctcp,3,100,NET # Override sqlhosts nettype
> parameters
> NETTYPE soctcp,4,100,NET # Override sqlhosts nettype parameters
> #07sep03
> DEADLOCK_TIMEOUT 60 # Max time to wait of lock in
> distributed env.
> RESIDENT 0 # Forced residency flag (Yes = 1, No =
> 0)
> MULTIPROCESSOR 1 # 0 for single-processor, 1 for
> multi-processor
> #07sep03 NUMCPUVPS 3 # Number of user (cpu) vps
> NUMCPUVPS 4 # Number of user (cpu) vps #07sep03
> #VPCLASS CPU,NUM=3 #Recom.Rob.Machare
> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps
> to one
> NOAGE 0 # Process aging
> AFF_SPROC 0 # Affinity start processor
> #07sep03 AFF_NPROCS 3 # Affinity number of
> processors
> AFF_NPROCS 4 # Affinity number of processors
> #07sep03
> # Shared Memory Parameters
> LOCKS 600000 # Maximum number of locks
> #14sep03BUFFERS 52413 # Maximum number of shared
> buffers
> BUFFERS 90000 # Maximum number of shared buffers

Looks like you have the memory, so you could increase BUFFERS up to ...
250,000??? (i.e. to keep below 1.75 Gig 'cos of NT things).

> #14sep03 Recom.F.Sliva
> #NUMAIOVPS 1 # Number of IO vps
> #07sep03 NUMAIOVPS 20 #4X#DISCOS
> #NUMAIOVPS 48 #4X#DISCOS #07sep03
> NUMAIOVPS 4 #14sep03 Recom.F.Sliva
> #VPCLASS AIO,NUM=20 #Recom.Rob.Machare
> #PHYSBUFF 256 # Physical log buffer size (Kbytes)
> #14sep03PHYSBUFF 384 # Physical log buffer size
> (Kbytes) #FSILVA08nov02
> PHYSBUFF 512 # Physical log buffer size (Kbytes)

This is large in comparison to your LOGBUFF - why?

If anything perhaps just set them both to 64 each.

> #14sep03 Recom.F.Sliva
> LOGBUFF 32 # Logical log buffer size (Kbytes)
> LOGSMAX 235 # Maximum number of logical log files
> #CLEANERS 4 # Number of buffer cleaner processes
> #CLEANERS 5 # NUMERO DE DISCOS EN EL RAID
> #07sep03 CLEANERS 6 # NUMERO DE DISCOS EN EL RAID #CFAB17feb03
> CLEANERS 12 # NUMERO DE DISCOS EN EL RAID #07sep03
> SHMBASE 0xc000000 # Shared memory base address
> #SHMVIRTSIZE 327680 # initial virtual shared memory
> segment size
> SHMVIRTSIZE 655360 # initial virtual shared memory segment
> size (CFAB08abr02ara asignar inicialmente la memoria requerida en un
> dia)
> SHMADD 163840 # Size of new shared memory segments
> (Kbytes) (CFAB08abr02:como antes de 20ago01)
> #SHMADD 327680 # 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 12 # LRU percent dirty begin cleaning
> limit
> LRU_MIN_DIRTY 8 # LRU percent dirty end cleaning limit

Shut these down to represent 10 and 5 Mb each (i.e. the minumum)

(i.e. BUFFERS 256,000, LRU_MAX_DIRTY 2, LRU_MIN_DIRTY 1

> #LTXHWM 50 # Long transaction high water mark
> percentage
> #LTXEHWM 60 # Long transaction high water mark
> (exclusive)
> LTXHWM 40 # Long transaction high water mark
> percentage
> LTXEHWM 70 # Long transaction high water mark
> (exclusive)
> TXTIMEOUT 0x12c # 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)
> DRLOSTFOUND \tmp # DR lost+found file path
> # CDR Variables
> #CDR_LOGBUFFERS 2048 # size of log reading buffer pool
> (Kbytes)
> CDR_LOGBUFFERS 4096 # size of log reading buffer pool
> (Kbytes)
> CDR_EVALTHREADS 1,2 # evaluator threads
> (per-cpu-vp,additional)
> CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
> #CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR
> queue (Kbytes)
> CDR_QUEUEMEM 8192 # Maximum amount of memory for any CDR
> queue (Kbytes)
> # Backup/Restore variables
> BAR_ACT_LOG C:\Informix\bar_act.log #Path of log file for onbar.exe
> BAR_MAX_BACKUP 1
> BAR_RETRY 1
> BAR_NB_XPORT_COUNT 10
> BAR_XFER_BUF_SIZE 15
> # Read Ahead Variables
> RA_PAGES 4 # Number of pages to attempt to read
> ahead
> RA_THRESHOLD # Number of pages left before next
> group
> # DBSPACETEMP:
> # OnLine equivalent of DBTEMP for SE. This is the list of dbspaces
> # that the OnLine SQL Engine will use to create temp tables etc.
> # If specified it must be a colon separated list of dbspaces that exist
> # when the OnLine system is brought online. If not specified, or if
> # all dbspaces specified are invalid, various ad hoc queries will
> create
> # temporary files in /tmp instead.
>
> #CFAB11jul04 DBSPACETEMP rootdbs # Default temp dbspaces
> DBSPACETEMP baandb_tmp # Default temp dbspaces
> #CFAB11jul04

Add your extra temporary dbspaces to DBSPACETEMP

>
> # DUMP*:
> # The following parameters control the type of diagnostics information
> which
> # is preserved when an unanticipated error condition (assertion
> failure) occurs
> # during OnLine operations.
> # For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.
> #DUMPDIR C:\tmp # Preserve diagnostics in this directory
> DUMPDIR F:\tmp # Preserve diagnostics in this directory #CFAB19feb03
> DUMPSHMEM 1 # Dump a copy of shared memory
> DUMPGCORE 0 # Dump a core image using 'gcore'
> DUMPCORE 0 # Dump a core image (Warning:this
> aborts OnLine)
> DUMPCNT 1 # Number of shared memory or gcore
> dumps for
> # a single user's session
> # ADT*
> # ADT* parameters moved to adtcfg file
> FILLFACTOR 60 # Fill factor for building indexes
> # method for OnLine to use when determining current time
> USEOSTIME 0 # 0: use internal time(fast), 1: get
> time from OS(slow)
> # Parallel Database Queries (pdq)
> # OFF => 0, LOW => 1, HIGH => 100
> MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
> DS_MAX_QUERIES 1 # Maximum number of decision support
> queries
> DS_TOTAL_MEMORY 2048 # Decision support memory (Kbytes)
> DS_MAX_SCANS 1048576 # Maximum number of decision support
> scans
This is crap if you do use PDQ

> DATASKIP off # List of dbspaces to skip
> # OPTCOMPIND
> # 0 => Nested loop joins will be preferred (where
> # possible) over sortmerge joins and hash joins.
> # 1 => If the transaction isolation mode is not
> # "repeatable read", optimizer behaves as in (2)
> # below. Otherwise it behaves as in (0) above.
> # 2 => Use costs regardless of the transaction isolation
> # mode. Nested loop joins are not necessarily
> # preferred. Optimizer bases its decision purely
> # on costs.
> OPTCOMPIND 0 # To hint the optimizer
> ONDBSPACEDOWN 0 # Dbspace down option: 0 = CONTINUE, 1
> = ABORT, 2 = WAIT
> LBU_PRESERVE 0 # Preserve last log for log backup
> BAR_BSALIB_PATH C:\ISM\1.00\bin\libbsa.dll # Location of ISM XBSA DLL
> LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL,
> CONT)
> OPTICAL_LIB_PATH # Location of Optical Subsystem driver DLL
>
> #OPTICAL_LIB_PATH  # Location of Optical Subsystem
> driver DLL
> CDR_LOGDELTA 30 # % of log space allowed in queue
> memory
> CDR_NUMCONNECT 16 # Expected connections per server
> CDR_NIFRETRY 300 # Connection retry (seconds)
> CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0
> none, 9 max)
> RESTARTABLE_RESTORE off #To support restartable
> restore..values on/off
> ISM_DATA_POOL ISMData
> ISM_LOG_POOL ISMLogs
> OPCACHEMAX 128 # Maximum optical cache size (Kbytes)
> HETERO_COMMIT 0
> OPT_GOAL -1
> DIRECTIVES 1
>
> BLOCKTIMEOUT 3600 # Default timeout for system block ***
> ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or anything
> but 1)
>

Tricky to make any suggestions without something to work from :

How many users?

Update statistics was last run ... when?

onstat -p
onstat -t
onstat -l
onstat -u

Heck, onstat -a

In addition to some good recommendations of TBP...

From the keyboard of "Jaime Casanova" <systemguards@gmail.com>:

> Hi,
>
> i'm just starting in an enterprise that has BaaN IV c with the
> following specifications:
>
> Server:
> 3 RAID5 (with 4 disks each one)
> 4 processors
> 4Gb Ram
>
> OS: NT4
>
> DataBase: Informix 9.2x (i don't remeber the exact number right
> now
>
> The enterprise has a dbspace called baandbs and all the Informix
> objects created in that dbspace, the size of the extents for all
> the objects is default (16k) and about 50 chunks (the chunks are
> created almost in a random fashion between 2 of the RAID's)

Parallel archives are parallel only on dbspace level. With this
one dbspace only approach there is no parallel archive. If your
archive extends into the daytime it slows down online processing.
I'd distribute the data over several dbspaces.

[...]

> But from BaaN's distributor says that BaaN will scream if i do
> something like that...

Seems that they don't know much about Informix :-)

> they give to me a procedure to defragment
> the databases but because of the size of the database here it
> seems very impractical or at least a procedure that cannot be
> done once a month, not even once every six months...

[...]

> ROOTPATH C:\IFMXDATA\ol_server\rootdbs_dat.000 # Path

This is the system disk. Presumably it also contains the swap
file. No good idea IMHO to put the rootdbs on his particular disk.

[...]

> MIRROR 0 # Mirroring flag (Yes = 1, No = 0)

For security reasons I'd mirror rootdbs.

[...]

> LTAPEDEV NUL # Log tape device path

This is really bad on a production system! You won't be able to
recover your database in case a restore is necessary. RAID5 will
not deliver you from it. In case of resore you reset all your work
to the time of the archive. Everything after that is lost. And
it's you head that's going to roll...

[...]

> NUMCPUVPS 4 # Number of user (cpu) vps

Is this a stand alone database server or is there also BaaN
running on it. In the latter case I'd allow BaaN to use the CPUs
too. Reduce to 2.

[...]

> NUMAIOVPS 4 #14sep03 Recom.F.Sliva

This is senseless (and ignored) on NT. It is hard coded to 1 and
just leaves a note in the message log.

[...]

> FILLFACTOR 60 # Fill factor for building

This leads to bigger than neccessary indexes. I'd set it to 90.

> LBU_PRESERVE 0 # Preserve last log for log

Set this to 1 after you have fixed LTAPEDEV or you risk the need
to restore when your log backup procedure fails.

[...]


HTH
Christian
--
#include <std_disclaimer.h>
/* The opinions stated above are my own and not
necessarily those of my employer. */

Since this is Baan and the # of tables is as usual incredible you
may want to set also DS_HASHSIZE and DS_POOLSIZE
in onconfig.

see old article from Art:

--------
DS_HASHSIZE is the number of hash list and must be a prime number.
It defaults to 31 in IDS 7.21->7.31.


DS_POOLSIZE is the number of entries in each hash list. It defaults to

50 in IDS 7.21->7.24 and 127 in IDS 7.31.
--------

set them bigger.

Superboer.

Christian Knappke schreef:

> In addition to some good recommendations of TBP...
>
> From the keyboard of "Jaime Casanova" <systemguards@gmail.com>:
>
> > Hi,
> >
> > i'm just starting in an enterprise that has BaaN IV c with the
> > following specifications:
> >
> > Server:
> > 3 RAID5 (with 4 disks each one)
> > 4 processors
> > 4Gb Ram
> >
> > OS: NT4
> >
> > DataBase: Informix 9.2x (i don't remeber the exact number right
> > now
> >
> > The enterprise has a dbspace called baandbs and all the Informix
> > objects created in that dbspace, the size of the extents for all
> > the objects is default (16k) and about 50 chunks (the chunks are
> > created almost in a random fashion between 2 of the RAID's)
>
> Parallel archives are parallel only on dbspace level. With this
> one dbspace only approach there is no parallel archive. If your
> archive extends into the daytime it slows down online processing.
> I'd distribute the data over several dbspaces.
>
> [...]
>
> > But from BaaN's distributor says that BaaN will scream if i do
> > something like that...
>
> Seems that they don't know much about Informix :-)
>
> > they give to me a procedure to defragment
> > the databases but because of the size of the database here it
> > seems very impractical or at least a procedure that cannot be
> > done once a month, not even once every six months...
>
> [...]
>
> > ROOTPATH C:\IFMXDATA\ol_server\rootdbs_dat.000 # Path
>
> This is the system disk. Presumably it also contains the swap
> file. No good idea IMHO to put the rootdbs on his particular disk.
>
> [...]
>
> > MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
>
> For security reasons I'd mirror rootdbs.
>
> [...]
>
> > LTAPEDEV NUL # Log tape device path
>
> This is really bad on a production system! You won't be able to
> recover your database in case a restore is necessary. RAID5 will
> not deliver you from it. In case of resore you reset all your work
> to the time of the archive. Everything after that is lost. And
> it's you head that's going to roll...
>
> [...]
>
> > NUMCPUVPS 4 # Number of user (cpu) vps
>
> Is this a stand alone database server or is there also BaaN
> running on it. In the latter case I'd allow BaaN to use the CPUs
> too. Reduce to 2.
>
> [...]
>
> > NUMAIOVPS 4 #14sep03 Recom.F.Sliva
>
> This is senseless (and ignored) on NT. It is hard coded to 1 and
> just leaves a note in the message log.
>
> [...]
>
> > FILLFACTOR 60 # Fill factor for building
>
> This leads to bigger than neccessary indexes. I'd set it to 90.
>
> > LBU_PRESERVE 0 # Preserve last log for log
>
> Set this to 1 after you have fixed LTAPEDEV or you risk the need
> to restore when your log backup procedure fails.
>
> [...]
>
>
> HTH
> Christian
> --
> #include <std_disclaimer.h>
> /* The opinions stated above are my own and not
> necessarily those of my employer. */

ahum getting old...

DD_HASHSIZE,DD_HASHMAX

Superboer.

Jaime Casanova wrote:

> one of the things i tought of was create more dbspaces and ALTER
> FRAGMENT some tables
>
> But from BaaN's distributor says that BaaN will scream if i do
> something like that...
>

It's not true.
We have BaaN and we have fragmented tables.
And BaaN has a special file called 'inf_storage' where you can set
storage options for individual tables.

>> But from BaaN's distributor says that BaaN will scream if i do
>> something like that... they give to me a procedure to defragment the
>> databases but because of the size of the database here it seems very
>> impractical or at least a procedure that cannot be done once a month,
>> not even once every six months...
>
>Can you do a table or two each weekend?
>

That was my original idea, but their procedure include drop and
recreate all tables... the tables are not created with informix (ie:
with a script) but the BaaN does the work... and it adds some hash
columns for indexing... (don't ask me why BaaN does that when informix
has its own indexes)...

regards,
Jaime Casanova

Well first get a consultant like me in to review things!

I don't believe Baan don't have tools to move individual tables. They
just need to supply them to you.

I change the following ONCONFIG parameters.

RESIDENT to -1 (if you have enough phyiscal memory)
NOAGE to 1
CLEANERS to 50 ( I assume "NUMERO DE DISCOS EN EL RAID" means number of
disks in RAID)
As Andrew Hamme has demonstrated you can more than I/O at one to a
drive.
LRUS to 50 (same as CLEANERS).
RA_PAGES to 16
RA_THRESHOLD to 8.
LRU_PRESERVE tp 1.

You need to sort out this disk layout. Query sysmaster:sysextents and
group by dbsname/tabname.
How many extents per table?

Think about recreating indexes in seperate dbspaces.

IF Baan say they have no tools to move individual tables then let me
know (david@smooth1.co.uk) and I'll
kick their arse! I'll also use my IBM contacts to kick them as well.

Do Baan support IDS 10?

Query sysmaster:syscfgtab (sysmaster:sysconfig is a view that hides
undocumented paramaters)!

There are

DS_ parameters (?? Data dictionary caching).
DD_ parameters (?? Data distribution caching)
PC_ parameters (SPL caching)

Check the IDS 10 manuals they document this stuff now!

> ( I assume "NUMERO DE DISCOS EN EL RAID" means number of disks in RAID)

good assuption


> IF Baan say they have no tools to move individual tables then let me know (d...@smooth1.co.uk) and I'll
> kick their arse! I'll also use my IBM contacts to kick them as well.

not BaaN but my local distributor


i will try your suggestions....