INFORMIX and utilization of system resources on HP-UX 10.20

I have a new double HP9000 system with MC/ServiceGuard, and I,ve installed IDS 7.3 UC7 on this cluster. At the beginning I've notticed, that database is running rather slowly (thats mean it is unacceptable slow ;-). I've carefully read release notes for IDS 7.3 and I had tuned kernel as it was described in this document, but the situation was the same. For instance I am doing dbexport & dbimport and there is onperf runnig at background, which monitors cpu utilisation (machine has 2 CPU). The diagram is awfully shaterred. In 2-3 sec. periods CPU utilization can change from 0% to 80% and back to 0% and it is regular. On the other way average utilization measured in longer periods isn't constant, but becomes smaller and smaller. I have already set NOAGE parameter to 1 in $ONCONFIG. Anybody knows what is going on? I'm not sure, if I should search the solution at the HP-UX kernel or IDS 7.3 tuning. I'm attching my onconfig file. Thanks for any advice.

Without doing an exhaustive analysis, I've noticed a number of problems with your onconfig. 1) Your processor configurations is really confused: MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor NUMCPUVPS 1 # Number of user (cpu) vps SINGLE_CPU_VP 1 # If non-zero, limit number of cpu vps to one -- you say use multiprocessor, but you have only 1 cpu vp, and limit cpu vps to 1. -- suggestion: if you have other stuff (ie. apps) on the box, set to: MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor NUMCPUVPS 1 # Number of user (cpu) vps SINGLE_CPU_VP 1 # If non-zero, limit number of cpu vps to one -- if this box is only for database, do this: MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor NUMCPUVPS 2 # 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 2 # Affinity start processor AFF_NPROCS 2 # Affinity number of processors -- if you really want affinity (don't know hp arch), set aff_sproc to 0, and nprocs to 1 or 2 depending on above LOCKS 1000000 # Maximum number of locks BUFFERS 40000 # Maximum number of shared buffers You seem to have lots of locks, and only a few buffers..... NUMAIOVPS 1 # Number of IO vps -- do you have kio? If not, increase aiovps LRUS 4 # Number of LRU queues LRU_MAX_DIRTY 25 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 15 # LRU percent dirty end cleaning limit -- num lrus seems low (how many disks do you have?), and look at your max and min dirty - depends on your application how you want to structure it. OPTCOMPIND 2 # To hint the optimizer -- if this is oltp, make it 0 LBU_PRESERVE 0 # Preserve last log for log backup -- should probably be 1 Look in the performance tuning manual for more hints. Vic Andrzej Piotrowski wrote: > I have a new double HP9000 system with MC/ServiceGuard, and I,ve > installed IDS 7.3 UC7 on this cluster. At the beginning I've notticed, > that database is running rather slowly (thats mean it is unacceptable > slow ;-). I've carefully read release notes for IDS 7.3 and I had tuned > kernel as it was described in this document, but the situation was the > same. For instance I am doing dbexport & dbimport and there is onperf > runnig at background, which monitors cpu utilisation (machine has 2 > CPU). The diagram is awfully shaterred. In 2-3 sec. periods CPU > utilization can change from 0% to 80% and back to 0% and it is regular. > On the other way average utilization measured in longer periods isn't > constant, but becomes smaller and smaller. I have already set NOAGE > parameter to 1 in $ONCONFIG. > -- Vic Goldberg Manager, Data Operations Cornell University (607) 254-7441 (Voice) (607) 255-6982 (Fax)

In article <370CCB0E.E6005A1D@apexim.com.pl>, Andrzej Piotrowski <a.piotrowski@apexim.com.pl> writes You don't mention what type of HP9000 or how much memory. Also how big is the database that you're trying to export/import? Are you just concerned with data loading or is your system OLTP as well? If you are tying to improve the export/import performance then you could start by increasing the buffers from 40,000 (80Mb) to 80,000 (160) but this really depends on how much spare memory you have. Coupled with this increase LRU_MAX_DIRTY to 80 and LRU_MIN_DIRTY to 70, this will hopefully get online to do most of its buffer pool flushing at CHECKPOINT and increase %cached (bufwrites) rates. Conversely, for OLTP setting LRU_MAX_DIRTY/LRU_MIN_DIRTY to low setting (10/5 or 5/2 even) is recommended to reduce CKPT intervals and will encourage LRU flushing between CHECKPOINTS, again these settings depend on how much write activity is going on (for OLTP this should be low) check onstat -p for buffer write %cached rates, ideally you should see 85% or better, if you reduce MAX_DIRTY and MIN_DIRTY values too much then your buffer wrties cache rates may suffer (and it may be better to then increase MAX/MIN until the %cached improves). Your rootdbs seems small at 50 Mb and your plogdbs rather large at 1Gb, I assume you've moved logical logs into the plogdbs, in which case I'd recommend creating a llogdbs on a separate disk just for logical logs. To monitor how big your plogdbs should be, do a number of "onstat -l" just before some checkpoints are due and it will tell you how much (%) plog space its used. If you have two CPUs you could try MULTIPROCESSOR=1, NUMCPUVPS=2, SINGLE_CPU_VP=0, the manual does state to set NUMCPUVPS to be "number of real CPUs - 1", so your current settings could be the best. I have a 4-way K460 and because it is only used for Informix I have always set NUMCPUVPS=4 with no problems. If you are a single CPU box then I'd recommend getting some more (but set MULTIPROCESSOR=0 meanwhile)! AFF_SPROCS and AFF_NPROCS should be set to 0 as processor affinity isn't supported by HP-UX (as far as I'm aware). Your AIO vps is small, you don't mention if you're using KAIO, if you're not using KAIO then I'd try setting NUMAIOVPS=12, LRUS=12, CLEANERS=12 and maybe look to increase from here (use "onstat -g ioq" and "onstat -g iov" and refer to the Informix Performance Guide), but these settings do depend on the type of disk setup in place. SHMVIRTSIZE at 16Mb maybe small, run "onstat -g seg" to see if you have more than one class V segment (Virtual Memory), if you do see more than one then increase SHMVIRTSIZE to prevent Informix from acquiring extra segments in the future - as there is a well-documented performance issue on HP-UX systems regarding multiple memory segments. Set PHYSBUFF and LOGBUFF to higher values, I set PHYSBUFF=256 and LOGBUFF=64. Also I set OPTCOMPIND=0 on Informix's advice on experiencing slow query performance on upgrading to Informix v7. Also check PDQPRIORITY settings (with a view to turning off, PDQPRIORITY=0). Informix also advised, after upgrading to 7.30.UC6, to set two environment variables before starting up Informix (oninit), you may want to double-check with Informnix to see if they're still applicable, they are: NO_SUBQRY=1 NO_KEYFIRST=1 Otherwise, ensure your HP-UX is patched up regularly (a good starting point is to use the HP Extension S/W cd to install a patch bundle of recommended patches, these are shipped every two or three months so make sure you get a recent one and you have to request them (they used to come automatically which was nice, but now you have to sign-on to HP's Electronic Support Centre and request them (it was a lot easier when they just turned up, but that's progress I suppose!). Also check with HP Response engineers about any critical patches (especially for LVM, VxFS/JFS,etc) that are newer than the Extension CD, as they can ship these to you on a tape for installation after applying CD bundle). You can also ask them to check for any patches recommended generally for Informix sites. And last but not least, make sure you run "update statistics" according to the new version 7.3 Online manuals (or see the numerous postings/FAQ about running the correct update statistics, even better get some of the scripts off the IIUG to automate the process overnight). I probably haven't answered your original question (whatever that was, sorry), but the above pointers should be worth checking out for use in your environment, you may want to look to set up some sample 'benchmark' sql's that you can run to investigate performance (especially after making changes) rather monitoring than export/import . >I have a new double HP9000 system with MC/ServiceGuard, and I,ve >installed IDS 7.3 UC7 on this cluster. At the beginning I've notticed, >that database is running rather slowly (thats mean it is unacceptable >slow ;-). I've carefully read release notes for IDS 7.3 and I had tuned >kernel as it was described in this document, but the situation was the >same. For instance I am doing dbexport & dbimport and there is onperf >runnig at background, which monitors cpu utilisation (machine has 2 >CPU). The diagram is awfully shaterred. In 2-3 sec. periods CPU >utilization can change from 0% to 80% and back to 0% and it is regular. >On the other way average utilization measured in longer periods isn't >constant, but becomes smaller and smaller. I have already set NOAGE >parameter to 1 in $ONCONFIG. > >Anybody knows what is going on? I'm not sure, if I should search the >solution at the HP-UX kernel or IDS 7.3 tuning. > >I'm attching my onconfig file. >Thanks for any advice. >--------------1BDF045F91B4032E8276E3E0 >Content-Type: text/plain; charset=us-ascii; > name="onconfig" >Content-Transfer-Encoding: 7bit >Content-Disposition: inline; > filename="onconfig" > >#************************************************************************** ># ># INFORMIX SOFTWARE, INC. ># ># Title: onconfig.std ># Description: INFORMIX-OnLine Configuration Parameters ># >#************************************************************************** > ># Root Dbspace Configuration > >ROOTNAME rootdbs # Root dbspace name >ROOTPATH /dev/vg01/rrootdbs # Path for device containing root dbspace >ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) >ROOTSIZE 50000 # 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 # Location (dbspace) of physical log >PHYSFILE 1024000 # Physical log file size (Kbytes)> ># Logical Log Configuration > >LOGFILES 5 # Number of logical log files >LOGSIZE 60000 # Logi

Hi; with which protocol (ipcshm or soctcp) did you connect the dbservername baan? I didn't see a DBSERVERALIAS in your onconfig. If it's connected with soctcp, try ipcshm. Can make a very big difference. Andrzej Piotrowski wrote: >