Stats before collected on candidate - pgtst_vacuum and other table - pgtst_tbl1
INSERT INTO pgtst_schema.pgtst_vacuum
SELECT i, lpad('TST',mod(i,100),'CHK')
FROM generate_series (10001,20000) s(i);
Stats after collected on candidate - pgtst_vacuum and other table - pgtst_tbl1
Now the difference:
other table - pgtst_tbl1:
Age changed.. pg_class view:
Table:
age | 43 | age | 44
age | 43 | age | 44
Toast:
age | 44 | age | 45
No other changes.
Candidate table - pgtst_vacuum:
Age changed.. pg_class view:
Table:
age | 9 | age | 10
age | 9 | age | 10
Toast:
age | 0 | age | 1
age | 0 | age | 1
pg_stat_user_tables changes:
n_tup_ins | 20001 | n_tup_ins | 30001
n_live_tup | 10025 | n_live_tup | 20025
n_mod_since_analyze | 20000 | n_mod_since_analyze | 30000
n_ins_since_vacuum | 0 | n_ins_since_vacuum | 10000
n_live_tup | 10025 | n_live_tup | 20025
n_mod_since_analyze | 20000 | n_mod_since_analyze | 30000
n_ins_since_vacuum | 0 | n_ins_since_vacuum | 10000
No other changes in data collection.
Vacuum the candidate table:
pgtst_db=# vacuum verbose pgtst_vacuum;
INFO: vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum"
INFO: finished vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum": index scans: 0
pages: 0 removed, 211 remain, 129 scanned (61.14% of total)
tuples: 0 removed, 20064 remain, 0 are dead but not yet removable
removable cutoff: 782, which was 0 XIDs old when operation ended
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "pgtst_vacuum_pkey": pages: 83 in total, 0 newly deleted, 26 currently deleted, 26 reusable
avg read rate: 51.502 MB/s, avg write rate: 81.716 MB/s
buffer usage: 304 hits, 75 misses, 119 dirtied
WAL usage: 112 records, 2 full page images, 20384 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
INFO: vacuuming "pgtst_db.pg_toast.pg_toast_32811"
INFO: finished vacuuming "pgtst_db.pg_toast.pg_toast_32811": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 782, which was 0 XIDs old when operation ended
new relfrozenxid: 782, which is 1 XIDs ahead of previous value
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 4.814 MB/s, avg write rate: 0.000 MB/s
buffer usage: 19 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 188 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
INFO: vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum"
INFO: finished vacuuming "pgtst_db.pgtst_schema.pgtst_vacuum": index scans: 0
pages: 0 removed, 211 remain, 129 scanned (61.14% of total)
tuples: 0 removed, 20064 remain, 0 are dead but not yet removable
removable cutoff: 782, which was 0 XIDs old when operation ended
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
index "pgtst_vacuum_pkey": pages: 83 in total, 0 newly deleted, 26 currently deleted, 26 reusable
avg read rate: 51.502 MB/s, avg write rate: 81.716 MB/s
buffer usage: 304 hits, 75 misses, 119 dirtied
WAL usage: 112 records, 2 full page images, 20384 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
INFO: vacuuming "pgtst_db.pg_toast.pg_toast_32811"
INFO: finished vacuuming "pgtst_db.pg_toast.pg_toast_32811": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 782, which was 0 XIDs old when operation ended
new relfrozenxid: 782, which is 1 XIDs ahead of previous value
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 4.814 MB/s, avg write rate: 0.000 MB/s
buffer usage: 19 hits, 1 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 188 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
pgtst_db=#
Data collection:
pg_class:
table:
relpages | 105 | relpages | 211
reltuples | 10025 | reltuples | 20064
relallvisible | 105 | relallvisible | 211
table:
relpages | 105 | relpages | 211
reltuples | 10025 | reltuples | 20064
relallvisible | 105 | relallvisible | 211
toast:
age | 1 | age | 0
age | 1 | age | 0
pg_stat_user_tables:
table:
n_live_tup | 20025 | n_live_tup | 20064
n_ins_since_vacuum | 10000 | n_ins_since_vacuum | 0
last_vacuum | 2023-01-25 02:27:18.031977-05 | last_vacuum | 2023-01-30 15:06:06.588934-05
vacuum_count | 5 | vacuum_count | 6
table:
n_live_tup | 20025 | n_live_tup | 20064
n_ins_since_vacuum | 10000 | n_ins_since_vacuum | 0
last_vacuum | 2023-01-25 02:27:18.031977-05 | last_vacuum | 2023-01-30 15:06:06.588934-05
vacuum_count | 5 | vacuum_count | 6
Observation: The age of all the tables increases with a TX happening on the db; which was expected.
No comments:
Post a Comment