IDS Forum

Re: Question on SHMVIRTSIZE

Posted By: Keith Simmons
Date: Wednesday, 9 May 2007, at 5:13 a.m.

In Response To: RE: Question on SHMVIRTSIZE (Mary Gregory)

On 08/05/07, Mary Gregory <mary.gregory@rheem.com> wrote:
> I still haven't found a good way to determine whether or not adding more
> virtual memory to the instance would speed up queries or not. I think I
> might try to add another 128 MB just to see what happens. Also, I am
> thinking about adding some more BUFFERS but I am trying to be very
> cautious about not increasing my checkpoint times. From what I see
> right now, everything is performing really well and I don't really need
> to make any changes but you all might see something I don't. (I removed
> some of the onconfig file parameters to save a little space.)
>
> ONCONFIG PARAMETERS
>
> ROOTNAME a_rootdbs # Root dbspace name
> ROOTPATH /dev/inf/chunk001 # Path for device containing root
> dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device
> (Kbytes)
> ROOTSIZE 500000 # Size of root dbspace (Kbytes)
> PHYSDBS a_plogdbs # Location (dbspace) of physical log
> PHYSFILE 100000 # Physical log file size (Kbytes)
> LOGFILES 41 # Number of logical log files
> LOGSIZE 50000 # Logical log size (Kbytes)
> SERVERNUM 1 # Unique id corresponding to a OnLine
> instance
> DBSERVERNAME online_shm # Name of default database server
> DBSERVERALIASES online_tcp # List of alternate dbservernames
> NETTYPE ipcshm,3,75,CPU # Configure poll thread(s) for nettype
> NETTYPE soctcp,6,200,NET # Configure poll thread(s) for nettype
> DEADLOCK_TIMEOUT 240 # Max time to wait of lock in
> distributed env.
> RESIDENT 1 # Forced residency flag (Yes = 1, No =
> 0)
> MULTIPROCESSOR 1 # 0 for single-processor, 1 for
> multi-processor
> NUMCPUVPS 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 0 # Affinity start processor
> AFF_NPROCS 0 # Affinity number of processors
> LOCKS 500000 # Maximum number of locks
> BUFFERS 155000 # Maximum number of shared buffers
> NUMAIOVPS 2 # Number of IO vps
> PHYSBUFF 64 # Physical log buffer size (Kbytes)
> LOGBUFF 64 # Logical log buffer size (Kbytes)
> CLEANERS 64 # Number of buffer cleaner processes
> SHMBASE 0x0 # Shared memory base address
> SHMVIRTSIZE 1792000 # initial virtual shared memory segment
> size
> SHMADD 256000 # Size of new shared memory segments
> (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
> 0=>unlimited
> CKPTINTVL 600 # Check point interval (in sec)
> LRUS 64 # Number of LRU queues
> LRU_MAX_DIRTY 4.000 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 2.000 # LRU percent dirty end cleaning limit
> TXTIMEOUT 0x12c # Transaction timeout (in sec)
> STACKSIZE 64 # Stack size (Kbytes)
>
> DYNAMIC_LOGS 2
> LTXHWM 40
> LTXEHWM 55
>
> # Read Ahead Variables
> RA_PAGES 20 # Number of pages to attempt to read
> ahead
> RA_THRESHOLD 8 # Number of pages left before next group
>
> DBSPACETEMP
> tempdbs1,tempdbs2,tempdbs3,tempdbs4,tempdbs5,tempdbs6,tempdbs7
>
> FILLFACTOR 90 # Fill factor for building indexes
>
> # Parallel Database Queries (pdq)
> MAX_PDQPRIORITY 0 # Maximum allowed pdqpriority
> #DS_MAX_QUERIES # Maximum number of decision support
> queries
> #DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURN THIS
> OFF AFTER THE IMPORT
> DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURN
> THIS OFF AFTER THE IMPORT
> DS_MAX_SCANS 1048576 # Maximum number of decision support
> scans
> DATASKIP off
> 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
> 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
> BLOCKTIMEOUT 3600 # Default timeout for system block
> SYSALARMPROGRAM /home/informix/9.40/etc/evidence.sh # System Alarm
> program path
> # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
> OPT_GOAL -1
> ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or
> anything but 1)
> BAR_BSALIB_PATH /opt/omni/lib/libob2informix_64bit.sl
> DS_MAX_QUERIES 6 # Maximum number of decision support
> queries
>
> ONSTAT -P
>
> IBM Informix Dynamic Server Version 9.40.FC4 -- On-Line -- Up 4 days
> 14:29:2
> 2 -- 2196484 Kbytes
>
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 535710112 1308089992 17065711597 96.86 31296277 73506490 243924199
> 87.17
>
> isamtot open start read write rewrite delete commit
> rollbk
> 12889143879 61483810 410801398 10058850470 60258603 10476107 6562628
> 9953760 3
> 732
>
> 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 0 157714.53 101146.03 1047 2882
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 34018215 3531 16248413925 10 0 2150 3447796
> 2746919
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 137210564 13433495 105455888 253872383 7973596
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Robert Roussey(IT)
> Sent: Wednesday, May 02, 2007 5:14 PM
> To: ids@iiug.org
> Subject: RE: Question on SHMVIRTSIZE [9061]
>
> If you know the session of the batch job, you can see what pieces of
> memory it is using with "onstat -g mem".
> "onstat -g ses" will show all sessions and their total use of SHM.
> If you are using PDQ for the batch job, you should see it reflected by
> getting snapshots of "onstat -g mgm" while it is running.
>
> Maybe post your ONCONFIG file and people can comment on it as well as
> a 24hr snapshot of "onstat -p".
>
> Bob Roussey
> Unix / Informix Administration
> Spirit Airlines
> Robert.Roussey@SpiritAir.com
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Mary

With 9.4 you will be using 'fuzzy' checkpoints, therefore increasing
BUFFERS will have minimal effect on checkpoints. Your Buffers Read and
Write %ages are looowww, I would bump buffers to 200000 (to start)
then 250000 or even 300000. (Can't remember your platform but have a
recollection of plenty of memory and even on a 4Kb page machine this
is only 1.2 Gb). That will improve your %ages. Also you need to
increase LRUs (and Cleaners) to reduce the number of Buffer Waits. Try
127 for each.
If the engine has not added any additional virtual segment (onstat -g
seg) then there is little point in increasing SHMVIRTSIZE as more is
not required.

Keith

Messages In This Thread

• Question on SHMVIRTSIZE
  Mary Gregory -- Monday, 30 April 2007, at 2:33 p.m.
  • RE: Question on SHMVIRTSIZE
    Robert Roussey\(IT\) -- Monday, 30 April 2007, at 4:29 p.m.
    • RE: Question on SHMVIRTSIZE
      Mary Gregory -- Monday, 30 April 2007, at 4:37 p.m.
      • Re: Question on SHMVIRTSIZE
        DL Redden -- Monday, 30 April 2007, at 10:08 p.m.
        • RE: Question on SHMVIRTSIZE
          Mary Gregory -- Tuesday, 1 May 2007, at 9:19 a.m.
          • Re: Question on SHMVIRTSIZE
            Andreas Breitfeld -- Wednesday, 2 May 2007, at 10:55 a.m.
          • RE: Question on SHMVIRTSIZE
            Robert Roussey\(IT\) -- Wednesday, 2 May 2007, at 6:14 p.m.
            • RE: Question on SHMVIRTSIZE
              Mary Gregory -- Tuesday, 8 May 2007, at 12:06 p.m.
              • RE: Question on SHMVIRTSIZE
                Jack Parker -- Tuesday, 8 May 2007, at 12:55 p.m.
              • Re: Question on SHMVIRTSIZE
                Keith Simmons -- Wednesday, 9 May 2007, at 5:13 a.m.
                • Re: Question on SHMVIRTSIZE
                  ART KAGEL, BLOOMBERG/ 731 LEXIN -- Monday, 14 May 2007, at 12:29 p.m.
                  • Re: Question on SHMVIRTSIZE
                    DL Redden -- Thursday, 17 May 2007, at 4:29 p.m.
                    • Re: Question on SHMVIRTSIZE
                      ART KAGEL, BLOOMBERG/ 731 LEXIN -- Thursday, 17 May 2007, at 6:36 p.m.
                  • RE: Question on SHMVIRTSIZE
                    Mary Gregory -- Monday, 21 May 2007, at 10:06 a.m.
                    • RE: Question on SHMVIRTSIZE
                      ART KAGEL, BLOOMBERG/ 731 LEXIN -- Monday, 21 May 2007, at 12:48 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.