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

"Jason" <jasondinsdale@bigpond.com> wrote in message news:494e6103.0408262219.6784787e@posting.google.com... > 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. You're aware that you are running a very old, unsupported, 32-bit databases system on a 64-bit box, but it would be remiss not to spell this out ... Do you know how your disks are arranged? RAID 1+0? Should be lightning-fast if so, with fibre-channel connectivity and decent cache. You're certain you aren't using RAID-5? > 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? Not necessarily. It *is* fair to say that in even moderately complex SAN environments there now exists such a level of indirection between the presented logical volumes and the actual physical location(s) of your data that traditional disk methods are (IMHO) a waste of time. But fragmentation encompasses more that this and can still be turned to advantage. > We use HP KAIO (export KAIOON=1 etc) and I've configured 4 KIO VPs, > with 3 AIO VPs. With only 2 physical processors? Seems too high. What does onstat -g iov show? Seen it now - I'd reduce this to two. > - 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? onstat -g mgm > 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 Used to be a *severe* problem with HP-UX 10.20, and although less so in HP-UX 11 this is not ideal. > ROOTSIZE 524288 # Size of root dbspace (Kbytes) Using much of this? Overall your system looks reasonably tuned. Time to start looking at the SQL that's runing I think. Have you run UPDATE STATISTICS recently?

please ensure the array is not running auto raid. Have seen major performance problems before on HP boxes when the LVM had been configured as Auto Raid!!! "Neil Truby" <neil.truby@ardenta.com> wrote in message news:<2p8acuFg45uiU1@uni-berlin.de>... > "Jason" <jasondinsdale@bigpond.com> wrote in message > news:494e6103.0408262219.6784787e@posting.google.com... > > > 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. > > You're aware that you are running a very old, unsupported, 32-bit databases > system on a 64-bit box, but it would be remiss not to spell this out ... > Do you know how your disks are arranged? RAID 1+0? Should be > lightning-fast if so, with fibre-channel connectivity and decent cache. > You're certain you aren't using RAID-5? > > > 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? > > Not necessarily. It *is* fair to say that in even moderately complex SAN > environments there now exists such a level of indirection between the > presented logical volumes and the actual physical location(s) of your data > that traditional disk methods are (IMHO) a waste of time. But fragmentation > encompasses more that this and can still be turned to advantage. > > > > We use HP KAIO (export KAIOON=1 etc) and I've configured 4 KIO VPs, > > with 3 AIO VPs. > > With only 2 physical processors? Seems too high. What does onstat -g iov > show? Seen it now - I'd reduce this to two. > > > - 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? > > onstat -g mgm> > > 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> > Used to be a *severe* problem with HP-UX 10.20, and although less so in > HP-UX 11 this is not ideal. > > > ROOTSIZE 524288 # Size of root dbspace (Kbytes)> > Using much of this? > > Overall your system looks reasonably tuned. Time to start looking at the > SQL that's runing I think. > Have you run UPDATE STATISTICS recently?

"Neil Truby" <neil.truby@ardenta.com> wrote in message news:<2p8acuFg45uiU1@uni-berlin.de>... > "Jason" <jasondinsdale@bigpond.com> wrote in message > news:494e6103.0408262219.6784787e@posting.google.com... > > > 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. > > You're aware that you are running a very old, unsupported, 32-bit databases > system on a 64-bit box, but it would be remiss not to spell this out ... > Do you know how your disks are arranged? RAID 1+0? Should be > lightning-fast if so, with fibre-channel connectivity and decent cache. > You're certain you aren't using RAID-5? Yes, yes and yes. I'm applying pressure to get us on 9.4 64 bit, but these things dont happen overnight. The VA7110 disk array uses HP AutoRAID which, if you're not familiar, is a combination of RAID 1+0 and RAID 5. The AutoRAID algorithm keeps the most frequently accessed data in RAID 1+0 and the rest in RAID 5 ... that's what it says on the packet anyway! I did run some back-to-back benchmarking of AutoRAID vs straight 1+0 using our database and 100 concurrent sessions running a mixture of heavy queries, and the difference was about 6% ie. not a huge amount. > > 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? > > Not necessarily. It *is* fair to say that in even moderately complex SAN > environments there now exists such a level of indirection between the > presented logical volumes and the actual physical location(s) of your data > that traditional disk methods are (IMHO) a waste of time. But fragmentation > encompasses more that this and can still be turned to advantage. Thats interesting; so there would be some benefit to using fragmentation anyway? Our database is around 18Gb with several large tables (and indexes) in the 4-6 million rows range and probably a couple of gig in size. I imagine these are the ones that will benefit from fragmentation (even with a SAN backend)? > > We use HP KAIO (export KAIOON=1 etc) and I've configured 4 KIO VPs, > > with 3 AIO VPs. > > With only 2 physical processors? Seems too high. What does onstat -g iov > show? Seen it now - I'd reduce this to two. Again, during the benchmarking phase I played with the KIOs and 4 seemed to be the sweet spot. Will bear it in mind though. > > - 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? > > onstat -g mgm rp3440# onstat -g mgm Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 9 days 15:34:53 -- 2097152 Kbytes Memory Grant Manager (MGM) -------------------------- MAX_PDQPRIORITY: 80 DS_MAX_QUERIES: 2 DS_MAX_SCANS: 1048576 DS_TOTAL_MEMORY: 2048 KB Queries: Active Ready Maximum 0 0 2 Memory: Total Free Quantum (KB) 2048 2048 1024 Scans: Total Free Quantum 1048576 1048576 1 Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit) Gate 1 Gate 2 Gate 3 Gate 4 Gate 5 (Queue Length) 0 0 0 0 0 Active Queries: None Ready Queries: None Free Resource Average # Minimum # -------------- --------------- --------- Memory 128.0 +- 0.0 128 Scans 1048575.0 +- 0.0 1048575 Queries Average # Maximum # Total # -------------- --------------- --------- ------- Active 1.0 +- 0.0 1 10 Ready 0.0 +- 0.0 0 0 Resource/Lock Cycle Prevention count: 0 > > 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> > Used to be a *severe* problem with HP-UX 10.20, and although less so in > HP-UX 11 this is not ideal. Yes read about that, and again that was one of my aims in benchmarking ... trying KIO vs AIO ... KIO won by 10-15% margin. > > ROOTSIZE 524288 # Size of root dbspace (Kbytes)> > Using much of this? No, 41% used. I had the disk space and didnt want it to fill up if someone accidentally created a table in the rootdbs (which I've seen happen before). > Overall your system looks reasonably tuned. Time to start looking at the > SQL that's runing I think. Thanks for that conclusion ... same as mine ... > Have you run UPDATE STATISTICS recently? Every night. Jason