Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Location
    Melbourne, Florida
    Posts
    4

    Unhappy Unanswered: Informix Performance on Solaris Sun Blade

    I am running Informix 7.31 32 bit on a Sun Blade 1500 running Solaris 5.8 and my performance is very poor.

    sar indicates no problems with CPU, RAM or I/O.

    I have listed my configuration parameters below. Does anyone know of anything I can do to improve performance?

    $ onstat -c

    Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 55 days 22:42:38 -- 30736 Kbytes

    Configuration File: /export/home/informix/etc/onconfig
    #
    # Script: onconfig.informix
    # Subsystem: 1
    # %version: 1.1.1 %
    #
    # INFORMIX SOFTWARE, INC.
    #
    # Title: onconfig.std
    # Description: INFORMIX-OnLine Configuration Parameters
    #
    # During installation the location DISKNAME must be changed to raw partition
    #
    #************************************************* *************************

    # Root Dbspace Configuration

    ROOTNAME rootdbs # Root dbspace name
    ROOTPATH /dev/rdsk/c0t0d0s6 # Path for device containing root dbspace
    ROOTOFFSET 100 # Offset of root dbspace into device (Kbytes)
    ROOTSIZE 1000000 # 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 1000 # Physical log file size (Kbytes)

    # Logical Log Configuration

    LOGFILES 9 # Number of logical log files
    LOGSIZE 1000 # Logical log size (Kbytes)

    # Diagnostics

    MSGPATH /export/home/informix/online.log # System message log file path
    CONSOLE /export/home/informix/console.log # System console message path
    ALARMPROGRAM # Alarm program path

    # System Archive Tape Device

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

    # Log Archive Tape Device

    LTAPEDEV /dev/null # Log tape device path
    LTAPEBLK 16 # 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 corresponding to a OnLine instance
    DBSERVERNAME omcserver # Name of default database server
    DBSERVERALIASES dbremote # of alternate dbservernames
    NETTYPE # 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 0 # 0 for single-processor, 1 for multi-processor
    NUMCPUVPS 1 # Number of user (cpu) vps
    SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one

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

    # Shared Memory Parameters

    LOCKS 100000 # Maximum number of locks
    BUFFERS 200 # Maximum number of shared buffers
    NUMAIOVPS # Number of IO vps
    PHYSBUFF 32 # Physical log buffer size (Kbytes)
    LOGBUFF 32 # Logical log buffer size (Kbytes)
    LOGSMAX 40 # Maximum number of logical log files
    CLEANERS 1 # Number of buffer cleaner processes
    SHMBASE 0xa000000 # Shared memory base address
    SHMVIRTSIZE 8000 # 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 8 # 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 32 # Stack size (Kbytes)

    # System Page Size
    # BUFFSIZE - OnLine no longer supports this configuration parameter.

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

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

    # DBSPACETEMP:
    # OnLine equivalent of DBTEMP for SE.

    DBSPACETEMP # 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 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*
    # The following parameters control the type and level of secure auditing
    # present in the OnLine system. By default, ADTMODE is 0 and auditing
    # is disabled

    ADTMODE 0 # Auditing mode
    ADTPATH /tmp # Directory where audit trails will be written by OnLine
    ADTSIZE 50000 # Maximum size of any single audit trail file
    ADTERR 0 # 0 ==> retry failed audit writes; 1 ==> log failure

    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)
    PDQPRIORITY 0 # Degree of parallelism: 0 ... 100,
    # OFF => 0, LOW => 1, HIGH => 100
    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 0 # 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)

    $ onstat -g iof

    Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 55 days 22:46:44 -- 30736 Kbytes

    AIO global files:
    gfd pathname totalops dskread dskwrite io/s
    3 c0t0d0s6 9974580 8587646 1386934 2.1

    $ onstat -p

    Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 55 days 22:47:34 -- 30736 Kbytes

    Profile
    dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
    28461424 530031 449106960 93.66 1904448 1214565 7091575 73.14

    isamtot open start read write rewrite delete commit rollbk
    235529842 7273150 18114360 157123512 1488876 168187 450015 405928 51

    gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
    0 0 0 0 0 0 0

    ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
    0 0 43 9156.71 1153.19 17871 35804

    bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
    6458856 0 432367738 0 0 1950 146846 429791

    ixda-RA idx-RA da-RA RA-pgsused lchwaits
    172167 26679 26518650 26648083 16198

  2. #2
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    Hi,

    You have quite some work to do, the configuration is far from optimal. First of all you probable need to create several dbspaces. But most important and some easy steps that you can follow are some parameter changes:

    BUFFERS:
    Now 200, this measn 400KB cache. Raise this a lot. I don't know your database size but let's say raising it upto 128 MB. So new value: 64000
    Main idea is, the bigger the better (as long as you have memory ofcourse)

    LRUS:
    Change this value to 64.

    CLEANERS:
    Change this to 4

    OPTCOMPIND:
    Change this to 0

    And give the command: UPDATE STATISTICS for every database.

    I think your performance will be beter after this.

    Greetz,

    Rob Prop

  3. #3
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    Also increase the size of you PHYSLOG

  4. #4
    Join Date
    Jan 2005
    Location
    Melbourne, Florida
    Posts
    4

    Thanks for the help

    I modified the parameters suggested above and achieved better than 50% reduction in wait times.

    What do you mean by "First of all you probably need to create several dbspaces"?

    Thanks for the help.

  5. #5
    Join Date
    Jan 2005
    Location
    Melbourne, Florida
    Posts
    4

    PHYSxxxx

    I don't know what PHYSLOG is, but I have PHYSFILE set to 1000 KBytes and PHYSBUFF set to 32 KBytes.

    My page size is 8192, and my number of concurrent connections should be very, very low.

    What do you recommend for PHYSFILE and PHYSBUFF?

  6. #6
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    The physical log parameters have a relation with the writing of data to disk,and will not have a enormous impact on the overall performance. About your question regarding my advice to create multiple dbspaces I can say: When you have multiple dbspaces you can split for example data and indexes and improve parallel disk access. In the manuals of IDS you can find a lot more info about this topic.

    Hope this helps,

    Rob

Posting Permissions

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