IDS Forum

Update stats impacts insert performance

Posted By: HOWARD ANDERSON
Date: Wednesday, 19 August 2009, at 1:19 p.m.

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

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.