STV_PARTITIONS - Amazon Redshift

STV_PARTITIONS

Use the STV_PARTITIONS table to find out the disk speed performance and disk utilization for Amazon Redshift.

STV_PARTITIONS contains one row per node per logical disk volume.

STV_PARTITIONS is visible only to superusers. For more information, see Visibility of data in system tables and views.

Table columns

Column name Data type Description
owner integer Disk node that owns the partition.
host integer Node that is physically attached to the partition.
diskno integer Disk containing the partition.
part_begin bigint Offset of the partition. Raw devices are logically partitioned to open space for mirror blocks.
part_end bigint End of the partition.
used integer Number of 1 MB disk blocks currently in use on the partition.
tossed integer Number of blocks that are ready to be deleted but are not yet removed because it is not safe to free their disk addresses. If the addresses were freed immediately, a pending transaction could write to the same location on disk. Therefore, these tossed blocks are released as of the next commit. Disk blocks might be marked as tossed, for example, when a table column is dropped, during INSERT operations, or during disk-based query operations.
capacity integer Total capacity of the partition in 1 MB disk blocks.
reads bigint Number of reads that have occurred since the last cluster restart.
writes bigint Number of writes that have occurred since the last cluster restart.
seek_forward integer Number of times that a request is not for the subsequent address given the previous request address.
seek_back integer Number of times that a request is not for the previous address given the subsequent address.
is_san integer Whether the partition belongs to a SAN. Valid values are 0 (false) or 1 (true).
failed integer This column is deprecated.
mbps integer Disk speed in megabytes per second.
mount character(256) Directory path to the device.

Sample query

The following query returns the disk space used and capacity, in 1 MB disk blocks, and calculates disk utilization as a percentage of raw disk space. The raw disk space includes space that is reserved by Amazon Redshift for internal use, so it is larger than the nominal disk capacity, which is the amount of disk space available to the user. The Percentage of Disk Space Used metric on the Performance tab of the Amazon Redshift Management Console reports the percentage of nominal disk capacity used by your cluster. We recommend that you monitor the Percentage of Disk Space Used metric to maintain your usage within your cluster's nominal disk capacity.

Important

We strongly recommend that you do not exceed your cluster's nominal disk capacity. While it might be technically possible under certain circumstances, exceeding your nominal disk capacity decreases your cluster's fault tolerance and increases your risk of losing data.

This example was run on a two-node cluster with six logical disk partitions per node. Space is being used very evenly across the disks, with approximately 25% of each disk in use.

select owner, host, diskno, used, capacity, (used-tossed)/capacity::numeric *100 as pctused from stv_partitions order by owner; owner | host | diskno | used | capacity | pctused -------+------+--------+--------+----------+--------- 0 | 0 | 0 | 236480 | 949954 | 24.9 0 | 0 | 1 | 236420 | 949954 | 24.9 0 | 0 | 2 | 236440 | 949954 | 24.9 0 | 1 | 2 | 235150 | 949954 | 24.8 0 | 1 | 1 | 237100 | 949954 | 25.0 0 | 1 | 0 | 237090 | 949954 | 25.0 1 | 1 | 0 | 236310 | 949954 | 24.9 1 | 1 | 1 | 236300 | 949954 | 24.9 1 | 1 | 2 | 236320 | 949954 | 24.9 1 | 0 | 2 | 237910 | 949954 | 25.0 1 | 0 | 1 | 235640 | 949954 | 24.8 1 | 0 | 0 | 235380 | 949954 | 24.8 (12 rows)