Update Statistics
A database copied from production to a test server took ~19 hours to run "update statistics low/medium/for procedure", versus 3.5 hours in production, with the "medium" step being the bottleneck. Suggestions included reading the Informix Performance Guide and John Miller's update-stats papers, using Art Kagel's dostats or AUS instead of blanket medium stats, skipping "update statistics for procedure", checking SET EXPLAIN output, PDQ, machine/ONCONFIG/environment differences, logical logs, and buffer/read-ahead settings (a wait() on the stack suggests I/O waits). No fix was found, even by IBM support; the poster planned to check disk throughput, so no resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, we took a database from Prod to a test server, and after a week, the
following job is taking almost a day to complete. Can you give me a few ideas
as to where to start looking for the possible culprit?
update statistics low;
update statistics medium;
update statistics for procedure;
I'm just starting to look into what these commands actually do, but any
guidance would be appreciated. In prod, this job takes 3.5 hours, and in test,
it is taking 19 hours...
Benji:
As far as update statistics is concerned, you can learn from reading the
Informix Performance Guide section on the subject as well as John Miller
III's paper on optimizing running update statistics:
https://www.ibm.com/developerworks/data/zones/informix/library/techarticle/mille
r/0203miller.html
And this one:
https://www.ibm.com/developerworks/data/library/techarticle/dm-0803changappa/ind
ex.html
As far as doing it correctly, the best thing is to get my dostats utility
which is part of my utils2a_ak package which you can download from my web
site:
www.askdbmgt.com/my-utilities.html
This is the most popular, and in my jaded opinion the best, tool for
managing update statistics.
As far as why your task takes SO MUCH LONGER on dev than on production,
there are far to many possibilities without more information than we could
possibly cover here. Update stats isn't a bad place to start.
Art
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=s
ig-email&utm_content=webmail&utm_term=icon>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=s
ig-email&utm_content=webmail&utm_term=link>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
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 23, 2017 at 7:14 AM, BENJI LONG <ruggedmouse@hotmail.com> wrote:
> Hi, we took a database from Prod to a test server, and after a week, the
> following job is taking almost a day to complete. Can you give me a few
> ideas
> as to where to start looking for the possible culprit?
>
> update statistics low;
> update statistics medium;
> update statistics for procedure;>
> I'm just starting to look into what these commands actually do, but any
> guidance would be appreciated. In prod, this job takes 3.5 hours, and in
> test,
> it is taking 19 hours...
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Thanks for information Art. I will take a look. They are expecting a quick fix, but I'm not even sure where to start looking.
Are there any differences in the machine specification and / or the ONCONFIG? Are there any differences in environment variables? > On 23 Jun 2017, at 13:29, BENJI LONG <ruggedmouse@hotmail.com> wrote: > > Thanks for information Art. I will take a look. They are expecting a quick > fix, but I'm not even sure where to start looking. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Everything is the same, except possible change in data, which I'm waiting for an answer on. The first command finishes quickly. It is hung up on the second one (update statistics medium)
what kind of things could I check in the onconfig file. They are working on getting me IBM technical support help, but that will be a few hours away.
is it possible for a system wait() call to be hung in the database?
That makes very little sense. Does it complete, or is it just sitting there? Your logical logs might be full. > On 23 Jun 2017, at 14:42, BENJI LONG <ruggedmouse@hotmail.com> wrote: > > Everything is the same, except possible change in data, which I'm waiting for > an answer on. > > The first command finishes quickly. It is hung up on the second one (update > statistics medium) > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
> The first command finishes quickly. It is hung up on the second one (update statistics medium) With "update statistics medium" it is easy to see what is going on by first doing "set explain on" and looking at what is happening in the sqexplain.out file. If you are using PDQ, it is probably slowing you down. I would recommend against running "update statistics medium" in this way. I have experimented with something similar to this a few times and it's fair to say that the optimiser can choose poor query plans without high mode distributions on indexed columns. You'd be far better off using either Auto Update Statistics (AUS) or dostats to do this. Running "update statistics for procedure" is rarely necessary and can be bad for concurrency on a live busy system. Updating statistics on a table will increment its version number flagging all procedures referencing it for recompilation the next time they are called. Unless you are preparing a new system for first time use and want to avoid procedures plans being recompiled on first use, don't bother. I am currently preparing a blog post on this subject. Ben.
The update statistics is taking 19 hours to complete. There are 36 logical logs. They each have a flag of U-B---- except the current one in use which is U---C-L and has only 25 % used. This is what I see in PROD as well, so the logs look like they are good to me.
Hi, the problem with making changes is they are doing development that will go into PROD, so they want this test instance to be the same as PROD. This job has been working for years in PROD. I haven't got an answer as to what the developers changed in TEST yet.
If you are seeing wait() at the top of the stack, then that means that the process is waiting on an IO completion. If you are constantly seeing wait() on top of the stack, then that implies that there might be a configuration issue. Perhaps your buffer size is too small, or your read ahead is too small. Madison Pruet Retired and Loving it On Friday, June 23, 2017 9:04 AM, BENJI LONG <ruggedmouse@hotmail.com> wrote: is it possible for a system wait() call to be hung in the database? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
No solution was found with IBM. They said the same as I already knew. The job was slow. On Monday, we will take a look at the throughput of the disks, and I will report back here on what we found.