Diving into disk usage

Bryan_HBryan_H Vertica Employee Administrator

Would you like to know how much disk space Vertica is using as it runs? This could be useful for capacity planning, monitoring trends, or debugging. Here are some ways to follow disk usage trends and also look at temporary events like Tuple Mover and join spills:

-- daily free bytes summary. Update '/data' with your data storage path(s)
select time,node_name,free_bytes_start_value,free_bytes_min_value,free_bytes_max_value,free_bytes_end_value from dc_storage_info_by_day where path = '/data' order by 1,2 limit 10;
-- total bytes spilled by query (add/edit a date filter to create a report)
select sum(counter_Value) from dc_execution_engine_profiles where counter_name = 'bytes spilled';
--bytes spilled by loading (insert/copy/tuple mover) (add/edit date filter to create a report)
select time_slice("time", 5, 'minute') as ts, sum(size_in_bytes)/1024/1024 as GB_created_from_spill
from dc_roses_created
where plan_type = 'TM_DIRECTLOAD'
and original_target = 'AUTO'
--and time >= current_date
group by 1 order by 1;
--spill events (add/edit a date filter to create a report)
select time_slice(event_timestamp, 5, 'minute') as ts, count(event_type) as num_spill_events
from query_Events
where event_type LIKE '%SPILL%'
--and event_timestamp >= current_date
group by 1 order by 1;

Have fun!

Reference:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/ManageDiskSpace/ManagingDiskSpace.htm

Tagged:
Sign In or Register to comment.