No image

Let's Study Vacuum 3

Part 3. Analyze VM and FSM structure

Vacuum Index

Part1: Definition and Necessity of Vacuum / Vacuum Execution Structure / Standard Vacuum vs. Vacuum full

Part2: XID Wraparound, Freeze / Autovacuum 

Part3: VM, FSM structure analysis

 

5.Visibility Map (= bitmap index)

  • Each heap relation tracks through the VM which pages contain only tuples that all appear to be active transactions.
  • It is stored along the main relation data, which is followed by a suffix ' _vm' after the filenode number.

           For example, if the filenode of the relation is '123', the vm created in '123_vm' form.

  • Stores 1bit per heap page.
  • Bit is set by the vacuum operation and can be cleared by the data change operation on page.

Data page in Table A

Visibility Map for Table A (bitmap)

  • Page 1 shows '1' without dead tuple, so there is no need for vacuum operation.
  • Page 2 shows '0' because there is a deleted tuple.
  • The newly added tuple at update time has no dead tuple but is marked as '0'.
  • There are all tuples on page 1 in Index-Only Scanning.

VM Layout

  • The visibility map simply stores one bit per heap page.
  • Visibility map bits are only set by vacuum, but are cleared by any data-modifying operations on a page.
  • Save one bit per Byte in Little-endian order (save from subordinate bit first)

insert into  t2 SELECT 'A' || generate_series(1000,1000+120-1),'A','A';
vacuum t2 ;
  • 120 inserts in t2 table (created 24 pages, 5cases per page )
  • Launch Vacuum
  • vm page Dump -> This is all Visible, so "1111111111111111111111" (fff ff ff - hexa )
delete from t2 where c1 = 'A1000' ;
  • Delete the first record on page 0 * cctid (0,1 ) : 1st record on page 0
  • vm page Dump -> Page 0 has dead tuple, so the number 8 bit of Byte 1 is clear
  • “1111 1110 1111 1111 1111 1111”( fe ff ff – hexa )
delete from t2 where c1 = 'A1025' ;
  • Delete the first record on page 5
  • vm page Dump -> Page 5 has dead tuple, so the number 3 bit of Byte 1 is clear
  • “1101 1110 1111 1111 1111 1111”( de ff ff – hexa )
delete from t2 where c1 = 'A1050' ;
  • Delete the first record on page 10
  • vm page Dump -> The dead tuple occurred on page 10, so the number 6 bit of Byte 2 is clear.
  • “1101 1110 1111 1011 1111 1111”( de fb ff – hexa )
delete from t2 where c1 = 'A1075' ;
  • Delete the first record on page 15
  • vm page Dump -> The dead tuple occurred on page 15, so the number 1 bit of Byte 2 is clear.
  • “1101 1110 1111 1011 1111 1111”( de fb ff – hexa )
delete from t2 where c1 in ( 'A1080','A1085' ) ;
  • Delete the first record from page 16, delete the first record from page 17.
  • vm page Dump ->Page 16 and 17 had dead tuple, so the number 3 Byte's number 7 and 8 bits were clear.
  • "1101 1110 0111 10111111100" (de 7b fc – hexa )
delete from t2 where c1 in ( 'A1100','A1105','A1110' ) ;
  • Delete the first record from page 20, delete the first record from page 21, delete the first record from page 22.
  • vm page Dump -> page 20,21 and 22 had dead tuple, so the bits 2, 3 and 4 of Byte were clear.
  • “1101 1110 0111 1011 1000 1100”( de 7b 8c – hexa )

Visibility Map

  • Add frozen bit in PostgreSQL version 9.6 (change the xmin value to 2 until version 9.3)
  • When working on Anti-Wraparound Vacuum, the page set to '1' is Skip.
  • Advantages: Vacuum performance improvement
  • Shortcoming: 2x increase in vm file size
  1. Results of vm, check after Vacuum operation is completed 55(Hexa Code) -> 01010101
  2. Results of vm, check after Vacuum Freeze operation is completed ff(Hexa Code) -> 11111111

6. Free Space Map (= Freelist) 

  • Each heap and index relation tracks the available space of the relation through fsm.
  • It is stored along the main relation data, which is followed by a suffix '_fsm' after the filenode number.

           For example, if the filenode of the relax is '123', fsm is generated in '123_fsm' form.

  • Each fsm page consists of a binary tree, 1byte per node
  • Each leaf node represents a heap page or a fsm page at a lower level
  • Store free space on each heap page on the fsm page at the lower level
  • Roots are stored at maximums because the higher level is information collected from lower levels

  • 28 (header) + 8191 (total of 13 levels) = 8219 bytes
  • 8219 - 8192 (8k) = over 27 bytes 
  • 27 bytes short of 4096 bytes (the last level) = 4069 bytes, i.e. 4069 blocks can be managed per page
  • If FSM is exceeded, another page is created.

FSM Layout

  • Use 1byte per block to keep the space divided by ‘BLCKSZ/ 256’ (1byte = 8bit = 28 =256)
  • FSM value = Free space / (BLCKSZ/ 256) ex) 1024 / (8192/256) = 32

Create Table and Insert 0 21

  • Seven records are loaded on one page.
  • Inserting 21 records creates three pages, and only two are visible on the FSM.

Vacuum

  • Once Vacuum work is processed, it is reflected in the FSM.

DELETE 2

  • If two cases are deleted, they will not be reflected in the FSM.

Vacuum

  • Once Vacuum work is processed, it is reflected in the FSM.

UPDATE 19

  • If you update 19 cases, the number of pages will increase to 6. But this is not reflected in the FSM. (5 cases)

Vacuum

  • Once Vacuum work is processed, it is reflected in the FSM. (Six confirmed) - p61
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]