Idle sessions keep deleted files open

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

Idle sessions keep deleted files open

Peter J. Holzer
I noticed this with PostgreSQL 11.7 on Linux:

I did a «vacuum full» on a table which had become quite bloated (size
before the vacuum: 392 GB, after: 291 GB). I expected the disk usage to
increase as the new table was written and then drop sharply after the
vacuum was finished.

However, that didn't happen. While usage increased by about 300 GB
during the vacuum, it stayed the same afterwards (as reported by «df»).

However, «du» reported about 400 GB less than «df». So apparently, files
had been unlinked but were still in use by some process.

«lsof» confirmed this: There were some idle sessions which were keeping
the files open. As soon as I got those sessions to execute some request,
they closed the files, causing the disk space to be freed.

I think I noticed that before when dropping tables, but didn't draw the
right conclusion at the time.

So, if you are reorganizing large tables, keep an eye on idle sessions -
they may keep deleted files around for quite some time.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Idle sessions keep deleted files open

Andres Freund
Hi,

On 2020-03-30 00:49:13 +0200, Peter J. Holzer wrote:

> I noticed this with PostgreSQL 11.7 on Linux:
>
> I did a «vacuum full» on a table which had become quite bloated (size
> before the vacuum: 392 GB, after: 291 GB). I expected the disk usage to
> increase as the new table was written and then drop sharply after the
> vacuum was finished.
>
> However, that didn't happen. While usage increased by about 300 GB
> during the vacuum, it stayed the same afterwards (as reported by «df»).
>
> However, «du» reported about 400 GB less than «df». So apparently, files
> had been unlinked but were still in use by some process.
>
> «lsof» confirmed this: There were some idle sessions which were keeping
> the files open. As soon as I got those sessions to execute some request,
> they closed the files, causing the disk space to be freed.
>
> I think I noticed that before when dropping tables, but didn't draw the
> right conclusion at the time.
>
> So, if you are reorganizing large tables, keep an eye on idle sessions -
> they may keep deleted files around for quite some time.

I think this is partially related to
https://postgr.es/m/20181003223725.elcu3t44fpd4lm56%40alap3.anarazel.de

Greetings,

Andres Freund