IDS Forum

RE: Dropping distributions after upgrade

Posted By: Simmons, Keith
Date: Thursday, 12 June 2003, at 3:58 a.m.

In Response To: Re: Dropping distributions after upgrade (Obnoxio The Clown)

John

I think you are hitting a known bug:-

Bug 157747 UPDATE STATISTICS LOW WITH MANY DELETED BTREE ITEMS CAN CAUSE
SEVERE PERFORMANCE DEGRADATION - resolved 7.31.UD6
After the upgrade update statistics had been run; not only did this take an
inordinate amount of time, this also resulted in more than 640,000 entries
on the btree cleaner queue (as seen by onstat -C). The result is that the
btree request queue becomes 'thrashed', and takes away so much power from
the engine, that it can make it unuseable. A workaround for this would be to
rebuild indexes which have a lot of deleted btree items.
It should be borne in mind that a major re-design of the btree cleaner has
been done in 9.40, and until that version the design issue persists, albeit
with a lesser impact.

OTC

Does your solution overcome/workaround this bug?

Anyone

Has this bug really been resolved in xD6 (my information was from Dec 2002
when UD5 was only a dream !!)

Keith

-> -----Original Message-----
-> From: Obnoxio The.... [mailto:obnoxio@hotmail.com]
-> Sent: Thursday, June 12, 2003 7:36 AM
-> To: ids@iiug.org
-> Subject: Re: Dropping distributions after upgrade [1340]
->
->
-> export PDQPRIORITY 100
-> export DBUPSTATS 512000
-> export PSORT_NPROCS 4 # do know if this will help, but set
-> it anyway. :-)
-> export PSORT_DBTEMP /tmp1:/tmp2:/tmp3:/tmp4 # any 4 fast
-> filesystems with
-> bags of space
->
-> More below.
->
-> --
-> Bye now,
-> Obnoxio
->
-> "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
-> - Coluche
->
-> From: "John Bejarano " <jbejarano@sbcglobal.net>
-> >
-> >So, we're preparing to upgrade our HDR pair of
-> >production databases from IDS version 7.31.UD1XF to
-> >version 7.31.FD6, which by then will be running on
-> >Solaris 8 64-bit. This upgrade is rising in priority
-> >since we're starting to experience a bug whereby
-> >indexes get corrupted on the secondary server.
-> >
-> >The issue I'm most concerned with is that Informix
-> >recommends dropping the distributions and re-creating
-> >them after the upgrade. This database is over 450
-> >gigabytes in size. Some tables have hundreds of
-> >millions of rows, and many have over one million.
-> >
-> >Not only am I concerned with the amount of time it
-> >takes to actually re-create the statistics, (our
-> >current update statistics cycle runs over 12 hours and
-> >it uses 10 parallel update statistics processes to do
-> >it), but also the amount of time to drop the
-> >distributions in the first place as it must be done
-> >using "update statistics low drop distributions," and
-> >this fires off a giant index re-balancing. When we
-> >did this similar procedure on our smaller 45 gigabyte
-> >database it took several hours just to drop the
-> >distrbitions because of this.
->
-> Have you considered dropping the indexes before the upgrade?
-> Even just the
-> big ones. Then build them fresh using the parameters above.
-> I think you will
-> be pleasantly shocked, provided your tables are fragmented.
->
-> >In addition, we can't upgrade on one box, and then
-> >upgrade on the other because HDR requires the versions
-> >on each side to be the same.
->
-> Break the pair? But you're going to run into some things
-> that can't be done
-> in parallel along the way, so it's not going to help a lot.
->
-> >And, we can't run the
-> >update statistics on the secondary, because, well, you
-> >can't.
->
-> You can, but it does cause an awful lot of trouble and I
-> don't recommend it.
->
-> >This is our main production database, and I can't
-> >bring it down for 12-36 hours as our company will be
-> >out of business for that time. Nor would our website
-> >be very useful if it's up but queries don't work for
-> >hours and hours because there are no distributions.
-> >
-> >Surely we can't be the only large production Informix
-> >database that needs to upgrade our version once in a
-> >while. How can we perform this upgrade in a sensible
-> >way with as little downtime as possible? Or am I just
-> >screwed?
->
-> You are somewhat screwed, I think.
->
-> >Thanks for any insight you can lend.
-> >
-> >Kind regards,
-> >
-> >John Bejarano.
-> >
-> >
-> >
->
-> _________________________________________________________________
-> It's fast, it's easy and it's free. Get MSN Messenger today!
-> http://www.msn.co.uk/messenger
->
->


**********************************************************************************
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.
**********************************************************************************

Messages In This Thread

• Dropping distributions after upgrade
  John Bejarano -- Wednesday, 11 June 2003, at 5:35 p.m.
  • Re: Dropping distributions after upgrade
    Obnoxio The Clown -- Thursday, 12 June 2003, at 2:36 a.m.
    • RE: Dropping distributions after upgrade
      Simmons, Keith -- Thursday, 12 June 2003, at 3:58 a.m.
    • RE: Dropping distributions after upgrade
      Obnoxio The Clown -- Thursday, 12 June 2003, at 12:51 p.m.
      • RE: Dropping distributions after upgrade
        Simmons, Keith -- Friday, 13 June 2003, at 4:24 a.m.
  • Re: Dropping distributions after upgrade
    John Bejarano -- Friday, 13 June 2003, at 9:03 p.m.
    • Re: Dropping distributions after upgrade
      Obnoxio The Clown -- Sunday, 15 June 2003, at 5:36 a.m.
      • Re: Dropping distributions after upgrade
        Mark Scranton -- Monday, 16 June 2003, at 11:31 a.m.
        • Re: Dropping distributions after upgrade
          John Bejarano -- Monday, 16 June 2003, at 2:18 p.m.
    • Fwd: Re: Dropping distributions after upgrade
      ART KAGEL, BLOOMBERG/ 65E 55TH -- Monday, 16 June 2003, at 8:40 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.