Professional Web Applications Themes

Poor Informix performance - Informix

Hi, We have a database, a rather huge one, with about 10G of metadata...and almost 400 users... We have serious performance problems over the past few mnths....and all sorts of tuning etc...didnt seem to help much.. The System Admins claim that nothing is wrong with the network and its all the database's fault...Sometimes, the users are just waiting and waiting till a query returns.. Running 'top' on the system gives this O/P load averages: 3.42, 3.49, 3.56 16:33:39 291 processes: 287 sleeping, 4 on cpu CPU states: 59.4% idle, 30.9% user, 9.7% kernel, 0.0% iowait, 0.0% swap Memory: 16G real, ...

  1. #1

    Default Poor Informix performance

    Hi,

    We have a database, a rather huge one, with about 10G of
    metadata...and almost 400 users...
    We have serious performance problems over the past few mnths....and
    all sorts of tuning etc...didnt seem to help much..
    The System Admins claim that nothing is wrong with the network and its
    all the database's fault...Sometimes, the users are just waiting and
    waiting till a query returns..
    Running 'top' on the system gives this O/P


    load averages: 3.42, 3.49, 3.56
    16:33:39
    291 processes: 287 sleeping, 4 on cpu
    CPU states: 59.4% idle, 30.9% user, 9.7% kernel, 0.0% iowait, 0.0%
    swap
    Memory: 16G real, 9110M free, 4350M swap in use, 25G swap free

    PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
    16164 ccm_root 1 0 0 23M 22M cpu/10 185.0H 12.43% ccm_aci
    15187 informix 1 41 -10 2853M 2140M sleep 187.7H 5.82% oninit
    15189 informix 1 40 -10 2853M 2490M cpu/2 193.8H 5.69% oninit
    15190 informix 1 40 -10 2853M 2133M cpu/3 166.8H 5.03% oninit
    15191 informix 1 59 -10 2853M 2142M sleep 152.3H 4.09% oninit
    15192 informix 1 52 -10 2853M 2808M sleep 143.1H 2.57% oninit
    15198 informix 1 59 -10 2853M 2017M sleep 754:11 0.93% oninit
    15200 informix 1 59 -10 2848M 2012M sleep 554:28 0.60% oninit
    15201 informix 1 59 -10 2848M 2012M sleep 504:17 0.47% oninit
    25836 ccm_root 1 58 0 9832K 8144K sleep 0:17 0.33%
    ccm_eng_inf
    15202 informix 1 59 -10 2848M 2012M sleep 534:22 0.28% oninit
    15199 informix 1 59 -10 2848M 2012M sleep 525:20 0.23% oninit
    15193 informix 1 59 -10 2846M 2005M sleep 230:16 0.18% oninit
    15194 informix 1 59 -10 2846M 2005M sleep 117:16 0.12% oninit
    15195 informix 1 59 -10 2846M 2036M sleep 86:45 0.05% oninit

    And its almost always like this...

    In fact I have seen the load averages to be upto 8 and 9 sometimes...

    Here's our onconfig..

    #************************************************* *************************
    #
    # INFORMIX SOFTWARE, INC. & Continuus Software
    Corporation
    #
    # Title: onconfig.std
    # Description: INFORMIX-OnLine Configuration Parameters
    # C/CM Server: sdcms001
    #
    #************************************************* *************************

    # Root Dbspace Configuration

    ROOTNAME rootdbs # Root dbspace name
    ROOTPATH /local/groups/gscm/informix/sdcms001/sdcms001_root.dbs
    # Path for device containing root
    dbspace
    ROOTOFFSET 0 # Offset of root dbspace into device
    (Kbytes)
    ROOTSIZE 553248 # Size of root dbspace (Kbytes)

    # Disk Mirroring Configuration

    MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
    MIRRORPATH # Path for device containing root
    dbspace mirror
    MIRROROFFSET 0 # Offset into mirror device (Kbytes)

    # Physical Log Configuration

    PHYSDBS rootdbs # Name of dbspace that contains
    physical log
    PHYSFILE 50176 # Physical log file size (Kbytes)

    # Logical Log Configuration

    LOGFILES 1024 # Number of logical log files
    LOGSIZE 1024 # Size of each logical log file
    (Kbytes)

    # Message Files

    MSGPATH /apps/ccm/informix/log/sdcms001.log
    # OnLine message log pathname
    CONSOLE /apps/ccm/informix/log/sdcms001.msg
    # System console message pathname

    # Archive Tape Device

    TAPEDEV /apps/ccm/informix/etc/sdcms001.tapedev
    # Archive tape device pathname
    TAPEBLK 16 # Archive tape block size (Kbytes)
    TAPESIZE 1024000 # Max amount of data to put on tape
    (Kbytes)
    # Logical Log Backup Tape Device

    LTAPEDEV /dev/null # Logical log tape device pathname
    LTAPEBLK 16 # Logical log tape block size (Kbytes)
    LTAPESIZE 10240 # Max amount of data to put on log
    tape (Kbytes)

    # Optical

    STAGEBLOB # INFORMIX-OnLine/Optical staging area

    # System Configuration

    SERVERNUM 1 # Unique id associated with this
    OnLine instance
    DBSERVERNAME sdcms001 # Unique name of this OnLine instance
    DBSERVERALIASES sdcms001_net # List of alternate dbservernames
    NETTYPE ipcshm,5,100,CPU # Configure poll thread(s) for
    nettype
    NETTYPE tlitcp,5,100,NET # Configure poll thread(s) for
    nettype
    DEADLOCK_TIMEOUT 60 # Max time to wait for lock in
    distributed env.
    RESIDENT -1 # Forced residency flag (Yes = 1, No =
    0)

    MULTIPROCESSOR 1 # 0 for single-processor, 1 for
    multi-processor
    NUMCPUVPS 5 # 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 1 # Affinity start processor
    AFF_NPROCS 5 # Affinity number of processors

    # Shared Memory Parameters

    LOCKS 750000 # Maximum number of locks
    BUFFERS 900000 # Maximum number of shared memory
    buffers
    NUMAIOVPS # Number of IO vps
    PHYSBUFF 256 # Size of physical log buffers
    (Kbytes)
    LOGBUFF 1024 # Size of logical log buffers (Kbytes)
    LOGSMAX 1024 # Maximum number of logical log files
    CLEANERS 32 # Number of page-cleaner processes
    SHMBASE 0xa000000 # Shared memory base address
    SHMVIRTSIZE 100000 # initial virtual shared memory
    segment size
    SHMADD 32768 # Size of new shared memory segments
    (Kbytes)
    SHMTOTAL 0 # Total shared memory (Kbytes).
    0=>unlimited
    CKPTINTVL 300 # Checkpoint interval (in seconds)

    LRUS 127 # Number of LRU queues
    LRU_MAX_DIRTY 1 # LRU modified begin-cleaning limit
    (percent)
    LRU_MIN_DIRTY 0 # LRU modified end-cleaning limit
    (percent)
    LTXHWM 50 # Long TX high-water mark (percent)
    LTXEHWM 60 # Long TX exclusive high-water mark
    (percent)
    TXTIMEOUT 0x12c # Transaction timeout (in seconds)
    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'.
    # Machine- and Product-Specific Parameters

    # 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 tion 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 /apps/ccm/informix/etc/dr.lostfound # DR lost+found
    file path

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

    # 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

    # Read Ahead Variables
    RA_PAGES # Number of pages to attempt to read
    ahead
    RA_THRESHOLD # Number of pages left before next
    group

    # 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 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

    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 100 # Maximum allowed pdqpriority
    DS_MAX_QUERIES # Maximum number of decision support
    queries
    DS_TOTAL_MEMORY # 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

    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

    ALARMPROGRAM /apps/ccm/informix/etc/no_log.sh # Alarm program path
    SYSALARMPROGRAM /apps/ccm/informix/etc/evidence.sh # System Alarm
    program path
    TBLSPACE_STATS 0
    CDR_LOGDELTA 30 # % of log space allowed in queue
    memory
    :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

    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

    ALARMPROGRAM /apps/ccm/informix/etc/no_log.sh # Alarm program path
    SYSALARMPROGRAM /apps/ccm/informix/etc/evidence.sh # System Alarm
    program path
    TBLSPACE_STATS 0
    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)
    ISM_DATA_POOL ISMData # If the data pool name is changed, be
    sure to
    ISM_LOG_POOL ISMLogs
    DBSPACETEMP temp # Default temp dbspaces
    OPT_GOAL -1
    DIRECTIVES 1
    RESTARTABLE_RESTORE off
    # 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.


    Any help would be much appreciated ...
    Thanks in advance
    Viji
    Viji Guest

  2. #2

    Default Re: Poor Informix performance

    Viji wrote: 

    VPS # Number of IO vps 

    You might check the NUMAIOVPS configuration parameter.
    Your rootdbs seems to be a cooked file, not a raw device
    (I'm not sure, it just a guess.).

    So I guess that your other dbspaces are cooked files too.
    If this is the case, you will not benefit from the advantages
    of kernel AIO. You have to make sure that you configured enough
    NUMAIOVPS.

    HTH.

    Best regards

    Eric
    --
    IT-Consulting Herber
    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************
    Eric Guest

  3. #3

    Default Re: Poor Informix performance

    You don't mention the OS, the informix version or anything about the
    server. But ...

    you appear to be using cooked files
    you say there are 400 users but you've configure for 200 at startup
    there are no temp dbspaces configured
    what is ccm_aci

    Fire out your
    onstat -R,
    onstat -p,
    onstat -F,
    onstat -m,
    onstat -l

    Viji wrote: 
    [cutting]

    --
    Paul Watson #
    Oninit Ltd # Growing old is mandatory
    Tel: +44 1436 672201 # Growing up is optional
    Fax: +44 1436 678693 #
    Mob: +44 7818 003457 #
    www.oninit.com #
    Paul Guest

  4. #4

    Default Re: Poor Informix performance


    "Paul Watson" <com> wrote in message
    news:com... 

    onstat -u ...what are the sessions with the largest reads/writes doing...
     
    > [cutting]
    >
    > --
    > Paul Watson #
    > Oninit Ltd # Growing old is mandatory
    > Tel: +44 1436 672201 # Growing up is optional
    > Fax: +44 1436 678693 #
    > Mob: +44 7818 003457 #
    > www.oninit.com #[/ref]


    David Guest

  5. #5

    Default Re: Poor Informix performance

    Does oncheck run clean, or have you a corrupted index somewhere that
    is no longer been used?

    has an index been dropped?

    is update stats continuing to be run or has it not been run in a long
    time?

    Eric Herber <org> wrote in message news:<c4j1o3$bank.dresdner.net>... 
    >
    > VPS # Number of IO vps 
    >
    > You might check the NUMAIOVPS configuration parameter.
    > Your rootdbs seems to be a cooked file, not a raw device
    > (I'm not sure, it just a guess.).
    >
    > So I guess that your other dbspaces are cooked files too.
    > If this is the case, you will not benefit from the advantages
    > of kernel AIO. You have to make sure that you configured enough
    > NUMAIOVPS.
    >
    > HTH.
    >
    > Best regards
    >
    > Eric[/ref]
    scottishpoet Guest

  6. #6

    Default Re: Poor Informix performance

    You also don't give us any clues as to what it is you're trying to do
    that's running badly, which is a bit like asking for directions
    without telling us where you want to go.

    Post some queries, explain output and timings.

    Andy
    Andy Guest

Similar Threads

  1. Poor Performance when using data
    By neilmiddleton in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: September 5th, 04:13 PM
  2. CFMX 7 Poor Performance
    By jorkel99 in forum Macromedia ColdFusion
    Replies: 14
    Last Post: April 11th, 02:45 PM
  3. Poor Network Performance
    By sam in forum Mac Applications & Software
    Replies: 2
    Last Post: September 1st, 08:39 PM
  4. Poor performance with 1GB of RAM
    By J. in forum Debian
    Replies: 22
    Last Post: August 13th, 11:51 PM
  5. Poor 2D Graphics performance
    By Paul Curren in forum Sun Solaris
    Replies: 6
    Last Post: July 4th, 09:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139