From: David Zhang <david.zhang@highgo.ca>
To: pgsql-hackers@lists.postgresql.org
Cc: Thomas Munro <thomas.munro@gmail.com>
Subject: Re: BUG #16663: DROP INDEX did not free up disk space: idle connection hold file marked as deleted
Date: Tue, 24 Nov 2020 18:36:33 +0000
Message-ID: <160624299337.7563.13562988821961887884.pgcf@coridan.postgresql.org> (raw)
In-Reply-To: <CALT9ZEGD_QdW2n2_R5vhuxAES5OtvEdiZcp9QQBrPk298mwntA@mail.gmail.com>
References: <16663-fe97ccf9932fc800@postgresql.org>
<CALT9ZEGD_QdW2n2_R5vhuxAES5OtvEdiZcp9QQBrPk298mwntA@mail.gmail.com>
I verified the patch "v2-0001-Free-disk-space-for-dropped-relations-on-commit.patch" on master branch "0cc99327888840f2bf572303b68438e4caf62de9". It works for me. Below is my test procedure and results.
=== Before the patch ===
#1 from psql console 1, create table and index then insert enough data
postgres=# CREATE TABLE test_tbl ( a int, b text);
postgres=# CREATE INDEX idx_test_tbl on test_tbl (a);
postgres=# INSERT INTO test_tbl SELECT generate_series(1,80000000),'Hello world!';
postgres=# INSERT INTO test_tbl SELECT generate_series(1,80000000),'Hello world!';
#2 check files size
david:12867$ du -h
12G .
#3 from psql console 2, drop the index
postgres=# drop index idx_test_tbl;
#4 check files size in different ways,
david:12867$ du -h
7.8G .
david:12867$ ls -l
...
-rw------- 1 david david 0 Nov 23 20:07 16402
...
$ lsof -nP | grep '(deleted)' |grep pgdata
...
postgres 25736 david 45u REG 259,2 0 12592758 /home/david/sandbox/postgres/pgdata/base/12867/16402 (deleted)
postgres 25736 david 49u REG 259,2 1073741824 12592798 /home/david/sandbox/postgres/pgdata/base/12867/16402.1 (deleted)
postgres 25736 david 53u REG 259,2 1073741824 12592739 /home/david/sandbox/postgres/pgdata/base/12867/16402.2 (deleted)
postgres 25736 david 59u REG 259,2 372604928 12592800 /home/david/sandbox/postgres/pgdata/base/12867/16402.3 (deleted)
...
The index relnode id "16402" displays size "0" from postgres database folder, but when using lsof to check, all 16402.x are still in used by a psql connection except 16402 is set to 0. Check it again after an hour, lsof shows the same results.
=== After the patch ===
Repeat step 1 ~ 4, lsof shows all the index relnode files (in this case, the index relnode id 16389) are removed within about 1 minute.
$ lsof -nP | grep '(deleted)' |grep pgdata
...
postgres 32707 david 66u REG 259,2 0 12592763 /home/david/sandbox/postgres/pgdata/base/12867/16389.1 (deleted)
postgres 32707 david 70u REG 259,2 0 12592823 /home/david/sandbox/postgres/pgdata/base/12867/16389.2 (deleted)
postgres 32707 david 74u REG 259,2 0 12592805 /home/david/sandbox/postgres/pgdata/base/12867/16389.3 (deleted)
...
One thing interesting for me is that, if the index is created after data records has been inserted, then lsof doesn't show this issue.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: david.zhang@highgo.ca, pgsql-hackers@lists.postgresql.org, thomas.munro@gmail.com
Subject: Re: BUG #16663: DROP INDEX did not free up disk space: idle connection hold file marked as deleted
In-Reply-To: <160624299337.7563.13562988821961887884.pgcf@coridan.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox