Обсуждение: clean out ./data/base/pgsql_tmp
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
On Thu, Dec 7, 2017 at 5:18 PM, Dylan Luong <Dylan.Luong@unisa.edu.au> 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
On Thu, Dec 7, 2017 at 1:18 PM, Dylan Luong <Dylan.Luong@unisa.edu.au> 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
On Wed, Dec 6, 2017 at 8:18 PM, Dylan Luong <Dylan.Luong@unisa.edu.au> 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
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:thomas.munro@enterprisedb.com] Sent: Thursday, 7 December 2017 2:56 PM To: Dylan Luong <Dylan.Luong@unisa.edu.au> Cc: pgsql-general@lists.postgresql.org Subject: Re: clean out ./data/base/pgsql_tmp On Thu, Dec 7, 2017 at 5:18 PM, Dylan Luong <Dylan.Luong@unisa.edu.au> 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 awaypgsql_tmp data on the theory that it might be useful for forensics. -- Thomas Munro http://www.enterprisedb.com
On Wed, Dec 6, 2017 at 9:29 PM, Dylan Luong <Dylan.Luong@unisa.edu.au> 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