Close to 16.7 million pages and sweating ...

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

Neil: Let me explain a little more about what "pages used" really means. This can be explain a little better by saying it is the "largest page ever used". When you delete index or data rows the "pages used" will not be reduced, because the "highest" page ever used is still a the same. Now this does not mean you do not have many many free pages in your table. You just need to run a different command, oncheck -pT will produce a page utilization summary report. I do notremember if this grabs a lock on the table or not. The utilization report is also available in OAT, under storage and tables information pod (this will not lock anything). The only way the highest page ever used will go down is by running "repack + shrink". John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) From: "NEIL TRUBY" <neil.truby@ardenta.com> To: ids@iiug.org, Date: 10/12/2012 08:54 AM Subject: Close to 16.7 million pages and sweating ... [28502] Sent by: ids-bounces@iiug.org 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.

I'd like to see the full oncheck -pT report. I feel like when the "rebuilt" the foreign keys that were supported by legacy indexes, and the primary key, they did not drop the constraints and the indexes and build the indexes first independent of the constraints and placed in an explicit dbspace and then build the constraints afterwards. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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, Oct 12, 2012 at 11:53 AM, 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. > > --90e6ba614c800b832804cbdf1e7b

Sometimes, John, your postings are clear as glass. Other times .... ;-) Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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, Oct 12, 2012 at 12:17 PM, John Miller iii <miller3@us.ibm.com>wrote: > > Neil: > > Let me explain a little more about what "pages used" really means. This > can be > explain a little better by saying it is the "largest page ever used". > When you delete > index or data rows the "pages used" will not be reduced, because the > "highest" > page ever used is still a the same. Now this does not mean you do not have > many many free pages in your table. You just need to run a different > command, > oncheck -pT will produce a page utilization summary report. I do not> remember > if this grabs a lock on the table or not. The utilization report is also > available in > OAT, under storage and tables information pod (this will not lock > anything). > > > > The only way the highest page ever used will go down is by running > "repack + shrink". > > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > > > > From: "NEIL TRUBY" <neil.truby@ardenta.com> > To: ids@iiug.org, > Date: 10/12/2012 08:54 AM > Subject: Close to 16.7 million pages and sweating ... [28502] > Sent by: ids-bounces@iiug.org > > > > 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. > > > > *******************************************************************************

We have no facility to run an oncheck -pT. It would take forever, and it would lock out live users. :-(

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. >

If you increase the page size, then you'll reduce the number of pages well below the limit!