IDS Forum

Re: Update stats impacts insert performance

Posted By: Art Kagel
Date: Wednesday, 19 August 2009, at 1:29 p.m.

In Response To: Update stats impacts insert performance (HOWARD ANDERSON)

If you post the list of all indexes (including any constraints supported by
hidden indexes) I will post my recommended set of commands.

What you are currently doing was optimal long ago, however, since IDS v
7.31xD3 (and 9.30xC2) and all newer servers, there are more efficient ways
to gather the required statistics in minimal time and with minimal impact.
Also note that several environment variables will positively or negatively
affect the runtimes of your update statistics commands and you may not be
configuring these to take best advantage of the resources you have
available. Please read John Miller III's white paper that details the
changes made to these newer servers and how to take advantage of them to
speed up processing of UPDATE STATISTICS (link below) and also note that my
dostats utility implements the protocols that John describes automatically
for you (though you still have to set the environment and server
configuration properly for best results). Dostats is part of the package
utils2_ak which you can download from the Oninit web site (
www.oninit.com/utils) or the IIUG Software Repository (www.iiug.org/software
).

Link to John's paper:
Understanding and Tuning Update
Statistics<http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html>:
http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Wed, Aug 19, 2009 at 1:19 PM, HOWARD ANDERSON <
howard.anderson@edl.uk.eds.com> wrote:

> My customer has a large database table (over 1000,000,000 rows) stored in
> Informix 7.31FD7-1 on a Unix server.
> The table forms an active part of a 24/7 OLTP production system that
> handles
> around 860,000 inserts per day.
> No data is deleted or updated in this table, it is only inserted.
>
> We run an 'UPDATE STATISTICS MEDIUM FOR TABLE xxx DISTRIBUTIONS ONLY'
> command
> followed by 'UPDATE STATISTICS HIGH FOR TABLE xxx (aaa, bbb, ccc, ddd)'
> command on this table at 02:00 on every Sunday. {where xxx = table name and
> aaa, bbb, ccc and ddd are indexed columns in the table.)
>
> When first implemented, this job completed before Monday morning, (when the
> system starts to be used much more intensively.) however this update stats
> job
> can now still be running on Tuesday, or even Wednesday morning. We assume
> that
> this is due to the steadily increasing size of this table.
> We have recently found that when the job nears completion, it starts to use
> too many resources on the server, (CPU and to some extent memory.) in turn
> this system loading can cause data inserts to be delayed by 20 minutes or
> more. This delay is unacceptable to our customer.
>
> Our main question is: How best can we shorten the length of time taken for
> this update statistics job to run, and/or prevent this update from
> impacting
> on insertion of data performance?
>
> Our current thinking is to Run the update statistics in LOW mode, followed
> by
> MEDIUM DISTRIBUTIONS ONLY. Not running HIGH at all.
>
> So the commands run, in order, will be:
>
> UPDATE STATISTICS LOW FOR TABLE xxx (aaa, bbb, ccc, ddd)
> UPDATE STATISTICS MEDIUM FOR TABLE xxx DISTRIBUTIONS ONLY
>
> Quoting from the Informix manual: 'LOW - updates systables, syscolumns and
> sysindexes catalog tables'
>
> 'MEDIUM - does a LOW and updates sysdistrib catalog table'
>
> 'DISTRIBUTIONS ONLY leaves existing index information in place'
>
> - I am assuming that 'leaves existing index information in place' means all
> the updates LOW has made in this context. So I get a 'LOW' statistic set
> plus
> a sampled sysdistrib statistic?
>
> We are not proposing to alter the RESOLUTION clause from its default
> setting.
>
> We think that this will complete more quickly, but will it slow the query
> response time on this table appreciably ?
> Are there other methods we should consider ?
> Do we need to update statistics at all on a table into which we only insert
> data ? Is one single run of update stats enough when the table is first
> created, or do we need to do this again on a weekly/monthly basis ?
>
> Thanks for any help,
>
> Regards,
>
> Howard Anderson
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--0015174bdcc8b72a55047181f917

Messages In This Thread

• Update stats impacts insert performance
  HOWARD ANDERSON -- Wednesday, 19 August 2009, at 1:19 p.m.
  • Re: Update stats impacts insert performance
    Art Kagel -- Wednesday, 19 August 2009, at 1:29 p.m.
    • Re: Update stats impacts insert performance
      HOWARD ANDERSON -- Friday, 21 August 2009, at 6:51 a.m.
      • Re: Update stats impacts insert performance
        Art Kagel -- Friday, 21 August 2009, at 11:21 a.m.
        • Re: Update stats impacts insert performance
          KAMRAN HAQ -- Monday, 24 August 2009, at 10:27 a.m.
          • Re: Update stats impacts insert performance
            Art Kagel -- Monday, 24 August 2009, at 10:35 a.m.
      • Re: Update stats impacts insert performance
        Art Kagel -- Friday, 21 August 2009, at 12:33 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.