Update statistics after the upgrade

All, in a recent post on IIUG forum (http://www.iiug.org/forums/ids/ index.cgi/read/18706) Art and others explained why it's important to run update statistics after a major upgrade. I'd now like to ask how, or more precise, what is the most efficient way to do it in a 24/7 environment, with the least impact on the system? IDS10.00.FC9 being upgraded to IDS11.50FC5 AIX 5.3 The database size is over half terabyte, so not exactly the largest one around, but some heavily used tables have hundreds of millions of rows and it takes significant amount of time to run the usual set of updstats on them. Just dropping the distributions for all tables after the upgrade with "delete from sysdistrib" and then running the usual set of stats for hours is not an option. I presume the optimiser may be completely confused and query plans chosen could be very poor with no distribution data for optimiser to work with. This would then drive query execution times very high and bring the applications to their knees which is effectively almost as having the site down. Not really 24/7. So my current plan is: 0. updstats for all stored procedures for each table: 1. LOW for all indexed columns 2. delete from sysdistrib where tabid = (select tabid from systables where tabname = <current_table>) 3. MEDIUM for all leading index columns (to give basic distrib info to optimiser) 4. HIGH for all leading index columns (to give maximum info for the same columns) 5. HIGH for the first column that differs in multi-column indexes beginning with same subset of columns (as advised by Performance Guide) 6. MEDIUM for all previously not touched columns end for The resulting script would be split into few files for parallel execution. This way I'm hoping to use old distributions (probably useless, but still potentially better than no distribution at all) until the new ones are built, table by table. All suggestions are very much appreciated. Thanks Davorin

A couple of points: - You can combine your #1 & #2 by running the LOW's with the DROP DISTRIBUTIONS clause included. - You can speed things up by combining all MEDIUM columns into a single command (as long as the statement is smaller than 64K) and all of the HIGH columns into a second single command. The engine will then be able to optimize/minimize the number of separate data passes and sorts it has to do to collect the stats. - Run the stats with PDQPRIORITY as high as you can afford to (divided by the number of parallel runs you want to do) without impacting production. - Run the stats with PSORT_NPROCS set to 2x the number of CPU cores on the system (I know the docs say max of 10 - I've seen the engine use more than 10 sort threads). - Run the stats with PSORT_DBTEMP set to at least 3 (but as many as 6) filesystems with enough free space to hold the sort-work files. Try to have each parallel run use different filesystems. - If you are NOT setting PDQPRIORITY set DS_NONPDQ_QUERY_MEM to AT LEAST 50MB (in earlier engines DBUPSPACE controlled in-memory sorting resources and defaulted to 15MB w/50MB max - DS_NONPDQ_QUERY_MEM defaults to 128K if you don't set it to something greater and it overrides any DBUPSPACE setting!) Set this up correctly and most sorting will be done in memory which is a huge gain. - Get my dostats utility and let it do most of this all for you. - Use the drive_dostats script to run multiple copies of dostats in parallel for you automatically. No planning to do, no hand coding long lists of commands. Drive dostats sorts the tables by descending size and and assigns them round-robin to <N> copies of dostats. This gets the largest tables with the most potential to impact the system done first leaving the little guys for last. You can also create your own include or exclude table list to set up a separate parallel run for a few big tables and a separate sequential or parallel run for all of the smaller tables. Dostats and drive_dostats are included in the package utils2_ak which you can download free from the IIUG Software Repository. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 3, 2010 at 11:36 AM, Davorin Kremenjas < davorin.kremenjas@gmail.com> wrote: > All, > > in a recent post on IIUG forum (http://www.iiug.org/forums/ids/ > index.cgi/read/18706) Art and others explained why it's important to > run update statistics after a major upgrade. I'd now like to ask how, > or more precise, what is the most efficient way to do it in a 24/7 > environment, with the least impact on the system? > > IDS10.00.FC9 being upgraded to IDS11.50FC5 > AIX 5.3 > > The database size is over half terabyte, so not exactly the largest > one around, but some heavily used tables have hundreds of millions of > rows and it takes significant amount of time to run the usual set of > updstats on them. Just dropping the distributions for all tables after > the upgrade with "delete from sysdistrib" and then running the usual > set of stats for hours is not an option. I presume the optimiser may > be completely confused and query plans chosen could be very poor with > no distribution data for optimiser to work with. This would then drive > query execution times very high and bring the applications to their > knees which is effectively almost as having the site down. Not really > 24/7. > > So my current plan is: > > 0. updstats for all stored procedures > > for each table: > > 1. LOW for all indexed columns > 2. delete from sysdistrib where tabid = (select tabid from systables > where tabname = <current_table>) > 3. MEDIUM for all leading index columns (to give basic distrib info to > optimiser) > 4. HIGH for all leading index columns (to give maximum info for the > same columns) > 5. HIGH for the first column that differs in multi-column indexes > beginning with same subset of columns (as advised by Performance > Guide) > 6. MEDIUM for all previously not touched columns > > end for > > The resulting script would be split into few files for parallel > execution. > This way I'm hoping to use old distributions (probably useless, but > still potentially better than no distribution at all) until the new > ones are built, table by table. > > All suggestions are very much appreciated. > > Thanks > > Davorin > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >