Shulou(Shulou.com)06/01 Report--

For Update/Delete operations, PostgreSQL's MVCC mechanism still retains previous versions of data, which will be erased during VACUUM. However, if there are active transactions that precede VACUUM operations when executing VACUUM, assuming that the smallest transaction ID of these active transactions is OldestXmin, then VACUUM will not clean up and delete the tuples of transaction ID (that is, xmax) > OldestXmin. If the business is busy and the OldestXmin transaction is not committed, it will cause the storage space to expand until the space is exhausted.

Experimental verification

Data preparation

Create a regular table and insert a row of data

Drop table if exists tincture pagescape create table t_page (id int,c1 char (8), c2 varchar (16)); insert into t_page values (1meme)

Get the data file corresponding to the table

10:26:31 (xdb@ [local]: 5432) testdb=# select pg_relation_filepath ('tweepage'); pg_relation_filepath-- base/16402/50824 (1 row)

Query the space occupied by the data table

10:42:43 (xdb@ [local]: 5432) testdb=#\ set v_tablename t_page10:43:09 (xdb@ [local]: 5432) testdb=# SELECT pg_size_pretty (pg_total_relation_size (: 'vested tablename`)); pg_size_pretty-8192 bytes (1 row)

Process

Session 1 starts the transaction, session 2 executes pg_bench for testing (starts after session 1), session 3 monitors the spatial growth of data tables / executes vacuum

Session 1

10:46:48 (xdb@ [local]: 5432) testdb=# begin;BEGIN10:46:50 (xdb@ [local]: 5432) testdb=#* select txid_current (); txid_current-397083 (1 row) 10:46:54 (xdb@ [local]: 5432) testdb=#*

Session 2 executes pg_bench

[xdb@localhost script] $cat test.sql\ set id random (1Magne10000) begin;update t_page set c1roomc1' | |: id;commit

Session 3 monitors spatial growth of datasheets / executes vacuum

-- Space 10:49:00 (xdb@ [local]: 5432) testdb=# SELECT pg_size_pretty (pg_total_relation_size (: 'vroomtablename`)); pg_size_pretty-192 kB-- > increase from 8K to 192KB (1 row) during stress test-- execute vacuum10:49:16 (xdb@ [local]: 5432) testdb=# vacuum verbose t_page INFO: vacuuming "public.t_page" INFO: "t_page": found 0 removable, 10825 nonremovable row versions in 59 out of 59 pagesDETAIL: 10824 dead row versions cannot be removed yet, oldest xmin: 397083There were 0 unused item pointers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 0.00s, system: 0.00s, elapsed: 0.00s.VACUUM

Output information of vacuum command: 10824 dead row versions cannot be removed yet, oldest xmin: 397083

These tuples cannot be cleared because the deleted xmax > OldestXmin.

Source code analysis

The visibility judgment of tuple to VACUUM is similar to that of tuple to SELECT operation. The visibility judgment function of SELECT query is HeapTupleSatisfiesMVCC, while the visibility judgment function of VACUUM is HeapTupleSatisfiesVacuum, which is called by lazy_scan_heap.

Lazy_scan_heap

The logic of the lazy_scan_heap function has been introduced previously, so I won't go into detail here. Let's briefly sort out the logic related to tuple cleanup.

Static voidlazy_scan_heap (Relation onerel, int options, LVRelStats * vacrelstats, Relation * Irel, int nindexes, bool aggressive) {. For (blkno = 0; blkno)

Num_dead_tuples] = * itemptr; / / vacrelstats- > num_dead_tuples++; lazy_record_dead_tuple (vacrelstats, & (tuple.t_self)); all_visible = false; continue }. / / here, the main purpose is whether a tuple can be visible to all running transactions. Switch (& tuple, OldestXmin, buf) {case HEAPTUPLE_DEAD:... Case HEAPTUPLE_LIVE:... Case HEAPTUPLE_RECENTLY_DEAD: / / these tuples cannot be cleared! Nkeep + = 1; all_visible = false; break;.}}.}

If ItemIdIsDead, record the tuple and proceed to the next tuple; for example, ItemIdIsNormal, call the HeapTupleSatisfiesVacuum function to determine the tuple visibility.

The judgment of ItemIdIsDead is very simple, to determine whether the lp_flags tag of ItemId is LP_DEAD.

((itemId)-> lp_flags = = LP_DEAD)

In fact, when executing update, the lp_flags of ItemId is still 0x01, that is, Normal state.

11:20:49 (xdb@ [local]: 5432) testdb=# select lp,lp_off,lp_flags,t_xmin,t_xmax,t_field3 as tinccidreparing tinctidDifferent infomask2 revising tincture infomask from heap_page_items (get_raw_page ('ttransipageReclamation 0)) Lp | lp_off | lp_flags | t_xmin | t_xmax | t_cid | t_ctid | t_infomask2 | t_infomask-+-- -1 | 8152 | 1 | 457343 | 457343 | 0 | (0Magazine 1) | 3 | 10642 2 | 8112 | 1 | 457342 | 457343 | 0 | 94743 | 8072 | 1 | 457341 | 457342 | 0 | (0Power2) | 3 | 9474.

View the information of tuple 3 in this block

[xdb@localhost pg111db] $hexdump-C base/16402/50831-s 32-n 200000020 88 9f |.. | 00000022 [xdb@localhost pg111db] $hexdump-C base/16402/50831-s 34-n 200000022 4e 00 | N. | 000024

Calculate offset / size / marker

[xdb@localhost pg111db] $# offset [xdb@localhost pg111db] $echo $((0x9f88 & ~ $(11)) 39 [xdb@localhost pg111db] $# lp_ 's [XDB @ localhost pg111db] $echo $((0x004e & 0x0001)) [xdb@localhost pg111db] $echo $((0x9f88 > > 15)) 1

Lp_flags=0x01, or LP_NORMAL

Next, a brief description of the implementation logic of HeapTupleSatisfiesVacuum, which determines the visibility of tuples to VACUUM operations.

HeapTupleSatisfiesVacuum

HTSV_ResultHeapTupleSatisfiesVacuum (HeapTuple htup, TransactionId OldestXmin, Buffer buffer) {... If (! HeapTupleHeaderXminCommitted (tuple)) {/ / xmin transaction not committed.} / / does not meet the above conditions, you can determine that the xmin transaction committed if (tuple- > t_infomask & HEAP_XMAX_INVALID) / / xmax is an invalid transaction ID return HEAPTUPLE_LIVE;. If (! (tuple- > t_infomask & HEAP_XMAX_COMMITTED)) {/ / xmax transaction is not committed.} / / does not meet the above conditions, you can determine that the xmax transaction has been committed if (! TransactionIdPrecedes (HeapTupleHeaderGetRawXmax (tuple), OldestXmin)) / / 6. Tuple xmax ≥ OldestXmin, marked as recently deleted return HEAPTUPLE_RECENTLY_DEAD;...

Tuples xmax ≥ OldestXmin, marked HEAPTUPLE_RECENTLY_DEAD, these tuples cannot be cleaned!

The reason why long transactions need to be avoided is that if the OldestXmin transaction is not committed, then the tuples that are subsequently deleted are retained and cannot be cleared through VACUUM.

In the above case, after the stress test is completed, the space is 330 times larger and cannot be cleaned up through VACUUM (including VACUUM FULL)!

10:50:13 (xdb@ [local]: 5432) testdb=# SELECT pg_size_pretty (pg_total_relation_size (: 'row')); pg_size_pretty-2640 kB (1 row) 11:01:28 (xdb@ [local]: 5432) testdb=# vacuum verbose t_page INFO: vacuuming "public.t_page" INFO: "t_page": found 0 removable, 60255 nonremovable row versions in 326 out of 326 pagesDETAIL: 60254 dead row versions cannot be removed yet, oldest xmin: 397083There were 0 unused item pointers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 0.02s, system: 0.00s, elapsed: 0.02s.VACUUM11:01:31 (xdb@ [local]: 5432) testdb=# 11:10:14 (xdb@ [local]: 5432) testdb=# vacuum full VACUUM11:17:56 (xdb@ [local]: 5432) testdb=# SELECT pg_size_pretty (pg_total_relation_size (: 'vroomtablename`)); pg_size_pretty-2640 kB (1 row)

Application suggestion

In practical application, long transactions should be avoided as much as possible, and batch operations should be arranged to be carried out during off-peak hours as far as possible. If it is determined to be a read-only transaction, it is recommended to turn on autocommit.

For Java applications and connection pooling is enabled, for example, if JDBC is set to automatically commit to false, even if it is a select operation, be sure to execute commit after execution (Oracle is not required, but PG needs!).

references

PostgreSQL Source Code

Interpretation of PostgreSQL Source Code-MVCC#18 (vacuum procedure-HeapTupleSatisfiesVacuum function)

Interpretation of PostgreSQL Source Code (130)-MVCC#14 (vacuum procedure-lazy_scan_heap function)

PostgreSQL Source Code interpretation (118)-MVCC#3 (Tuple visibility judgment)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.