10.XC10 - Extent size modification

Jack Parker wrote: > A feature request I made shows up in this release of IDS. Going through the > documentation yesterday, the reasons for the feature are mentioned in > passing but not really explained. I thought I'd take the opportunity to do > so here. > > As you may (or may not) know (or care). A pet project of mine has been the > use of partitioning to age off old data. On a daily basis you create a new > partition/fragment for tomorrows data. When that data ages off, instead of > deleting it, you just drop the old partition/fragment. Works great and very > quickly. > > The issue comes in sizing tomorrows extent. Based on recent history, we > know about how much data it should house, and to ensure that the dbspace > does not get overly fragmented, we allocate size in multiples of some figure > so that we don't waste space with overly large extents, nor require too many > extra extents for that data. The problem is that the size of new partition > is controlled by FIRST EXTENT, not NEXT EXTENT. Historically you could not > change the first extent size. Which means that we could only work with NEXT > EXTENT size. So if we wanted 20MB for tomorrow, the partition would have a > 512K default starting size + 19.5MB next size. Even worse, if the data size > prediction shrank to 100K, we would still allocate 512K and then attempt to > set NEXT SIZE to -400K (which drops a shared memory dump and af file for > some strange reason). > > As of XC10, you can now modify the first extent size with ALTER TABLE foo > MODIFY EXTENT SIZE nnn. Which means we now have control over the size of > tomorrows data fragment. It does not change the size of existing extents, > just new partitions/fragments. > > cheers > j. > > Sane ego te vocavi. Forsitan capedictum tuum desit. > Thanks... for the feature and for the explanation. This has also some other implications: - in truncate table - In the table definition. Good for doing migrations. You can change it in the original system and obtain a clean dbschema. I believe Art's tools could take care of that currently, but it's always nice to have the features in the engine - in features to appear in the near future. Keep and open eye on it... If you're in the EVP you know what I'm talking about. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

On Mar 19, 8:13 am, Fernando Nunes <domusonl...@gmail.com> wrote: > Jack Parker wrote: > > A feature request I made shows up in this release of IDS.  Going through the > > documentation yesterday, the reasons for the feature are mentioned in > > passing but not really explained.  I thought I'd take the opportunity to do > > so here. > > > As you may (or may not) know (or care).  A pet project of mine has been the > > use of partitioning to age off old data.  On a daily basis you create a new > > partition/fragment for tomorrows data.  When that data ages off, instead of > > deleting it, you just drop the old partition/fragment.  Works great and very > > quickly. > > > The issue comes in sizing tomorrows extent.  Based on recent history, we > > know about how much data it should house, and to ensure that the dbspace > > does not get overly fragmented, we allocate size in multiples of some figure > > so that we don't waste space with overly large extents, nor require too many > > extra extents for that data.  The problem is that the size of new partition > > is controlled by FIRST EXTENT, not NEXT EXTENT.  Historically you could not > > change the first extent size.  Which means that we could only work with NEXT > > EXTENT size.  So if we wanted 20MB for tomorrow, the partition would have a > > 512K default starting size + 19.5MB next size.  Even worse, if the data size > > prediction shrank to 100K, we would still allocate 512K and then attempt to > > set NEXT SIZE to -400K (which drops a shared memory dump and af file for > > some strange reason). > > > As of XC10, you can now modify the first extent size with ALTER TABLE foo > > MODIFY EXTENT SIZE nnn. Which means we now have control over the size of > > tomorrows data fragment.  It does not change the size of existing extents, > > just new partitions/fragments. > > > cheers > > j. > > > Sane ego te vocavi. Forsitan capedictum tuum desit. > > Thanks... for the feature and for the explanation. > This has also some other implications: > > - in truncate table > - In the table definition. Good for doing migrations. You can change it in the > original system and obtain a clean dbschema. I believe Art's tools could take > care of that currently, but it's always nice to have the features in the engine > - in features to appear in the near future. Keep and open eye on it... If > you're in the EVP you know what I'm talking about. > > Regards. > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... Two features I asked for was trim/drop LAST extent and better control of extent size doubling. This is useful when you need to save the data in a partition that will never be written to again and you do not want to waste the space there - for example, you keep monthly partitions and your average daily use is around 1GB, so you define first extent of 25GB and next size 1GB. But different months carry different amounts of data, because of number of days and variability in data. So the system will allocate more extents (and larger ones, as it doubles the extent size), and the last extent will retain unused space, which might grow to be 4GB. This space currently cannot be returned to the system. Were these features also implemented in this release (or in 11.1 or 11.5)?

A feature request I made shows up in this release of IDS. Going through the documentation yesterday, the reasons for the feature are mentioned in passing but not really explained. I thought I'd take the opportunity to do so here. As you may (or may not) know (or care). A pet project of mine has been the use of partitioning to age off old data. On a daily basis you create a new partition/fragment for tomorrows data. When that data ages off, instead of deleting it, you just drop the old partition/fragment. Works great and very quickly. The issue comes in sizing tomorrows extent. Based on recent history, we know about how much data it should house, and to ensure that the dbspace does not get overly fragmented, we allocate size in multiples of some figure so that we don't waste space with overly large extents, nor require too many extra extents for that data. The problem is that the size of new partition is controlled by FIRST EXTENT, not NEXT EXTENT. Historically you could not change the first extent size. Which means that we could only work with NEXT EXTENT size. So if we wanted 20MB for tomorrow, the partition would have a 512K default starting size + 19.5MB next size. Even worse, if the data size prediction shrank to 100K, we would still allocate 512K and then attempt to set NEXT SIZE to -400K (which drops a shared memory dump and af file for some strange reason). As of XC10, you can now modify the first extent size with ALTER TABLE foo MODIFY EXTENT SIZE nnn. Which means we now have control over the size of tomorrows data fragment. It does not change the size of existing extents, just new partitions/fragments. cheers j. Sane ego te vocavi. Forsitan capedictum tuum desit.

Zachi wrote: > On Mar 19, 8:13 am, Fernando Nunes <domusonl...@gmail.com> wrote: >> Jack Parker wrote: >>> A feature request I made shows up in this release of IDS. Going through the >>> documentation yesterday, the reasons for the feature are mentioned in >>> passing but not really explained. I thought I'd take the opportunity to do >>> so here. >>> As you may (or may not) know (or care). A pet project of mine has been the >>> use of partitioning to age off old data. On a daily basis you create a new >>> partition/fragment for tomorrows data. When that data ages off, instead of >>> deleting it, you just drop the old partition/fragment. Works great and very >>> quickly. >>> The issue comes in sizing tomorrows extent. Based on recent history, we >>> know about how much data it should house, and to ensure that the dbspace >>> does not get overly fragmented, we allocate size in multiples of some figure >>> so that we don't waste space with overly large extents, nor require too many >>> extra extents for that data. The problem is that the size of new partition >>> is controlled by FIRST EXTENT, not NEXT EXTENT. Historically you could not >>> change the first extent size. Which means that we could only work with NEXT >>> EXTENT size. So if we wanted 20MB for tomorrow, the partition would have a >>> 512K default starting size + 19.5MB next size. Even worse, if the data size >>> prediction shrank to 100K, we would still allocate 512K and then attempt to >>> set NEXT SIZE to -400K (which drops a shared memory dump and af file for >>> some strange reason). >>> As of XC10, you can now modify the first extent size with ALTER TABLE foo >>> MODIFY EXTENT SIZE nnn. Which means we now have control over the size of >>> tomorrows data fragment. It does not change the size of existing extents, >>> just new partitions/fragments. >>> cheers >>> j. >>> Sane ego te vocavi. Forsitan capedictum tuum desit. >> Thanks... for the feature and for the explanation. >> This has also some other implications: >> >> - in truncate table >> - In the table definition. Good for doing migrations. You can change it in the >> original system and obtain a clean dbschema. I believe Art's tools could take >> care of that currently, but it's always nice to have the features in the engine >> - in features to appear in the near future. Keep and open eye on it... If >> you're in the EVP you know what I'm talking about. >> >> Regards. >> >> -- >> Fernando Nunes >> Portugal >> >> http://informix-technology.blogspot.com >> My email works... but I don't check it frequently... > > Two features I asked for was trim/drop LAST extent and better control > of extent size doubling. This is useful when you need to save the data > in a partition that will never be written to again and you do not want > to waste the space there - for example, you keep monthly partitions > and your average daily use is around 1GB, so you define first extent > of 25GB and next size 1GB. But different months carry different > amounts of data, because of number of days and variability in data. So > the system will allocate more extents (and larger ones, as it doubles > the extent size), and the last extent will retain unused space, which > might grow to be 4GB. This space currently cannot be returned to the > system. Were these features also implemented in this release (or in > 11.1 or 11.5)? The feature Jack wrote about was introduced in 10.00.FC10, 11.10.FC3 and 11.50.FC3. As far as I know nothing was changed in the extent doubling. Further features related to this may be delivered soon. Keep an open eye for next releases. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...