Update Stats Time

We are currently running IDS 7.3 (I know its time to upgrade) and we are having problems with all the stats and backup completing in time for the server to reboot. I am looking for a way to make the stats complete faster. Some nights they run for 4 hours. I have tuned the DBUPSPACE with not significant improvement and was looking to maybe use PDQ. Here is the output from the Memory Grant Manager, why does it look as if it is allocated but not being used? Any other tips would are also welcomed. Thanks much! Informix Dynamic Server Version 7.31.UD3 -- On-Line -- Up 1 days 20:49:31 -- 1656896 Kbytes Memory Grant Manager (MGM) -------------------------- MAX_PDQPRIORITY: 10 DS_MAX_QUERIES: 25 DS_MAX_SCANS: 1048576 DS_TOTAL_MEMORY: 4000000 KB Queries: Active Ready Maximum 12 3 25 Memory: Total Free Quantum (KB) 4000000 0 160000 Scans: Total Free Quantum 1048576 1048564 1 Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit) Gate 1 Gate 2 Gate 3 Gate 4 Gate 5 (Queue Length) 1 0 2 0 0 Active Queries: --------------- Session Query Priority Thread Memory Scans Gate 27326 62a9c744 15 0/60000 0/1 - 27331 6ce3c97c 10 0/40000 0/1 - 27333 6d0e08c4 10 0/40000 0/1 - 27330 62ffe10c 10 0/40000 0/1 - 27325 6d35602c 10 0/40000 0/1 - 27335 646c602c 10 0/40000 0/1 - 27365 6d52802c 10 0/40000 0/1 - 27359 6d41265c 10 0/40000 0/1 - 27360 6d0e28b4 10 0/40000 0/1 - 27324 6486a25c 10 0/40000 0/1 - 27363 6d3e08d4 10 0/40000 0/1 - 27361 6d4f3344 10 0/40000 0/1 - Ready Queries: -------------- Session Query Priority Thread Memory Scans Gate 27372 6d484abc 10 7f720018 0/40000 0/1 1 27366 6477820c 10 606975f0 0/40000 0/1 3 27362 6d3b699c 10 6d3cc170 0/40000 0/1 3 Free Resource Average # Minimum # -------------- --------------- --------- Memory 46274.5 +- 0.0 0 Scans 1048565.3 +- 0.0 1048563 Queries Average # Maximum # Total # -------------- --------------- --------- ------- Active 9.8 +- 2.7 12 51 Ready 5.9 +- 4.5 15 32 Resource/Lock Cycle Prevention count: 0

DAVE HAYDU wrote: > We are currently running IDS 7.3 (I know its time to upgrade) and we are > having problems with all the stats and backup completing in time for the > server to reboot. I am looking for a way to make the stats complete faster. > Some nights they run for 4 hours. I have tuned the DBUPSPACE with not > significant improvement and was looking to maybe use PDQ. Here is the output > from the Memory Grant Manager, why does it look as if it is allocated but not > being used? Any other tips would are also welcomed. Thanks much! > MGM memory is only used for sorting & update statistics in 7.31 (I assume you didn't mean 7.30) if PDQPRIORITY is greater than 1. You can also try enabling parallel sorting by using the environment variable PSORT_NPROCS to set the number of concurrent sort threads used and optionally use PSORT_DBTEMP to redirect sort-work files from temp dbspaces to filesystem (use 3 to 6 filesystems preferably on separate structures for best performance). Sort-work files are so short lived that using filesystem space instead of dbspace space prevents them from actually being written to disk reducing system overhead and possibly sort runtime. Sorting is the biggest part of gathering the stats for update statistics. Question: How are you running update stats? At the whole DB level? By table? Individual key sets? Are you following the recommendations in the Performance Guide and/or John Miller's white paper (or using dostats)? If you are using dostats then: 1- consider using drive_dostats to update multiple tables in parallel, and 2- consider using the -a/-A and -b/-B options to update tables a few at a time as needed by running with those options every day. That will reduce the daily. Not every table needs its stats recalculated daily, those options select tables based on criteria you provide so that the stats for all tables are useful and as up-to-date as needed without running every table every night. Art S. Kagel Oninit ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========

Art, we are currently using IDS 7.31UD3. I have the PSORT_NPROCS variable set to 3. We are currently running stats by table and our script generates multiple sql scripts so the tables are done in parallel. I was following with the recommendations in the performance guide and working with a script provided in our venders application. The flags that you are referencing -a/-A or -b/-B are apart of which script? It also appears when I try to do multiple database at once I start to run into IO problems as well.

Dave The flags are part of dostats from IIUG and written by Art. If you are having IO issues when running update stats, then your Disks are the limiting factor and getting more or faster disks is the issue you face. Dostats may help you since it will reduce the volume of stats required daily. If you need to reboot your server, just do not run update stats that night. MW -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVE HAYDU Sent: Wednesday, 16 January 2008 9:11 a.m. To: ids@iiug.org Subject: Re: Update Stats Time [10916] Art, we are currently using IDS 7.31UD3. I have the PSORT_NPROCS variable set to 3. We are currently running stats by table and our script generates multiple sql scripts so the tables are done in parallel. I was following with the recommendations in the performance guide and working with a script provided in our venders application. The flags that you are referencing -a/-A or -b/-B are apart of which script? It also appears when I try to do multiple database at once I start to run into IO problems as well. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

DAVE HAYDU wrote: > Art, we are currently using IDS 7.31UD3. I have the PSORT_NPROCS variable set > to 3. We are currently running stats by table and our script generates > multiple sql scripts so the tables are done in parallel. I was following with > the recommendations in the performance guide and working with a script > So far so good. However, you are using 7.31UD3 which is an early version which includes several optimizations to how the data distributions are gathered. The result of that optimization is that if you take advantage of those new features, by executing fewer statements containing more columns in each, the total runtime will be reduced. See John Miller III's white paper: http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/ 0203miller.html Dostats is an ESQL/C application I wrote that implements all of the recommendations in the Performance Guide and in John's paper, with several options, including the two pairs of options I mentioned, -a/-A and -b/-B. Dostats detects the server version and adjusts its operations depending on whether you are running it against an optimized version of the server or not (details in John's paper, but you are running an optimized version). On an optimized server John's recommendations are triggered and dostats builds massive update statistics statements up to 64K in length (the maximum IDS will allow) containing a list of all columns that require updating at each level for each table. (There's even an option to reverse the behavior of the version detection so you can test which set of output runs better on your particular system as some smaller systems still work better using the older algorithm.) Here's a description of the dostats options I mentioned. Using them you'll minimize the number of tables actually updated each night. -a I call the aging option. It selects tables to update ONLY if their existing data distributions are at least <N> day old, you can select the aging timeframe with the -A option by looking at the sysdistrib table entry for every column in the table and comparing today to the oldest such record. -b I call the browsing option. It selects tables to update ONLY if the number of rows in the table has changed (up or down) by more than a specified percentage of the table's size the last time stats were updated to at least LOW levels. The -B options allows you to set the detection threshold. This is done, if you want to write your own version, by comparing the nrows column in systables (which is only maintained by UPDATE STATISTICS LOW and HIGH) with that stored in the table's partition header page (sysmaster:sysptnhdr.nrows) which is always up-to-date. The browsing and aging options can work together in an OR fashion to select tables that satisfy either criterion. At my former employer they typically run: dostats -d mydatabase -a -A 7 -d -D 15 daily so that every table is updates at least once a week and more frequently if they are more volatile. They also have a larger window on weekends, so they run certain databases without the -a/-A or -b/-B options on the weekend to reduce the load during the week even further. So all tables in those databases get updated on the weekend with volatile tables being updated again sometime during the week. Less critical databases are run only during the week with the aging and browse options while low volatility database are only run on the weekend or monthly without them. My drive dostats script works with dostats and it's -i @table_list_file feature to run <M> copies of dostats each with a list of tables to update. Both dostats and drive_dostats are included in the package utils2_ak which you can download from the IIUG Software Repository. > provided in our venders application. The flags that you are referencing -a/-A > or -b/-B are apart of which script? It also appears when I try to do multiple > database at once I start to run into IO problems as well. Yes, there is a limit to how many tables you can update in parallel given your available memory, CPU, and IO resources. Unfortunately, there's no formula. You have to determine that limit yourself for your system mostly by trial and error. Art S. Kagel Oninit ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========