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

On Fri, 27 Aug 2004 02:19:47 -0400, Jason wrote: > 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. Not nearly enough AIO VPs, your AIO VPs show io/wup between 9.9 and 14.3 meaning that 90+% of all writes to filesystem (the message log, any filesystem based chunks, etc.) are waiting. Some of these writes cause synchronous behavior within the engine, like during checkpoints. Normally on a server using KAIO you need from 4-6 AIO VPs (yeah I know the manual suggests 2, forget that), but in your case I'd start with 10. Try to get the io/wup down until one AIO VP shows io/wup < 1.0 except during peak or unusual loads. (And if peak/unusual is frequent, you may want to consider tuning for that condition as well.) > Using the 'ratios' script from IIUG I get the following figures which seem > to indicate a healthy system?: The ratios look OK. > 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? No, shared memory connections should always be configured to run in ALL of the CPU VPs and NEVER in NET VPs. So the entry should be (assuming you keep the 4 CPU VPS): NETTYPE ipcshm,4,200,CPU # This gives a total of 800 connections so you maywant to reduce the 200 a bit. > - 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? Nothing in the application needs to change to take advantage of PDQ, however, if your tables are not fragmented and your queries are simple (joins of 1-4 tables with only one of them large) then there is not much to be gained from PDQ. If you fragment large tables, then you can take advantage of PDQPRIORITY >= 2 to perform parallel queries against the fragments. > - LOGBUFF ... is 32Kb enough? Doesnt seem like a lot. Don't want much. If your databases are UNBUFFERED log then the buffer is flushed whenever a COMMIT is written to it. If all databases are BUFFERED then the buffer is only flushed when it fills (or during a checkpoint) and a larger buffer puts more committed data at risk to become lost if the system crashes. Art S. Kagel > - PHYSBUFF ... is 64Kb enough? Again, doesnt seem like a lot. Again, don't need much. > If there's any other stats you'd like me to provide then let me know. > > Thanks in advance, > > Jason

"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.08.27.10.03.59.229562.1355@bloomberg.net>... > On Fri, 27 Aug 2004 02:19:47 -0400, Jason wrote: > [cut] > > configured 4 KIO VPs, with 3 AIO VPs. > > Not nearly enough AIO VPs, your AIO VPs show io/wup between 9.9 and 14.3 > meaning that 90+% of all writes to filesystem (the message log, any filesystem > based chunks, etc.) are waiting. Some of these writes cause synchronous > behavior within the engine, like during checkpoints. Normally on a server > using KAIO you need from 4-6 AIO VPs (yeah I know the manual suggests 2, > forget that), but in your case I'd start with 10. Try to get the io/wup down > until one AIO VP shows io/wup < 1.0 except during peak or unusual loads. (And > if peak/unusual is frequent, you may want to consider tuning for that > condition as well.) Thanks for that ... I'll implement 10 and monitor the effect. > > Using the 'ratios' script from IIUG I get the following figures which seem > > to indicate a healthy system?: > > The ratios look OK. Great. Having corresponded with Alexey Sonkin he's of the opinion that I have a major problem with foreground writes ... > |rp3440# onstat -F > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > days 17:02:18 -- 2097152 Kbytes > > Fg Writes LRU Writes Chunk Writes > 25 3146257 2975943 How to I go about eliminating these? Could this be (one of) the root causes of my performance problems? > > 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? > > No, shared memory connections should always be configured to run in ALL of the > CPU VPs and NEVER in NET VPs. So the entry should be (assuming you keep the 4 > CPU VPS): > > NETTYPE ipcshm,4,200,CPU # This gives a total of 800 connections so you may> want to reduce the 200 a bit. Thanks for the advice ... will adjust as you suggest. Generally speaking though, and I right in saying that this would only affect connection performance, not query performance, for clients executing on the UNIX server iself and connecting via shared memory? Finally on this NETTYPE topic, what do you suggest for the other NETTYPE statement (NETTYPE soctcp,2,250,CPU) ... should I use a NET or CPU VP type here, and do the other args look OK? > > - 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? > > Nothing in the application needs to change to take advantage of PDQ, however, > if your tables are not fragmented and your queries are simple (joins of 1-4 > tables with only one of them large) then there is not much to be gained from > PDQ. If you fragment large tables, then you can take advantage of PDQPRIORITY > >= 2 to perform parallel queries against the fragments. I dont think the dev team have implemented fragmentation *at all*. Although I'm of the impression that it's negated somewhat by use of a SAN, could this be an area where we could improve performance anyway? The makeup of the database is that we have lots of smaller tables and a minority of much large tables and indexes that are getting hit heavily. > > - LOGBUFF ... is 32Kb enough? Doesnt seem like a lot. > > Don't want much. If your databases are UNBUFFERED log then the buffer is > flushed whenever a COMMIT is written to it. If all databases are BUFFERED > then the buffer is only flushed when it fills (or during a checkpoint) and a > larger buffer puts more committed data at risk to become lost if the system > crashes. We have a BUFFERED database, so all the above applies. Jason

On Sat, 28 Aug 2004 05:07:23 -0400, Jason wrote: > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:<pan.2004.08.27.10.03.59.229562.1355@bloomberg.net>... >> On Fri, 27 Aug 2004 02:19:47 -0400, Jason wrote: >> >> > [cut] > >> > configured 4 KIO VPs, with 3 AIO VPs. >> >> Not nearly enough AIO VPs, your AIO VPs show io/wup between 9.9 and 14.3 >> meaning that 90+% of all writes to filesystem (the message log, any >> filesystem based chunks, etc.) are waiting. Some of these writes cause >> synchronous behavior within the engine, like during checkpoints. Normally >> on a server using KAIO you need from 4-6 AIO VPs (yeah I know the manual >> suggests 2, forget that), but in your case I'd start with 10. Try to get >> the io/wup down until one AIO VP shows io/wup < 1.0 except during peak or >> unusual loads. (And if peak/unusual is frequent, you may want to consider >> tuning for that condition as well.) > > Thanks for that ... I'll implement 10 and monitor the effect. > >> > Using the 'ratios' script from IIUG I get the following figures which >> > seem to indicate a healthy system?: >> >> The ratios look OK. > > Great. Having corresponded with Alexey Sonkin he's of the opinion that I > have a major problem with foreground writes ... 25 FG writes is insignificant in its effect on performance, though as someone implied it can indicate that there is another problem. While your BR is OK, increasing LRUS (and CLEANERS in parallel) can load balance users better to reduce the contention that there is that might have caused the FG writes. However, not to hedge TOO much, I suspect that if you monitor the stats more frequently you'll find the FG writes happening during some large batch insert/delete/update process, and so mostly unavoidable and irrelevant. If FG writes get into three digits daily, begin to worry. >> |rp3440# onstat -F >> >> Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 days >> 17:02:18 -- 2097152 Kbytes >> >> Fg Writes LRU Writes Chunk Writes 25 3146257 2975943 > > How to I go about eliminating these? Could this be (one of) the root causes > of my performance problems? > >> > 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? >> >> No, shared memory connections should always be configured to run in ALL of >> the CPU VPs and NEVER in NET VPs. So the entry should be (assuming you >> keep the 4 CPU VPS): >> >> NETTYPE ipcshm,4,200,CPU # This gives a total of 800 connections so you>> may want to reduce the 200 a bit. > > Thanks for the advice ... will adjust as you suggest. Generally speaking > though, and I right in saying that this would only affect connection > performance, not query performance, for clients executing on the UNIX server > iself and connecting via shared memory? No, every requests, not just connects, has to be picked up by a listener and assigned to a user thread. So, listener delays slow responsiveness. In addition, having ipcshm listeners in NET VPs burns CPU cycles slowing everything down. Similarly, having TCP listeners in CPU VPs SERIOUSLY slows network connection responsiveness, burns CPU time in general, and ties up the CPU VPs unneccessarily. I deal with the reasons for this extensively in other postings, one of which IB is in the Informix FAQ...yes, near the bottom of section 6.15. Worst of all if a significant portion of queries use TCP, the CPU listener in the two CPU VPs out of 4 will not load balance well, as dealt with in the FAQ article. > Finally on this NETTYPE topic, what do you suggest for the other NETTYPE > statement (NETTYPE soctcp,2,250,CPU) ... should I use a NET or CPU VP type > here, and do the other args look OK? Sorry, thought that was commented out. Should be: NETTYPE soctcp,2,250,NET >> > - 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? >> >> Nothing in the application needs to change to take advantage of PDQ, >> however, if your tables are not fragmented and your queries are simple >> (joins of 1-4 tables with only one of them large) then there is not much to >> be gained from PDQ. If you fragment large tables, then you can take >> advantage of PDQPRIORITY >> >= 2 to perform parallel queries against the fragments. > > I dont think the dev team have implemented fragmentation *at all*. Although > I'm of the impression that it's negated somewhat by use of a SAN, could this > be an area where we could improve performance anyway? The makeup of the > database is that we have lots of smaller tables and a minority of much large > tables and indexes that are getting hit heavily. Yes. Fragmenting large tables, even when all dbspaces live on a single disk structure, can imporve performance by permitting parallel queries and fragment elimination which will effectively make the table much smaller. >> > - LOGBUFF ... is 32Kb enough? Doesnt seem like a lot. >> >> Don't want much. If your databases are UNBUFFERED log then the buffer is >> flushed whenever a COMMIT is written to it. If all databases are BUFFERED >> then the buffer is only flushed when it fills (or during a checkpoint) and >> a larger buffer puts more committed data at risk to become lost if the >> system crashes. > > We have a BUFFERED database, so all the above applies. Personally I prefer UNBUFFERED. I believe that the performance hit is minimal if any and well worth the additional data safety. With UNBUFFERED logging it is virtually impossible for ANY committed transaction to become lost. With BUFFERED logging that's just not true. As to Alexey's comments, we differ on several points. I want to see, in an OLTP server, 70% of my writes to be LRU writes and only 30% or less Chunk writes. Yes, the LRU writes will imcrease the total IO load on the system because more pages will be written several times between checkpoints. However, this serves to minimize any checkpoint delay (admittedly less critical with soft checkpoints but not insignificant) however, it does not, as Alexey contends: "dramatically decrease database caching". The database caches exactly as well whether there are 100% LRU writes or 100% Chunk Writes so long as the cache size and load are identical. Yes, the beneficial effect on the OS's IO overhead is not as great, but the user's will experience EXACTLY the same cache benefits either way. As to the effect on the system, unless there are significant non-database related IO activity going to/from the same SAN, LRU writes have no negative effect. I do champion using dedicated disk structures for IDS as well as dedicated server machines, but even when that's not practical, or practicable, my concern as the DBA has to be for my server's performance and responsiveness, not the overall performance of the machine. If the machine cannot supply the IO bandwidth or CPU cycles needed for what's running