ANBOB™

去年在记录<案例：openGauss/postgreSQL 数据库手动清理膨胀Heap Bloat (dead tup)>时遇到过在opengauss系统drop table后，操作系统并未立即释放问题，当时没有过多研究，只是建议通过kill SESSIONS或重启可以达到效果，近日发现同事在postgresql中同样遇到了该问题，但Postgresql是进程模式可以和oracle一样通过kill OS 进程释放文件句柄，并且PostgreSQL在2021-02-11 Release的版本都改进这一问题，目前Opengauss V5依旧存在，下面简单演示。

修复如下:

Ensure that disk space allocated for a dropped relation is released promptly at commit (Thomas Munro)

Previously, if the dropped relation spanned multiple 1GB segments, only the first segment was truncated immediately. Other segments were simply unlinked, which doesn’t authorize the kernel to release the storage so long as any other backends still have the files open.

以前，如果删除的关系跨越多个 1GB 段，则只会立即截断第一个段。其他段只是取消链接，只要任何其他后端仍然打开文件，就不会授权内核释放存储。 修复版本2021-02-11 后 Release的(9.5.25+, 9.6.21+, 10.16+, 11.11+, 12.6+, 13.2+, 14+, 15+, 16+ …);

测试方法

构建1GB以上的测试表：

create unlogged table test1 (id1 int ,id2 int,id3 int,v1 varchar,v2 varchar);

insert into test1 select a,a,a,'hello','hello' from generate_series(1,22000000) a;

PostgreSQL中修复以前

# session 1

postgres=# create unlogged table  test1 (id1 int ,id2 int,id3 int,v1 varchar,v2 varchar);
CREATE TABLE
postgres=# insert into test1 select a,a,a,'hello','hello' from generate_series(1,22000000) a;
INSERT 0 22000000

postgres=# select pg_backend_pid();
         550580
postgres=# select * from test1 where id1=1;
 id1 | id2 | id3 |  v1   |  v2
-----+-----+-----+-------+-------
   1 |   1 |   1 | hello | hello

# session 2
postgres=# drop table test1;
DROP TABLE

# session 3

$ lsof -p 550580|grep delete
postgres 550580 postgres 28u REG 253,0 74186752 672021 /app/pg12/data/base/13672/97283.1 (deleted)

# session 2
postgres=# checkpoint ;
CHECKPOINT

# session 3
$ lsof -p 550580|grep delete
postgres 550580 postgres 27u REG 253,0         0  670183 /app/pg12/data/base/13672/97283 (deleted)
postgres 550580 postgres 28u REG 253,0  74186752  672021 /app/pg12/data/base/13672/97283.1 (deleted)

Note:
注意同事这里lsof 只是grep了deleted，如果grep 文件号会更好，但也能看到其实除了第1个段，drop和checkpoint后其它段并未释放空间给OS。

PostgreSQL中修复后

# session 1
-- same above ,ignore

# session 2
postgres=# drop table test1;
DROP TABLE

# session 3
$ lsof -p 3651791|grep delete
postgres 3651791 postgres 39u REG 253,0         0  692750 /app/pg12_8/data/base/13672/16457.1 (deleted)

# session 2
postgres=# checkpoint ;
CHECKPOINT
postgres=#

# session 3
$ lsof -p 3651791|grep delete
postgres 3651791 postgres 38u REG 253,0         0  683618 /app/pg12_8/data/base/13672/16457 (deleted)
postgres 3651791 postgres 39u REG 253,0         0  692750 /app/pg12_8/data/base/13672/16457.1 (deleted)

Note:
除了第一个段以外，XX.n的文件也已经为0 bytes，已释放给OS.

当前的Opengauss v5(Mogdb 5.0.1 )

# session 1
anbob=# create unlogged table  test100 (id1 int ,id2 int,id3 int,v1 varchar,v2 varchar);
CREATE TABLE

anbob=# insert into test100 select a,a,a,'hello','hello' from generate_series(1,22000000) a;
INSERT 0 22000000
db1=# \dt+ test100
                                      List of relations
 Schema |  Name   | Type  | Owner |  Size   |             Storage              | Description
--------+---------+-------+-------+---------+----------------------------------+-------------
 public | test100 | table | omm   | 1102 MB | {orientation=row,compression=no} |
(1 row)

anbob=# select pg_relation_filepath('test100');
 pg_relation_filepath
----------------------
 base/18596/49829

db1=# select count(*) from test100;
  count
----------
 22000000
(1 row)

# session 2

[omm@mogdb1 18596]$ ls -l 49829*
-rw------- 1 omm omm 1073741824 Mar 17 16:21 49829
-rw------- 1 omm omm   81543168 Mar 17 16:20 49829.1
-rw------- 1 omm omm     303104 Mar 17 16:21 49829_fsm
-rw------- 1 omm omm          0 Mar 17 16:19 49829_init

[omm@mogdb1 18596]$ lsof |grep 49829
mogdb      8384                             omm  417u      REG              253,0 1073741824    2844512 /MogDB/data/base/18596/49829
mogdb      8384                             omm  418u      REG              253,0     303104    2844524 /MogDB/data/base/18596/49829_fsm
mogdb      8384                             omm  419u      REG              253,0   81543168    2841034 /MogDB/data/base/18596/49829.1
...

# session 3

anbob=# drop table test100;
DROP TABLE

# session 2

$ ls -l 49829*
-rw------- 1 omm omm 1073741824 Mar 17 17:30 49829

$ lsof |grep 49829
mogdb      8384                             omm  417u      REG              253,0 1073741824    2844512 /MogDB/data/base/18596/49829
mogdb      8384                             omm  418u      REG              253,0     303104    2844524 /MogDB/data/base/18596/49829_fsm (deleted)
mogdb      8384                             omm  419u      REG              253,0   81543168    2841034 /MogDB/data/base/18596/49829.1 (deleted)

# session 3
anbob=# checkpoint;
CHECKPOINT

# session 2
$ ls -l 49829*
ls: cannot access 49829*: No such file or directory

[omm@mogdb1 18596]$ lsof |grep 49829
mogdb      8384                             omm  417u      REG              253,0 1073741824    2844512 /MogDB/data/base/18596/49829 (deleted)
mogdb      8384                             omm  418u      REG              253,0     303104    2844524 /MogDB/data/base/18596/49829_fsm (deleted)
mogdb      8384                             omm  419u      REG              253,0   81543168    2841034 /MogDB/data/base/18596/49829.1 (deleted)

# session 1
 -- query another 
anbob =# select now();
              now
-------------------------------
 2024-03-17 16:33:16.098603+08
(1 row)

# session 2
$ lsof |grep 49829
-- none
[omm@mogdb1 18596]$

Note:
在OPENGAUSS中的表现更糟糕，checkpoint后都没有释放给OS。直到session 1做了一个新的查询。

Summary:

小结：
在OpenGauss 中drop tables释放会等到直到所有曾经“最后”一个查询是此表会话断开或执行另一个不相关的SQL.

打赏

微信扫一扫，打赏作者吧～