IDS Forum

RE: Dropping distributions after upgrade

Posted By: Simmons, Keith
Date: Friday, 13 June 2003, at 4:24 a.m.

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

I certainly did on 7.31 UD4.
Update stats on a 3.3 Million row table, with 18 multi-column indexes, that
had had 250,000 rows recently removed brought the engine to its knees with
Bug 157747.
We ended up having to kill the oninits (Tech Support Advice) because the
engine wouldn't come down and now dare not attempt update stats on this (and
other) tables until we have a chance to rebuild all the indexes or the bug
is fixed.
Luckily the volume of records on this table do not vary overall (we cull
almost as many as we create) and the application (3rd party) 'forces' a
particular index path to be used.

Keith

-> -----Original Message-----
-> From: Obnoxio The.... [mailto:obnoxio@hotmail.com]
-> Sent: Thursday, June 12, 2003 5:51 PM
-> To: ids@iiug.org
-> Subject: RE: Dropping distributions after upgrade [1347]
->
->
-> I didn't have a problem with 7.31.FD2...?
->
-> --
-> Bye now,
-> Obnoxio
->
-> "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
-> - Coluche
->
->
->
->
-> >From: "Simmons, Keith" <keith.simmons@bbslimited.co.uk>
-> >To: "'Obnoxio The....'" <obnoxio@hotmail.com>
-> >CC: "'ids@iiug.org'" <ids@iiug.org>
-> >Subject: RE: Dropping distributions after upgrade [1340]
-> Date: Thu, 12 Jun
-> >2003 08:55:51 +0100
-> >
-> >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.
-> >************************************************************
-> **********************
->
-> _________________________________________________________________
-> Stay in touch with absent friends - get MSN Messenger
-> 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.