Thread: How does vacuum full works if table is partitioned?
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?
br
Kaido
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?
br
Kaido
On Wed, 16 Jun 2021 at 18:37, kaido vaikla <kaido.vaikla@gmail.com> 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
Hi Vijay,
Thnx for your test. But did you check, if t1 and/or t2 relfile was deleted before complete vacuum?
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)
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;
$ 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
====
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.003s
Wed Jun 16 20:20:50 EEST 2021
vacuumdb: vacuuming database "kvpg"
real 0m0.601s
user 0m0.001s
sys 0m0.003s
You see, 0.5sec vacuuming, but about 2min 335 extra files
br
Kaido
br
Kaido
On Wed, 16 Jun 2021 at 17:26, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:
On Wed, 16 Jun 2021 at 18:37, kaido vaikla <kaido.vaikla@gmail.com> 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
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
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:1785
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:1785
unless the experts correct me.
On Wed, 16 Jun 2021 at 22:58, kaido vaikla <kaido.vaikla@gmail.com> 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@gmail.com> wrote:On Wed, 16 Jun 2021 at 18:37, kaido vaikla <kaido.vaikla@gmail.com> 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,
Vijay
Mumbai, India
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@gmail.com> 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@gmail.com> 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@gmail.com> wrote:On Wed, 16 Jun 2021 at 18:37, kaido vaikla <kaido.vaikla@gmail.com> 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
On Thu, 17 Jun 2021 at 15:17, kaido vaikla <kaido.vaikla@gmail.com> wrote: > > 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. for me it is mostly the partitions and its indexes and some metadata relates to those tables. how i check that. create table before__vacuum as select oid, relname, relfilenode,reltablespace from pg_class order by oid; vacuum full t; create table after__vacuum as select oid, relname, relfilenode,reltablespace from pg_class order by oid; and then check relfilenode that do not exist in the db after full vacuum, select b.* from before__vacuum b where not exists ( select 1 from after__vacuum a where b.relfilenode = a.relfilenode) order by oid; oid | relname | relfilenode | reltablespace -------+---------+-------------+--------------- 26497 | t0 | 26497 | 0 26500 | t0_pkey | 26500 | 0 26502 | t1 | 26502 | 0 26505 | t1_pkey | 26505 | 0 26507 | t2 | 26507 | 0 26510 | t2_pkey | 26510 | 0 (6 rows) I am not sure what I am doing wrong, but you can run that yourself, and help figure out what is there in those 300+ changes.