IDS Forum

Re: Close to 16.7 million pages and sweating ...

Posted By: Keith Simmons
Date: Friday, 12 October 2012, at 12:34 p.m.

In Response To: Close to 16.7 million pages and sweating ... (NEIL TRUBY)

Neil

Think out loud (not always a good idea, but here goes).
The database has allocated all the pages it can and the difference between
that and the number of pages used are pages that have never been used by
data or index.
The pages between the data page count and the number of pages used are
those that have been used previously by the attached indexes but are now
empty.
I would suggest that the engine is using all the 'never been used' pages first
(as these would be in large consecutive 'lumps') before it starts hunting up
and down the data to find smaller bits of free space.
Are the rows on the table quite small ?? I have a vague recollection (from IDS7
admittedly) that the number of data slots on a page is limited and do not get
reused (unless the page is completely freed). Don't know if that is the case
here but is something else to consider (worry about !!)

Keith

On 12 October 2012 16:53, NEIL TRUBY <neil.truby@ardenta.com> wrote:
> 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
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>

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.