Re: Strange behaviour deleteing blobs

Jonathan Leffler wrote: > ifxuser@gmail.com wrote: > > IDS 9.40FC5 HP-UX11.11 > > > > We've got a table with a byte field stored all in a single dbspaces, I > > mean no blobspace. oncheck -pt shows all allocated pages as used. When > > we delete the old rows, we should expect the number of used pages > > decrease, but that doesn't happen. > > New rows are been inserted and no new extents are been claimed, so I > > must assume that Informix is managing it somehow. > > I've read in an old post that after a level 0 copy it will show > > correctly, but it was talking about blobspaces, wich is not my case, > > and anyhow it didn't work for me. > > Probably an alter table ... init, or a unload/load will work, but I > > need to do that in a downtime and for the moment I prefer not. > > > > Any clues on what's happening? > > This is standard; once IDS allocates tablespace space to a table, it > doesn't release it on the grounds that if the table used to need it, it > probably will again -- and you said your blobs were stored in the table. I already know this. What disturbs me is the fact that all allocated pages are shown as used, although I know they aren't. > To actually release the space, you'd have to do something more > dramatic, such as causing the table to be rebuilt. Since you're on > 9.40, you can't use TRUNCATE TABLE, so you could consider using ALTER > INDEX pk_table TO CLUSTER - which will rebuild the table in a new > partition and thereby release the space (assuming it is not fragmented; > the verbiage changes but the concept doesn't if it is fragmented). It isn't fragmented. It has allocated 26GB. As it is getting near the 32GB per dbspace limit, what I want to have, is a real measure of how much of those 26GB are used before going for fragmentation. > You > might need to do ALTER INDEX pk_table TO NOT CLUSTER first - that is a > very cheap operation. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

You could look at sysmaster:systabinfo.ti_npdata and compare it to ti_npused. ti_npdata will show how much actual data is in the pages used (get ti_partnum from sysmaster:systabnames). I like to use the dustbin (trashcan) analogy: you start off with 1 dustbin. When you've filled it, you either have to throw the rubbish away, or get another dustbin to hold the extra rubbish (repeat as necessary). When you finally get rid of the rubbish and the dustbins are empty, you still have two (or more) dustbins still taking up all that space in your yard. Mapping extents --> dustbins and data --> rubbish of course ;-) A kinder example may be bookcases and books. But where I work, the first applies. Malc Don't ask me about management strategy re. deciding whether to upgrade or deciding not to relicense the existing obsolete and unsupported installation due to the code testing costs involved, because "we haven't placed a support call in 5 years". No seriously. ifxuser@gmail.com wrote: > Jonathan Leffler wrote: > > ifxuser@gmail.com wrote: > > > IDS 9.40FC5 HP-UX11.11 > > > > > > We've got a table with a byte field stored all in a single dbspaces, I > > > mean no blobspace. oncheck -pt shows all allocated pages as used. When > > > we delete the old rows, we should expect the number of used pages > > > decrease, but that doesn't happen. > > > New rows are been inserted and no new extents are been claimed, so I > > > must assume that Informix is managing it somehow. > > > I've read in an old post that after a level 0 copy it will show > > > correctly, but it was talking about blobspaces, wich is not my case, > > > and anyhow it didn't work for me. > > > Probably an alter table ... init, or a unload/load will work, but I > > > need to do that in a downtime and for the moment I prefer not. > > > > > > Any clues on what's happening? > > > > This is standard; once IDS allocates tablespace space to a table, it > > doesn't release it on the grounds that if the table used to need it, it > > probably will again -- and you said your blobs were stored in the table. > > I already know this. What disturbs me is the fact that all allocated > pages are shown as used, although I know they aren't. > > > To actually release the space, you'd have to do something more > > dramatic, such as causing the table to be rebuilt. Since you're on > > 9.40, you can't use TRUNCATE TABLE, so you could consider using ALTER > > INDEX pk_table TO CLUSTER - which will rebuild the table in a new > > partition and thereby release the space (assuming it is not fragmented; > > the verbiage changes but the concept doesn't if it is fragmented). > > It isn't fragmented. It has allocated 26GB. As it is getting near the > 32GB per dbspace limit, > what I want to have, is a real measure of how much of those 26GB are > used before going for fragmentation. > > > You > > might need to do ALTER INDEX pk_table TO NOT CLUSTER first - that is a > > very cheap operation. > > > > -- > > Jonathan Leffler #include <disclaimer.h> > > Email: jleffler@earthlink.net, jleffler@us.ibm.com > > Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Thanks Malc, I liked your analogy with trashbins :-) Unfortunatelly systabinfo/sysptnhdr is where oncheck takes it numbers from. Though, ti_nptotal = ti_npused. ti_npdata is much smaller, but that is because it doesn't account the blob usage. On Jan 8, 4:16 pm, mal...@btinternet.com wrote: > You could look at sysmaster:systabinfo.ti_npdata and compare it to > ti_npused. ti_npdata will show how much actual data is in the pages > used (get ti_partnum from sysmaster:systabnames). > > I like to use the dustbin (trashcan) analogy: > you start off with 1 dustbin. When you've filled it, you either have to > throw the rubbish away, or get another dustbin to hold the extra > rubbish (repeat as necessary). When you finally get rid of the rubbish > and the dustbins are empty, you still have two (or more) dustbins still > taking up all that space in your yard. > Mapping extents --> dustbins and data --> rubbish of course ;-) > > A kinder example may be bookcases and books. But where I work, the > first applies. > > Malc > > Don't ask me about management strategy re. deciding whether to upgrade > or deciding not to relicense the existing obsolete and unsupported > installation due to the code testing costs involved, because "we > haven't placed a support call in 5 years". No seriously. > > ifxu...@gmail.com wrote: > > Jonathan Leffler wrote: > > > ifxu...@gmail.com wrote: > > > > IDS 9.40FC5 HP-UX11.11 > > > > > We've got a table with a byte field stored all in a single dbspaces, I > > > > mean no blobspace. oncheck -pt shows all allocated pages as used. When > > > > we delete the old rows, we should expect the number of used pages > > > > decrease, but that doesn't happen. > > > > New rows are been inserted and no new extents are been claimed, so I > > > > must assume that Informix is managing it somehow. > > > > I've read in an old post that after a level 0 copy it will show > > > > correctly, but it was talking about blobspaces, wich is not my case, > > > > and anyhow it didn't work for me. > > > > Probably an alter table ... init, or a unload/load will work, but I > > > > need to do that in a downtime and for the moment I prefer not. > > > > > Any clues on what's happening? > > > > This is standard; once IDS allocates tablespace space to a table, it > > > doesn't release it on the grounds that if the table used to need it, it > > > probably will again -- and you said your blobs were stored in the table. > > > I already know this. What disturbs me is the fact that all allocated > > pages are shown as used, although I know they aren't. > > > > To actually release the space, you'd have to do something more > > > dramatic, such as causing the table to be rebuilt. Since you're on > > > 9.40, you can't use TRUNCATE TABLE, so you could consider using ALTER > > > INDEX pk_table TO CLUSTER - which will rebuild the table in a new > > > partition and thereby release the space (assuming it is not fragmented; > > > the verbiage changes but the concept doesn't if it is fragmented). > > > It isn't fragmented. It has allocated 26GB. As it is getting near the > > 32GB per dbspace limit, > > what I want to have, is a real measure of how much of those 26GB are > > used before going for fragmentation. > > > > You > > > might need to do ALTER INDEX pk_table TO NOT CLUSTER first - that is a > > > very cheap operation. > > > > -- > > > Jonathan Leffler #include <disclaimer.h> > > > Email: jleff...@earthlink.net, jleff...@us.ibm.com > > > Guardian of DBD::Informix v2005.02 --http://dbi.perl.org/

ifxuser@gmail.com wrote: > Jonathan Leffler wrote: > > ifxuser@gmail.com wrote: > > > IDS 9.40FC5 HP-UX11.11 > > > > > > We've got a table with a byte field stored all in a single dbspaces, I > > > mean no blobspace. oncheck -pt shows all allocated pages as used. When > > > we delete the old rows, we should expect the number of used pages > > > decrease, but that doesn't happen. > > > New rows are been inserted and no new extents are been claimed, so I > > > must assume that Informix is managing it somehow. > > > I've read in an old post that after a level 0 copy it will show > > > correctly, but it was talking about blobspaces, wich is not my case, > > > and anyhow it didn't work for me. > > > Probably an alter table ... init, or a unload/load will work, but I > > > need to do that in a downtime and for the moment I prefer not. > > > > > > Any clues on what's happening? > > > > This is standard; once IDS allocates tablespace space to a table, it > > doesn't release it on the grounds that if the table used to need it, it > > probably will again -- and you said your blobs were stored in the table. > > I already know this. What disturbs me is the fact that all allocated > pages are shown as used, although I know they aren't. The pages are still allocated to the table. They are in use by the table, even if they aren't holding any data at the moment. AFAIK, it is the same as if the table once held a billion rows and you then deleted 900 million of them; the space that was in use for the 900 million rows still shows up as in use by the table. > > To actually release the space, you'd have to do something more > > dramatic, such as causing the table to be rebuilt. Since you're on > > 9.40, you can't use TRUNCATE TABLE, so you could consider using ALTER > > INDEX pk_table TO CLUSTER - which will rebuild the table in a new > > partition and thereby release the space (assuming it is not fragmented; > > the verbiage changes but the concept doesn't if it is fragmented). > > It isn't fragmented. It has allocated 26GB. As it is getting near the > 32GB per dbspace limit, > what I want to have, is a real measure of how much of those 26GB are > used before going for fragmentation. Look at some of the other answers you've been given. You can consider doing some calculations based on (average) row size, the index sizes if the indexes are still attached, and the blob data that is still in use. Even a fairly crude calculation will probably give you a reasonable answer (eg 20 GB to spare -- no problem; or 6 GB to spare -- not an immediate problem; or we ran out of space 4 GB ago -- time to refine the calculation).

On Jan 9, 9:15 am, "Jonathan Leffler" <jonathan.leff...@gmail.com> wrote: > ifxu...@gmail.com wrote: > > Jonathan Leffler wrote: > > > ifxu...@gmail.com wrote: > > > > IDS 9.40FC5 HP-UX11.11 > > > > > We've got a table with a byte field stored all in a single dbspaces, I > > > > mean no blobspace. oncheck -pt shows all allocated pages as used. When > > > > we delete the old rows, we should expect the number of used pages > > > > decrease, but that doesn't happen. > > > > New rows are been inserted and no new extents are been claimed, so I > > > > must assume that Informix is managing it somehow. > > > > I've read in an old post that after a level 0 copy it will show > > > > correctly, but it was talking about blobspaces, wich is not my case, > > > > and anyhow it didn't work for me. > > > > Probably an alter table ... init, or a unload/load will work, but I > > > > need to do that in a downtime and for the moment I prefer not. > > > > > Any clues on what's happening? > > > > This is standard; once IDS allocates tablespace space to a table, it > > > doesn't release it on the grounds that if the table used to need it, it > > > probably will again -- and you said your blobs were stored in the table. > > > I already know this. What disturbs me is the fact that all allocated > > pages are shown as used, although I know they aren't.The pages are still allocated to the table. They are in use by the > table, even if they aren't holding any data at the moment. AFAIK, it > is the same as if the table once held a billion rows and you then > deleted 900 million of them; the space that was in use for the 900 > million rows still shows up as in use by the table. > > > > To actually release the space, you'd have to do something more > > > dramatic, such as causing the table to be rebuilt. Since you're on > > > 9.40, you can't use TRUNCATE TABLE, so you could consider using ALTER > > > INDEX pk_table TO CLUSTER - which will rebuild the table in a new > > > partition and thereby release the space (assuming it is not fragmented; > > > the verbiage changes but the concept doesn't if it is fragmented). > > > It isn't fragmented. It has allocated 26GB. As it is getting near the > > 32GB per dbspace limit, > > what I want to have, is a real measure of how much of those 26GB are > > used before going for fragmentation.Look at some of the other answers you've been given. You can consider > doing some calculations based on (average) row size, the index sizes if > the indexes are still attached, and the blob data that is still in use. > Even a fairly crude calculation will probably give you a reasonable > answer (eg 20 GB to spare -- no problem; or 6 GB to spare -- not an > immediate problem; or we ran out of space 4 GB ago -- time to refine > the calculation). Thank you all, What I've actually done is get an unload of part of the table and load it on a test system. Now I've got a rough calculation on the average row size. Indexes are no problem, as they dettached.