Hi,
But my question was did you check, if t1 and/or t2 relfile was deleted before complete vacuum?
Why I had, in my example, temporarily 335 files extra (old relfiles?). And removed after vacuum completely finished.
br
Kaido
But my question was did you check, if t1 and/or t2 relfile was deleted before complete vacuum?
Why I had, in my example, temporarily 335 files extra (old relfiles?). And removed after vacuum completely finished.
br
Kaido
On Wed, 16 Jun 2021 at 22:33, Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> wrote:
thank you for asking this,
i have first time followed the debugger into the source :)
postgres/vacuum.c at 0734b0e983443882ec509ab4501c30ba9b706f5f · postgres/postgres (github.com)
anyways.
if you notice, one filenode changes at a time, (while i have my gdb debugger on, iterating to one partition vacuum full at a time)
postgres@db:~/playground/pg14/base/17718$ oid2name -d test
From database "test":
Filenode Table Name
----------------------
17944 t1
17948 t2
17952 t3^^^^ before any vacuuming-- now start vacuuming
postgres@db:~/playground/pg14/base/17718$ oid2name -d test
From database "test":
Filenode Table Name
----------------------
17956 t1 --- this one is first
17948 t2
17952 t3
postgres@db:~/playground/pg14/base/17718$ oid2name -d test
From database "test":
Filenode Table Name
----------------------
17956 t1
17948 t2
17952 t3
postgres@db:~/playground/pg14/base/17718$ oid2name -d test
From database "test":
Filenode Table Name
----------------------
17956 t1
17960 t2 --this one is second and so on
17952 t3
if you want to play along,
i put a break point at vacuum_rel
Breakpoint 1, vacuum_rel (relid=17890, relation=0x0, params=params@entry=0x7ffe6ff560d0) at vacuum.c:1785unless the experts correct me.On Wed, 16 Jun 2021 at 22:58, kaido vaikla <kaido.vaikla@xxxxxxxxx> wrote:Hi Vijay,
Thnx for your test. But did you check, if t1 and/or t2 relfile was deleted before complete vacuum?I did simple test with partitioned table. 66 empty partitions.kvpg=# \d test2.time_taptest_table2
Partitioned table "test2.time_taptest_table2"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------------
col1 | integer | | |
col2 | text | | | 'stuff'::text
col3 | timestamp with time zone | | not null | now()
Partition key: RANGE (col3)
Indexes:
"time_taptest_table2_col3_idx" btree (col3)
Number of partitions: 67 (Use \d+ to list them.)
kvpg=# select count(*) from test2.time_taptest_table2;
count
-------
0
(1 row)Terminal 1:
$ oid2name
All databases:
Oid Database Name Tablespace
----------------------------------
16388 kvpg pg_default
38353 kvpg2 pg_default
14187 postgres pg_default
14186 template0 pg_default
1 template1 pg_default
$ cd $PGDATA/base/16388
$ while true ; do date ; ls -l | wc -l; echo ====; sleep 10; done;Wed Jun 16 20:20:34 EEST 2021
672
====
Wed Jun 16 20:20:44 EEST 2021
672
====
Wed Jun 16 20:20:54 EEST 2021
1007
====
Wed Jun 16 20:21:04 EEST 2021
1007
====...
Wed Jun 16 20:22:24 EEST 2021
1007
====
Wed Jun 16 20:22:34 EEST 2021
672
====Same time Terminal 2:$ date; time vacuumdb -f -d kvpg -t test2.time_taptest_table2
Wed Jun 16 20:20:50 EEST 2021
vacuumdb: vacuuming database "kvpg"
real 0m0.601s
user 0m0.001s
sys 0m0.003sYou see, 0.5sec vacuuming, but about 2min 335 extra files
br
KaidoOn Wed, 16 Jun 2021 at 17:26, Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> wrote:On Wed, 16 Jun 2021 at 18:37, kaido vaikla <kaido.vaikla@xxxxxxxxx> wrote:
>
> Hi,
>
> Will VACUUM FULL create all new partitions at once and removes all together if vacuum is done or does it per partition? Or something third?
>
I think it is one partition at a time.
***********************
# in session [1] do the setup
postgres=# create table t(id int, value int) partition by list(id)
with (autovacuum_enabled = off);
CREATE TABLE
postgres=# create table t1 partition of t for values in (1) with
(autovacuum_enabled = off);
CREATE TABLE
postgres=# create table t2 partition of t for values in (2) with
(autovacuum_enabled = off);
CREATE TABLE
postgres=# create table t3 partition of t for values in (3) with
(autovacuum_enabled = off);
CREATE TABLE
postgres=# \d+ t
Partitioned table "public.t"
Column | Type | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
id | integer | | | | plain |
| |
value | integer | | | | plain |
| |
Partition key: LIST (id)
Partitions: t1 FOR VALUES IN (1),
t2 FOR VALUES IN (2),
t3 FOR VALUES IN (3)
Options: autovacuum_enabled=off
postgres=# insert into t select (1), (2), (3);
do $$
declare i int;
begin
for i in 1..10000 loop
update t set id = id;
end loop;
end; $$;
DO
***********************
in a parallel session [2], lock one partition for update
postgres=# begin;
BEGIN
postgres=*# select * from t3 where id = 3 for update;
id | value
----+-------
3 | 3
(1 row)
***********************
in another parallel session [3] strace the session pid to track
truncate syscall.
strace -r -f -p 2050 -e trace=truncate
strace: Process 2050 attached
0.000000 truncate("base/14008/17850", 0) = 0
0.000291 truncate("base/14008/17850.1", 0) = -1 ENOENT (No such
file or directory)
0.000372 truncate("base/14008/17850_fsm", 0) = 0
0.000226 truncate("base/14008/17850_fsm.1", 0) = -1 ENOENT (No
such file or directory)
0.000061 truncate("base/14008/17850_vm", 0) = -1 ENOENT (No such
file or directory)
0.000019 truncate("base/14008/17850_init", 0) = -1 ENOENT (No
such file or directory)
0.009866 truncate("base/14008/17853", 0) = 0
0.000263 truncate("base/14008/17853.1", 0) = -1 ENOENT (No such
file or directory)
0.000151 truncate("base/14008/17853_fsm", 0) = 0
0.000134 truncate("base/14008/17853_fsm.1", 0) = -1 ENOENT (No
such file or directory)
0.000041 truncate("base/14008/17853_vm", 0) = -1 ENOENT (No such
file or directory)
0.000048 truncate("base/14008/17853_init", 0) = -1 ENOENT (No
such file or directory)
1.003913 --- SIGALRM {si_signo=SIGALRM, si_code=SI_KERNEL} ---
55.328486 truncate("base/14008/17841", 0) = 0 ----- this was
waiting for lock and hence did not complete vaccum full, but remaining
0.000397 truncate("base/14008/17841.1", 0) = -1 ENOENT (No such
file or directory)
0.000063 truncate("base/14008/17841_fsm", 0) = 0
0.000208 truncate("base/14008/17841_fsm.1", 0) = -1 ENOENT (No
such file or directory)
0.000094 truncate("base/14008/17841_vm", 0) = -1 ENOENT (No such
file or directory)
0.000022 truncate("base/14008/17841_init", 0) = -1 ENOENT (No
such file or directory)
***********************
in paralllel session [3], run vaccum full, it completes for partition
t1, and t2 but wait for t3 which in a transaction. [3]
vacuum (full,verbose,analyze) t;
INFO: analyzing "public.t" inheritance tree
INFO: "t1": scanned 45 of 45 pages, containing 1 live rows and 10000
dead rows; 1 rows in sample, 1 estimated total rows
INFO: "t2": scanned 45 of 45 pages, containing 1 live rows and 10000
dead rows; 1 rows in sample, 1 estimated total rows
INFO: "t3": scanned 89 of 89 pages, containing 1 live rows and 144
dead rows; 1 rows in sample, 1 estimated total rows
INFO: vacuuming "public.t1"
INFO: "t1": found 10000 removable, 1 nonremovable row versions in 45 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: analyzing "public.t1"
INFO: "t1": scanned 1 of 1 pages, containing 1 live rows and 0 dead
rows; 1 rows in sample, 1 estimated total rows
INFO: vacuuming "public.t2"
INFO: "t2": found 10000 removable, 1 nonremovable row versions in 45 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: analyzing "public.t2"
INFO: "t2": scanned 1 of 1 pages, containing 1 live rows and 0 dead
rows; 1 rows in sample, 1 estimated total rows
--------- this was waiting for accessexclusive lock on t3.
INFO: vacuuming "public.t3"
INFO: "t3": found 56 removable, 1 nonremovable row versions in 89 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: analyzing "public.t3"
INFO: "t3": scanned 1 of 1 pages, containing 1 live rows and 0 dead
rows; 1 rows in sample, 1 estimated total rows
VACUUM
--
Thanks,
Vijay
Mumbai, India
--Thanks,VijayMumbai, India
