Smarts NCM: Large pgsql_tmp files created in /tmp - what is creating the pgsql_tmp files?

Products

• VMware Smart Assurance

Issue/Introduction

Symptoms:


These files can grow quite large and cause memory and CPU issues

Large pgsql_tmp files created in /tmp, need to know what is creating the pgsql_tmp files so it can be stopped

Environment

VMware Smart Assurance - NCM

Cause

As per KB article 192244, pgsql_tmp is a temporary tablespace. This directory contains temporary files generated by the query executor. 

 

Resolution

if you find these files have been created you can identify the source by the tmp filename itself. The name of the filename contains the pid of the query which generated it.
For example if the file name is 'base/pgsql_tmp/pgsql_tmp15587.25', then the pid is 15587. 

In order to identify the query associated with pid 15587, you can execute below steps in the database.

voyencedb=#\o /tmp/querystring
voyencedb=#SELECT datname, procpid, current_query, query_start, backend_start, client_addr,client_port FROM pg_stat_activity WHERE procpid='15587';
voyencedb=#\q

After these steps, /tmp/querystring will have necessary details to figure out the query which caused so many tmp files to be generated.

If you want to delete these tmp files, then the best approach is to restart the controldb service so that the tmp files are automatically be deleted.