Pivotal Knowledge Base

Follow

Can I Change Where Workfiles / Spillfiles / Transaction Files are Stored?

Environment

  • GPDB - all versions

Question

How do I change the location where work files or transaction logs are created?

Background

By default workfiles are created int he default filespace for each database. Though there are mechanisms in place to limit the amount of spill files created at any one time it is still possible to consume all of the available drive space resulting in a segment failure or to swamp the device with I/O resulting in poor performance for other queries which need to read or write data files.

Solution

This process is fully documented in the Database Administrator Guide for GPDB and relies on using gpfilespace to add and modify the storage for a given database. For more details please see the Administrator Guide Under "Creating and Managing Tablespaces". For instructions on using gpfilespace please review the command documentation in the GPDB Utility Guide.

The default location for workfiles is: <segment_directory>/base/<tablespace_oid>/<database_oid>/pgsql_tmp

Moving the transaction files will relocate the following directories and their contents:

  • <segment_directory>/pg_clog
  • <segment_directory>/pg_distributedlog
  • <segment_directory>/pg_distributedxidmap
  • <segment_directory>/pg_multixact
  • <segment_directory>/pg_subtrans
  • <segment_directory>/pg_xlog

Process for changing the location of these files is as follows:

  1. Creat a new file space config using the command

     gpfilespace -o gpfilespace_config
    

    on the master.

  2. Verify the configuration file and if no issues are found, create the new filespaces using the command:

     gpfilespace -c gpfilespace_config
    
  3. To move the location where the workfiles are created issue the command:

     gpfilespace --movetempfilespace <fs_name>
    

    where <fs_name> is the name of the newly created filespace.

  4. To move the location of the transaction files use the command:

     gpfilespace --movetransfilespace <fs_name>
    

    where <fs_name> is the name of the newly created filespace.

A few notes:

  • running these commands will stop the database, please ensure there is no activity when these commands are run otherwise they will fail
  • the temporary files and transaction files do not need to be stored on the same filespace.
  • to revert back to the default locations, re-run the above command with the value default for the filespace name

Comments

Powered by Zendesk