I'm not totally sure when FabricPool data first appeared in the OCI 7.3.x DWH. I was under the impression it would need 7.3.11, but this is not so (the below works in 7.3.10). So did 7.3 Service Pack 9 enable collecting (and subsequent reporting) of object tiering data? I think so (essentially an ONTAP datasource patch). All I can say for sure is that you'll definitely be able to see 'objectStoreUsedSpace' in data acquired from operational OCI servers running 7.3.10 with SP9.
The below simple MySQL query will extract the NetApp Internal Volume Identifier, Snapshot Used by the Internal Volume, and objectStoreUsedSpace / internal volume space tiered to FabricPool, from the OCI DataWarehouse database.
SELECT
iv.identifier AS 'Internal Volume',
FLOOR(iv.snapshotUsedCapacityMB / 1024) AS 'Snapshot Used GB',
ed.fieldValue AS 'Tiered GB'
FROM dwh_inventory.extended_data AS ed
JOIN dwh_inventory.internal_volume AS iv ON iv.id = ed.objectid
WHERE objectType = 'INTERNAL_VOLUME'
AND fieldName = 'objectStoreUsedSpace';
The above is just to demonstrate how to get the 'objectStoreUsedSpace' data. The data comes from dwh_inventory.extended_data. In this situation we are only tiering snapshots to FabricPool, so just interested in getting 'Snapshot Used GB' from internal_volume, to compare with how many GB is tiered.
What Else is in dwh_inventory.extended_data?
Whilst I'm on the subject of dwh_inventory.extended_data, I might as well show what other fields are available (in 7.3.10 SP9).
The below comes from the output of -
SELECT DISTINCT objectType,fieldName FROM dwh_inventory.extended_data WHERE objectType != 'SWITCH';
- skipping SWITCH because it's just line cards.
objectType | fieldName
----------------+----------------------
STORAGE_NODE | managementIpAddresses
STORAGE_POOL | isCompactionSavingsEn
STORAGE_POOL | isEncrypted
VOLUME | isEncrypted
VOLUME | storageGroups
DISK | isEncrypted
INTERNAL_VOLUME | comment
INTERNAL_VOLUME | isEncrypted
INTERNAL_VOLUME | objectStoreTieringPol
INTERNAL_VOLUME | junctionPath
INTERNAL_VOLUME | qosLimitIOPS
INTERNAL_VOLUME | qosLimitRaw
INTERNAL_VOLUME | qosPolicy
INTERNAL_VOLUME | objectStoreUsedSpace
Extending this to Get Total Tiered Data for Aggregate
I was asked to see if I could get ActiveIQs FabricPool 'Total Tiered Data' from the OCI DWH data. You have to remember that it's always going to be a little bit out. ActiveIQ normally downloads on a Sunday, the dwh_inventory data is only as old as the last complete ETL. Also, important note is that the units in ActiveIQ are actually TB. Anyway, this below seemed to come to a reasonable accurate calculation.We take the sum of 'objectStoreUsedSpace' for all internal volumes on an aggregate and then multiply them by the compression factor (not totally sure why this works):
SELECT
sp.name
AS 'Aggregate',
(sp.totalAllocatedCapacityMB
/ (1024 * 1024)) AS 'Aggr Allocated TB',
(sp.dataUsedCapacityMB
/ (1024 * 1024)) AS 'Aggr Used TB',
spcf.dedupeRatio
AS 'Aggr Dedupe Ratio',
spcf.compressionRatio
AS 'Aggr Compression Ratio',
spcf.compactionRatio
AS 'Aggr Compaction Ratio',
SUM(ed.fieldValue)
/ 1024 AS 'Tiered TB',
SUM(ed.fieldValue)
* spcf.compressionRatio / 1024 AS 'Tiered TB * Compression'
FROM
dwh_inventory.extended_data AS ed
JOIN
dwh_inventory.internal_volume AS iv ON iv.id = ed.objectid
JOIN
dwh_inventory.storage_pool AS sp ON sp.id = iv.storagePoolId
JOIN
dwh_capacity.storage_pool_dimension AS spd ON spd.id = sp.id
JOIN
dwh_capacity.storage_and_storage_pool_capacity_fact AS spcf ON spd.tk =
spcf.storagePoolTk
JOIN
dwh_capacity.date_dimension AS dd ON dd.tk = spcf.dateTk
WHERE
ed.objectType = 'INTERNAL_VOLUME'
AND
ed.fieldName = 'objectStoreUsedSpace'
AND
dd.latest = 1
GROUP
BY sp.id;
Comments
Post a Comment