Fragmented table, best way to free pages in DBspace
mohita...@gmail.com
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 ALTER FRAGMENT ON TABLE T INIT FRAGMENT BY
ROUND ROBIN clause. So my question is:
1. If we don't run alter fragment would 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 alter fragment after
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 alter fragment b) For 40+ M rows it's going to be
awfully long to do alter fragment.
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
---------
Murray Wood
1. Yes, the database has free pages which it will use for later
inserts.
2. If you needed this space for the table once you will probably
need it again so do not do anything. If you are adding rows I do not
understand why you would want to reclaim the space which the table is
just going to use again.
3. It depends upon how fast your server and disk are and how busy
they are at the time.
Once correctly sized we never touch tables again. It is the number of
extents that you have not shown that is more important.
MW
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Jack Parker
Fragment the tables by expression, according to your timeframe and 'delete'
schedule. When it comes time delete old data, detach the fragment (and then
drop it) and add a new fragment for the next period. With V10, this can be
done with partitions in the same dbspace.
1 - the 'delete' takes seconds.
2 - the space you free up is in fact freed up as contiguous space that can
be re-allocated to wherever you choose (typically back to the table).
3 - you leave no half empty pages in the table that may or may not be reused
over time.
Yes, #3 is really just an addendum to #2.
j.
-----Original Message-----
From: informix-l...@iiug.org
[mailto:informix-l...@iiug.org]On Behalf Of
mohita...@gmail.com
Sent: Wednesday, July 18, 2007 9:24 PM
To: inform...@iiug.org
Subject: Fragmented table, best way to free pages in DBspace
_______________________________________________
Art S. Kagel
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 or fragment.
Otherwise, just: insert, delete, insert, delete......
Art S. Kagel
Jack Parker
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 alter fragment detach 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.
Art S. Kagel
_______________________________________________
Art S. Kagel
>
> [mailto:informix-list-boun...@iiug.org]On Behalf Of Art S. Kagel
> Sent: Thursday, July 19, 2007 7:50 PM
Jack, he can't use fragment DETACH, he's using ROUND ROBIN
fragmentation, not fragment by 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
Jack Parker
this.
j.
Art S. Kagel
_______________________________________________
mohita...@gmail.com
> 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
> > 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.
>
> > > 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
> > > 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......
>
> 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
>
> - 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
Art S. Kagel
>
> > - 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 within allocated extents by object
(table or index) and dbspace (ie fragment). If you want lower level
detail remove the aggregations and the group by clause.
Art S. Kagel