Greenplum-database-new-features - Workfile Disk Spill Space

Greenplum Database creates spill files, also known as workfiles, on disk if it does not have sufficient memory to execute an SQL query in memory. 

By default maximum no of spill files that can be created is 100,000 which is sufficient for the majority of queries. However, If a query creates more than the specified number of spill files, Greenplum Database returns error:

ERROR: number of workfiles per query limit exceeded

Reasons behind  large number of spill files to be generated include:

1. Data skew is present in the queried data.

2. The amount memory allocated for the query is too low.

when you get this error there are many ways you solve this problem.

Note: You can control the maximum amount of memory that can used by a query with the Greenplum Database server configuration parameters max_statement_mem,statement_mem or through resource queues.

Monitoring spill file usage is done by looking at the information available using gp_workfile_* views

sachi=# \dv gp_toolkit.gp_workfile_*

List of relations

Schema | Name | Type | Owner | Storage

------------+-------------------------------+------+---------+---------

gp_toolkit | gp_workfile_entries | view | gpadmin | none

gp_toolkit | gp_workfile_usage_per_query | view | gpadmin | none

gp_toolkit | gp_workfile_usage_per_segment | view | gpadmin | none

(3 rows)

Lets look at the definition of these views.

1. 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.

2. 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.

3. 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.

gp_workfile_usage_per_segment (one row for each segment)

||

||

V

gp_workfile_usage_per_query ( one row for each query using disk space for workfiles on a segment at the current time) 

||

||

V

gp_workfile_entries (one row for each operator using disk space for workfiles on a segment at the current time)