IDS Forum

Close to 16.7 million pages and sweating ...

Posted By: NEIL TRUBY
Date: Friday, 12 October 2012, at 11:53 a.m.

A colleague writes ...

11.50.FC8W2XG on Solaris 10

The database has an elderly table which is fast approaching 16.7m pages limit. It was due to be rebuilt next month, but at current growth it will only take another week of traffic or so.

The problem is, we already did the usual corrective actions - dropping legacy attached indexes (supporting two foreign keys) out of table’s partition into their own detached partitions.

The first time we did this, back in August, it worked as expected and gave the table another few months on lifetime, but the second time, yesterday, it did not work as expected and the growth has continued as if we had done nothing.

Below is the partial oncheck -pt output just before the maintenance window and one from today.

The two foreign keys we rebuilt did not have their system-generated indexes shown in their own partitions in ‘oncheck-pt’ before the index detach operation, whereas they do have them now (each one using around 650k pages, which is what we expected to see freed from table’s partition and reused for new data).

This is further confirmed by “Number of keys” figure which was 3 before, and now it’s 1 - the remaining key is the primary key which had also been built attached in table’s partition when the table was created years ago, but this one we cannot rebuild since there are too many FKs from child tables, whose rebuild would take 10+ hours if done the standard way.

Looking at the growth since yesterday just after the change and now:

Before the change

Number of keys 3
Number of pages allocated 16777215
Number of pages used 16351643
Number of data pages 12600143

Today

Number of keys 1
Number of pages allocated 16777215
Number of pages used 16387718
Number of data pages 12632207

it can be seen the difference between pages used between today and yesterday is roughly the same as the one between the data pages, 30k+ pages roughly.
This suggests that most of the new growth is coming from the new data pages being inserted, with the remaining few thousand pages a day coming from the remaining primary key (which is fine, this is what we based our table lifetime estimates on).

From all of the above, our only conclusion is that the space which we expected to be freed up (around 1.3m pages for the two rebuilt FKs combined) is not being used for the new data/PK pages at all, but instead the new ones are eating away of what little space has left.

With 1.3m pages available and 30k+ pages daily growth the table would have another month or so of extra life, enough to make it till scheduled downtime and table rebuild/fragmentation, but in the current situation this is down to 9 days, depending on the traffic.

We did full update stats (dostats) on the table, hoping that index pages previously allocated to attached system indexes supporting the two FKs, now marked as deleted will be cleaned up and reused, with no luck.
We are thinking about the table repack too, but we're not sure if it’s going to have any positive impact.

thx
Neil

PS PMR 90458 001 866 refers

Messages In This Thread

• Close to 16.7 million pages and sweating ...
  NEIL TRUBY -- Friday, 12 October 2012, at 11:53 a.m.
  • Re: Close to 16.7 million pages and sweating ...
    John Miller iii -- Friday, 12 October 2012, at 12:17 p.m.
    • Re: Close to 16.7 million pages and sweating ...
      Art Kagel -- Friday, 12 October 2012, at 12:25 p.m.
  • Re: Close to 16.7 million pages and sweating ...
    Art Kagel -- Friday, 12 October 2012, at 12:24 p.m.
    • Re: Close to 16.7 million pages and sweating ...
      NEIL TRUBY -- Friday, 12 October 2012, at 12:28 p.m.
  • Re: Close to 16.7 million pages and sweating ...
    Keith Simmons -- Friday, 12 October 2012, at 12:34 p.m.
  • Re: Close to 16.7 million pages and sweating ...
    FRANK J. COMPUTER -- Wednesday, 17 October 2012, at 12:21 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.