Re: Mysterious update statistics
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"?
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 memory
> (DS_TOTAL_MEMORY) settings.
>
> > Did I set the necessary environment variables and is the
> invocation of
> > dostats rather reasonable?
> >
> > Regards,
> > Reinhard.
> >
>
> Art S. Kagel
> Oninit
>
> >
> >
> >> -----Ursprüngliche Nachricht-----
> >> Von: informix-list-bounces@iiug.org
> >> [mailto:informix-list-bounces@iiug.org]Im Auftrag von Art S. Kagel
> >> (Oninit)
> >> Gesendet am: Montag, 14. April 2008 16:10
> >> An: Habichtsberg, Reinhard
> >> Cc: Informix-List (E-Mail)
> >> Betreff: Re: Mysterious update statistics
> >>
> >> Habichtsberg, Reinhard wrote:
> >>
> >>> Hi all.
> >>>
> >>>
> >> Hi.
> >>
> >> First to answer your first direct question. No it doesn't
> make any
> >> difference. The UPDATE STATISTICS commands do not operate
> >> ONLY on the
> >> changes to the table but on the entire table (or a sample of
> >> the table
> >> if MEDIUM level stats are requested).
> >>
> >> No, on to something different. There are several ways to
> >> speed up the
> >> generation of statistics, depending on what version of the
> engine you
> >> are running (it is always