How to find occupancy of active files only in specified pool?

[mirrorsaw]

Hi all,
Q OCC will give me occupancy by pool for each node, but if I'm trying to find out the amount (either number of files, or total GB) of active files per node, for a given storage pool, is there a SELECT statement I can use for this?

Thanks

 

[marclant]

It would be a complex query because you'd need data from multiple tables.
You would need to get the list of nodes in that stgpool, that's easy:

select distinct node_name from occupancy where stgpool_name='POOL_NAME'

You would need the object_id from all the files that belongs to the nodes listed above:

select object_id from backups where state='ACTIVE_VERSION' and node_name in (select distinct node_name from occupancy where stgpool_name='POOL_NAME')

Then you need to sum the filesize from the contents table (assuming you are not using container pools).

select node_name,sum(file_size) as BYTES, count(file_size) as NUMBER_OF_FILES from contents where object_id in (select object_id from backups where state='ACTIVE_VERSION' and node_name in (select distinct node_name from occupancy where stgpool_name='POOL_NAME')) group by node_name

DISCLAIMER:
I have not tested this on a large server, I tested it on a small test box, the logic works, but I don't know how it will behave on a large server. Use at your own risk.

 

[mirrorsaw]

This is exactly what I needed- thankyou so much marclant

 

[mirrorsaw]

wait no I think I spoke to soon. I'm pretty sure that query gives me the total number of active files (and total) size from any nodes that have data in that stgpool.

But I need the number of active files per node *IN* that stgpool. for example if a node has half its active files in one pool, and half in another, your query will give me the total across both pools.

 

[marclant]

You are absolutely right.

You'd need a combination of the VOLUMES table to know which volumes belong to the storage pool
The BACKUPS table to have only active objects.
The CONTENTS table to get info only about the volumes that belong to that pool, and only the object_ids from BACKUPS that are active. And of course, also match the node.

It's getting to be above my pay grade.