Re: 7.22.UC1X2 Performance tuning questions....
David,
Thanks for your response, My reply is below...... under the hash
bits....
----------
From: David Williams
To: informix-list@rmy.emory.edu
Subject: Re: 7.22.UC1X2 Performance tuning questions....
Date: Friday, 4 April 1997 11:45
In article <jlumbleyE80sEz.DBu@netcom.com>, Joe Lumbley
<jlumbley@netcom.com> writes
>tony edwards (tony.edwards@bellsouth.co.nz) wrote:
>: Team,
>: We have recently upgraded our HP-UX 9.04 / 5.07 to HP-UX 10.20 /
>: 7.22.UC1X2. Here is some information about the system
>: configuration......
>
>: Database size 50 GB, chunks 43, memory 3 GB, disk nike arrays 120GB,
>: cpus 8
>
>: NUMCPUS 7 LOCKS 600000 BUFFERS 300000 CKPTINTVL 600
>: NUMAIOVPS 20 PHYSBUFF 400 LOGBUFF 400 LOGSMAX 165
>: CLEANERS 45 SHMVIRTSIZE 1000000 SHMADD 200000
>: LRU_MAX_DIRTY 5 LRU_MIN_DIRTY 1 LRUS 45
>: STACKSIZE 32 OPTCOMPIND 2 NOAGE 0
>
How many physical disks do you have?
# 60 x 4GB in nike arrays.
>: We don't have KAIO set because processor affinity is not supported on
>: HP.
>
KAIO is NOT configurable (except you can set the environment
variable KAIO_OFF=1 before starting the engine to disable it
but YOU CANNOT ENABLE IT). If it is supported and you are using raw
disk you will AUTOMATICALLY get 1 kaio thread per CPU VP.
# My release notes say that KAIO is disabled by default. To enable KAIO
set the environment variable KAIOON. But first I have to add the
asyncdsk driver into the kernel.
Make sure you are using raw partitons...and set processor affinity
anyway. If it is not supported you will get a warning in
online.log but NOTHING bad will happen - it just gets ignored.
However a future release make support it and when whoever installs
that blindl.automatically uses the same ONCONFIG they will
automatically get the benefit. Saves having to check for it.
# Yes we are as raw as it gets, Which reminds me of the time I went on a
Oracle course and had a DICUSSION (I won) with one of their techo's
about that fact that cooked was faster than raw. HA HA HA HA
Then set AIOVPS to 1.
# Because it's a wee database I'm going to set AIOVPS to two. The manual
says "If OnLine implements KAIO on your platform, and all of your
dbspaces are composed of raw file space, one AIOVP might be sufficient",
which is just a very scary statement. But we will monitor them and look
at reducing them if possible.
Run vmstat 3 3 - if you get many page ins/page outs reduce BUFFERS.
sar -r 1 1 gives some idea of how much memory is in use and will
give you an idea for how much to reduce BUFFERS by.. ALso
remembe DSS queries will proably use LIGHT_SCANS and so not use
buffers anyway... they allocate memory in the virtual portion for
their disk buffering not using buffers which are in the fixed size
resident portion (more effecient memort usage).
# I like the vmstat and will set up a monitor as we are unsure about our
BUFFER setting which only 20 % of physical memory and we are thinking
about maybe increasing the buffers up to 25% (375000). We can only go up
to 26 % (390000) because of the maximum value of buffers. HP-UX doesn't
like sar -r , Do you know the HP equalivalent (I will also do some
reading).
Reduce checkpoint interval to the default of 300 to get nice
regular small checkpoints (gives more predictably performance.
# We had the checkpoint interval set to 300 with the MAX_DIRTY set at 5
and the checkpoint duration was about 24-29. I havn't tried reducing the
interval back to 300 since changing MAX_DIRTY to 4 and later this
morning we will change it to 3 and maybe look at going as low as 2
tomorrow. The LRU writes are about 82-86% of the onstat -F command, the
check point interval with MAX_DIRTY set to four is now 15-19.
Run onstat -b | tail -5 to get the buffer size for your port of
Online measured in bytes (either 2L/4K). Then run onstat -l and
set PHYSBUF so that pages/io * page size (2 or 4) for physical log
= 75% of PHYSBUFF. Then set LOGBUFF so that pages/io * page size
(2 or 4) for logical log = 75% of LOGBUFF. Note that if you use
unbuffered logging for the database then this you will probably be
set LOGBUFF<4.
# pages/io (222.37) * buffer size (2) = (444.74), PHYBUFF 500, thus
PHYBUFF should be set to 600. Is this what you mean about PHYSBUF?
Reduce CLEANERS they do very little work in Online 7. They just
scan the buffers are put write requests on the aio/kaio queues.
Unlike online 5 where they actually do the disk IO. Set CLEANERS=
NUMCPUVPS. Since page cleaner thread run on cpu vps anyway you
can never have more then NUMCPUVPS page cleaners running at the
same time.
# CLEANERS = 45 , NUMCPUVPS = 6 so the CLEANERS should be 6 and do I do
anything with LRUS ?
Run onstat -g mem and set SHMVIRTSIZE = the total amount of shared
memory allocated where the class = V...not USED but allocated.
Having >1 virutal shared memory segment can give very bad
performance on some platforms (I think HP included) as the OS has
to check permissions of the shared memory segment each time you
access it and only 3 are cached in the CPU).
# Yip, orginally we set SHMVIRTSIZE to 200000 and now it is 1000000 so
it will not allocate another segment.
Try setting LRU_MAX_DIRTY/LRU_MIN_DIRTY as low as possible (I have
heard of 1 and 2 being used!!). This is to reduce checkpoint time.
# LRU_MIN_DIRTY = 1, And LRU_MAX_DIRTY is now 3 and depending on the
checkpoint duration may go to 2 tonight.
LRUS should be = NUMCPUVPS as the maximum number of concurrent
threads that can be running = NUMCPUVPS and each thread can only
access only LRU queue at a time.
# So I guess the answer to my cleaners question is already answered
here.
STACKSIZE should only be changed if you realy need to (may need
to be increased if deeply nested stored procedures are used).
You will know if you need to as Online will crash if it is set too
low. It comes at the bottom of the list of parameters to change
if performance is poor.
# Still 32, don't understand it much so don't intend to change it.
Set OPTCOMPIND=0 - this will greatly reduce memory usage on OLTP
queries as indicies will be used rather than dynamic hash joins.
Also disk I/O will be much less.
...I wish the default was 0....
# It is my impression that if we leave OPTCOMPIND at two we need to run
alot more update statistics and one advantage is that badly written sql
is very easy to find because the performance is nowhere near as good as
it was under version five. Following on these throughts would you say
that people setting OPTCOMPIND back to zero are maybe hiding SQL QA
problems or I suppose to be fair my have purchased apps that they can't
change.
NOAGE should be ..looks for online manual...Ah! got it...0 thats
ok.
Possibly reduce LOCKS if possible to save memory.
# Memory