Dropping distributions after upgrade

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. 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. And, we can't run the update statistics on the secondary, because, well, youcan't. 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? Thanks for any insight you can lend. Kind regards, John Bejarano.

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

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

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

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

Thanks to everyone who has responded. A couple things: We did have the uberlong "update statistics low drop distributions" problem even after upgrading to 7.31.FD6 which I understand is the latest and greatest in the 7.x line. This happened when we upgraded our smaller lab database I keep hearing that this has to do with a buildup of deletion flags in indexes. But, how can it get that big? The btree cleaners wake up every 60 seconds and take care of them, right? I confirmed with onstat -C that they weren't getting much higher than 100 or so. During that first upgrade, I hadn't dropped the distributions and re-created them for 2-3 weeks after the upgrade (since I didn't know it was necessary). Could that have contributed to why it took so long? Is there a way to drop distributions without running low statistics and setting off this process? Thank you for the tip about DBUPSTATS. I'd forgotten about that one. Does the memory that you allot using DBUPSTATS live in the virtual segment? What is the default amount of memory used if DBUPDSTATS is not set? I'd love to drop indexes every once in a while for maintenance and good health, but can't in my 7x24 environment. In addition, it's not the index builds that take so much time (with PDQPRIORITY set high, and liberal fragmentation), it's the re-creation of the constraints that some of these indexes help to enforce. That can take far longer than the index build in our experience. As for PSORT_DBTEMP, does Informix use these spaces in addition to, or instead of, the tempdbspaces that I have already set up (3 each with 2Gb.) > 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 > 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. >

From: "John Bejarano " <jbejarano@sbcglobal.net> > >Thanks to everyone who has responded. A couple >things: > >We did have the uberlong "update statistics low drop >distributions" problem even after upgrading to >7.31.FD6 which I understand is the latest and greatest >in the 7.x line. This happened when we upgraded our >smaller lab database I keep hearing that this has to >do with a buildup of deletion flags in indexes. But, >how can it get that big? The btree cleaners wake up >every 60 seconds and take care of them, right? I Not necessarily. That's why they rewrote the Btree "cleaner" in 9.40 completely. >confirmed with onstat -C that they weren't getting >much higher than 100 or so. During that first >upgrade, I hadn't dropped the distributions and >re-created them for 2-3 weeks after the upgrade (since >I didn't know it was necessary). Could that have >contributed to why it took so long? Is there a way to >drop distributions without running low statistics and >setting off this process? Not that I know of. >Thank you for the tip about DBUPSTATS. I'd forgotten >about that one. Does the memory that you allot using >DBUPSTATS live in the virtual segment? What is the >default amount of memory used if DBUPDSTATS is not >set? Default is 15MB. If you want to use more than 50MB, you have to set PDQPRIORITY. >I'd love to drop indexes every once in a while for >maintenance and good health, but can't in my 7x24 >environment. In addition, it's not the index builds >that take so much time (with PDQPRIORITY set high, and >liberal fragmentation), it's the re-creation of the >constraints that some of these indexes help to >enforce. That can take far longer than the index >build in our experience. Eh? Please explain? >As for PSORT_DBTEMP, does Informix use these spaces in >addition to, or instead of, the tempdbspaces that I >have already set up (3 each with 2Gb.) Instead of. > > 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 > > > 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. > > > > _________________________________________________________________ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile

----- Original Message ----- From: John Bejarano <jbejarano@sbcglobal.net> At: 6/13 22:04 Thanks to everyone who has responded. A couple things: <SNIP> As for PSORT_DBTEMP, does Informix use these spaces in addition to, or instead of, the tempdbspaces that I have already set up (3 each with 2Gb.) The filesystems listed in PSORT_DBTEMP are used only for sort-work space and if this var is set they are used instead of DBSPACETEMP dbspaces to hold these temporary files. Since the files are temporary and short-lived the engine does not open them OSYNC so they take advantage of the system buffer cache and speed up sorting significantly when it has to go to disk. Three filesystems are the minimum you should set PSORT_DBTEMP to list to improve sort performance but 4 or 5 increase performance a bit more if you have the space available. > 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 <SNIP> Art S. Kagel

Hey John - Did you mean DBUPSPACE vs. DBUPSTATS? Mark Mark Scranton Principal Consultant/Teacher IBM Denver IBM Software Group - Data Management Office: 303-773-5067 Cell: 303-929-0914 email: mscranto@us.ibm.com "Obnoxio The...." <obnoxio@hotmail. To: ids@iiug.org com> cc: Sent by: Subject: Re: Dropping distributions after upgrade [1365] forum.subscriber@ iiug.org 06/15/2003 03:36 AM From: "John Bejarano " <jbejarano@sbcglobal.net> > >Thanks to everyone who has responded. A couple >things: > >We did have the uberlong "update statistics low drop >distributions" problem even after upgrading to >7.31.FD6 which I understand is the latest and greatest >in the 7.x line. This happened when we upgraded our >smaller lab database I keep hearing that this has to >do with a buildup of deletion flags in indexes. But, >how can it get that big? The btree cleaners wake up >every 60 seconds and take care of them, right? I Not necessarily. That's why they rewrote the Btree "cleaner" in 9.40 completely. >confirmed with onstat -C that they weren't getting >much higher than 100 or so. During that first >upgrade, I hadn't dropped the distributions and >re-created them for 2-3 weeks after the upgrade (since >I didn't know it was necessary). Could that have >contributed to why it took so long? Is there a way to >drop distributions without running low statistics and >setting off this process? Not that I know of. >Thank you for the tip about DBUPSTATS. I'd forgotten >about that one. Does the memory that you allot using >DBUPSTATS live in the virtual segment? What is the >default amount of memory used if DBUPDSTATS is not >set? Default is 15MB. If you want to use more than 50MB, you have to set PDQPRIORITY. >I'd love to drop indexes every once in a while for >maintenance and good health, but can't in my 7x24 >environment. In addition, it's not the index builds >that take so much time (with PDQPRIORITY set high, and >liberal fragmentation), it's the re-creation of the >constraints that some of these indexes help to >enforce. That can take far longer than the index >build in our experience. Eh? Please explain? >As for PSORT_DBTEMP, does Informix use these spaces in >addition to, or instead of, the tempdbspaces that I >have already set up (3 each with 2Gb.) Instead of. > > 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 > > > 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. > > > > _________________________________________________________________ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile

Mark, Sorry, that should be DBUPSPACE. That's what I meant. Thanks. --John. --- Mark Scranton <mscranto@us.ibm.com> wrote: > > > > > > Hey John - > > Did you mean DBUPSPACE vs. DBUPSTATS? > > Mark > > Mark Scranton > Principal Consultant/Teacher > IBM Denver > > IBM Software Group - Data Management > Office: 303-773-5067 > Cell: 303-929-0914 > email: mscranto@us.ibm.com > > > > > > > > "Obnoxio The...." > > > <obnoxio@hotmail. To: > ids@iiug.org > > com> cc: > > > Sent by: > Subject: Re: Dropping distributions after upgrade > [1365] > forum.subscriber@ > > > iiug.org > > > > > > > > > 06/15/2003 03:36 > > > AM > > > > > > > > > > From: "John Bejarano " <jbejarano@sbcglobal.net> > > > >Thanks to everyone who has responded. A couple > >things: > > > >We did have the uberlong "update statistics low > drop > >distributions" problem even after upgrading to > >7.31.FD6 which I understand is the latest and > greatest > >in the 7.x line. This happened when we upgraded > our > >smaller lab database I keep hearing that this has > to > >do with a buildup of deletion flags in indexes. > But, > >how can it get that big? The btree cleaners wake > up > >every 60 seconds and take care of them, right? I > > Not necessarily. That's why they rewrote the Btree > "cleaner" in 9.40 > completely. > > >confirmed with onstat -C that they weren't getting > >much higher than 100 or so. During that first > >upgrade, I hadn't dropped the distributions and > >re-created them for 2-3 weeks after the upgrade > (since > >I didn't know it was necessary). Could that have > >contributed to why it took so long? Is there a way > to > >drop distributions without running low statistics > and > >setting off this process? > > Not that I know of. > > >Thank you for the tip about DBUPSTATS. I'd > forgotten > >about that one. Does the memory that you allot > using > >DBUPSTATS live in the virtual segment? What is the > >default amount of memory used if DBUPDSTATS is not > >set? > > Default is 15MB. If you want to use more than 50MB, > you have to set > PDQPRIORITY. > > >I'd love to drop indexes every once in a while for > >maintenance and good health, but can't in my 7x24 > >environment. In addition, it's not the index > builds > >that take so much time (with PDQPRIORITY set high, > and > >liberal fragmentation), it's the re-creation of the > >constraints that some of these indexes help to > >enforce. That can take far longer than the index > >build in our experience. > > Eh? Please explain? > > >As for PSORT_DBTEMP, does Informix use these spaces > in > >addition to, or instead of, the tempdbspaces that I > >have already set up (3 each with 2Gb.) > > Instead of. > > > > 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 > > > > > 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. > > > > > > > > > _________________________________________________________________ > On the move? Get Hotmail on your mobile phone > http://www.msn.co.uk/msnmobile > > > > >