Re: 7.22.UC1X2 Performance tuning questions....
In article <5i3p74$ibr@cssun.mathcs.emory.edu>, tony edwards
<tony.edwards@bellsouth.co.nz> writes
>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
No - with OPTCOMPIND set to 2 you get sequential sca