Re: Mysterious update statistics

Ahh, ok. The first (generating files) is what I did so far, the second (use of drive_dostats) is what I want to do in future. Sorry for the confusion. And thank you for your great assistance and patience. Reinhard. > -----Ursprüngliche Nachricht----- > Von: Art S. Kagel (Oninit) [mailto:art@oninit.com] > Gesendet am: Donnerstag, 17. April 2008 09:09 > An: Habichtsberg, Reinhard > Cc: Informix-List (E-Mail) > Betreff: Re: Mysterious update statistics > > Habichtsberg, Reinhard wrote: > > Hi Art. > > > > Thanks again for your answer. > > > > I'm afraid I don't grasp the following paragraph: > > > > > >>> - I use -a -A 20 and -b -B 5 meaning tables are processed > if the last run > >>> > > on > > > >>> the table is older than 20 days _or_ the number of rows > has changed more > >>> than 5 percent. Is this true especial for the _or_? > >>> > >>> > > > > > >> That's fine if you regenerate the scripts every time you > want to run > >> them. Using -a and -b with scripts that will be reused > over and over > >> doesn't work. > >> > > > > I understood that it is one of the greatest advantages your > dostats provide > > to be able to control which tables are processed. As said > below I wanted to > > call the script with the options -a -A 20 and -b -B 5 as a > daily or weekly > > cronjob. What do you reckon with "regenerate the scripts > every time you want > > to run them"? > > > > Above this you had said: "I use your dostats to generate UPDATE > STATISTICS statements putting them in a file which I run > periodically." > So I was thinking that this means that you run dostats say > once a year > with -f filename and then run that SQL file weekly or daily > or whenever > for a year. If that's what you are doing then using -a or -b doesn't > make much sense. > > I know that that doesn't match with what you said later about running > dostats using drive_dostats with -a & -b which runs I assume are made > either to execute the stats immediately or to generate a > script to run > shortly thereafter. That's no problem, but you can > understand my confusion. > > Art S. Kagel > Oninit > > > Regards, Reinhard. > > > > > >> -----Ursprüngliche Nachricht----- > >> Von: informix-list-bounces@iiug.org > >> [mailto:informix-list-bounces@iiug.org]Im Auftrag von Art S. Kagel > >> (Oninit) > >> Gesendet am: Mittwoch, 16. April 2008 18:03 > >> An: Habichtsberg, Reinhard > >> Cc: Informix-List (E-Mail) > >> Betreff: Re: Mysterious update statistics > >> > >> Habichtsberg, Reinhard wrote: > >> > >>> Hi Art. > >>> > >>> First thank you for the detailed answer. I read the manuals > >>> > >> and the paper of > >> > >>> John Miller now again but some questions remain. > >>> > >>> I use your dostats to generate UPDATE STATISTICS statements > >>> > >> putting them in > >> > >>> a file which I run periodically. I never used parallelism > >>> > >> because the > >> > >>> servers run a high workload 24x7. > >>> > >>> > >> It might be OK to at least use parallel sorting during the > >> update stats > >> runs. I'd set: > >> PDQPRIORITY=34 # or higher allow at least 5 CPU VPS to > >> participate (1/3 > >> * 15) > >> PSORT_NPROCS=15 # OK it's very aggressive to use all 15 CPU > >> VPs for a > >> sort, but you do have billions of rows. Try lower settings > >> first and see > >> how it works out. Adjust as you like - your system your call. > >> > >> > >>> With a new server I try to use your drive_dostats utility. > >>> > >> There is only one > >> > >>> database on this server, though tables with more than a > >>> > >> billion rows. > >> > >>> Version of IDS is 9.40 FC9W2 on Solaris 8, 9, and 10. Host > >>> > >> has 16 processors > >> > >>> and 32 GB memory, 15 CPU VP's are configured, > >>> IDS uses 6.476.800 Kbytes, SHMTOTAL is 12000000, > >>> > >> DS_TOTAL_MEMORY is 300000.> >> > >>> - I have set DBSPACETEMP in onconfig to four exclusive temp > >>> > >> dbspaces. Have I > >> > >>> to set it in the environment again? > >>> > >>> > >> No, the ONCONFIG setting acts as a default. > >> > >>> - When running dostats I see the message "Warning! > >>> > >> Optimized servers > >> > >>> produce statistics fastest with PSORT_DBTEMP set." Manuals > >>> > >> don't recommend > >> > >>> to set PSORT_DBTEMP but DBSPACETEMP what is done in > >>> > >> onconfig. Do I miss > >> > >>> something here? > >>> > >>> > >> OK, this is my thing. DBSPACETEMP space, though it's at least not > >> logged, still will flush to disk as the LRU queues fill with > >> dirty pages > >> and certainly at checkpoint time. This is often unnecessary > >> IO. If you > >> use PDQPRIORITY, and PSORT_NPROCS with PSORT_DBTEMP set to > contain a > >> list of 3 to 6 filesystems each with enough free space to hold the > >> sorted keys of the largest table sorts that cannot fit in memory > >> (DS_TOTAL_MEMORY * PDQPRIORITY/100) will be sent there > >> instead of to the > >> dbspaces listed in DBSPACETEMP. This has several advantages: > >> > >> * Doesn't use the buffer cache so it doesn't affect > >> running queries > >> as much > >> * Takes advantage of the system buffer cache as > sort-work memory > >> * The sort-work files rarely live long enough for the > OS to decide > >> to flush them to disk so writing and rereading them > >> will be almost > >> as fast as the in-memory sorting > >> > >> > >> > >>> - I use -a -A 20 and -b -B 5 meaning tables are processed > >>> > >> if the last run on > >> > >>> the table is older than 20 days _or_ the number of rows > >>> > >> has changed more > >> > >>> than 5 percent. Is this true especial for the _or_? > >>> > >>> > >> That's fine if you regenerate the scripts every time you > want to run > >> them. Using -a and -b with scripts that will be reused > over and over > >> doesn't work. Yes, -a and -b are treated as an OR > >> relationship. Tables > >> satisfying either or both criteria will be processed. > >> > >> > >>> - Is DBUPSPACE of any use if PDQPRIORITY ist set? > >>> > >>> > >> No. With PDQPRIORITY set the memory granted for in-memory > >> sorting will > >> be DS_TOTAL_MEMORY * (PDQPRIORITY/100). With PDQPRIORITY set > >> as below > >> and DS_TOTAL_MEMORY set to 300000 that will give you ~150MB for > >> in-memory sorting. That's 3 times the maximum DBUPSPACE > >> setting of 50MB. > >> > >> > >>> - For running dostats I call drive_dostats in a little > >>> > >> shell script which I > >> > >>> want to run via crontab frequently: > >>> . /home/informix/.informix.rc # Informix environment > >>> PATH=$PATH:/home/informix/tools/utils2_ak; export PATH> >>> DBUPSPACE=50; export DBUPSPACE > >>> PSORT_NPROC=4; export PSORT_NPROC > >>> PDQPRIORITY=50; export PDQPRIORITY > >>> > >>> > >> As noted above, I'm