Reclaim space after drop table in IQ_SYSTEM_MAIN

[william-paul_philibert]

Bonjour,

    I have been designated as volonteer for DBA job for Sybase IQ 15.4 ;o)

I have noticed that space usage was increasing in IQ_SYSTEM_MAIN while it should be faily stable and found out tables that should have been in IQ_MAIN instead.

I have moved the tables to the correct DBSPACE and then droped the tables from IQ_SYSTEM_MAIN but the space usage doesn't change.  It was at 84% used before the drop tables and remained at 84% after the drop tables.  Blk Types of the sp_iqdbspace output shows no X blocks.

Is there anything left to do to reclaim the space used by the droped tables?  Will the system fills these spots as space is required?

[markmumy · Accepted solution]

Send the output of sp_iqdbspace and sp_iqdbspaceinfo IQ_SYSTEM_MAIN.  This will show us was the sizes are and if there are any objects left.

Once you move objects the space should be freed immediately upon commit/checkpoint.

If the space isn't freed then either things were not moved or the space being consumed is part of the IQ system and not related to user data.

IQ_SYSTEM_MAIN should be 32-64gb in size at a minimum and roughly 1-4% of the total main store size.

Mark

[william-paul_philibert · Accepted solution]

Thanks for your time Mark.

Ok I tried to find a way to display it better but to no avail

DBSpaceName,DBSpaceType,Writable,Online,Usage,TotalSize,Reserve,NumFiles,NumRWFiles,Stripingon,StripeSize,BlkTypes,OkToDrop

'IQ_MAIN','MAIN','T','T','64','1.63T','1.17G',4,4,'T','1K','4H,139394321A','N'

'IQ_SYSTEM_MAIN','MAIN','T','T','84','46.6G','0B',1,1,'T','1K','1H,1221120F,32D,3879426O,128M,466B,32C','N'

'IQ_SYSTEM_TEMP','TEMPORARY','T','T','1','800G','593G',4,4,'T','1K','4H,5728F,84A,16I','N'

sp_iqdbspaceinfo returns nothing.

[william-paul_philibert · Accepted solution]

I must say, the system I am showing above is a replication of my prod environment.  When I do the sp_iqdbspaceinfo procedure on my prod environement I can see thet three tables I moved.  Here is the ouput of the prod environement :

dbspace_name,object_type,owner,object_name,object_id,id,columns,indexes,metadata,primary_key,unique_constraint,foreign_key,dbspace_online

'IQ_SYSTEM_MAIN','table','DBA','f_Dem_intervention_temp',29393,1207,'2.86G','0B','61.2M','0B','0B','0B','Y'

'IQ_SYSTEM_MAIN','table','DBA','f_usage2015',28860,1199,'6.22G','8.09G','50.8M','4.57G','0B','0B','Y'

'IQ_SYSTEM_MAIN','table','DBA','f_usage2016',31397,1216,'3.07G','4.12G','54.8M','2.38G','0B','0B','Y'

[markmumy · Accepted solution]

Based on the output you have just a 46GB (0B free) system main for a 1.63 TB main store.  Might be a little low, but that's not the main issue.

You have 1.2 million blocks used for the free list (1221120F).  This cannot be reduced unless you start dropping files from dbspaces.  Given the current space allocated, this is roughly 20-25% of your total system main.

Notice that it shows "3879426O".  That's 3.8 million blocks of "old versions".  Is this a multiplex?  If so, that space could be related to open versions on the other nodes.  Whether a simplex or multiplex, run two checkpoints on each node in the IQ system.  This usually clears up old versions.

Worst case, can you restart IQ?  That should clear everything out for you.

Did you run?  This could return no rows which means there are no tables in that space.  Or it will return a row per table and index.

     sp_iqdbspaceinfo IQ_SYSTEM_MAIN

Mark

[markmumy · Accepted solution]

What does sp_iqdbspace show in production?

Mark

[william-paul_philibert · Accepted solution]

This is the output of the sp_iqdbspace on my prod environment.

My systems are not multiplexed.

Note that IQ_SYSTEM_MAIN is now 88% when it was 84% a couple of month ago because of the tables in the wrong dbspace.

DBSpaceName,DBSpaceType,Writable,Online,Usage,TotalSize,Reserve,NumFiles,NumRWFiles,Stripingon,StripeSize,BlkTypes,OkToDrop

'IQ_MAIN','MAIN','T','T','63','1.63T','1.17G',4,4,'T','1K','4H,138304282A,781O','N'

'IQ_SYSTEM_MAIN','MAIN','T','T','88','46.6G','0B',1,1,'T','1K','1H,1221120F,32D,4130028A,18O,36X,128M,1136B','N'

'IQ_SYSTEM_TEMP','TEMPORARY','T','T','1','800G','593G',4,4,'T','1K','4H,5728F,80A,16X,16I','N'

Before I hit send on this reply I went and check the status of my replicated database, it seems time has made its effect, IQ_SYSTEM_MAIN is now down to 21% of Utilization...

DBSpaceName,DBSpaceType,Writable,Online,Usage,TotalSize,Reserve,NumFiles,NumRWFiles,Stripingon,StripeSize,BlkTypes,OkToDrop

'IQ_MAIN','MAIN','T','T','64','1.63T','1.17G',4,4,'T','1K','4H,139394321A','N'

'IQ_SYSTEM_MAIN','MAIN','T','T','21','46.6G','0B',1,1,'T','1K','1H,1221120F,32D,128M,466B','N'

'IQ_SYSTEM_TEMP','TEMPORARY','T','T','1','800G','593G',4,4,'T','1K','4H,5728F,32A,16I','N'

So, looks like space has been freed but only after a short while.

[markmumy · Accepted solution]

How long did it take to clear for you?

By default, IQ always reserves 20% of IQ_SYSTEM_MAIN.  We do this for internal stuff like the TLV and free list.  If there are no objects in the main store and it is above 20% in use, that usually tells me that it is undersized.  Now, 21% is just over the limit so I wouldn't be too concerned.

On your production system look for blocks that are labeled as A or O (active versions and old versions).  A tells you that there is data in that dbpsace.  O tells you that the data was moved/removed but that someone or something was holding it open.  You could look at sp_iqtransaction, sp_iqversionuse, etc to see who is holding open that data.  If no one is holding versions, it should free up immediately.

Mark

[william-paul_philibert · Accepted solution]

I can't be sure but 3 hours pass between the drop tables and the last time I check space on my IQ database and find out the 21% which is right before I sent the reply so, bottom line, somewhere between 2 and 3 hours.

I will keep in mind the sp_iqtransaction and sp_iqversionuse for whe I do the modification on the prod environment.

I thank you verry much for your time and expertise, it is appreciated.

William