Performance tuning 7.3 newbie questions 
Author Message
 Performance tuning 7.3 newbie questions

We are running 7.3 IDS on SCO OpenServer 5.0.5.  The checkpoints can be
fairly long at 34 seconds.  The server is a Dell 6300 400mhz Xeon with 4
chips and 4 gig of memory serving approx. 140 users.

We are going to move the Informix DB to a Sun box within 2 months, but I
would like to improve things if possible on the SCO server.

What is the basic logic behind how you control checkpoints?

Thanks
Gary Quiring

onstat -m
Informix Dynamic Server Version 7.30.UC2   -- On-Line -- Up 5 days 02:12:03
-- 8
19200 Kbytes

Message Log File: /u/ifmx/online.log
12:39:07  Checkpoint Completed:  duration was 9 seconds.
12:39:21  Logical Log 36835 Complete.
12:44:22  Checkpoint Completed:  duration was 13 seconds.
12:47:48  Logical Log 36836 Complete.
12:49:29  Checkpoint Completed:  duration was 4 seconds.
12:54:36  Checkpoint Completed:  duration was 4 seconds.
12:55:59  Logical Log 36837 Complete.
12:59:49  Checkpoint Completed:  duration was 9 seconds.
13:01:56  Logical Log 36838 Complete.
13:05:16  Checkpoint Completed:  duration was 22 seconds.
13:07:30  Logical Log 36839 Complete.
13:08:08  Logical Log 36840 Complete.
13:09:04  Logical Log 36841 Complete.
13:10:45  Checkpoint Completed:  duration was 26 seconds.
13:10:51  Logical Log 36842 Complete.
13:11:15  Logical Log 36843 Complete.
13:12:39  Logical Log 36844 Complete.
13:15:34  Logical Log 36845 Complete.
13:16:22  Checkpoint Completed:  duration was 34 seconds.
13:21:28  Checkpoint Completed:  duration was 3 seconds.

onstat -p
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
72239898 12873201 454734667 84.11   3819540  7009652  101916872 96.25

isamtot  open     start    read     write    rewrite  delete   commit
rollbk
1659967372 37450249 89446603 1266707703 45615345 2535901  843958   923039
1858

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        127960.91 47995.51 1070     2878

bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
9714820  130      2729206457 0        0        2756     204433   2336455

ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
5444678  259047   55880622 61543809   434324

onconfig file:

# Root Dbspace Configuration

ROOTNAME        rootdbs         # Root dbspace name
ROOTPATH        /dev/lrootdbs   # Path for device containing root dbspace
ROOTOFFSET      0               # Offset of root dbspace into device
(Kbytes)
ROOTSIZE        200000          # 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         plogdbs
PHYSFILE        100000

# Logical Log Configuration

LOGFILES        100             # Number of logical log files
LOGSIZE         2500            # Logical log size (Kbytes)

# Diagnostics

MSGPATH         /u/ifmx/online.log # System message log file path
CONSOLE         /dev/console    # System console message path
ALARMPROGRAM    # /u/ifmx/etc/log_full.sh # Alarm program path
SYSALARMPROGRAM /u/ifmx/etc/evidence.sh # System Alarm program path
TBLSPACE_STATS  1

# System Archive Tape Device

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

# Log Archive Tape Device

#LTAPEDEV        /dev/rct0       # Log tape device path
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 Dynamic Server/Optical staging
area

# System Configuration

SERVERNUM       0               # Unique id corresponding to a Dynamic
Server in
stance
DBSERVERNAME    emco1_shm       # Name of default database server
DBSERVERALIASES emco1_net       # List of alternate dbservernames
NETTYPE         ipcshm,2,200,CPU # Configure poll thread(s) for shared mem
NETTYPE         tlitcp,2,130,NET # Configure poll thread(s) for network
DEADLOCK_TIMEOUT 60              # 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       3               # 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         300000          # 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         200             # Maximum number of logical log files
CLEANERS        20              # Number of buffer cleaner processes
SHMBASE         0x82000000        # Shared memory base address
SHMVIRTSIZE     131072           # 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             # Check point interval (in sec)
LRUS            20              # Number of LRU queues
LRU_MAX_DIRTY   3              # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY   1              # LRU percent dirty end cleaning limit
LTXHWM          40              # Long transaction high water mark
percentage
LTXEHWM         50              # Long transaction high water mark
(exclusive)
TXTIMEOUT       0x12c             # Transaction timeout (in sec)
STACKSIZE       32              # Stack size (Kbytes)

# System Page Size
# BUFFSIZE - Dynamic Server no longer supports this configuration
parameter.
#            To determine the page size used by Dynamic Server 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
# 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     /u/ifmx/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 (Kb
ytes)

# 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

# Informix Storage Manager variables
ISM_DATA_POOL   ISMData         # If the data pool name is changed, be sure
to
                                # update $INFORMIXDIR/bin/onbar.  Change to
                                # ism_catalog -create_bootstrap -pool <new
name>

ISM_LOG_POOL    ISMLogs

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

# DBSPACETEMP:
# Dynamic Server equivalent of DBTEMP for SE. This is the list of dbspaces
# that the Dynamic Server SQL Engine will use to create temp tables etc.
# If specified it must be a colon separated list of dbspaces that exist
# when the Dynamic Server 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     temp1dbs:temp2dbs # 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 Dynamic Server 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
Dynamic
 Server)
DUMPCNT         1               # Number of shared memory or gcore dumps
for
                                # a single user's session

FILLFACTOR      90              # Fill factor for building indexes

# method for Dynamic Server to use when determining current time
USEOSTIME       0               # 0: use internal time(fast), 1: get time
from O
S(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 ...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 Performance tuning 7.3 newbie questions

Quote:

> We are running 7.3 IDS on SCO OpenServer 5.0.5.  The checkpoints can be
> fairly long at 34 seconds.  The server is a Dell 6300 400mhz Xeon with 4
> chips and 4 gig of memory serving approx. 140 users.
> We are going to move the Informix DB to a Sun box within 2 months, but I
> would like to improve things if possible on the SCO server.

> What is the basic logic behind how you control checkpoints?

More LRUS, more CLEANERS, lower LRU_MAX/MIN_DIRTY numbers, longer
CKPTINTVL to reduce the impact of longish checkpoints, fewer buffers
if other factors permit, faster disk farm (ie RAID10 instead of RAID5
or singleton drives), faster controllers, more controllers, more
spindles, higher RPM drives.  I'd max out LRUS/CLEANERS to 128, set
the LRU_ parameters to 1/0, and increase CKPTINTVL to 600.

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Newbie seeking help on performance tuning - Oracle 7.3 Server, NT4, Dual PPro 180, 64Meg

2. 7.3 performance + tuning class material

3. Oracle 7.3 performance vs. Oracle 7.2 performance

4. Newbie: EZ Performance Tuning?

5. IDS 7.3 : Stored procedures tuning

6. IDS 7.3 : Stored procedures tuning

7. Net8 Performance Tuning (client/server tuning)

8. newbie tuning question.

9. Tuning disk writes (newbie question)

10. I/O Performance tuning questions


 
Powered by phpBB® Forum Software