Spill files

Daria Barysheva

Overview

Spill files (also known as work files) are created on disks if there is not enough memory to store temporary data when executing data queries. This temporary data may include JOIN hashes, sorting results, redistributed tables, etc. The directory for storing spill files can be defined via the server configuration parameter temp_tablespaces.

Since spill files are stored on the hard disk and the speed of reading/writing data from disks is several times slower than from RAM, the total execution time of queries grows. It can also increase the queues for resources from other users. Ideally, there should be no queries that generate spill files. However, a small amount of spill files is allowed. In fact, spill files represent a flexible method of executing a query despite the limited amount of RAM. But you should avoid situations when spill files are too many.

There are some typical cases when a large number of spill files can be generated:

  • Data skew is present in the queried data. To see how to avoid it, refer to Recommendations on avoiding data skew.

  • The amount of memory allocated for the query is too low. You can control the maximum memory size to be used by a query with the server configuration parameters max_statement_mem and statement_mem. You can also configure resource groups.

  • The query is not optimized:

    • DISTINCT is used on a large number of fields. Every unique combination of columns specified in the DISTINCT clause is added to RAM. Accordingly, the more such combinations are detected, the faster the memory fills up and spill files are generated. It is recommended to limit the number of requested columns and also not to use huge size columns in the DISTINCT clause.

    • ORDER BY is used. Intermediate sorting results are initially stored in RAM. The larger the amount of data is to be sorted, the faster the generation of spill files begins. In Greenplum, it is not recommended to use sorting in queries, especially for large tables. Instead, it is better to sort the results directly in the data receiver (on the client side).

    • A huge amount of data is processed without filters in the WHERE clause. When performing JOIN queries, data from one of the tables is added to a hash table in RAM. Accordingly, if the requested data of this table is not filtered, the memory will overflow very quickly. It is recommended to use filters whenever possible, especially for dates and timestamps. Even if you need to get results for the entire period, it is desirable to form several queries in short time intervals, after which you can combine the results using UNION ALL. This approach is more effective than trying to process the data in its entirety.

    • When executing JOIN-queries, a hash table is built on a larger of several tables. When executing INNER JOIN, the hash table mentioned above is built on the basis of the table with the smallest size (in order to minimize RAM usage). However, when you use LEFT JOIN — the table defined on the right side is always taken as the basis for the hash table, regardless of its volume. It can lead to problems if this table is the largest in the pair. Therefore, pay attention to the order of all tables in data queries. Also try to minimize the number of extracted columns.

TIP

  • Always try to reduce the number of spill files by changing queries, distribution policy, or memory configuration.

  • If these methods do not help, you can change some server configuration parameters to limit the number of spill files and the disk space they use.

Analyze spill files usage

The default schema gp_toolkit contains views that show information about all the queries that are currently using spill files. These views can be useful for analyzing queries and for troubleshooting:

  • gp_workfile_entries — shows query operators that use disk space for spill files on a segment at the current time. A single row corresponds to a single query operator.

    Structure
    Column Description

    datname

    The database name

    pid

    Process ID of the server process

    sess_id

    Session ID

    command_cnt

    Command ID of the query

    usename

    The role name

    query

    The current query being running by the process

    segid

    The content identifier of a segment (or master) instance. Corresponds to gp_segment_configuration.content. For more information, see the System catalog tables → gp_segment_configuration section of the Tables article

    slice

    The query plan slice. Shows the portion of the query plan that is being run

    optype

    The type of the query operator that created the spill file

    size

    Spill files size (in bytes)

    numfiles

    The number of spill files created

    prefix

    The prefix used when naming a related spill files set

    Example:

    SELECT * FROM gp_toolkit.gp_workfile_entries;

    The result is:

     datname | pid  | sess_id | command_cnt | usename |                    query                    | segid | slice | optype |  size   | numfiles | prefix
---------+------+---------+-------------+---------+---------------------------------------------+-------+-------+--------+---------+----------+--------
 adb     | 5332 |      65 |          42 | gpadmin | SELECT DISTINCT(id1,id2,gen1,gen2) from t1; |    -1 |     0 | Sort   | 4292608 |        1 | Sort_1
(1 row)

  • gp_workfile_usage_per_query — shows queries that use disk space for spill files on a segment at the current time. A single row corresponds to a single query.

    Structure
    Column Description

    datname

    The database name

    pid

    Process ID of the server process

    sess_id

    Session ID

    command_cnt

    Command ID of the query

    usename

    The role name

    query

    The current query being running by the process

    segid

    The content identifier of a segment (or master) instance. Corresponds to gp_segment_configuration.content. For more information, see the System catalog tables → gp_segment_configuration section of the Tables article

    size

    Spill files size (in bytes)

    numfiles

    The number of spill files created

    Example:

    SELECT * FROM gp_toolkit.gp_workfile_usage_per_query;

    The result is:

     datname | pid  | sess_id | command_cnt | usename |                    query                    | segid |   size   | numfiles
---------+------+---------+-------------+---------+---------------------------------------------+-------+----------+----------
 adb     | 5332 |      65 |          42 | gpadmin | SELECT DISTINCT(id1,id2,gen1,gen2) from t1; |    -1 | 20807680 |        1
(1 row)

  • gp_workfile_usage_per_segment — shows the total size of disk space used for spill files on each segment at the current time. A single row corresponds to a single segment.

    Structure
    Column Description

    segid

    The content identifier of a segment (or master) instance. Corresponds to gp_segment_configuration.content. For more information, see the System catalog tables → gp_segment_configuration section of the Tables article

    size

    Spill files size (in bytes)

    numfiles

    The number of spill files created

    Example:

    SELECT * FROM gp_toolkit.gp_workfile_usage_per_segment;

    The result is:

     segid |   size   | numfiles
-------+----------+----------
     5 |        0 |
    13 |        0 |
    10 |        0 |
    11 |        0 |
     1 |        0 |
     6 |        0 |
    12 |        0 |
     0 |        0 |
     2 |        0 |
     7 |        0 |
    15 |        0 |
     4 |        0 |
     8 |        0 |
     3 |        0 |
    -1 | 31752192 |        1
    14 |        0 |
     9 |        0 |
(17 rows)
    NOTE
    The views listed above are accessible to all users. However, non-superusers can get information only on the databases that they have permission to access.

Manage spill files

To limit the number of spill files and the disk space consumed by them, you can use several configuration parameters. Their description is given below.

Name Description Default value Type

gp_workfile_limit_files_per_query

The maximum number of spill files allowed per query at each segment. If the limit is reached, the following error occurs: ERROR: number of workfiles per query limit exceeded. The 0 value allows an unlimited number of spill files

100000

master
session
reload

gp_workfile_limit_per_query

The maximum disk size each query is allowed to use for spill files at each segment (in KB). If the limit is reached, the following error occurs: ERROR: workfile per query size limit exceeded. The 0 value means no limit

25000000

master
session
reload

gp_workfile_limit_per_segment

The maximum total disk size that all running queries are allowed to use for spill files at each segment (in KB). If the limit is reached, the following error occurs: ERROR: workfile per segment size limit exceeded. The 0 value means no limit

50000000

local
system
restart

gp_workfile_compression

Specifies whether to use compression for spill files when a hash aggregation or hash join operation spills to disk during query processing. The default value off means no compression is used. Setting the value to on enables compression

off

master
session
reload
IMPORTANT

  • The default value of the gp_workfile_limit_files_per_query parameter (100000) is sufficient for the majority of queries. Before raising it, try to optimize your queries and distribution/memory options.

  • Limiting the number or size of spill files prevents queries from disrupting DBMS. But it also leads to termination of your queries when the limits are reached.

  • Enabling compression via the gp_workfile_compression parameter can improve performance of the queries that generate spill files. But remember that data compression reduces the number of I/O operations at the expense of increased CPU usage.
Found a mistake? Seleсt text and press Ctrl+Enter to report it