Monday, January 30, 2023

What happens to the other table's relfrozenxid & its Tx age when we update our candidate table

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

Toast:
age                 | 44                                      | age                 | 45

No other changes.

Candidate table - pgtst_vacuum:

Age changed.. pg_class view:

Table:
age                 | 9                                       | age                 | 10

Toast:
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

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
pgtst_db=#

Data collection:

pg_class:
table:
relpages            | 105                                     | relpages            | 211
reltuples           | 10025                                   | reltuples           | 20064
relallvisible       | 105                                     | relallvisible       | 211

toast:
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


Observation: The age of all the tables increases with a TX happening on the db; which was expected.

No comments:

Post a Comment

Troubleshooting the “Cannot Generate SSPI Context” Error After SQL Server Migration

  Introduction After a recent  SQL Server migration from 2012 to 2022 , our team encountered a classic authentication issue: The target prin...