Re: onconfig recommendations
- From: darko <darko.krstic@xxxxxxxxx>
- Date: Fri, 4 Jan 2008 00:45:38 -0800 (PST)
On Jan 2, 11:09 pm, "Kennedy, Randy" <RKenn...@xxxxxxxxxxxxxxxx>
wrote:
Would like to receive some feedback on our onconfig setup for current
platform. This is the 3rd physical server that the database has moved
to and just want to ensure we are using the best settings for given
platform.
Current Server: HP rp5405 4-Way (650 MHz) with 8GB RAM. 1 73GB hard
drive (4 drives installed, but sysadmin took 2 for O/S, system and other
2 are mirrored pair so effectively 1 spindle to work with). I didn't
get to specify the configuration.
Old Server: HP something 4 way with 4GB RAM. 5 spindles to work with.
When moved to current server, I did up the buffers and locks.
We use ontape for Level 0 and logical log backups. They are done via
files and links to remote server instead of actual tape.
System is primarily OLTP with some batch jobs run overnight (off
business hours).
Any insight will be appreciated. Please let me know if you would like
any other information.
TIA,
Randy
Your system seems very unbalanced with regard to disk I/O capacity in
comparison to CPU power and RAM size. I would expect that you should
have at least a dozen, and even better 2-3 dozens of disks to have a
balance.
At least, I hope that you have a caching disk controller with lots of
battery backed memory in it. It could alleviate the problems with
write performance (you have logs and database on the same spindle).
One of primary goals here should be to minimize physical access to
disk. Hints that I think might be useful are embedded in your ONCONFIG
file listing.
Darko Krstic
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace name
ROOTPATH /db/links/rootdbs # Path for device containing root
dbspace
ROOTOFFSET 0 # Offset of root dbspace into device
(Kbytes)
ROOTSIZE 384000 # 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 60000 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 85 # Number of logical log files
LOGSIZE 12000 # Logical log size (Kbytes)
LOG_BACKUP_MODE CONT
# Diagnostics
MSGPATH /i9.4/informix/online.log # System message log file path
CONSOLE /dev/console # System console message path
ALARMPROGRAM /db/scripts/no_log.sh # Alarm program path
# System Archive Tape Device
#TAPEDEV /dev/rmt/2m # Tape device path
TAPEDEV /remote/recovery/informix/level0 # Tape device path
TAPEBLK 16 # Tape block size (Kbytes)
TAPESIZE 30000000 # Maximum amount of data to put on tape
(Kbytes)
# Log Archive Tape Device
#LTAPEDEV /dev/rmt/2m # Log tape device path
LTAPEDEV /ltape1/informix/log1 # Log tape device path
LTAPEBLK 16 # Log tape block size (Kbytes)
LTAPESIZE 4500000 # 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 courtshm # Name of default database server
DBSERVERALIASES # List of alternate dbservernames
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in
distributed env.
RESIDENT 0 # Forced residency flag (Yes = 1, No =
0)
NETTYPE soctcp,4,150,NET
NETTYPE ipcshm,1,25,NET
"CPU" could be better choice for ipcshm NETTYPE.
MULTIPROCESSOR 1 # 0 for single-processor, 1 for
multi-processor
NUMCPUVPS 16 # 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 80000 # Maximum number of locks
BUFFERS 150000 # Maximum number of shared buffers
NUMAIOVPS 26 # Number of IO vps
PHYSBUFF 64 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
CLEANERS 28 # Number of buffer cleaner processes
SHMBASE 0x0 # Shared memory base address
SHMVIRTSIZE 160000 # initial virtual shared memory segment
size
SHMADD 80000 # Size of new shared memory segments
(Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 20 # Number of LRU queues
LRU_MAX_DIRTY 5.000000 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 2.000000 # 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)
PC_POOLSIZE 110 # Stored Procedures Cache
I would multiply BUFFERS and SHMVIRTSIZE by factors between 2 and 4,
at least. You can increase LRUS and CLEANERS accordingly. Then you
might need to decrease LRU_MAX_DIRTY and LRU_MIN_DIRTY. Since they are
listed as decimals, I assume you have IDS engine 9.40 or newer.
# 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 15 # Default number of offline worker
threads
ON_RECVRY_THREADS 15 # Default number of online worker
threads
# Data Replication Variables
# DRAUTO: 0 manual, 1 retain type, 2 reverse type
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 64 # Number of pages to attempt to read
ahead
RA_THRESHOLD 8 # 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 tempdbs1,tempdbs2,tempdbs3,tempdbs4 # Default temp
dbspaces
With lots of RAM, you can put 2 temporary dbspaces on filesystems, so
in many cases OS's cache would prevent data written to them to ever go
to physical disk. Designate them as temporary during creation.
# 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
# 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
FILLFACTOR 85 # 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 90 # Maximum allowed pdqpriority
DS_MAX_QUERIES 15 # Maximum number of decision support
queries
DS_TOTAL_MEMORY 160000 # Decision support memory (Kbytes)
DS_MAX_SCANS 30 # Maximum number of decision support
scans
DATASKIP off
# 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
OPCACHEMAX 0 # Maximum optical cache size (Kbytes)
CDR_EVALTHREADS 1,2 # evaluator threads
(per-cpu-vp,additional)
CDR_DSLOCKWAIT 10 # DS lockwait timeout (seconds)
CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR
queue (Kbytes)
BAR_ACT_LOG /tmp/bar_act.log
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 10
BAR_XFER_BUF_SIZE 31
HETERO_COMMIT 0
SYSALARMPROGRAM /i9.4/informix/etc/evidence.sh # System Alarm program
path
TBLSPACE_STATS 1
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
OPT_GOAL -1
DIRECTIVES 1
RESTARTABLE_RESTORE off
BAR_PROGRESS_FREQ 0
SBSPACENAME # Default smartblob space name - this is
where blobs
SYSSBSPACENAME # Default smartblob space for use by the
Informix
BLOCKTIMEOUT 3600 # Default timeout for system block
ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or
anything but 1)
JVPJAVAHOME # JRE installation root directory
JVPHOME # Krakatoa installation directory
JVPPROPFILE .jvpprops # JVP property file
JDKVERSION # JDK version supported by this server
JVMTHREAD green # Java VM thread type
...
read more »
.
- References:
- onconfig recommendations
- From: Kennedy, Randy
- onconfig recommendations
- Prev by Date: Re: onconfig recommendations
- Next by Date: Re: Identify Optimizer issues
- Previous by thread: Re: onconfig recommendations
- Next by thread: RE: onconfig recommendations
- Index(es):
Relevant Pages
|
