Checking Query Disk Spill Space Usage

Checking Query Disk Spill Space Usage

The gp_workfile_* views show information about all the queries that are currently using disk spill space. Greenplum Database creates work files on disk if it does not have sufficient memory to execute the query in memory. This information can be used for troubleshooting and tuning queries. The information in the views can also be used to specify the values for the Greenplum Database configuration parameters gp_workfile_limit_per_query and gp_workfile_limit_per_segment.

gp_workfile_entries

This view contains one row for each operator using disk space for workfiles on a segment at the current time. The view is accessible to all users, however non-superusers only to see information for the databases that they have permission to access.

Table 1. gp_workfile_entries
Column Type References Description
command_cnt integer   Command ID of the query.
content smallint   The content identifier for a segment instance.
current_query text   Current query that the process is running.
datname name   Greenplum database name.
directory text   Path to the work file.
optype text   The query operator type that created the work file.
procpid integer   Process ID of the server process.
sess_id integer   Session ID.
size bigint   The size of the work file in bytes.
numfiles bigint   The number of files created.
slice smallint   The query plan slice. The portion of the query plan that is being executed.
state text   The state of the query that created the work file.
usename name   Role name.
workmem integer   The amount of memory allocated to the operator in KB.

gp_workfile_usage_per_query

This view contains one row for each query using disk space for workfiles on a segment at the current time. The view is accessible to all users, however non-superusers only to see information for the databases that they have permission to access.

Table 2. gp_workfile_usage_per_query
Column Type References Description
command_cnt integer   Command ID of the query.
content smallint   The content identifier for a segment instance.
current_query text   Current query that the process is running.
datname name   Greenplum database name.
procpid integer   Process ID of the server process.
sess_id integer   Session ID.
size bigint   The size of the work file in bytes.
numfiles bigint   The number of files created.
state text   The state of the query that created the work file.
usename name   Role name.

gp_workfile_usage_per_segment

This view contains one row for each segment. Each row displays the total amount of disk space used for workfiles on the segment at the current time. The view is accessible to all users, however non-superusers only to see information for the databases that they have permission to access.

Table 3. gp_workfile_usage_per_segment
Column Type References Description
content smallint   The content identifier for a segment instance.
size bigint   The total size of the work files on a segment.
numfiles bigint   The number of files created.