Re: 7.22.UC1X2 Performance tuning questions....

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? >: 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. 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. Then set AIOVPS to 1. 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). Reduce checkpoint interval to the default of 300 to get nice regular small checkpoints (gives more predictably performance. 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. 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. 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). 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. 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. 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. 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.... NOAGE should be ..looks for online manual...Ah! got it...0 thats ok. Possibly reduce LOCKS if possible to save memory. Check LTAPEBLK and TAPEBLK are set to the largest your tape drive will support. I've heard of 64 as a good value but check this will work with your tape drive manufacturer/ HP. Same for Check OLTP queries do not run with MAX_PDQPRIORITY >0 to avoid them becoming DSS queries. NETTYPE - if you have many remote clients then run everything on NET VPs (Online Admin Guide Version 7.1 Volume 2 Page 10-28 just above the paragraph stating "The NETTYPE parameter"). Make sure you have one poll thread per 200 users. Also if you have many remote clients and >1 network card then create a DBSERVERALIAS and start one listen thread per network card...(Check Online Admin Guide Version 7.1 Volume 2 Page 10-32 to 10-34). Run onstat -p and calculate ixda-RA (index + data read-ahead - Keyonly reads?) + idx-RA (index page read aheads) + da-RA (data page read aheads) call this value Pages Read. RA_pgsused (Number of pages read-head that were actually used) call this Pages Used. Pages Used should be 97-98% of Pages Read if it is <97% reduce RA-PAGES and RA_THESHOLD (I would keep the default 2:1 ratio...never tried changing the ratio.) If it is < say 80% halve both and try again. If it is >98% then try increasing RA-PAGES and RA_THESHOLD. Note this will especially help DSS queries which do sequential table scans). Set RESIDENT = 1 to stop the shared memory segments getting paged/swapped out - we need these. Monitor page ins / outs with vmstat 3 3 and reduce BUFFERS if much paging is occurring. If you do not need < 1 second accuracy when getting the current time (this level of accuracy is not even supported on all plaforms) then set USEOSTIME=0 to get a 4-5% seedup (everythign helps). Run update statistics on all tables..you will generally not need to run update statistics high/low unless you data has an unbalanced distribution of index keys. Extra distibution info will only slow down the optimzer as it wastes looking at it when it does not need to. Thats all for now folks.... PS I joined the dicussions a bit late so quite a bit of the above repeats what has already been said but perhaps the vmstat/sar commands make give a bit help tuning those points that have already been mentioed. PPS Another big document to put on my Web site when I get around to creating it...and more hints to add to my ontune (online tuning tool) when I get around to writing it.... -- David Williams