postgresql createas创建大表过程中查看空间占用

概述

近期遇到一个数据目录磁盘使用率突增的问题，分析问题的过程中，查看活动会话发现有一个"create table XXX as"语句在创建新表，怀疑磁盘使用率突增跟该会话有关，那么如何确定新建的表占用了多少磁盘空间呢

现象重现

会话1-使用create table XXX as创建一个大表

postgres=# create unlogged table test_s as select (select num from a limit 1) from generate_series(1,100000000);
复制

会话2-在会话1执行的过程中查看活动会话

postgres=# select pid,xact_start,state,query from pg_stat_activity where state<>'idle' ;
   pid   |          xact_start           | state  |                                                 query
---------+-------------------------------+--------+-------------------------------------------------------------------------------------------------------
 2726148 | 2024-08-13 14:44:15.640673+08 | active | create unlogged table test_s as select (select num from a limit 1) from generate_series(1,100000000);
 2719775 | 2024-08-13 14:44:43.938851+08 | active | select pid,xact_start,state,query from pg_stat_activity where state<>'idle' ;
(2 rows)

postgres=# select oid,relfilenode from pg_class where relname='test_s';
 oid | relfilenode
-----+-------------
(0 rows)
复制

此时pg_class中是查不到该表的，根据活动会话pid使用strace工具查看进程调用信息，找到pwrite64在写41号链接，查看41号链接实际指向的文件就是正在创建的新表数据文件，同时可以看到数据文件大小还在增长

[postgres@mydb1b data]$ strace -tt -p 2726148
......
14:44:25.496915 pwrite64(41, "\0\0\0\0\0\0\0\0\0\0\0\0\244\4\270\4\0 \4 \0\0\0\0\350\2370\0\320\2370\0"..., 8192, 461070336) = 8192
14:44:25.496938 pwrite64(41, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 477847552) = 8192
14:44:25.497005 pread64(38, "\0\0\1\0\0\0\30\0J\2\3\1\16\0\0\0\1\0\0\0\30\0K\2\3\1\16\0\0\0\1\0"..., 8192, 237641728) = 8192
14:44:25.497130 lseek(41, 0, SEEK_END)  = 477855744
14:44:25.497178 pwrite64(41, "\0\0\0\0\0\0\0\0\0\0\0\0\244\4\270\4\0 \4 \0\0\0\0\350\2370\0\320\2370\0"..., 8192, 461078528^C) = 8192
strace: Process 2726148 detached

[postgres@mydb1b data]$ ls -ltr /proc/2726148/fd/41
lrwx------. 1 postgres postgres 64  8月 13 14:42 /proc/2726148/fd/41 -> /app/pg12/data/base/13672/97399
[postgres@mydb1b data]$ ll /app/pg12/data/base/13672/97399*
-rw-------. 1 postgres postgres 1.0G  8月 13 14:44 /app/pg12/data/base/13672/97399
-rw-------. 1 postgres postgres 109M  8月 13 14:44 /app/pg12/data/base/13672/97399.1
-rw-------. 1 postgres postgres    0  8月 13 14:44 /app/pg12/data/base/13672/97399_init
[postgres@mydb1b data]$ ll /app/pg12/data/base/13672/97399*
-rw-------. 1 postgres postgres 1.0G  8月 13 14:44 /app/pg12/data/base/13672/97399
-rw-------. 1 postgres postgres 182M  8月 13 14:44 /app/pg12/data/base/13672/97399.1
-rw-------. 1 postgres postgres    0  8月 13 14:44 /app/pg12/data/base/13672/97399_init
[postgres@mydb1b data]$ ll /app/pg12/data/base/13672/97399*
-rw-------. 1 postgres postgres 1.0G  8月 13 14:44 /app/pg12/data/base/13672/97399
-rw-------. 1 postgres postgres 378M  8月 13 14:44 /app/pg12/data/base/13672/97399.1
-rw-------. 1 postgres postgres    0  8月 13 14:44 /app/pg12/data/base/13672/97399_init
[postgres@mydb1b data]$
复制

会话2-在会话1执行完成后，查看pg_class->oid为97399，与上边看到的一致

postgres=# select oid,relfilenode from pg_class where relname='test_s';
  oid  | relfilenode
-------+-------------
 97399 |       97399
(1 row)

复制

解决方案

本次问题处理中，通过以上方法定位到是这个会话导致的磁盘突增，确认该会话来源并停止会话后空间即释放。