Bad Perfomance Baan + Informix 9.20 + AIX4.3.3 
Author Message
 Bad Perfomance Baan + Informix 9.20 + AIX4.3.3

We buy a new server RS6000 H80 with 2 CPU and 2 Gb and install
Informix 9.20 and try install Baan IV but we got a big trouble
with perfomance. We notice that Informix use tempdbs too intensive
Very strange but Informix do sort not in the memory but in disk
space (I/O 3 Mb/s with tempdbs).

pdqpriority=1
INFX_CPUVP_PRIORITY=60
NODEFDAC=yes

onconfig

#**************************************************************************
#
#                          INFORMIX SOFTWARE, INC.
#
#  Title:       onconfig.std
#  Description: Informix Dynamic Server 2000 Configuration Parameters
#
#**************************************************************************

# Root Dbspace Configuration

ROOTNAME        rootdbs         # Root dbspace name
ROOTPATH        /informix/db/raw_root
                                # Path for device containing root
dbspace
ROOTOFFSET      8               # Offset of root dbspace into device
(Kbytes)
ROOTSIZE        605352          # 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         rootdbs         # Location (dbspace) of physical log
PHYSFILE        50000           # Physical log file size (Kbytes)

# Logical Log Configuration

LOGFILES        10              # Number of logical log files
LOGSIZE         100480          # Logical log size (Kbytes)
LOG_BACKUP_MODE CONT            

# Diagnostics

MSGPATH         /informix/online.log # System message log file path
CONSOLE         /informix/console # System console message path
ALARMPROGRAM    /informix/etc/log_full.sh # Alarm program path
TBLSPACE_STATS  0               # Maintain tblspace statistics

# System Archive Tape Device

TAPEDEV         /dev/null       # Tape device path
TAPEBLK         64              # Tape block size (Kbytes)
TAPESIZE        600000          # Maximum amount of data to put on
tape (Kbytes)

# Log Archive Tape Device

LTAPEDEV        /dev/null       # Log tape device path
LTAPEBLK        64              # Log tape block size (Kbytes)
LTAPESIZE       600000          # Max amount of data to put on log
tape (Kbytes)

# Optical

STAGEBLOB                       # Informix Dynamic Server 2000 staging
area

# System Configuration

SERVERNUM       0               # Unique id corresponding to a OnLine
instance
DBSERVERNAME    h80lo_on        # Name of default database server
#DBSERVERALIASES h80lo_ol          # List of alternate dbservernames
#NETTYPE         soctcp,2,,NET   # Override sqlhosts nettype
parameters

#NETTYPE        ipcshm,2,,CPU   # Override sqlhosts nettype parameters

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
NUMCPUVPS       2               # Number of user (cpu) vps
SINGLE_CPU_VP   0               # If non-zero, limit number of cpu vps
to one

NOAGE           1               # Process aging
AFF_SPROC       0               # Affinity start processor
AFF_NPROCS      0               # Affinity number of processors

# Shared Memory Parameters

LOCKS           1000000         # Maximum number of locks
BUFFERS         200000          # Maximum number of shared buffers
NUMAIOVPS       10              # Number of IO vps
PHYSBUFF        64              # Physical log buffer size (Kbytes)
LOGBUFF         64              # Logical log buffer size (Kbytes)
LOGSMAX         16              # Maximum number of logical log files
CLEANERS        127             # Number of buffer cleaner processes
SHMBASE         0x30000000        # Shared memory base address
SHMVIRTSIZE     400000          #131072# initial virtual shared memory
segment size
SHMADD          65536           #32768# Size of new shared memory
segments (Kbytes)
SHMTOTAL        0               # Total shared memory (Kbytes).
0=>unlimited
CKPTINTVL       600             # Check point interval (in sec)
LRUS            127             # 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       0x12c             # Transaction timeout (in sec)
STACKSIZE       64              # 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
DRINTERVAL      30              # DR max time between DR buffer
flushes (in sec)
DRTIMEOUT       30              # DR network timeout (in sec)
DRLOSTFOUND     /informix/etc/dr.lostfound # DR lost+found file path

# CDR Variables
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_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)

# Backup/Restore variables
BAR_ACT_LOG     /tmp/bar_act.log
BAR_MAX_BACKUP  0              
BAR_RETRY       1              
BAR_NB_XPORT_COUNT 10              
BAR_XFER_BUF_SIZE 31              
RESTARTABLE_RESTORE off            
BAR_PROGRESS_FREQ 0              

# Informix Storage Manager variables
ISM_DATA_POOL   ISMData        
ISM_LOG_POOL    ISMLogs        

# Read Ahead Variables
RA_PAGES        32              # Number of pages to attempt to read
ahead
RA_THRESHOLD    28              # 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.

DBSPACETEMP     tempdbs,tempdbs1,tempdbs2,templogdbs # Default temp
dbspaces

# 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         /tmp            # Preserve diagnostics in this
directory
DUMPSHMEM       0               # 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

FILLFACTOR      90              # 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)
MAX_PDQPRIORITY 40              # Maximum allowed pdqpriority
DS_MAX_QUERIES  10              # Maximum number of decision support
queries
DS_TOTAL_MEMORY 1677721         #8192# 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      2               # To hint the optimizer

DIRECTIVES      1               # Optimizer DIRECTIVES ON (1/Default)
or OFF (0)

ONDBSPACEDOWN   2               # Dbspace down option: 0 = CONTINUE, 1
= ABORT, 2 = WAIT
LBU_PRESERVE    0               # Preserve last log for log backup
OPCACHEMAX      0               # Maximum optical cache size (Kbytes)

# HETERO_COMMIT (Gateway participation in distributed transactions)
# 1 => Heterogeneous Commit is enabled
# 0 (or any other value) => Heterogeneous Commit is disabled
HETERO_COMMIT   0              

SBSPACENAME                     # Default smartblob space name - this
is where blobs
                       # go if no sbspace is specified when the
smartblob is
                       # created. It is also used by some datablades
as
                       # the location to put their smartblobs.
SYSSBSPACENAME                  # Default smartblob space for use by
the Informix
                       # Server. This is used primarily for Informix
Server
                       # system statistics collection.

BLOCKTIMEOUT    3600            # Default timeout for system block
SYSALARMPROGRAM /informix/etc/evidence.sh # System Alarm program path

# Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
OPT_GOAL        -1              

ALLOW_NEWLINE   0            
...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 Bad Perfomance Baan + Informix 9.20 + AIX4.3.3

update statistics (dostats) done ?
From onstat -p you can see there have been 100000 seqscans in 7,5 h
uptime. This is really much.
Further more you can decrease SHMVIRTSIZE. only about 10 percent of it
are used (onstat -g seg). You can decrease it from 400000 to 80000.
You are using about 1,3 GB memory for informix. What else is running on
this machine ?
Are you running out of free physical memory ? (vmstat)
PHYSBUFF and LOGBUFF could be increased (doubled). From onstat -l you
can see, that pages/io is nearly equal to the size of the buffer. This
should be about 75% of size.

Hope this will help a bit to increase performance.
Keep us informed.

Frank

ka schrieb:

Quote:

> We buy a new server RS6000 H80 with 2 CPU and 2 Gb and install
> Informix 9.20 and try install Baan IV but we got a big trouble
> with perfomance. We notice that Informix use tempdbs too intensive
> Very strange but Informix do sort not in the memory but in disk
> space (I/O 3 Mb/s with tempdbs).

> pdqpriority=1
> INFX_CPUVP_PRIORITY=60
> NODEFDAC=yes

> onconfig

> #**************************************************************************
> #
> #                          INFORMIX SOFTWARE, INC.
> #
> #  Title:       onconfig.std
> #  Description: Informix Dynamic Server 2000 Configuration Parameters
> #
> #**************************************************************************

> # Root Dbspace Configuration

> ROOTNAME        rootdbs         # Root dbspace name
> ROOTPATH        /informix/db/raw_root
>                                 # Path for device containing root
> dbspace
> ROOTOFFSET      8               # Offset of root dbspace into device
> (Kbytes)
> ROOTSIZE        605352          # 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         rootdbs         # Location (dbspace) of physical log
> PHYSFILE        50000           # Physical log file size (Kbytes)

> # Logical Log Configuration

> LOGFILES        10              # Number of logical log files
> LOGSIZE         100480          # Logical log size (Kbytes)
> LOG_BACKUP_MODE CONT

> # Diagnostics

> MSGPATH         /informix/online.log # System message log file path
> CONSOLE         /informix/console # System console message path
> ALARMPROGRAM    /informix/etc/log_full.sh # Alarm program path
> TBLSPACE_STATS  0               # Maintain tblspace statistics

> # System Archive Tape Device

> TAPEDEV         /dev/null       # Tape device path
> TAPEBLK         64              # Tape block size (Kbytes)
> TAPESIZE        600000          # Maximum amount of data to put on
> tape (Kbytes)

> # Log Archive Tape Device

> LTAPEDEV        /dev/null       # Log tape device path
> LTAPEBLK        64              # Log tape block size (Kbytes)
> LTAPESIZE       600000          # Max amount of data to put on log
> tape (Kbytes)

> # Optical

> STAGEBLOB                       # Informix Dynamic Server 2000 staging
> area

> # System Configuration

> SERVERNUM       0               # Unique id corresponding to a OnLine
> instance
> DBSERVERNAME    h80lo_on        # Name of default database server
> #DBSERVERALIASES h80lo_ol          # List of alternate dbservernames
> #NETTYPE         soctcp,2,,NET   # Override sqlhosts nettype
> parameters

> #NETTYPE        ipcshm,2,,CPU   # Override sqlhosts nettype parameters

> 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
> NUMCPUVPS       2               # Number of user (cpu) vps
> SINGLE_CPU_VP   0               # If non-zero, limit number of cpu vps
> to one

> NOAGE           1               # Process aging
> AFF_SPROC       0               # Affinity start processor
> AFF_NPROCS      0               # Affinity number of processors

> # Shared Memory Parameters

> LOCKS           1000000         # Maximum number of locks
> BUFFERS         200000          # Maximum number of shared buffers
> NUMAIOVPS       10              # Number of IO vps
> PHYSBUFF        64              # Physical log buffer size (Kbytes)
> LOGBUFF         64              # Logical log buffer size (Kbytes)
> LOGSMAX         16              # Maximum number of logical log files
> CLEANERS        127             # Number of buffer cleaner processes
> SHMBASE         0x30000000        # Shared memory base address
> SHMVIRTSIZE     400000          #131072# initial virtual shared memory
> segment size
> SHMADD          65536           #32768# Size of new shared memory
> segments (Kbytes)
> SHMTOTAL        0               # Total shared memory (Kbytes).
> 0=>unlimited
> CKPTINTVL       600             # Check point interval (in sec)
> LRUS            127             # 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       0x12c             # Transaction timeout (in sec)
> STACKSIZE       64              # 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
> DRINTERVAL      30              # DR max time between DR buffer
> flushes (in sec)
> DRTIMEOUT       30              # DR network timeout (in sec)
> DRLOSTFOUND     /informix/etc/dr.lostfound # DR lost+found file path

> # CDR Variables
> 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_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)

> # Backup/Restore variables
> BAR_ACT_LOG     /tmp/bar_act.log
> BAR_MAX_BACKUP  0
> BAR_RETRY       1
> BAR_NB_XPORT_COUNT 10
> BAR_XFER_BUF_SIZE 31
> RESTARTABLE_RESTORE off
> BAR_PROGRESS_FREQ 0

> # Informix Storage Manager variables
> ISM_DATA_POOL   ISMData
> ISM_LOG_POOL    ISMLogs

> # Read Ahead Variables
> RA_PAGES        32              # Number of pages to attempt to read
> ahead
> RA_THRESHOLD    28              # 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.

> DBSPACETEMP     tempdbs,tempdbs1,tempdbs2,templogdbs # Default temp
> dbspaces

> # 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         /tmp            # Preserve diagnostics in this
> directory
> DUMPSHMEM       0               # 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

> FILLFACTOR      90              # 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)
> MAX_PDQPRIORITY 40              # Maximum allowed pdqpriority
> DS_MAX_QUERIES  10              # Maximum number of decision support
> queries
> DS_TOTAL_MEMORY 1677721         #8192# 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      2               # To hint the optimizer

> DIRECTIVES      1               # Optimizer DIRECTIVES ON (1/Default)
> or OFF (0)

> ONDBSPACEDOWN   2  

...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 Bad Perfomance Baan + Informix 9.20 + AIX4.3.3


Quote:
>update statistics (dostats) done ?
>From onstat -p you can see there have been 100000 seqscans in 7,5 h
>uptime. This is really much.
>Further more you can decrease SHMVIRTSIZE. only about 10 percent of it
>are used (onstat -g seg). You can decrease it from 400000 to 80000.
>You are using about 1,3 GB memory for informix. What else is running on
>this machine ?
>Are you running out of free physical memory ? (vmstat)
>PHYSBUFF and LOGBUFF could be increased (doubled). From onstat -l you
>can see, that pages/io is nearly equal to the size of the buffer. This
>should be about 75% of size.

>Hope this will help a bit to increase performance.
>Keep us informed.

>Frank

I have made recommended changes, but the productivity has not grown
noticeably.

Quote:
>BUFFERS         250000        
>PHYSBUFF        128        
>LOGBUFF         128        
>SHMVIRTSIZE     200000        

The main problem seems to me in that that not looking
on a great many RAM ( we have 2GB RAM) Informix
make sorting on the disk (there is a great many
of the tables in tempdbs,
Also there is a very intensive disk I/O with disks on which is
disposed tempdbs).

TOP :
Real memory:    1280.9M procs   37.3M files  729.8M free 2048.0M total
Virtual memory: 2.4M used       2941.6M free 2944.0M total

# vmstat 1
kthr     memory             page              faults        cpu    
----- ----------- ------------------------ ------------ -----------
 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa
 0  0 302953 186575   0   0   0   0    0   0 203  400 234  2  1 96  0
 0  1 302953 186575   0   0   0   0    0   0 550 5603 938  5  2 90  4

# onstat -g seg

Informix Dynamic Server 2000 Version 9.20.UC4   -- On-Line -- Up
01:41:42 -- 1312544 Kbytes

Segment Summary:
id       key        addr     size       ovhd     class blkused
blkfree
1        1381386241 30000000 1139245056 37156    R     278115   21
2        1381386242 80000000 204800000  6856     V     9186     40814
Total:   -          -        1344045056 -        -     287301   40835

   (* segment locked in memory)
# onstat -l

Informix Dynamic Server 2000 Version 9.20.UC4   -- On-Line -- Up
01:41:11 -- 1312544 Kbytes

Physical Logging
Buffer bufused  bufsize  numpages numwrits pages/io
  P-2  0        32       107      8        13.38
      phybegin physize  phypos   phyused  %used  
      100107   12500    2706     0        0.00    

Logical Logging
Buffer bufused  bufsize  numrecs  numpages numwrits recs/pages
pages/io
  L-3  0        32       15439    352      20       43.9       17.6
        Subsystem    numrecs  Log Space used
        OLDRSAM      15439    1415048      

address  number   flags    uniqid   begin        size     used
%used
3523cb9c 1        U-B----  31       1031db       2620     2620
100.00
3523cbf0 4        U---C-L  32       200035      25120     5447
21.68
3523cc0c 5        U-B----  23       206255      25120    25120
100.00
3523cc28 6        U-B----  24       20c475      25120    25120
100.00
3523cc44 7        U-B----  25       212695      25120    25120
100.00
3523cc60 8        U-B----  26       2188b5      25120    25120
100.00
3523cc7c 9        U-B----  27       21ead5      25120    25120
100.00
3523cc98 10       U-B----  28       224cf5      25120    25120
100.00
3523ccb4 11       U-B----  29       22af15      25120    25120
100.00
3523ccd0 12       U-B----  30       231135      25120    25120
100.00

- Show quoted text -

Quote:
>ka schrieb:

>> We buy a new server RS6000 H80 with 2 CPU and 2 Gb and install
>> Informix 9.20 and try install Baan IV but we got a big trouble
>> with perfomance. We notice that Informix use tempdbs too intensive
>> Very strange but Informix do sort not in the memory but in disk
>> space (I/O 3 Mb/s with tempdbs).

>> pdqpriority=1
>> INFX_CPUVP_PRIORITY=60
>> NODEFDAC=yes

>> onconfig

>> #**************************************************************************
>> #
>> #                          INFORMIX SOFTWARE, INC.
>> #
>> #  Title:       onconfig.std
>> #  Description: Informix Dynamic Server 2000 Configuration Parameters
>> #
>> #**************************************************************************

>> # Root Dbspace Configuration

>> ROOTNAME        rootdbs         # Root dbspace name
>> ROOTPATH        /informix/db/raw_root
>>                                 # Path for device containing root
>> dbspace
>> ROOTOFFSET      8               # Offset of root dbspace into device
>> (Kbytes)
>> ROOTSIZE        605352          # 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         rootdbs         # Location (dbspace) of physical log
>> PHYSFILE        50000           # Physical log file size (Kbytes)

>> # Logical Log Configuration

>> LOGFILES        10              # Number of logical log files
>> LOGSIZE         100480          # Logical log size (Kbytes)
>> LOG_BACKUP_MODE CONT

>> # Diagnostics

>> MSGPATH         /informix/online.log # System message log file path
>> CONSOLE         /informix/console # System console message path
>> ALARMPROGRAM    /informix/etc/log_full.sh # Alarm program path
>> TBLSPACE_STATS  0               # Maintain tblspace statistics

>> # System Archive Tape Device

>> TAPEDEV         /dev/null       # Tape device path
>> TAPEBLK         64              # Tape block size (Kbytes)
>> TAPESIZE        600000          # Maximum amount of data to put on
>> tape (Kbytes)

>> # Log Archive Tape Device

>> LTAPEDEV        /dev/null       # Log tape device path
>> LTAPEBLK        64              # Log tape block size (Kbytes)
>> LTAPESIZE       600000          # Max amount of data to put on log
>> tape (Kbytes)

>> # Optical

>> STAGEBLOB                       # Informix Dynamic Server 2000 staging
>> area

>> # System Configuration

>> SERVERNUM       0               # Unique id corresponding to a OnLine
>> instance
>> DBSERVERNAME    h80lo_on        # Name of default database server
>> #DBSERVERALIASES h80lo_ol          # List of alternate dbservernames
>> #NETTYPE         soctcp,2,,NET   # Override sqlhosts nettype
>> parameters

>> #NETTYPE        ipcshm,2,,CPU   # Override sqlhosts nettype parameters

>> 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
>> NUMCPUVPS       2               # Number of user (cpu) vps
>> SINGLE_CPU_VP   0               # If non-zero, limit number of cpu vps
>> to one

>> NOAGE           1               # Process aging
>> AFF_SPROC       0               # Affinity start processor
>> AFF_NPROCS      0               # Affinity number of processors

>> # Shared Memory Parameters

>> LOCKS           1000000         # Maximum number of locks
>> BUFFERS         200000          # Maximum number of shared buffers
>> NUMAIOVPS       10              # Number of IO vps
>> PHYSBUFF        64              # Physical log buffer size (Kbytes)
>> LOGBUFF         64              # Logical log buffer size (Kbytes)
>> LOGSMAX         16              # Maximum number of logical log files
>> CLEANERS        127             # Number of buffer cleaner processes
>> SHMBASE         0x30000000        # Shared memory base address
>> SHMVIRTSIZE     400000          #131072# initial virtual shared memory
>> segment size
>> SHMADD          65536           #32768# Size of new shared memory
>> segments (Kbytes)
>> SHMTOTAL        0               # Total shared memory (Kbytes).
>> 0=>unlimited
>> CKPTINTVL       600             # Check point interval (in sec)
>> LRUS            127             # 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       0x12c             # Transaction timeout (in sec)
>> STACKSIZE       64              # 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
>> DRINTERVAL      30              # DR max time between DR buffer
>> flushes (in sec)
>> DRTIMEOUT       30              # DR network timeout (in sec)
>> DRLOSTFOUND     /informix/etc/dr.lostfound # DR lost+found file path

>> # CDR Variables
>> 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_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

...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 Bad Perfomance Baan + Informix 9.20 + AIX4.3.3
Hi!

Have you tried this with OPTCOMPIND = 0 instead of 2. You should
be able to setup shell variable, then run some tests instead of changing config.
It made a big difference for me (different system, but still with ERP package).

HTH

Michael

Quote:



> >update statistics (dostats) done ?
> >From onstat -p you can see there have been 100000 seqscans in 7,5 h
> >uptime. This is really much.
> >Further more you can decrease SHMVIRTSIZE. only about 10 percent of it
> >are used (onstat -g seg). You can decrease it from 400000 to 80000.
> >You are using about 1,3 GB memory for informix. What else is running on
> >this machine ?
> >Are you running out of free physical memory ? (vmstat)
> >PHYSBUFF and LOGBUFF could be increased (doubled). From onstat -l you
> >can see, that pages/io is nearly equal to the size of the buffer. This
> >should be about 75% of size.

> >Hope this will help a bit to increase performance.
> >Keep us informed.

> >Frank

> I have made recommended changes, but the productivity has not grown
> noticeably.

> >BUFFERS         250000
> >PHYSBUFF        128
> >LOGBUFF         128
> >SHMVIRTSIZE     200000

> The main problem seems to me in that that not looking
> on a great many RAM ( we have 2GB RAM) Informix
> make sorting on the disk (there is a great many
> of the tables in tempdbs,
> Also there is a very intensive disk I/O with disks on which is
> disposed tempdbs).

> TOP :
> Real memory:    1280.9M procs   37.3M files  729.8M free 2048.0M total
> Virtual memory: 2.4M used       2941.6M free 2944.0M total

> # vmstat 1
> kthr     memory             page              faults        cpu
> ----- ----------- ------------------------ ------------ -----------
>  r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa
>  0  0 302953 186575   0   0   0   0    0   0 203  400 234  2  1 96  0
>  0  1 302953 186575   0   0   0   0    0   0 550 5603 938  5  2 90  4

> # onstat -g seg

> Informix Dynamic Server 2000 Version 9.20.UC4   -- On-Line -- Up
> 01:41:42 -- 1312544 Kbytes

> Segment Summary:
> id       key        addr     size       ovhd     class blkused
> blkfree
> 1        1381386241 30000000 1139245056 37156    R     278115   21
> 2        1381386242 80000000 204800000  6856     V     9186     40814
> Total:   -          -        1344045056 -        -     287301   40835

>    (* segment locked in memory)
> # onstat -l

> Informix Dynamic Server 2000 Version 9.20.UC4   -- On-Line -- Up
> 01:41:11 -- 1312544 Kbytes

> Physical Logging
> Buffer bufused  bufsize  numpages numwrits pages/io
>   P-2  0        32       107      8        13.38
>       phybegin physize  phypos   phyused  %used
>       100107   12500    2706     0        0.00

> Logical Logging
> Buffer bufused  bufsize  numrecs  numpages numwrits recs/pages
> pages/io
>   L-3  0        32       15439    352      20       43.9       17.6
>         Subsystem    numrecs  Log Space used
>         OLDRSAM      15439    1415048

> address  number   flags    uniqid   begin        size     used
> %used
> 3523cb9c 1        U-B----  31       1031db       2620     2620
> 100.00
> 3523cbf0 4        U---C-L  32       200035      25120     5447
> 21.68
> 3523cc0c 5        U-B----  23       206255      25120    25120
> 100.00
> 3523cc28 6        U-B----  24       20c475      25120    25120
> 100.00
> 3523cc44 7        U-B----  25       212695      25120    25120
> 100.00
> 3523cc60 8        U-B----  26       2188b5      25120    25120
> 100.00
> 3523cc7c 9        U-B----  27       21ead5      25120    25120
> 100.00
> 3523cc98 10       U-B----  28       224cf5      25120    25120
> 100.00
> 3523ccb4 11       U-B----  29       22af15      25120    25120
> 100.00
> 3523ccd0 12       U-B----  30       231135      25120    25120
> 100.00

> >ka schrieb:

> >> We buy a new server RS6000 H80 with 2 CPU and 2 Gb and install
> >> Informix 9.20 and try install Baan IV but we got a big trouble
> >> with perfomance. We notice that Informix use tempdbs too intensive
> >> Very strange but Informix do sort not in the memory but in disk
> >> space (I/O 3 Mb/s with tempdbs).

> >> pdqpriority=1
> >> INFX_CPUVP_PRIORITY=60
> >> NODEFDAC=yes

> >> onconfig

> >> #**************************************************************************
> >> #
> >> #                          INFORMIX SOFTWARE, INC.
> >> #
> >> #  Title:       onconfig.std
> >> #  Description: Informix Dynamic Server 2000 Configuration Parameters
> >> #
> >> #**************************************************************************

> >> # Root Dbspace Configuration

> >> ROOTNAME        rootdbs         # Root dbspace name
> >> ROOTPATH        /informix/db/raw_root
> >>                                 # Path for device containing root
> >> dbspace
> >> ROOTOFFSET      8               # Offset of root dbspace into device
> >> (Kbytes)
> >> ROOTSIZE        605352          # 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         rootdbs         # Location (dbspace) of physical log
> >> PHYSFILE        50000           # Physical log file size (Kbytes)

> >> # Logical Log Configuration

> >> LOGFILES        10              # Number of logical log files
> >> LOGSIZE         100480          # Logical log size (Kbytes)
> >> LOG_BACKUP_MODE CONT

> >> # Diagnostics

> >> MSGPATH         /informix/online.log # System message log file path
> >> CONSOLE         /informix/console # System console message path
> >> ALARMPROGRAM    /informix/etc/log_full.sh # Alarm program path
> >> TBLSPACE_STATS  0               # Maintain tblspace statistics

> >> # System Archive Tape Device

> >> TAPEDEV         /dev/null       # Tape device path
> >> TAPEBLK         64              # Tape block size (Kbytes)
> >> TAPESIZE        600000          # Maximum amount of data to put on
> >> tape (Kbytes)

> >> # Log Archive Tape Device

> >> LTAPEDEV        /dev/null       # Log tape device path
> >> LTAPEBLK        64              # Log tape block size (Kbytes)
> >> LTAPESIZE       600000          # Max amount of data to put on log
> >> tape (Kbytes)

> >> # Optical

> >> STAGEBLOB                       # Informix Dynamic Server 2000 staging
> >> area

> >> # System Configuration

> >> SERVERNUM       0               # Unique id corresponding to a OnLine
> >> instance
> >> DBSERVERNAME    h80lo_on        # Name of default database server
> >> #DBSERVERALIASES h80lo_ol          # List of alternate dbservernames
> >> #NETTYPE         soctcp,2,,NET   # Override sqlhosts nettype
> >> parameters

> >> #NETTYPE        ipcshm,2,,CPU   # Override sqlhosts nettype parameters

> >> 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
> >> NUMCPUVPS       2               # Number of user (cpu) vps
> >> SINGLE_CPU_VP   0               # If non-zero, limit number of cpu vps
> >> to one

> >> NOAGE           1               # Process aging
> >> AFF_SPROC       0               # Affinity start processor
> >> AFF_NPROCS      0               # Affinity number of processors

> >> # Shared Memory Parameters

> >> LOCKS           1000000         # Maximum number of locks
> >> BUFFERS         200000          # Maximum number of shared buffers
> >> NUMAIOVPS       10              # Number of IO vps
> >> PHYSBUFF        64              # Physical log buffer size (Kbytes)
> >> LOGBUFF         64              # Logical log buffer size (Kbytes)
> >> LOGSMAX         16              # Maximum number of logical log files
> >> CLEANERS        127             # Number of buffer cleaner processes
> >> SHMBASE         0x30000000        # Shared memory base address
> >> SHMVIRTSIZE     400000          #131072# initial virtual shared memory
> >> segment size
> >> SHMADD          65536           #32768# Size of new shared memory
> >> segments (Kbytes)
> >> SHMTOTAL        0               # Total shared memory (Kbytes).
> >> 0=>unlimited
> >> CKPTINTVL       600             # Check point interval (in sec)
> >> LRUS            127             # 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       0x12c             # Transaction timeout (in sec)
> >> STACKSIZE       64              # 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
> >> DRINTERVAL      30              # DR max time between DR buffer
> >> flushes (in sec)

...

read more »



Fri, 21 Mar 2003 10:36:37 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Error Connecting to a Informix Server 9.20

2. php and informix 9.20

3. UW7, Informix 9.20, and 10 GB dbspace

4. php and informix 9.20

5. Perfomance Worse after purge?

6. Terminal services and 9.20

7. IDS 2000 version 9.20 How stable?

8. IDS 9.20 crashes Linux

9. Problem with 9.20 and HDR

10. 7.31 vs 9.20

11. suse linux 7.3 / kernel parameter/ IDS 9.20

12. 7.31 vs 9.20


 
Powered by phpBB® Forum Software