 |
IDS Forum
Re: Update statistics issue
Posted By: medkba
Date: Friday, 27 June 2014, at 2:06 p.m.
In Response To: Re: Update statistics issue (Art Kagel)
Art,
I have given SET PDQPRIORITY 100; UPDATE STATISTICS FOR
TABLE chksum; and job running but not improving i.e. speed
is there any place to check whether PDQPRIORITY takes place?
Thank you very much
On Sat, Jun 28, 2014 at 1:30 AM, medkba <medkba@gmail.com> wrote:
> Art,
>
> Ok, I has enabled AUS
>
> Thank you very much
>
>
>
>
> On Sat, Jun 28, 2014 at 1:18 AM, Art Kagel <art.kagel@gmail.com> wrote:
>
>> Ooo, now you're getting into a bone of contention between very good
>> friends. John wrote the AUS functions and has good reason to think they
>> are doing the best job possible. I wrote dostats and have good reason to
>> think that it does the best job possible. Honestly in most cases both will
>> work excellently. There are some specific databases on which one or the
>> other will either be faster or will produce slightly better quality data
>> distributions. One nice feature of AUS is that it will duplicate your
>> existing levels of distributions if you turn on that flag in the
>> ph_threshold table so you can preset the levels of distributions you want
>> using dostats and then let AUS maintain them over time. You can do the
>> same with my tools by using the myschema --distributions=filename option
>> to
>> generate an SQL script you can run to reproduce/refresh the current level
>> of distributions.
>>
>> Art
>>
>> Art S. Kagel, Principal Consultant
>> ASK Database Management
>>
>> Blog: http://informix-myview.blogspot.com/
>>
>> Disclaimer: Please keep in mind that my own opinions are my own opinions
>> and do not reflect on 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 Fri, Jun 27, 2014 at 12:46 PM, medkba <medkba@gmail.com> wrote:
>>
>> > Art and John,
>> >
>> > Which is best way either to do manual or automatic. My Informix engine
>> > version is 11.50 FC9.
>> >
>> > Currently, this job is running manual using shell script on the HP-UX
>> > PA-RISC and recently we have migrated to HP integrity server so that
>> > expectation of this job running faster but it is slower compare to HP-UX
>> >
>> > Thank you very much
>> >
>> > On Sat, Jun 28, 2014 at 12:40 AM, John Miller iii <miller3@us.ibm.com>
>> > wrote:
>> >
>> > > Art:
>> > >
>> > > The newer (all the C versions) of aus_refesh wait for update to 5
>> minutes
>> > > for the aus_evaluator to
>> > > complete before starting any real processing. The accomplish this by
>> > > checking for the
>> > > existence of the aus_command table. If this table exists then the
>> > > aus_refresh will
>> > > sleep for 15 seconds and check again. It will loop for 20 times before
>> > > proceeding
>> > > I see now that a maximum of 5 minutes of wait time is to short and
>> will
>> > > increase
>> > > this wait time.
>> > >
>> > > John F. Miller III
>> > > STSM, Lead Architect
>> > > miller3@us.ibm.com
>> > > 503-747-1366
>> > > IBM Informix Dynamic Server (IDS)
>> > >
>> > > ids-bounces@iiug.org wrote on 06/27/2014 09:20:31 AM:
>> > >
>> > > > From: "Art Kagel" <art.kagel@gmail.com>
>> > > > To: ids@iiug.org,
>> > > > Date: 06/27/2014 09:22 AM
>> > > > Subject: Re: Update statistics issue [33302]
>> > > > Sent by: ids-bounces@iiug.org
>> > > >
>> > > > Yes, either disable them or change them to run further apart and
>> rely
>> > on
>> > > > what they do and disable your own scripts. Right now it looks like
>> the
>> > > > refresh is running 11 minutes after the evaluator starts running and
>> > that
>> > >
>> > > > can't be right unless your database has very few tables. If you
>> decide
>> > to
>> > >
>> > > > keep AUS running and abandon your own scripts then you should look
>> in
>> > the
>> > >
>> > > > ph_run table to see how long the evaluator actually runs for
>> (columns
>> > > > duration) and change the scheduling for the AUS refresh task:
>> > > >
>> > > > select * from ph_run where run_task_id = 18;
>> > > >
>> > > > Art
>> > > >
>> > > > Art S. Kagel, Principal Consultant
>> > > > ASK Database Management
>> > > >
>> > > > Blog: http://informix-myview.blogspot.com/
>> > > >
>> > > > Disclaimer: Please keep in mind that my own opinions are my own
>> > opinions
>> > > > and do not reflect on 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 Fri, Jun 27, 2014 at 11:51 AM, medkba <medkba@gmail.com> wrote:
>> > > >
>> > > > > Thank you very much Art
>> > > > >
>> > > > > The below output executing the query
>> > > > >
>> > > > > tk_id 18
>> > > > > tk_name Auto Update Statistics Evaluation
>> > > > > tk_enable t
>> > > > > tk_next_execution 2014-06-28 01:00:00
>> > > > >
>> > > > > tk_id 19
>> > > > > tk_name Auto Update Statistics Refresh
>> > > > > tk_enable t
>> > > > > tk_next_execution 2014-06-28 01:11:00
>> > > > >
>> > > > > It means that statistics running automatically. Am i right? I
>> need to
>> > > > > disable both
>> > > > >
>> > > > > On Fri, Jun 27, 2014 at 11:39 PM, Art Kagel <art.kagel@gmail.com>
>> > > wrote:
>> > > > >
>> > > > > > There are two AUS tasks, the AUS evaluator that determines what
>> > > tables
>> > > > > need
>> > > > > > their stats updated and the AUS refresh task that executes the
>> > > commands
>> > > > > > calculated by the evaluator. You can find the task manager
>> records
>> > > > > > governing their execution in the sysadmin database in the table
>> > > ph_task.
>> > > > > > Run:
>> > > > > >
>> > > > > > select tk_id, tk_name, tk_enable, tk_next_execution
>> > > > > > from sysadmin:ph_task
>> > > > > > where tk_execute matches 'aus*';
>> > > > > >
>> > > > > > If tk_enable is set to 't' then the AUS tasks are running.
>> > Obviously
>> > > if
>> > > > > > the evaluator is executing but the refresh is not, then stats
>> are
>> > not
>> > >
>> > > > > being
>> > > > > > updated by AUS and if the refresh is running without the
>> evaluator
>> > > > > running
>> > > > > > before it then the list of work its doing is probably
>> out-of-date
>> > and
>> > > may
>> > > > > > even be empty. The tk_next_execution column will tell you when
>> each
>> > > is
>> > > > > > scheduled to run next. Note that in 11.50 the evaluator was
>> rather
>> > > > > > inefficient and could take longer to run than the default
>> > difference
>> > > in
>> > > > > the
>> > > > > > scheduled run times of the two tasks (1 hour) so that when the
>> > > refresh
>> > > > > > launched the evaluator may not have finished running resulting
>> in
>> > the
>> > >
>> > > > > > refresh only processing some of the tables that needed to
>> updated.
>> > > This
>> > > > > > was fixed in 11.70 and again in 12.10 by recoding the evaluator
>> to
>> > > run
>> > > > > > faster and by changing the default scheduling of the tasks so
>> they
>> > > run
>> > > > > > further apart.
>> > > > > >
>> > > > > > Art
>> > > > > >
>> > > > > > Art S. Kagel, Principal Consultant
>> > > > > > ASK Database Management
>> > > > > >
>> > > > > > Blog: http://informix-myview.blogspot.com/
>> > > > > >
>> > > > > > Disclaimer: Please keep in mind that my own opinions are my own
>> > > opinions
>> > > > > > and do not reflect on 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 Fri, Jun 27, 2014 at 10:05 AM, medkba <medkba@gmail.com>
>> wrote:
>> > > > > >
>> > > > > > > Yes, i have running update statistics one of the database i.e.
>> > most
>> > >
>> > > > > used
>> > > > > > > the application
>> > > > > > > Run UPDATE STATISTICS LOW
>> > > > > > > Ok, i will read this article
>> > > > > > > Dosstat runs weekly once
>> > > > > > > This is new hp blade server with 32gb memory. It means that
>> > > hardware
>> > > > > very
>> > > > > > > powerful but statistics job running slow then the question
>> aries
>> > > why
>> > > > > slow
>> > > > > > > and hard to convenience management to change different way of
>> > > running.
>> > > > > > > How do i check aus running
>> > > > > > > Thank you
>> > > > > > > On 27 Jun 2014 21:18, "Art Kagel" <art.kagel@gmail.com>
>> wrote:
>> > > > > > >
>> > > > > > > > "from a shell script" wasn't what I was looking for. Are you
>> > > running
>> > > > > > plan
>> > > > > > > > UPDATE STATISTICS? UPDATE STATISTICS LOW? UPDATE STATISTICS
>> > > MEDIUM?
>> > > > > > > > UPDATE STATISTICS HIGH? Combinations of multiple commands?
>> Are
>> > > you
>> > > > > > > > running these in dbaccess? If so are you running against
>> just
>> > > this
>> > > > > one
>> > > > > > > > table in each command or against the entire database? Are
>> you
>> > > using
>> > > > > my
>> > > > > > > > dostats utility to generate an optimal set of commands?
>> > > > > > > >
>> > > > > > > > OK, first thing to do then is to read John's paper. Link
>> here:
>> > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > >
>> > > > > > >
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > > http://www.ibm.com/developerworks/data/zones/informix/library/
>> > > > techarticle/miller/0203miller.html#section3
>> > > > > > > >
>> > > > > > > > Quick-and-dirty:
>> > > > > > > >
>> > > > > > > > export PDQPRIORITY=100 # Or as high as you dare without
>> > > disrupting
>> > > > > > > > production resource requirements.
>> > > > > > > > export PSORT_NPROCS=16 # I know the max documented is 10,
>> trust
>> > > me.
>> > > > > > > > Make sure that you have lots of memory allocated to
>> > > DS_TOTAL_MEMORY
>> > > > > to
>> > > > > > > > allow for all sorting to be accomplished in memory. If you
>> want
>> > > to
>> > > > > know
>> > > > > > > how
>> > > > > > > > much memory you will need and you haven't zero'd out your
>> > server
>> > > > > stats
>> > > > > > > > since the last time you tried running the update statistics
>> (ie
>> > > > > onstat
>> > > > > > > -z)
>> > > > > > > > then the sysmaster:sysprofile table has a row containing the
>> > size
>> > > of
>> > > > > > the
>> > > > > > > > largest sort that's been executed in KB:
>> > > > > > > >
>> > > > > > > > select value from sysmaster:sysprofile where name =
>> > > 'maxsortspace';
>> > > > > > > >
>> > > > > > > > I think that I remember that you are using 11.50 so you
>> can't
>> > > take
>> > > > > > > > advantage or LOW sampling.
>> > > > > > > >
>> > > > > > > > BTW, in another reply you said you are only runing vanilla
>> > UPDATE
>> > >
>> > > > > > > > STATISTICS with no modifiers. This command does not update
>> the
>> > > data
>> > > > > > > > distributions for the table, it only updates a few columns
>> in
>> > > > > > systables,
>> > > > > > > > syscolumns, sysfragments, and sysindices/sysindexes like
>> nrows
>> > > and
>> > > > > > npused
>> > > > > > > > in systables, colmin and colmax in syscolumns, nlevels,
>> > nleaves,
>> > > > > uniq,
>> > > > > > > > clust, and nrows in sysindices. You need to run a
>> combination
>> > of
>> > > LOW,
>> > > > > > > > MEDIUM, and HIGH on individual sets of columns to get really
>> > > useful
>> > > > > > data
>> > > > > > > > distributions in minimum time. If you use my dostats
>> utility,
>> > as
>> > > do
>> > > > > > > > hundreds of Informix sites, it will take care of that for
>> you.
>> > > > > > > >
>> > > > > > > > BTW, have you disabled Auto Update Statistics (AUS)?
>> Because if
>> > > not
>> > > > > > then
>> > > > > > > > AUS is running every night updating stats for you (almost as
>> > well
>> > > as
>> > > > > > > > dostats would) so you should not have to be running update
>> > > statistics
>> > > > > > > > manually (or from cron) anyway.
>> > > > > > > >
>> > > > > > > > Art
>> > > > > > > >
>> > > > > > > > Art S. Kagel, Principal Consultant
>> > > > > > > > ASK Database Management
>> > > > > > > >
>> > > > > > > > Blog: http://informix-myview.blogspot.com/
>> > > > > > > >
>> > > > > > > > Disclaimer: Please keep in mind that my own opinions are my
>> own
>> > > > > > opinions
>> > > > > > > > and do not reflect on 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
>> > affiliatedwith
>> > > any
>> > > > > > > > entity with which I am affiliated nor those of the entities
>> > > > > themselves.
>> > > > > > > >
>> > > > > > > > On Fri, Jun 27, 2014 at 8:48 AM, medkba <medkba@gmail.com>
>> > > wrote:
>> > >
>> > > > > > > >
>> > > > > > > > > Running from cron jobs daily from shell script
>> > > > > > > > > Not using PDQPRIORITY
>> > > > > > > > > 23 CPUs
>> > > > > > > > > 8 cpu vps is used increased from 6 to 8 no improvement
>> > > > > > > > >
>> > > > > > > > > Please let me if require more information
>> > > > > > > > >
>> > > > > > > > > Thank you very much
>> > > > > > > > > On 27 Jun 2014 19:43, "Art Kagel" <art.kagel@gmail.com>
>> > wrote:
>> > > > > > > > >
>> > > > > > > > > > How are you running update statistics for that table?
>> What
>> > > > > > > environment
>> > > > > > > > > > variables have you set? Are you using PDQPRIORITY? What
>> > > engine
>> > > > > > > version
>> > > > > > > > > > and edition are you using? How many cores are on the
>> > machine
>> > > and
>> > > > > > how
>> > > > > > > > many
>> > > > > > > > > > CPU VPs are configured?
>> > > > > > > > > >
>> > > > > > > > > > Have you read John Miller's paper in optimizing update
>> > > statistics
>> > > > > > > runs?
>> > > > > > > > > >
>> > > > > > > > > > Art
>> > > > > > > > > >
>> > > > > > > > > > Art S. Kagel, Principal Consultant
>> > > > > > > > > > ASK Database Management
>> > > > > > > > > >
>> > > > > > > > > > Blog: http://informix-myview.blogspot.com/
>> > > > > > > > > >
>> > > > > > > > > > Disclaimer: Please keep in mind that my own opinions
>> are my
>> > > own
>> > > > > > > > opinions
>> > > > > > > > > > and do not reflect on 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 Fri, Jun 27, 2014 at 3:30 AM, medkba <
>> medkba@gmail.com>
>> > > > > wrote:
>> > > > > > > > > >
>> > > > > > > > > > > Hi All,
>> > > > > > > > > > >
>> > > > > > > > > > > I am having one table and contains data 23326189 and
>> > taking
>> > >
>> > > > > about
>> > > > > > > 15
>> > > > > > > > > mins
>> > > > > > > > > > > to complete update statistics
>> > > > > > > > > > >
>> > > > > > > > > > > Is there any opportunities to minimize this time?
>> > > > > > > > > > >
>> > > > > > > > > > > thank you very much
>> > > > > > > > > > >
>> > > > > > > > > > > --001a11c35338ab010704fccc4622
>> > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > >
>> > > > > > >
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>> *******************************************************************************
>> > >
>> > > > > > > > > > > Forum Note: Use "Reply" to post a response in the
>> > > discussion
>> > > > > > forum.
>> > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > > > --001a11c3eaaa3f037b04fccfcec4
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > >
>> > > > > > >
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>> *******************************************************************************
>> > >
>> > > > > > > > > > Forum Note: Use "Reply" to post a response in the
>> > discussion
>> > > > > forum.
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > >
>> > > > > > > > > --089e0158b87c65336e04fcd0b829
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > >
>> > > > > > >
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>> *******************************************************************************
>> > >
>> > > > > > > > > Forum Note: Use "Reply" to post a response in the
>> discussion
>> > > forum.
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > >
>> > > > > > > > --089e01182b54c5879104fcd123c5
>> > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > >
>> > > > > > >
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>> *******************************************************************************
>> > >
>> > > > > > > > Forum Note: Use "Reply" to post a response in the discussion
>> > > forum.
>> > > > > > > >
>> > > > > > > >
>> > > > > > >
>> > > > > > > --001a1133d1e23364d104fcd1ce86
>> > > > > > >
>> > > > > > >
>> > > > > > >
>> > > > > > >
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>> *******************************************************************************
>> > >
>> > > > > > > Forum Note: Use "Reply" to post a response in the discussion
>> > forum.
>> > >
>> > > > > > >
>> > > > > > >
>> > > > > >
>> > > > > > --001a11c3c44290f36d04fcd31c93
>> > > > > >
>> > > > > >
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>> *******************************************************************************
>> > >
>> > > > > > Forum Note: Use "Reply" to post a response in the discussion
>> forum.
>> > > > > >
>> > > > > >
>> > > > >
>> > > > > --001a1132edec24236a04fcd34826
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>> *******************************************************************************
>> > >
>> > > > > Forum Note: Use "Reply" to post a response in the discussion
>> forum.
>> > > > >
>> > > > >
>> > > >
>> > > > --001a11c34198a6cf4d04fcd3af97
>> > > >
>> > > >
>> > > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>> *******************************************************************************
>> > >
>> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
>> > > >
>> > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>> *******************************************************************************
>> > > Forum Note: Use "Reply" to post a response in the discussion forum.
>> > >
>> > >
>> >
>> > --001a11c268dac7efb604fcd40d76
>> >
>> >
>> >
>> >
>>
>> *******************************************************************************
>> > Forum Note: Use "Reply" to post a response in the discussion forum.
>> >
>> >
>>
>> --089e011826804a1c8504fcd47da0
>>
>>
>>
>> *******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
--14dae93d8c8659436704fcd52985
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
