clean out ./data/base/pgsql_tmp

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

clean out ./data/base/pgsql_tmp

Dylan Luong

Hi

 

We have an issue where one of the developers ran a large query that hung was filling up the DATA drive very rapidly. From 50% usage to 95% in less than 2hrs.

It created a very large pgsql_tmp size (300GB). To stop the drive filling up we had to kill the process manually using kill -9.

How do we clean up the pgsql_tmp folder? Will Postgres periodically clean it? Ie CHECKPOINT?

Thanks.

 

Regards

Dylan

Reply | Threaded
Open this post in threaded view
|

Re: clean out ./data/base/pgsql_tmp

Thomas Munro-3
On Thu, Dec 7, 2017 at 5:18 PM, Dylan Luong <[hidden email]> wrote:
> We have an issue where one of the developers ran a large query that hung was
> filling up the DATA drive very rapidly. From 50% usage to 95% in less than
> 2hrs.
>
> It created a very large pgsql_tmp size (300GB). To stop the drive filling up
> we had to kill the process manually using kill -9.
>
> How do we clean up the pgsql_tmp folder? Will Postgres periodically clean
> it? Ie CHECKPOINT?

Hi Dylan,

A clean shutdown/restart will clean it out.  A crash restart (as
happens if you kill processes like that) doesn't clear away pgsql_tmp
data on the theory that it might be useful for forensics.

--
Thomas Munro
http://www.enterprisedb.com

Reply | Threaded
Open this post in threaded view
|

Re: clean out ./data/base/pgsql_tmp

Michael Paquier
In reply to this post by Dylan Luong
On Thu, Dec 7, 2017 at 1:18 PM, Dylan Luong <[hidden email]> wrote:
> How do we clean up the pgsql_tmp folder? Will Postgres periodically clean
> it? Ie CHECKPOINT?

A postmaster restart cleans up those files automatically.
--
Michael

Reply | Threaded
Open this post in threaded view
|

Re: clean out ./data/base/pgsql_tmp

Jeff Janes
In reply to this post by Dylan Luong
On Wed, Dec 6, 2017 at 8:18 PM, Dylan Luong <[hidden email]> wrote:

Hi

 

We have an issue where one of the developers ran a large query that hung was filling up the DATA drive very rapidly. From 50% usage to 95% in less than 2hrs.

It created a very large pgsql_tmp size (300GB). To stop the drive filling up we had to kill the process manually using kill -9.


Did you try kill -15 first?  If you did try it and it did not work, then that is probably a bug.  And if you have a reproducible test case, then we will probably be able to fix it.

Cheers,

Jeff
Reply | Threaded
Open this post in threaded view
|

RE: clean out ./data/base/pgsql_tmp

Dylan Luong
In reply to this post by Thomas Munro-3
Since the temp files are easily identifiable as it has the PID in the fileaname.
Is it ok just manually deleting these files as the process has already being killed.

-----Original Message-----
From: Thomas Munro [mailto:[hidden email]]
Sent: Thursday, 7 December 2017 2:56 PM
To: Dylan Luong <[hidden email]>
Cc: [hidden email]
Subject: Re: clean out ./data/base/pgsql_tmp

On Thu, Dec 7, 2017 at 5:18 PM, Dylan Luong <[hidden email]> wrote:
> We have an issue where one of the developers ran a large query that
> hung was filling up the DATA drive very rapidly. From 50% usage to 95%
> in less than 2hrs.
>
> It created a very large pgsql_tmp size (300GB). To stop the drive
> filling up we had to kill the process manually using kill -9.
>
> How do we clean up the pgsql_tmp folder? Will Postgres periodically
> clean it? Ie CHECKPOINT?

Hi Dylan,

A clean shutdown/restart will clean it out.  A crash restart (as happens if you kill processes like that) doesn't clear away pgsql_tmp data on the theory that it might be useful for forensics.

--
Thomas Munro
http://www.enterprisedb.com
Reply | Threaded
Open this post in threaded view
|

Re: clean out ./data/base/pgsql_tmp

Jeff Janes
On Wed, Dec 6, 2017 at 9:29 PM, Dylan Luong <[hidden email]> wrote:
Since the temp files are easily identifiable as it has the PID in the fileaname.
Is it ok just manually deleting these files as the process has already being killed.

I've done that before without issue.

 Cheers,

Jeff
Previous Thread Next Thread