Truncated table still occupied many pages
Jacob truncated a huge table with TRUNCATE ... DROP STORAGE; used pages dropped but total pages (ti_nptotl) stayed at over a million, and an ALTER INDEX TO CLUSTER even doubled index pages. Art suggested checking sysextents, which showed the table space had actually been released (with a delay) down to the newly set extent size, but index partitions were still huge. Doug recommended sysadmin:task('table repack shrink',...); that failed on 11.50.FC3 (John Miller noted it arrived in FC4), so Doug's fallback was ALTER FRAGMENT ON INDEX ... INIT IN dbspace for each index, which locks the table.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Greeting, Folks. Today my client asked me to truncate a couple of tables on a heap of servers. In one case it was quite bad - 59 million rows. No problem, i used the command: truncate table <whatever> drop storage; Using my fragments.sh script (or just examining columns (ti_npused, ti_nptotl) in sysmaster:systabinfo) I can see that npused has indeed gone down to 1 or 2 on the data and index partitions. But the nptotl remains stubbornly the same, at over 1 million pages. I was *sure* that the truncate command would release it back to the free pages list! On the development server, I tried alter index to cluster on one of the now empty tables. The nptotal for the table stayed where it was while the nptotl on the index partition doubled! Now that looks just plain kooky! BTW, before I truncated the table I did: alter table <whatever> extent size 10000 next size 5000; Obviously, this made no difference. So what can I do to free up all that space? As I type this it occurs to me to try ALTER FRAGMENT INIT IN another dbspace, then do it again to get it back in its original place. I'm hoping someone here will come up with something more clever than that. Any takers here? :-) Thanks. -- Jacob S.
Hi Salomon. Try this:
EXECUTE FUNCTION sysadmin:task('table repack shrink', 'table', 'database');-- replace last two parameters with actual values
What does a search of sysextents show? Are there really any extents
assigned to the table or the index?
select dbsname, tabname, sum(size), count(*)
from sysextents
where dbsname = 'somedatabase'
and tabname in ('sometable', 'someindexname')
group by 1, 2
order by 1, 2;
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. 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, Mar 20, 2014 at 11:41 PM, JACOB SALOMON <jakesalomon@yahoo.com>wrote:
> Greeting, Folks.
>
> Today my client asked me to truncate a couple of tables on a heap of
> servers.
> In one case it was quite bad - 59 million rows. No problem, i used the
> command:
>
> truncate table <whatever> drop storage;
>
> Using my fragments.sh script (or just examining columns (ti_npused,
> ti_nptotl)
> in sysmaster:systabinfo) I can see that npused has indeed gone down to 1
> or 2
> on the data and index partitions. But the nptotl remains stubbornly the
> same,
> at over 1 million pages. I was *sure* that the truncate command would
> release
> it back to the free pages list!
>
> On the development server, I tried alter index to cluster on one of the now
> empty tables. The nptotal for the table stayed where it was while the
> nptotl
> on the index partition doubled! Now that looks just plain kooky!
>
> BTW, before I truncated the table I did:
> alter table <whatever> extent size 10000 next size 5000;
>
> Obviously, this made no difference. So what can I do to free up all that
> space?
>
> As I type this it occurs to me to try ALTER FRAGMENT INIT IN another
> dbspace,
> then do it again to get it back in its original place. I'm hoping someone
> here
> will come up with something more clever than that.
>
> Any takers here? :-)
>
> Thanks.
>
> -- Jacob S.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3f020038f3404f51c33bd
Sorry, I mean "Hi Jacob" - I haven't forgotten who you are!
> Hi Salomon. Try this:
>
> EXECUTE FUNCTION sysadmin:task('table repack shrink', 'table', 'database');> -- replace last two parameters with actual values
Thanks for the observation, Art. I ran your query the morning after I posted my question and it appears that the space reduction was a delayed action. The first and only extent of the truncated table partition is down to what I set it for in the "alter table extent size" command. However, the index partitions are still huge! This makes me want to look into Doug's suggestion. But doesn't that also do compression? I remember attending a seminar on the new features of 11.5 and I think this proc is the one that compresses. Need to look it up. HMMmm Which manual has that information?... Oh well, that's what the AcroRead search function is for. (Especially important, since a typo in my script set the extent size to 500,000 instead of 50,000! ) Thanks much, gentlemen! I'll let y'all know what happened. -- Jacob S.
Repack and shrink were deployed with compression to release space created by compressing a table but is a separate function. Art Art S. Kagel Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG or any other organization with which I sm associated either explicitely or implicitely not on individuals affiliated with those organizations. On Mar 21, 2014 10:22 AM, "JACOB SALOMON" <jakesalomon@yahoo.com> wrote: > Thanks for the observation, Art. > > I ran your query the morning after I posted my question and it appears that > the space reduction was a delayed action. The first and only extent of the > truncated table partition is down to what I set it for in the "alter table > extent size" command. > > However, the index partitions are still huge! This makes me want to look > into > Doug's suggestion. But doesn't that also do compression? I remember > attending > a seminar on the new features of 11.5 and I think this proc is the one that > compresses. > > Need to look it up. HMMmm Which manual has that information?... Oh well, > that's what the AcroRead search function is for. (Especially important, > since > a typo in my script set the extent size to 500,000 instead of 50,000! ) > > Thanks much, gentlemen! I'll let y'all know what happened. > > -- Jacob S. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0117720135dd3d04f51ec741
Doug suggested:
>EXECUTE FUNCTION sysadmin:task('table repack shrink', 'table', 'database');>-- replace last two parameters with actual values
I tried it:
execute function sysadmin:task('table repack shrink', '<table name>','<dbname');
(protecting client's info)
and got back:
(expression) Unknown command (table repack shrink).
Same for 'table repack' w/o the shrink. And adding the owner name made no
difference. (It shouldn't but sometimes an error message is wrong too so I was
clutching at a straw.)
This is IDS 11.50.FC3 on Solaris 10. It SHOULD work already! :-(
As I recall, the repack option for indexes was not yet ready in 11.5.
Still, it seemed to be the best idea..
Thanks Doug, but it didn't help here.
-- Jacob
These features were introduced with 11.50.FC4
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/21/2014 08:31:11 AM:
> From: "JACOB SALOMON" <jakesalomon@yahoo.com>
> To: ids@iiug.org,
> Date: 03/21/2014 08:31 AM
> Subject: Re: Truncated table still occupied many pages [32773]
> Sent by: ids-bounces@iiug.org
>
> Doug suggested:
> >EXECUTE FUNCTION sysadmin:task('table repack shrink', 'table','database');
> >-- replace last two parameters with actual values
>
> I tried it:
> execute function sysadmin:task('table repack shrink', '<table name>',> '<dbname');
> (protecting client's info)
>
> and got back:
> (expression) Unknown command (table repack shrink).
>
> Same for 'table repack' w/o the shrink. And adding the owner name made no
> difference. (It shouldn't but sometimes an error message is wrong
> too so I was
> clutching at a straw.)
>
> This is IDS 11.50.FC3 on Solaris 10. It SHOULD work already! :-(
>
> As I recall, the repack option for indexes was not yet ready in 11.5.
>
> Still, it seemed to be the best idea..
>
> Thanks Doug, but it didn't help here.
>
> -- Jacob
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi Jacob.
In that case, you'll need to run the following for each index:
ALTER FRAGMENT ON INDEX index-name INIT IN dbspace-name;
This will lock the table, though.
Regards,
Doug