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 a bit more aggressive with the PSORT_ parameters, >> but that looks workable. Just add PSORT_DBTEMP if you have the >> filesystems available. (Certainly test it both ways to >> verify that I'm >> not nuts here.) >> >> >>> ### drive_dostats nprocs database [tablespec] >>> >> [-x@excl|-xexctbl] [dostats >> >>> options] >>> >>> drive_dostats 5 kva -xkva_ergebnisse_alt -a -A 20 -b -B 5 >>> >> -X -v 2 -E -F -R >> >>> 0.1 -r 1.0 -m >>> >>> >> OK, so this runs 5 copies of dostats for database 'kva' >> except for table >> 'kva_ergebnisse_alt' including system catalog tables. Looks >> reasonable >> and the higher resolutions are a good idea for larger tables, >> so go for >> it. You can use the timing stats generated by the -E option >> to compare >> runs with different options (like PSORT_DBTEMP or not; >> PSORT_NROCS set >> ot 5, 10, 15, 30; different PDQPRIORITY; more or less MGM