rebuild making system run SLOOOooowwww...
A DBA on INFORMIX-OnLine 7.23.UC11 under HP-UX 11.0 reported queries slowing from one hour to four, and screens from 5 to 45 seconds, after the OS team reconfigured an array of four 74GB disks to use the previously unallocated space (raw devices still in use). Suggestions included running UPDATE STATISTICS, checking for hidden RAID 5, block vs. raw devices, extents/detached indexes, sar disk contention, mirroring, KAIO/AIO VPs, and older HP array performance issues. The poster confirmed UPDATE STATISTICS runs nightly and no RAID 5 was used; no resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We have old stuff here. INFORMIX-OnLine Version 7.23.UC11 HP-UX 11.0 We just updated one of our systems to use an array of four 74GB disks. We had only been using 32GB each of our 74GB disks. We didn't change the operating system, just rebuilt the foot print to allocate the unused space. The engine still uses RAW space. Is it common to experience a MAJOR slowdown in performance? What was taking about an hour for a workhorse query now takes about four hours. Captive User Interface scripts that used to take about five seconds to build and display user data now takes about 45 seconds and you can actually watch it put data up on the screen one piece at a time... This is killing me. The developers/OS engineers are telling me this is normal and to basically (insert expletive) shut up. QUESTION: Is this normal? (about the slowdown, not the abuse) Rob Who wants to join the People for the Ethical Treatment of Database Administrators (PETdbA)?
On 11/2/05, Konikoff, R.... <rob.konikoff@us.army.mil> wrote: > > We have old stuff here. > > INFORMIX-OnLine Version 7.23.UC11 > HP-UX 11.0 > > We just updated one of our systems to use an array of four 74GB disks. > We had only been using 32GB each of our 74GB disks. We didn't change > the operating system, just rebuilt the foot print to allocate the unused > space. > > The engine still uses RAW space. Is it common to experience a MAJOR > slowdown in performance? > > What was taking about an hour for a workhorse query now takes about four > hours. Captive User Interface scripts that used to take about five > seconds to build and display user data now takes about 45 seconds and > you can actually watch it put data up on the screen one piece at a > time... > > This is killing me. The developers/OS engineers are telling me this is > normal and to basically (insert expletive) shut up. > > QUESTION: Is this normal? (about the slowdown, not the abuse) > No - it shouldn't be normal. But if you just rebuilt your systems, did you remember to run UPDATE STATISTICS? (How often does that answer - counter-question - come up?). Another possibility is you're using block devices instead of raw (character) devices. However, that shouldn't be order-of-magnitude problems; UPD STATS really can make that much difference. (Unless they did some RAID 5 -- quiet Art; it's only a possibility at this point :-) -- behind the scenes.) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
Rob Have you run UPDATE STATISTICS? How is the data laid out on the disk, are indexes detached (for large tables)? Have you only got 1 or 2 extents per table? Is sar showing any disk/network contention? My personal preference would be more spindles of smaller size (although you may not have this option (one advantage of being DBA, Unix Admin and system specifier combined !!) ). Are you using mirroring any where, and if so is that to these same 4 disks? Keith -> -----Original Message----- -> From: Konikoff, R.... [mailto:rob.konikoff@us.army.mil] -> Sent: Wednesday, November 02, 2005 9:14 PM -> To: ids@iiug.org -> Subject: rebuild making system run SLOOOooowwww... [5952] -> -> -> We have old stuff here. -> -> INFORMIX-OnLine Version 7.23.UC11 -> HP-UX 11.0 -> -> We just updated one of our systems to use an array of four -> 74GB disks. -> We had only been using 32GB each of our 74GB disks. We didn't change -> the operating system, just rebuilt the foot print to -> allocate the unused -> space. -> -> The engine still uses RAW space. Is it common to experience a MAJOR -> slowdown in performance? -> -> What was taking about an hour for a workhorse query now -> takes about four -> hours. Captive User Interface scripts that used to take about five -> seconds to build and display user data now takes about 45 seconds and -> you can actually watch it put data up on the screen one piece at a -> time... -> -> This is killing me. The developers/OS engineers are telling -> me this is -> normal and to basically (insert expletive) shut up. -> -> QUESTION: Is this normal? (about the slowdown, not the abuse) -> -> Rob -> Who wants to join the People for the Ethical Treatment of Database -> Administrators (PETdbA)? -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **
I seem to recall that HP disk arrays were a known performance problem for Informix back when 7.3X was new. Sticking with no RAID/striping at all (except mirroring) was the preferred implementation. Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com 586.741.8991 -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Jonathan Le.... Sent: Thursday, November 03, 2005 12:17 AM To: ids@iiug.org Subject: Re: rebuild making system run SLOOOooowwww... [5954] On 11/2/05, Konikoff, R.... <rob.konikoff@us.army.mil> wrote: > > We have old stuff here. > > INFORMIX-OnLine Version 7.23.UC11 > HP-UX 11.0 > > We just updated one of our systems to use an array of four 74GB disks. > We had only been using 32GB each of our 74GB disks. We didn't change > the operating system, just rebuilt the foot print to allocate the unused > space. > > The engine still uses RAW space. Is it common to experience a MAJOR > slowdown in performance? > > What was taking about an hour for a workhorse query now takes about four > hours. Captive User Interface scripts that used to take about five > seconds to build and display user data now takes about 45 seconds and > you can actually watch it put data up on the screen one piece at a > time... > > This is killing me. The developers/OS engineers are telling me this is > normal and to basically (insert expletive) shut up. > > QUESTION: Is this normal? (about the slowdown, not the abuse) > No - it shouldn't be normal. But if you just rebuilt your systems, did you remember to run UPDATE STATISTICS? (How often does that answer - counter-question - come up?). Another possibility is you're using block devices instead of raw (character) devices. However, that shouldn't be order-of-magnitude problems; UPD STATS really can make that much difference. (Unless they did some RAID 5 -- quiet Art; it's only a possibility at this point :-) -- behind the scenes.) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
The number one question was a tie between: "Did they build it as RAID5?" "Did you run UPDATE STATISTICS?" The answer is: UPDATE STATISTICS runs every morning at about 4AM production time and NO RAID5! (www.BAARF.com) From what I'm told, they did NOT change the actual db portion of raw space and spindle configuration. A lot of feed back from a lot of people on what to check... THANKS! I've elected to reply to Jonathan Leffler simply because he included the ids forum in his line and reply all included it. :) Rob ________________________________________ From: Jonathan Leffler [mailto:jleffler.iiug@gmail.com] Sent: Thursday, November 03, 2005 12:10 AM To: Konikoff, Rob (Contractor) Cc: ids@iiug.org; forum.subscriber@iiug.org Subject: Re: rebuild making system run SLOOOooowwww... [5952] On 11/2/05, Konikoff, R.... <rob.konikoff@us.army.mil> wrote: We have old stuff here. INFORMIX-OnLine Version 7.23.UC11 HP-UX 11.0 We just updated one of our systems to use an array of four 74GB disks. We had only been using 32GB each of our 74GB disks. We didn't change the operating system, just rebuilt the foot print to allocate the unused space. The engine still uses RAW space. Is it common to experience a MAJOR slowdown in performance? What was taking about an hour for a workhorse query now takes about four hours. Captive User Interface scripts that used to take about five seconds to build and display user data now takes about 45 seconds and you can actually watch it put data up on the screen one piece at a time... This is killing me. The developers/OS engineers are telling me this is normal and to basically (insert expletive) shut up. QUESTION: Is this normal? (about the slowdown, not the abuse) No - it shouldn't be normal. But if you just rebuilt your systems, did you remember to run UPDATE STATISTICS? (How often does that answer - counter-question - come up?). Another possibility is you're using block devices instead of raw (character) devices. However, that shouldn't be order-of-magnitude problems; UPD STATS really can make that much difference. (Unless they did some RAID 5 -- quiet Art; it's only a possibility at this point :-) -- behind the scenes.) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
On
11/3/05, Konikoff, R.... <rob.konikoff@us.army.mil> wrote:
>
> The number one question was a tie between:
> "Did they build it as RAID5?"
> "Did you run UPDATE STATISTICS?"
>
> The answer is: UPDATE STATISTICS runs every morning at about 4AM
> production time and NO RAID5! (www.BAARF.com <http://www.BAARF.com>)
>
> From what I'm told, they did NOT change the actual db portion of raw space
> and spindle configuration.
>
> A lot of feed back from a lot of people on what to check... THANKS! I've
> elected to reply to Jonathan Leffler simply because he included the ids
> forum in his line and reply all included it. :)
I take it that the problem hasn't gone away? And any extra help would be
useful...
You're likely to need to explain more about the disk setup. Do you have any
statistics on database performance from before the changes? It really is
something every production system should have - a basic log of the
performance data (onstat output, maybe sar output) that is logged routinely.
Then you can backtrack in situations like this and see what's different.
OK - it's easy to be wise after the event.
So, you have 4x74GB disk drives - in an array? So RAID is in use? Which RAID
level? Previously, you were only using 32 GB of each disk. Now all 74 GB are
in use. What is happening on the other 42 GB? If there is disk intensive
activity occurring on the other section, the performance of IDS will suffer.
Previously, how was IDS given the space to work with? Were you using a LVM?
Are you now using an LVM? Were the devices raw (character devices) or cooked
(plain files, or block devices). Are you using sections of a single disk?
Given that IDS is 7.23, you can only be using 2GB chunks; how many of them
are you using? Any mirroring? Are the devices the same now as they were
before? Have you tracked through any and all symbolic links to ensure you
know what IDS is using? Have you got any measurements of the disk
performance (ignoring IDS) before and after the change? Could there be a
wonky connection as a result of moving something - something that slows
things down because of retries but doesn't fail over time?
Have you got KAIO configured? Should you? Have you got enough AIO VPs
configured?
Are you positive the only change was in the disk i/o subsystem?
________________________________________
> From: Jonathan Leffler [mailto:jleffler.iiug@gmail.com]
> On 11/2/05, Konikoff, R.... <rob.konikoff@us.army.mil> wrote:
> We have old stuff here.
>
> INFORMIX-OnLine Version 7.23.UC11
> HP-UX 11.0
>
> We just updated one of our systems to use an array of four 74GB disks.
> We had only been using 32GB each of our 74GB disks. We didn't change
> the operating system, just rebuilt the foot print to allocate the unused
> space.
>
> The engine still uses RAW space. Is it common to experience a MAJOR
> slowdown in performance?
>
> What was taking about an hour for a workhorse query now takes about four
> hours. Captive User Interface scripts that used to take about five
> seconds to build and display user data now takes about 45 seconds and
> you can actually watch it put data up on the screen one piece at a
> time...
>
> This is killing me. The developers/OS engineers are telling me this is
> normal and to basically (insert expletive) shut up.
>
> QUESTION: Is this normal? (about the slowdown, not the abuse)
>
>
> No - it shouldn't be normal. But if you just rebuilt your systems, did you
> remember to run UPDATE STATISTICS? (How often does that answer -
> counter-question - come up?).
>
> Another possibility is you're using block devices instead of raw
> (character) devices. However, that shouldn't be order-of-magnitude
problems;
> UPD STATS really can make that much difference. (Unless they did some RAID
5
> -- quiet Art; it's only a possibility at this point :-) -- behind the
> scenes.)
>
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/