Re: Questionable IDS 9.21 performance on HP-UX v11.11

What does onstat -g seg look like? "Jason" <jasondinsdale@bigpond.com> wrote in message news:494e6103.0408262219.6784787e@posting.google.com... > Greetings all, > > We're having severe performance problems with our OLTP Delphi app that > connects to IDS via ODBC. > Having researched the problem I can find no glaring operating system > or database-configuration issues > as such and my suspicion lies firmly with the application itself. > However, I need to eliminate the > O/S and IDS config as a source of the problem, and so I'd be grateful > if you could run you eyes over > the database config and confirm that I'm on the right track. > > First some background info: > > We're running IDS 9.21HC2 (32-bit, and yes, unsupported AFAIK) with > HP-UX 11.11 on a new HP RP3440 > which is a dual-processor (or dual-core to be more precise) system, > with 4Gb of memory and a dual > FC-attached VA disk array, which has dual 1Gb cache controllers and 10 > x 15K RPM 36Gb disks. > > The database is used in primarily an OLTP fashion, with reports and > batch jobs run concurrently > (not ideal I know). During normal system operation, there is 700Mb > free memory and minimal disk > I/O, and nearly zero swapping or paging activity. The server is > dedicated to IDS with on other > 3rd party apps running on the server. Monitoring of the disk array > shows that there is very little > serious I/O throughput during the business day unless a batch or > report process is kicked off, but even > then it's not streching the disk array by any means. > > Although we're using 32-bit IDS, I've managed to squeeze 1.6Gb BUFFERS > into the oninit address space by > using chatr as advised by the Informix platform release notes. Chunk > and dbspace-wise, we don't do > anything fancy with layout; I simply striped the database LVs across > the 2 disk LUNs that are presented > by the disk array. We dont use fragmentation or anything of that > sort, which as I understand it is more > geared towards spreading I/O across direct-attached disks, and is > therefore irrelvant in SAN environments? > We use HP KAIO (export KAIOON=1 etc) and I've configured 4 KIO VPs, > with 3 AIO VPs. > > Using the 'ratios' script from IIUG I get the following figures which > seem to indicate a healthy system?: > > rp3440# ./ratios > pgsused/(ixda-RA+idx-RA+da-RA)*100 > Read-Ahead Util (RAU): 99.09% > 30551519/(5005644+925893+24901674)*100 > > bufwaits*100 / (pagreads+bufwrits) > Bufwaits Ratio (BWR): 0.67% (3127899*100) / (23877194+442457049) > Buffer Turnover(Max): 582.92 (pagreads+bufwrits)/(BUFFERS=800000) > Buffer Turnover(Min): 40.13 > (pagreads+(bufwrits*(1-%cached)))/(BUFFERS) > BT Period-max: 2.79/hr every 21.51 minutes. > BT Period-min: 0.19/hr every 312.37 minutes. > > The only areas I have a question over are: > > - NETTYPE parameters (see below) ... do these look right, bearing in > mind that the vast majority of connections > to the database are via the network over TCP? > > - PDQ ... the developers here dont even know what Informix PDQ is, and > as I understand it and app has to be written > to specifically take advantage of it. How can I tell if PDQ *is* > being used or not? > > - LOGBUFF ... is 32Kb enough? Doesnt seem like a lot. > > - PHYSBUFF ... is 64Kb enough? Again, doesnt seem like a lot. > > If there's any other stats you'd like me to provide then let me know. > > Thanks in advance, > > Jason > > > > |rp3440# onstat -m > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > days 17:02:18 -- 2097152 Kbytes > > Message Log File: /informix/921/log/.log > 14:35:02 Logical Log 108277 - Backup Started > 14:35:40 Logical Log 108277 - Backup Completed > 14:42:37 Logical Log 108278 Complete. > 14:42:40 Logical Log 108278 - Backup Started > 14:43:18 Logical Log 108278 - Backup Completed > 14:48:06 Fuzzy Checkpoint Completed: duration was 0 seconds, 2741> buffers not flushed. > 14:48:06 Checkpoint loguniq 108279, logpos 0x13d690 > > 15:04:11 Logical Log 108279 Complete. > 15:04:14 Logical Log 108279 - Backup Started > 15:04:52 Logical Log 108279 - Backup Completed > 15:18:26 Fuzzy Checkpoint Completed: duration was 0 seconds, 2608> buffers not flushed. > 15:18:26 Checkpoint loguniq 108280, logpos 0x3cc030 > > 15:20:54 Logical Log 108280 Complete. > 15:20:57 Logical Log 108280 - Backup Started > 15:21:37 Logical Log 108280 - Backup Completed > 15:48:44 Fuzzy Checkpoint Completed: duration was 0 seconds, 1763> buffers not flushed. > 15:48:44 Checkpoint loguniq 108281, logpos 0x27a238> > FYI ... when database started these were the startup messages (which > includes KAIO message): > > 22:51:25 Informix Dynamic Server 2000 Started. > 22:51:25 Could not create single shared memory segment with resident> and non-resident par > > titions. Proceeding to create 2 shared memory segments instead. > 22:51:27 Segment locked: addr=0x40000000, size=1073741824 > 22:51:29 Segment locked: addr=0x80000000, size=1073741824> > Wed Aug 18 22:51:30 2004 > > 22:51:30 Event alarms enabled. ALARMPROG = > '/informix/921/etc/log_full.sh' > 22:51:30 Booting Language <c> from module <> > 22:51:30 Loading Module <CNULL> > 22:51:30 Booting Language <builtin> from module <> > 22:51:31 Loading Module <BUILTINNULL> > 22:51:36 Informix Dynamic Server 2000 Version 9.21.HC2 Software> Serial Number AAC#J994713 > 22:51:36 HPUX Version B.11.11 -> Using flag/select style KAIO > 22:51:36 HP KAIO concurrent requests changed from 1000 to 5000 > 22:51:36 (9) connection rejected - no calls allowed for sqlexec > 22:51:36 listener-thread: err = -27002: oserr = 0: errstr = : No> connections are allowed in Dynamic Server 2000 quiescent mode. > > 22:51:37 Informix Dynamic Server 2000 Initialized -- Shared Memory> Initialized. > > 22:51:37 Physical Recovery Started. > 22:51:37 Physical Recovery Complete: 0 Pages Restored. > > 22:51:37 Logical Recovery Started. > 22:51:37 10 recovery worker threads will be started. > 22:51:40 Logical Recovery Complete.> 0 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks > > 22:51:41 Dataskip is now OFF for all dbspaces > 22:51:42 Checkpoint Completed: duration was 1 seconds. > 22:51:42 Checkpoint loguniq 104363, logpos 0x2c6018 > > 22:51:42 Dropping temporary TBLspace 100120, recovering 8 pages. > 22:51:42 Dropping temporary TBLspace 100121, recovering 8 pages. > 22:51:42 Dropping temporary TBLspace 100124, recovering 8 pages. > 22:51:42 Dropping temporary TBLspace 100125, recovering 8 pages. > 22:51:42 Dropping temporary TBLspace 10012b, recovering 16 pages. > 22:51:42 On-Line Mode> > > > |rp3440# onstat -c > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > days 17:02:18 -- 2097152 Kbytes > > Configuration File: /informix/921/etc/onconfig. > #************************************************************************** > #@

"Madison Pruet" <mpruet@comcast.net> wrote in message news:<2JHXc.70108$Fg5.17160@attbi_s53>... > What does onstat -g seg look like? It was the last piece of info I posted .... only 3 segs. Jason > "Jason" <jasondinsdale@bigpond.com> wrote in message > news:494e6103.0408262219.6784787e@posting.google.com... > > Greetings all, > > [cut] > > I know that there is a performance hit with HP-UX if you have >4 shm > > segments; as you can see thats not the case: > > > > |rp3440# onstat -g seg > > > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > > days 17:16:40 -- 2097152 Kbytes > > > > Segment Summary: > > id key addr size ovhd class blkused > > blkfree > > 24580 1382696961 40000000 1073741824 35216 R* 262123 21 > > 5 1382696962 80000000 1073741824 33384 V* 205757 56387 > > 6 1382696963 c4841000 2772992 704 M 647 30 > > Total: - - 2150256640 - - 468527 56438 > > > > (* segment locked in memory)

Jason, You've received quite a bit of fairly good advice in here, so I won't cover anything except the segment issue. We can generally ignore the message segment "M". You're getting that because you've got shared memory interface configured. With HP, we try to combine the buffer pool with the first virtual memory segment. The reason is that generally things like the lock table, the dictionary cache, etc, tend to get loaded in the first segment. By combining those two, we reduce the probability of running into the shm issues on HP. The performance impact is encountered as soon as there is a lot of switching between three segments, but since we are generally not going to/from the message buffer so much, it not worth worrying about. However, since we are not able to get enough contiguous memory with your system, we are having to split the buffer and the first virtual memory segment into two IPC segments. That means that you need to be alert to the possibility of a dynamic shared memory segment needing to be allocated. not really a problem with what you've got, but probably something that is worth monitoring. M.P. "Jason" <jasondinsdale@bigpond.com> wrote in message news:494e6103.0408272316.9c853c@posting.google.com... > "Madison Pruet" <mpruet@comcast.net> wrote in message news:<2JHXc.70108$Fg5.17160@attbi_s53>... > > What does onstat -g seg look like? > > It was the last piece of info I posted .... only 3 segs. > > Jason > > > "Jason" <jasondinsdale@bigpond.com> wrote in message > > news:494e6103.0408262219.6784787e@posting.google.com... > > > Greetings all, > > > > > [cut] > > > > I know that there is a performance hit with HP-UX if you have >4 shm > > > segments; as you can see thats not the case: > > > > > > |rp3440# onstat -g seg > > > > > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > > > days 17:16:40 -- 2097152 Kbytes > > > > > > Segment Summary: > > > id key addr size ovhd class blkused > > > blkfree > > > 24580 1382696961 40000000 1073741824 35216 R* 262123 21 > > > 5 1382696962 80000000 1073741824 33384 V* 205757 56387 > > > 6 1382696963 c4841000 2772992 704 M 647 30 > > > Total: - - 2150256640 - - 468527 56438 > > > > > > (* segment locked in memory)