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

This paper introduces the reason for the "surge" of data table space caused by using pageinspect plug-in to analyze Update data table in the case of long transaction (open transaction, never commit / rollback).

1. Test scenario

Start session Session B using psql

Testdb=#-- Session Btestdb=#-- Open transaction testdb=# begin;BEGINtestdb=# testdb=# select txid_current (); txid_current-1600322 (1 row) testdb=#-create a table & insert 100 rows of data testdb=# drop table if exists T1 / drop TABLEtestdb=# create table T1 (id int,c1 varchar (50)) CREATE TABLEtestdb=# insert into T1 select generate_series (1100),'# abcd#';INSERT 0 100testdb=# select txid_current (); txid_current-1600322 (1 row) testdb=# select count (*) from T1; count-100 (1 row) testdb=# testdb=#-commit transaction testdb=# end;COMMITtestdb=#

Open a new Console creation and start the session Session A using psql

Testdb=#-- Session Atestdb=#-- enable transaction testdb=# begin;BEGINtestdb=# testdb=#-- query current transaction testdb=# select txid_current (); txid_current-1600324 (1 row) testdb=# testdb=#-- do nothing

Although nothing is done, Session A can still start a transaction where it is not committed.

Go back to Session B and view the data in datasheet T1:

Testdb=#-- Session Btestdb=#-- View data tables testdb=# select ctid, xmin, xmax, cmin, cmax,id from T1 limit 8 Ctid | xmin | xmax | cmin | cmax | id-+-(0Magne1) | 1600322 | 0 | 4 | 4 | 1 (0Mague 2) | 1600322 | 0 | 4 | 4 | 2 (0Mague 3) | 1600322 | 0 | 4 | 4 | 3 (0Magazine 4) | 1600322 | 0 | 4 | 4 | 4 | 4 (0 rows 5) | 1600322 | 0 | 4 | 4 | 5 (0 rows 6) | 1600322 | 0 | 4 | 4 | 6 (0 Mague 7) | 1600322 | 0 | 4 | 4 | 7 (0 Force 8) | 1600322 | 0 | 4 | 8 (8) set v_tablename t1testdb=# SELECT pg_size_pretty-View data occupied space testdb=#\ set v_tablename t1testdb=# SELECT pg_size_pretty (pg_total_relation_size (: 'venertablename`)) Pg_size_pretty-8192 bytes (1 row) testdb=#-- page_headertestdb=# SELECT * FROM page_header (get_raw_page ('t _ 1, 0)) Lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid-+-1/4476E4A0 | | 0 | 4192 | 4192 | 8192 | 8192 | 4 | 0 (1 row)

Then open a Shell window and use pgbench to continuously update T1, and analyze the data during this process.

[xdb@localhost benchmark] $cat update.sql\ set rowid random (1100) begin;update T1 set c1=:rowid where id=: rowid;end; [xdb@localhost benchmark] $pgbench-c 2-C-f. / update.sql-j 1-n-T 600-U xdb testdb II. Data analysis

Let's analyze the data in the T1 data page through the pageinspect plug-in.

Testdb=#\ set v_tablename t1testdb=# testdb=# SELECT pg_size_pretty (pg_total_relation_size (: 'vroomtablename`)); pg_size_pretty-160 kB (1 row) testdb=#-View the header data and user data of page 0 of the data testdb=# SELECT * FROM page_header (get_raw_page (' t1records, 0)) Lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid-+-+-1ACT 44787990 | 0 | 2 | 840 | 864 | 8192 | 8192 | 4 | 1600325 (1 row) testdb=# select * from heap_page_items (get_raw_page ('T1') 0) limit 10 Lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data-+ -+-- 1 | 8152 | 1 | 35 | 1600322 | 1600365 | 0 | (0141) | 16386 | 1282 | 24 |\ x010000000f236162636423 2 | 8112 | 1 | 35 | 1600322 | 1600325 | 0 | (0101) | 16386 | 1282 | 24 |\ x020000000f236162636423 3 | 8072 | 1 | 35 | 1600322 | 1600421 | 0 | (0197) | 16386 | 1282 | 24 | | |\ x030000000f236162636423 4 | 8032 | 1 | 35 | 1600322 | 1600435 | 0 | (1 x050000000f236162636423 7) | 2 | 1282 | 24 |\ x050000000f236162636423 5 | 7992 | 1 | 35 | 1600322 | 1600474 | 0 | (1Magazine 46) | 2 | 1282 | 24 |\ x050000000f236162636423 6 | 7952 | 1 | 35 | 1600322 | 1600538 | 0 | (1110) | 2 | 1282 | 24 |\ x060000000f236162636423 7 | 7912 | 1 | 35 | 1600322 | 1600396 | 0 | (0172) | 16386 | 1282 | 24 |\ x070000000f236162636423 8 | 7872 | 1 | 35 | 1600322 | 1600331 | 0 | (0107) | 16386 | 1282 | 24 |\ x080000000f236162636423 9 | 7832 | 1 | 35 | 1600322 | 1600531 | 0 | (1103) | 2 | 1282 | 24 |\ x090000000f236162636423 10 | 7792 | 1 | 35 | 1600322 | 1600413 | 0 | (0189) | 16386 | 1282 | 24 |\ x0a0000000f236162636423 (10 rows) testdb=#-check the space occupied testdb=# SELECT pg_size_pretty again (pg_total_relation_size (: 'vroomtablename`)) Pg_size_pretty-360 kB (1 row)

As you can see, the footprint of the data table has been growing, far beyond the scope of a data page. At the same time, we notice that some of the values in t_xmax, t_infomask2, and t_infomask are different from those of the previously inserted data for the first time.

T_xmax

The value > 0 indicates that the row of data is obsolete, and the value is the transaction number of the delete/update operation.

T_infomask2

The value is 16386, which is converted to hexadecimal display:

[xdb@localhost benchmark] $echo "obase=16;16386" | bc4002

The first (lower) 11 bits represent the number of attributes, with a value of 2, which means that the data table has 2 attributes (fields);\ x4000 indicates HEAP_HOT_UPDATED. The official explanation is as follows:

An updated tuple, for which the next tuple in the chain is a heap-only tuple. Marked with HEAP_HOT_UPDATED flag.

T_infomask

The value is 1282, which is converted to hexadecimal display:

[xdb@localhost benchmark] $echo "obase=16;1282" | bc502

\ 0x0502 = HEAP_XMIN_COMMITTED | HEAP_XMAX_COMMITTED

This means that both the transaction that inserts the data and the transaction that updates (or deletes) has been committed.

III. Space recovery

Data table T1 regardless of how Update, the actual number of data rows is only 100 rows, the size is far less than 8K, but why does it take up hundreds of KB of space? The reason is that PG retains the pre-update "junk" data for the sake of MVCC (multi-version concurrency control), which can be cleaned up periodically through the vacuum mechanism. However, in this case, the garbage data cannot be cleaned up properly due to the existence of a "long" transaction.

Testdb=# SELECT pg_size_pretty (pg_total_relation_size (: 'row tablename`)); pg_size_pretty-472TestDB (1 row) testdb=# vacuum T1 / VACUUMtestDB SELECT pg_size_pretty (pg_total_relation_size (:' Vroomtablename`)); pg_size_pretty-472TestDB (1 row) testdb=# vacuum full VACUUMtestdb=# SELECT pg_size_pretty (pg_total_relation_size (: 'row tablename`)); pg_size_pretty-472 kB (1 row)

Neither the commands vacuum T1 nor vacuum full can properly recycle garbage data because PG believes that the garbage data is visible to active transactions (Session A).

Let's recall that the transaction number of Session A: 1600324 session B when inserting data: 1600322, and the transaction number when updating data > 1600324 session A (active transaction) query T1, the "consistency" read is achieved through PG's snapshot mechanism. The snapshot of PG can be obtained through the txid_current_snapshot function:

Testdb=# select txid_current_snapshot (); txid_current_snapshot-1600324 row 1612465 purl 1600324

The return value is divided into three parts, namely xin, xmax, and xip_list:

Format: xin:xmax:xip_list

Xin:Earliest transaction ID (txid) that is still active. The smallest XID of uncommitted and active transactions

Xmax:First as-yet-unassigned txid. All txids greater than or equal to this are not yet started as of the time of the snapshot, and thus invisible. The largest XID + 1 of all committed transactions

Xip_list:Active txids at the time of the snapshot. All of them are between xmin and xmax. All records of A txid that is xmin activity transaction number xid (1600324) > xin cannot be recycled!

Testdb=#-Session Btestdb=# vacuum T1 / VACUMTestDB # SELECT pg_size_pretty (pg_total_relation_size (: 'row tablename`)); pg_size_pretty-472 kB (1 row) testdb=# vacuum full;VACUUMtestdb=# SELECT pg_size_pretty (pg_total_relation_size (:' vroomtablename')) Pg_size_pretty-472kB (1 row)

On the contrary, after the active transaction is committed, the space occupied by junk data can be recycled normally:

Testdb=#-- Session Atestdb=#-- end transaction testdb=# end;COMMIT

Execute the vacuum command to collect garbage data:

Testdb=#-Session Btestdb=# vacuum T1 / VACUMTestDB # SELECT pg_size_pretty (pg_total_relation_size (: 'row tablename`)); pg_size_pretty-472 kB (1 row) testdb=# vacuum full;VACUUMtestdb=# SELECT pg_size_pretty (pg_total_relation_size (:' vroomtablename')) Pg_size_pretty-8192 bytes (1 row)

Data cannot be recycled through the vacuum T1 command? Why? Please note the t_infomask2 flag HEAP_HOT_UPDATED. To put it simply, data in update chain will not be recycled. Since the HOT mechanism is involved, it will be parsed later in detail.

IV. Summary

There are three main points to be summarized:

1. Keep the original data: PG has no rollback segment, and there is no real update or deletion during the update / delete operation. Instead, it retains the original data and cleans up the garbage data through the vacuum mechanism when appropriate.

2. Avoid long transactions: in order to avoid the surge of junk data, transactions should be committed as soon as possible if business logic allows, so as to avoid the occurrence of long transactions.

3. Query operation: use JDBC driver or other drivers to connect to PG. If you clearly know that only query operations are performed, please enable automatic transaction submission.

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.