Re: Update statistics after the upgrade

Hi Art, thanks for the answer. Further questions/comments embedded below: >    - You can combine your #1 & #2 by running the LOW's with the DROP >    DISTRIBUTIONS clause included. I think I remember reading here on c.d.i that DROP DISTRIBUTIONS can be slow, and you asserting "delete from sysdistrib" is safe so I though of going safe&quick by splitting it into two steps. >    - 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. Sure, that was the plan... >    - 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. I'll definitely use PDQPRIORITY. We have plenty of physical memory on the box to assign it to virtual memory so PDQ/MGM can use it. I thought this eliminates the need to use PSORT_NPROCS and PSORT_DBTEMP? >    - 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. Was thinking of this, two little problems with it: 1. For a non-C programmer, compiling this on AIX is a nightmare (just spent half a day on it with little result). On my Linux desktop one "make" was enough. 2. dostats output to a file produces multiple LOW statements per table (as far as I can see it produces a statement for each index of the table) contrary to the suggestions above. I know, a bit of vi-ing and awk-ing and that's fixed, but I already have a script to generates update stats taking this into an account. Again, thanks for the valuable input. Davorin

See my responses below: 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 Thu, Feb 4, 2010 at 7:40 AM, Davorin Kremenjas < davorin.kremenjas@gmail.com> wrote: > Hi Art, > > thanks for the answer. > Further questions/comments embedded below: > > > - You can combine your #1 & #2 by running the LOW's with the DROP > > DISTRIBUTIONS clause included. > > I think I remember reading here on c.d.i that DROP DISTRIBUTIONS can > be slow, and you asserting "delete from sysdistrib" is safe so I > though of going safe&quick by splitting it into two steps. > Yup. Works either way. > > > - 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. > > Sure, that was the plan... > > > - 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. > > I'll definitely use PDQPRIORITY. We have plenty of physical memory on > the box to assign it to virtual memory so PDQ/MGM can use it. > I thought this eliminates the need to use PSORT_NPROCS and > PSORT_DBTEMP? > IFF you can assign enough memory so the sorts NEVER go to disk you don't need PSORT_DBTEMP, but sorting is single threaded unless PSORT_NPROCS is set. The fetches to get data for the sorting may be parallelized just with PDQPRIORITY depending on whether the table is fragmented but not the sort. > > > - 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. > > Was thinking of this, two little problems with it: > 1. For a non-C programmer, compiling this on AIX is a nightmare (just > spent half a day on it with little result). On my Linux desktop one > "make" was enough. > Yeah, finally working on 'configurizing' the package. I can help get you compiled if you want. Send me output from the make. 2. dostats output to a file produces multiple LOW statements per table > (as far as I can see it produces a statement for each index of the > table) contrary to the suggestions above. I know, a bit of vi-ing and > awk-ing and that's fixed, but I already have a script to generates > update stats taking this into an account. > Yeah, there's a LOW for each index key. In the most recent versions that's not strictly needed (John Miller just informed me of that a couple of months ago), the HIGH's take care of it for you if you don't do DISTRIBUTION ONLY. I have to remove the LOWs and DISTRIBUTION ONLY clauses in the next release. The overall LOW isn't needed at all any more since dostats does all columns either MEDIUM or HIGH. The MEDIUM doesn't do sysindex stats, but only non-key columns are MEDIUM. > > Again, thanks for the valuable input. > > Davorin > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Hi Art, thanks, I understand now the importance of PSORT_* parameters, will use them too. As for the compiling on AIX, I'll give it another try, and bother you privately if no luck. Regards Davorin