Free pages count does not increase after deleting
Hello all,
We have a 16k/page dbspace (with 1 chunk of the same size) which stores blob
data on Informix 11.5 (Linux x64).
I have been watching the blob data table grow in steps slowly as each next
extent is created with the SQL at the bottom of this email (the table contains
about 1 millions rows).
It got to over 90% full and so I have purged about 20,000 rows so far (~5GB).
However the free page count has not increased!
Checkpoints have run, I have even updated the table statistics, and I have
left it for a day to see if sysmaster is just slow at updating. However the
freepages count is still reporting the same count as before.
Is there something I have to do to force the extents which are now empty again
to be marked as free and thus increase the freepages count.
select d.name dbspace,
d.dbsnum,
c.chknum chunknum,
c.chksize pages, c.nfree FreePages,
trunc((((c.chksize - c.nfree)/c.chksize)*100), 2) PercentF
from sysmaster:sysdbspaces d,
sysmaster:syschunks c
where d.dbsnum = c.dbsnum
into temp dbs_chunks;
select dbspace, dbsnum,
count(*) nchunks,
sum(pages) tot_pages,
sum(freepages) free_pages,
round((((sum(pages) - sum(freepages))/sum(pages)) * 100), 2)
pcnt_full
from dbs_chunks
group by dbsnum, dbspace
order by dbsnum, dbspace;
drop table dbs_chunks;
Thanks in advance.
Andy
Blobspace pages are not released for reuse after a delete until you perform
an archive because blobspace pages are not logged in the logical log. Take
an archive and all will be well.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Thu, Apr 30, 2009 at 12:51 PM, ANDREW LEMIN <a_lemin@hotmail.com> wrote:
> Hello all,
> We have a 16k/page dbspace (with 1 chunk of the same size) which stores
> blob
> data on Informix 11.5 (Linux x64).
>
> I have been watching the blob data table grow in steps slowly as each next
> extent is created with the SQL at the bottom of this email (the table
> contains
> about 1 millions rows).
>
> It got to over 90% full and so I have purged about 20,000 rows so far
> (~5GB).
> However the free page count has not increased!
>
> Checkpoints have run, I have even updated the table statistics, and I have
> left it for a day to see if sysmaster is just slow at updating. However the
> freepages count is still reporting the same count as before.
>
> Is there something I have to do to force the extents which are now empty
> again
> to be marked as free and thus increase the freepages count.
>
> select d.name dbspace,
>
> d.dbsnum,
>
> c.chknum chunknum,
>
> c.chksize pages, c.nfree FreePages,
>
> trunc((((c.chksize - c.nfree)/c.chksize)*100), 2) PercentF
> from sysmaster:sysdbspaces d,
>
> sysmaster:syschunks c
> where d.dbsnum = c.dbsnum
> into temp dbs_chunks;
> select dbspace, dbsnum,>
> count(*) nchunks,
>
> sum(pages) tot_pages,
>
> sum(freepages) free_pages,
>
> round((((sum(pages) - sum(freepages))/sum(pages)) * 100), 2)
>
> pcnt_full
> from dbs_chunks
> group by dbsnum, dbspace
> order by dbsnum, dbspace;
> drop table dbs_chunks;>
> Thanks in advance.
> Andy
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5a5376e4d450468c93024
Hello,
Thank you for your fast response.
You were right, after an 'ontape -s -L 0' to /dev/null, the free pages count
increased.
However, I have been purging old data whilst new data is still being inserted.
As such am I right in thinking the cleared pages will only be marked as free
when the entire extent is free. I.e. I have to wait for an entire extent to be
cleared before any pages will be freed?
If this is true, my first extent is quite large (many times larger than the
additional extents), this first extent will never be empty as new data is
constantly being inserted.
Is it a good idea for blobspace performance and monitoring free space to have
the initial extent be fairly small and be equal to the size of additional
extents?
In short we need to improve blobspace performance as well as be able to
monitor free space.
Thanks in advance.
Andy.
No. Pages are freed individually for reuse even in blob spaces.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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, May 1, 2009 at 10:31 AM, ANDREW LEMIN <a_lemin@hotmail.com> wrote:
> Hello,
> Thank you for your fast response.
> You were right, after an 'ontape -s -L 0' to /dev/null, the free pages
> count
> increased.
>
> However, I have been purging old data whilst new data is still being
> inserted.
>
> As such am I right in thinking the cleared pages will only be marked as
> free
> when the entire extent is free. I.e. I have to wait for an entire extent to
> be
> cleared before any pages will be freed?
>
> If this is true, my first extent is quite large (many times larger than the
> additional extents), this first extent will never be empty as new data is
> constantly being inserted.
>
> Is it a good idea for blobspace performance and monitoring free space to
> have
> the initial extent be fairly small and be equal to the size of additional
> extents?
>
> In short we need to improve blobspace performance as well as be able to
> monitor free space.
>
> Thanks in advance.
> Andy.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e6d27ed1f9743a0468dd0ee2