Re: Fragmented table, best way to free pages in DBspace
On Jul 20, 1:10 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> True, I neglected to do more than imply that Round Robin would not work with
> this.
>
> j.
>
>
>
> -----Original Message-----
> From: informix-list-boun...@iiug.org
>
> [mailto:informix-list-boun...@iiug.org]On Behalf Of Art S. Kagel
> Sent: Friday, July 20, 2007 2:40 PM
> To: informix-l...@iiug.org
> Subject: Re: Fragmented table, best way to free pages in DBspace
>
> On Jul 19, 10:05 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> > I cannot agree with you on this one Art.
>
> > A 40 million row delete is a multi-hour process.
>
> > Granted, the space will be available for reuse quickly if the
> insert/delete
> > scheme is date based. If there is some difference in the two schemes,
> then
> > deleted row space may not necessarily free up, the page in question would
> > have to drop below half-deleted, then go through a compression, then the
> > space would be available again. But I digress.
>
> > Certainly an alterfragmentdetach is not going to be happy with others
> > holding even a shared lock on the table, we tried it with a lock mode
> wait,
> > until we discovered that there was a process that started, opened a cursor
> > to the table and then stayed there forever. We had to stop that process,
> do
> > the purge and then start it back up again.
>
> > Other things we ran into:
> > - prepared statements have to be re-prepared - the table layout has
> > changed.
> > - detach/drop table/attach will not be happy with FK/PK constraints.
> > - indexes (to detach cleanly) must have the same frqagmentation
> > strategy - i.e. date.
> > - ROWIDs cause an underlying index which does not have the date in it
> and
> > will take a long time to rebuild.
>
> > All of those are time sinks to work around or rebuild. Without those, a
> > detach/drop table/attach takes about 1 second on a rinky-dink machine.
> The
> > number of rows is immaterial to the length of time necessary to perform
> the
> > operation.
>
> > If our friend is a 24x7 shop and cannot go down momentarily occasionally,
> > then yes he is stuck with a delete scenario. He could speed that up with
> a
> > nibble strategy:
>
> > build a temp table of identifying keys (empty)
> > Stuff 10K keys to be deleted from the target table into the temp table
> > delete from target table where key in (select key from temp_table)> > Rinse and repeat until there are no more.
>
> > My .02
>
> > j.
>
> > -----Original Message-----
> > From: informix-list-boun...@iiug.org
>
> > [mailto:informix-list-boun...@iiug.org]On Behalf Of Art S. Kagel
> > Sent: Thursday, July 19, 2007 7:50 PM
> > To: informix-l...@iiug.org
> > Subject: Re: Fragmented table, best way to free pages in DBspace
>
> > On Jul 18, 9:24 pm, mohitanch...@gmail.com wrote:
> > > Because of the large size of the table we recently chose tofragment
> > > by round robin into 8 dbspaces. After inserting lot of rows we ran our
> > > delete process so that we free up the space. But it appears just
> > > delete alone is not freeing up the space/pages. I looked at oncheck -
> > > pT and pages are not being freed by delete alone. I read in the manual
> > > that the best way is to run ALTERFRAGMENTON TABLE T INITFRAGMENTBY
> > > ROUND ROBIN clause. So my question is:
>
> > > 1. If we don't run alterfragmentwould Informix know that these pages
> > > are available and will use them anyway ?
> > > 2. In real time scenario we could insert 40+ million rows which are of
> > > huge size. And as part of our process we delete them from the table
> > > after certain timeframe. Now if we have to run alterfragmentafter
> > > every delete then in production we are going to face a) downtime
> > > because I think we need to disconnect any connections to database
> > > before running alterfragmentb) For 40+ M rows it's going to be
> > > awfully long to do alterfragment.
> > > 3. How long does it take to execute this command.
>
> > > Is there any better strategy ? How can we make it better.
>
> > > Snippet from oncheck -pT
> > > ---------
> > > Pagesize (k) 8
> > > First extent size 1249999
> > > Next extent size 249999
> > > Number of pages allocated 10407781
> > > Number of pages used 10407781
> > > Number of data pages 55450
> > > Number of rows 3380349
> > > ---------
>
> > You're working at this too hard! IDS will reuse slots and pages from
> > deleted rows immediately for new data within the same table/fragment.
> > You would only have to release the enwly unused pages to the free pool
> > if you need to use that space for a DIFFERENT table orfragment.
> > Otherwise, just: insert, delete, insert, delete......
>
> Jack, he can't usefragmentDETACH, he's using ROUND ROBIN
> fragmentation, notfragmentby expression to isolate his older data.
> If her were, I'd agree. Meanwhile, there's no argument here. He is
> already deleting the rows he needs to delete (yes different
> fragmentation would make that easier) he just doesn't understand how
> IDS works and how it deals with deleted row space and whether or not
> it's reused. I'm confident that I answered the question. Aside, your
> implied suggestion to refragment by expression is a VERY good one, and
> I didn't look beyond the question at hand to see that it should be
> made.
>
> Art S. Kagel
>
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text -
>
> - Show quoted text -
How can I calculate free space in tablespaces in different dbspace for
a fragmented table because once extents are allocated those are never
reported as free in syschunks table. Is following query good enough:
select
name dbspace, -- dbspace name truncated to fit
f.chknum, -- chunk number
f.extnum, -- extent number of free space
f.start, -- starting address of free space
f.leng free_pages -- length of free space
from sysdbspaces d, syschunks c, syschfree f
where d.dbsnum = c.dbsnum
and c.chknum = f.chknum
order by dbspace, chknum
On Aug 6, 3:16 pm, mohitanch...@gmail.com wrote:
> On Jul 20, 1:10 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
>
>
>
> > True, I neglected to do more than imply that Round Robin would not work with
> > this.
>
> > j.
>
> > -----Original Message-----
> > From: informix-list-boun...@iiug.org
>
> > [mailto:informix-list-boun...@iiug.org]On Behalf Of Art S. Kagel
> > Sent: Friday, July 20, 2007 2:40 PM
> > To: informix-l...@iiug.org
> > Subject: Re: Fragmented table, best way to free pages in DBspace
>
> > On Jul 19, 10:05 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> > > I cannot agree with you on this one Art.
>
> > > A 40 million row delete is a multi-hour process.
>
> > > Granted, the space will be available for reuse quickly if the
> > insert/delete
> > > scheme is date based. If there is some difference in the two schemes,
> > then
> > > deleted row space may not necessarily free up, the page in question would
> > > have to drop below half-deleted, then go through a compression, then the
> > > space would be available again. But I digress.
>
> > > Certainly an alterfragmentdetach is not going to be happy with others
> > > holding even a shared lock on the table, we tried it with a lock mode
> > wait,
> > > until we discovered that there was a process that started, opened a cursor
> > > to the table and then stayed there forever. We had to stop that process,
> > do
> > > the purge and then start it back up again.
>
> > > Other things we ran into:
> > > - prepared statements have to be re-prepared - the table layout has
> > > changed.
> > > - detach/drop table/attach will not be happy with FK/PK constraints.
> > > - indexes (to detach cleanly) must have the same frqagmentation
> > > strategy - i.e. date.
> > > - ROWIDs cause an underlying index which does not have the date in it
> > and
> > > will take a long time to rebuild.
>
> > > All of those are time sinks to work around or rebuild. Without those, a
> > > detach/drop table/attach takes about 1 second on a rinky-dink machine.
> > The
> > > number of rows is immaterial to the length of time necessary to perform
> > the
> > > operation.
>
> > > If our friend is a 24x7 shop and cannot go down momentarily occasionally,
> > > then yes he is stuck with a delete scenario. He could speed that up with
> > a
> > > nibble strategy:
>
> > > build a temp table of identifying keys (empty)
> > > Stuff 10K keys to be deleted from the target table into the temp table
> > > delete from target table where key in (select key from temp_table)> > > Rinse and repeat until there are no more.
>
> > > My .02
>
> > > j.
>
> > > -----Original Message-----
> > > From: informix-list-boun...@iiug.org
>
> > > [mailto:informix-list-boun...@iiug.org]On Behalf Of Art S. Kagel
> > > Sent: Thursday, July 19, 2007 7:50 PM
> > > To: informix-l...@iiug.org
> > > Subject: Re: Fragmented table, best way to free pages in DBspace
>
> > > On Jul 18, 9:24 pm, mohitanch...@gmail.com wrote:
> > > > Because of the large size of the table we recently chose tofragment
> > > > by round robin into 8 dbspaces. After inserting lot of rows we ran our
> > > > delete process so that we free up the space. But it appears just
> > > > delete alone is not freeing up the space/pages. I looked at oncheck -
> > > > pT and pages are not being freed by delete alone. I read in the manual
> > > > that the best way is to run ALTERFRAGMENTON TABLE T INITFRAGMENTBY
> > > > ROUND ROBIN clause. So my question is:
>
> > > > 1. If we don't run alterfragmentwould Informix know that these pages
> > > > are available and will use them anyway ?
> > > > 2. In real time scenario we could insert 40+ million rows which are of
> > > > huge size. And as part of our process we delete them from the table
> > > > after certain timeframe. Now if we have to run alterfragmentafter
> > > > every delete then in production we are going to face a) downtime
> > > > because I think we need to disconnect any connections to database
> > > > before running alterfragmentb) For 40+ M rows it's going to be
> > > > awfully long to do alterfragment.
> > > > 3. How long does it take to execute this command.
>
> > > > Is there any better strategy ? How can we make it better.
>
> > > > Snippet from oncheck -pT
> > > > ---------
> > > > Pagesize (k) 8
> > > > First extent size 1249999
> > > > Next extent size 249999
> > > > Number of pages allocated 10407781
> > > > Number of pages used 10407781
> > > > Number of data pages 55450
> > > > Number of rows 3380349
> > > > ---------
>
> > > You're working at this too hard! IDS will reuse slots and pages from
> > > deleted rows immediately for new data within the same table/fragment.
> > > You would only have to release the enwly unused pages to the free pool
> > > if you need to use that space for a DIFFERENT table orfragment.
> > > Otherwise, just: insert, delete, insert, delete......
>
> > Jack, he can't usefragmentDETACH, he's using ROUND ROBIN
> > fragmentation, notfragmentby expression to isolate his older data.
> > If her were, I'd agree. Meanwhile, there's no argument here. He is
> > already deleting the rows he needs to delete (yes different
> > fragmentation would make that easier) he just doesn't understand how
> > IDS works and how it deals with deleted row space and whether or not
> > it's reused. I'm confident that I answered the question. Aside, your
> > implied suggestion to refragment by expression is a VERY good one, and
> > I didn't look beyond the question at hand to see that it should be
> > made.
>
> > Art S. Kagel
>
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list-Hide quoted text -
>
> > - Show quoted text -
>
> How can I calculate free space in tablespaces in different dbspace for
> a fragmented table because once extents are allocated those are never
> reported as free in syschunks table. Is following query good enough:
>
> select
> name dbspace, -- dbspace name truncated to fit
> f.chknum, -- chunk number
> f.extnum, -- extent number of free space
> f.start, -- starting address of free space
> f.leng free_pages -- length of free space
> from sysdbspaces d, syschunks c, syschfree f
> where d.dbsnum = c.dbsnum
> and c.chknum = f.chknum
> order by dbspace, chknum
No. That will report unused extents not assigned to any table. To
see the free space within the extents assigned to a table and its
fragments and its indexes and their fragments it's more like:
select dbsname, tabname, dbinfo( 'dbspace', sp.partnum ) as dbspace,sum( nptotal ), sum( nptotal - npused) as npfree
from systabnames st, sysptnhdr sp
where st.partnum = sp.lockid
and dbsname = 'mydatabase' and tabname = 'mytablename'
group by 1, 2, 3
order by 1, 2, 3;
This will report free space totals w