您好,登錄后才能下訂單哦!
這篇文章主要為大家展示了“怎么從零學習PostgreSQL Page結構”,內容簡而易懂,條理清晰,希望能夠幫助大家解決疑惑,下面讓小編帶領大家一起研究并學習一下“怎么從零學習PostgreSQL Page結構”這篇文章吧。
pg中的page和Oracle中的數據塊一樣,指的是數據庫的塊,操作系統(tǒng)塊的整數倍個,默認是8K也就是兩個操作系統(tǒng)塊(4k的文件系統(tǒng)塊)。這個大小在pg編譯安裝configure的時候通過--with-blocksize參數指定,單位是Kb。
2.2 PageHeaderData數據結構 (頁頭)
可以看到一個Page有 Pager header(頁頭),后面是linp(行指針),pd_lower和pd_upper分別是空閑空間的開始位置和結束位置;后面就是行數據(pg里面的行就是tuple)和special空間。整個page的結構比Oracle的數據塊結構簡單多了。
typedef struct PageHeaderData
{
/* XXX LSN is member of *any* block, not only page-organized ones */
PageXLogRecPtr pd_lsn; /* LSN: next byte after last byte of xlog
* record for last change to this page */
uint16 pd_checksum; /* checksum */
uint16 pd_flags; /* flag bits, see below */
LocationIndex pd_lower; /* offset to start of free space */
LocationIndex pd_upper; /* offset to end of free space */
LocationIndex pd_special; /* offset to start of special space */
uint16 pd_pagesize_version;
TransactionId pd_prune_xid; /* oldest prunable XID, or zero if none */
ItemIdData pd_linp[FLEXIBLE_ARRAY_MEMBER]; /* line pointer array */
} PageHeaderData;
具體的長度和描述也都有詳細說明:
Field | Type | Length | Description |
pd_lsn | PageXLogRecPtr | 8 bytes | LSN: next byte after last byte of WAL record for last change to this page |
pd_checksum | uint16 | 2 bytes | Page checksum |
pd_flags | uint16 | 2 bytes | Flag bits |
pd_lower | LocationIndex | 2 bytes | Offset to start of free space |
pd_upper | LocationIndex | 2 bytes | Offset to end of free space |
pd_special | LocationIndex | 2 bytes | Offset to start of special space |
pd_pagesize_version | uint16 | 2 bytes | Page size and layout version number information |
pd_prune_xid | TransactionId | 4 bytes | Oldest unpruned XMAX on page, or zero if none |
簡單來說,pd_lsn是指最后修改過這個page的lsn(log sequence number),這個和wal(write ahead log,同oracle redo)中記錄的lsn一致。數據落盤時redo必須先刷到wal,這個pd_lsn就記錄了最后data落盤時的相關redo的lsn。
pd_checksum是校驗和,在initdb初始化實例的時候通過-k參數指定開啟,默認是關閉的,initdb之后不能修改,它基于FNV-1a hash算法,做了相應的更改。這個校驗和與Oracle的checksum一樣用于數據塊在讀入和寫出內存時的校驗。比如我們在內存中修改了一個數據塊,寫入到磁盤的時候,在內存里面先計算好checksum,數據塊寫完后再計算一遍cheksum是否和之前在內存中的一致,確保整個寫出過程沒有出錯,保護數據結構不被破壞。
pd_flags有以下的值:
/*
* pd_flags contains the following flag bits. Undefined bits are initialized
* to zero and may be used in the future.
*
* PD_HAS_FREE_LINES is set if there are any LP_UNUSED line pointers before
* pd_lower. This should be considered a hint rather than the truth, since
* changes to it are not WAL-logged.
*
* PD_PAGE_FULL is set if an UPDATE doesn't find enough free space in the
* page for its new tuple version; this suggests that a prune is needed.
* Again, this is just a hint.
*/
#define PD_HAS_FREE_LINES 0x0001 /* are there any unused line pointers? */
#define PD_PAGE_FULL 0x0002 /* not enough free space for new tuple? */
#define PD_ALL_VISIBLE 0x0004 /* all tuples on page are visible to
* everyone */
#define PD_VALID_FLAG_BITS 0x0007 /* OR of all valid pd_flags bits */
pd_lower和pd_upper分別表示空閑空間起始位置和結束位置;pd_special在索引page才有效;pd_pagesize_version是page大小和page version的存儲位,在不同數據庫版本中,page version不一樣:
數據庫版本 | pd_pagesize_version | ||
<7.3 | 0 | ||
7.3 & 7.4 | 1 | ||
8.0 | 2 | ||
8.1 | 3 | ||
>8.3 | 4 |
prune_xid表示這個page上最早刪除或者修改tuple的事務id,在vacuum操作的時候會用到。(pg沒有undo,舊的數據也在page中,用vacuum來清理)
lp_off是tuple的開始的偏移量;lp_flags是標志位;lp_len記錄了tuple的長度。
Field | Length | Description |
lp_off | 15 bits | offset to tuple |
lp_flags | 2 bits | State of iteam pointer |
lp_len | 15 bits | Byte length of tuple |
typedef struct HeapTupleFields { TransactionId t_xmin; /* inserting xact ID */ TransactionId t_xmax; /* deleting or locking xact ID */ union { CommandId t_cid; /* inserting or deleting command ID, or both */ TransactionId t_xvac; /* old-style VACUUM FULL xact ID */ } t_field3; } HeapTupleFields;
typedef struct DatumTupleFields { int32 datum_len_; /* varlena header (do not touch directly!) */ int32 datum_typmod; /* -1, or identifier of a record type */ Oid datum_typeid; /* composite type OID, or RECORDOID */
/* * Note: field ordering is chosen with thought that Oid might someday * widen to 64 bits. */ } DatumTupleFields;
struct HeapTupleHeaderData { union { HeapTupleFields t_heap; DatumTupleFields t_datum; } t_choice;
ItemPointerData t_ctid; /* current TID of this or newer tuple (or a * speculative insertion token) */ /* Fields below here must match MinimalTupleData! */ uint16 t_infomask2; /* number of attributes + various flags */ uint16 t_infomask; /* various flag bits, see below */ uint8 t_hoff; /* sizeof header incl. bitmap, padding */ /* ^ - 23 bytes - ^ */ bits8 t_bits[FLEXIBLE_ARRAY_MEMBER]; /* bitmap of NULLs */ /* MORE DATA FOLLOWS AT END OF STRUCT */ }; (*這部分代碼在src/include/access/htup_details.h) |
也有對應的長度和描述的相詳細說明:
Field | Type | Length | Description |
t_xmin | TransactionId | 4 bytes | insert XID stamp |
t_xmax | TransactionId | 4 bytes | delete XID stamp |
t_cid | CommandId | 4 bytes | insert and/or delete CID stamp (overlays with t_xvac) |
t_xvac | TransactionId | 4 bytes | XID for VACUUM operation moving a row version |
t_ctid | ItemPointerData | 6 bytes | current TID of this or newer row version |
t_infomask2 | uint16 | 2 bytes | number of attributes, plus various flag bits |
t_infomask | uint16 | 2 bytes | various flag bits |
t_hoff | uint8 | 1 byte | offset to user data |
union是共享結構體,起作用的變量是最后一次賦值的成員。來看看tuple header的結構。
在HeapTupleFields中,t_xmin是插入這行tuple的事務id;t_xmax是刪除或者鎖住tuple的事務id;union結構中的t_cid是刪除或者插入這個tuple的命令id,也就是命令序號;t_xvac是以前格式的vacuum full用到的事務id。
在DatumTupleFields中,datum_len_ 指tuple的長度;datum_typmod是記錄的type;datum_typeid是記錄的id。
頁頭HeapTupleHeaderData包含了union結構體中的兩個變量HeapTupleFields和DatumTupleFields。t_ctid是tuple id,類似oracle的rowid,形式為(塊號,行號)。
t_infomask2 表示屬性和標志位
t_infomask 是flag標志位,具體值如下:
/* * information stored in t_infomask: */ #define HEAP_HASNULL 0x0001 /* has null attribute(s) */ #define HEAP_HASVARWIDTH 0x0002 /* has variable-width attribute(s) */ #define HEAP_HASEXTERNAL 0x0004 /* has external stored attribute(s) */ #define HEAP_HASOID 0x0008 /* has an object-id field */ #define HEAP_XMAX_KEYSHR_LOCK 0x0010 /* xmax is a key-shared locker */ #define HEAP_COMBOCID 0x0020 /* t_cid is a combo cid */ #define HEAP_XMAX_EXCL_LOCK 0x0040 /* xmax is exclusive locker */ #define HEAP_XMAX_LOCK_ONLY 0x0080 /* xmax, if valid, is only a locker */
/* xmax is a shared locker */ #define HEAP_XMAX_SHR_LOCK (HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK)
#define HEAP_LOCK_MASK (HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK | \ HEAP_XMAX_KEYSHR_LOCK) #define HEAP_XMIN_COMMITTED 0x0100 /* t_xmin committed */ #define HEAP_XMIN_INVALID 0x0200 /* t_xmin invalid/aborted */ #define HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID) #define HEAP_XMAX_COMMITTED 0x0400 /* t_xmax committed */ #define HEAP_XMAX_INVALID 0x0800 /* t_xmax invalid/aborted */ #define HEAP_XMAX_IS_MULTI 0x1000 /* t_xmax is a MultiXactId */ #define HEAP_UPDATED 0x2000 /* this is UPDATEd version of row */ #define HEAP_MOVED_OFF 0x4000 /* moved to another place by pre-9.0 * VACUUM FULL; kept for binary * upgrade support */ #define HEAP_MOVED_IN 0x8000 /* moved from another place by pre-9.0 * VACUUM FULL; kept for binary * upgrade support */ #define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN)
#define HEAP_XACT_MASK 0xFFF0 /* visibility-related bits */ |
t_hoff表示tuple header的長度
t_bits記錄了tuple中null值的列
它在源碼的crontrib目錄下面
postgres@cs-> cd postgresql-10.4/contrib/pageinspect
make && make install postgres@cs-> make postgres@cs-> make install
create extension就好了 postgres@cs-> psql psql (10.4) Type "help" for help.
postgres=# CREATE EXTENSION pageinspect; CREATE EXTENSION
postgres=# \x Expanded display is on. postgres=# \dx List of installed extensions -[ RECORD 1 ]------------------------------------------------------ Name | pageinspect Version | 1.6 Schema | public Description | inspect the contents of database pages at a low level -[ RECORD 2 ]------------------------------------------------------ Name | plpgsql Version | 1.0 Schema | pg_catalog Description | PL/pgSQL procedural language |
3.2 創(chuàng)建建測試表t1,插入數據
這里可以看到1000行數據用了6個數據塊來存儲(這里數據塊從0開始),第6個數據塊包含了73條記錄(tuple)
這里我們通過兩個函數來查看
page_header 可以看到頁頭的數據
heap_pageitems 可以看到具體tuple的數據
postgres=# \xExpanded display is on.postgres=# select * from page_header(get_raw_page('t1',0));-[ RECORD 1 ]--------lsn | 0/1671188checksum | 0flags | 0lower | 772upper | 784special | 8192pagesize | 8192version | 4prune_xid | 0postgres=# |
可以看到第0個page的pd_lsn為0/1671188,checksum和flags都是0,這里沒有開啟checksum;tuple開始偏移是772(pd_lower),結束偏移是784(pd_upper),這個page是個表,所以它沒有special,我們看到的sepcial就是8192了;pagesize是8192就是8K,version是4,沒有需要清理的tuple,所以存儲需要清理的tuple的最早事務的id就是0(prune_xid)。
我們來看一行記錄,可以看到它是第1行記錄(lp=1),tuple的開始偏移量8160(lp_off),tuple的長度是32 bytes(lp_len為32,這個tuple是第一個插入的tuple,所以lp_off+lp_len=8160+32=8192),這行記錄的插入事務id是557(t_min),和tuple的刪除事務id是0(tmax),這里數據沒有被刪除,所以都是0。我們還可以看到t_ctid是(0,1),這里表示這個tuple是這個page中第一個塊的第一條tuple;tinfomask2是2,t_infomask為2306,十六進制就是 0x0902 ,這個我們可以根據上面提到的值去看看具體的含義,0x0902 = 0x0100 + 0x0800 +0x0002;tuple頭部結構(行頭)的長度是24(t_hoff),t_data就是16進制存儲的真正的數據了。
我們刪除一行tuple可以看到prune_xid有了值,為559,這個559就是刪除這個tuple的事務id(當前最早的刪除或更改了tuple的事務id)
同樣,我們可以看到lp為1的這個tuple的t_xmax為559,這里就是刪除這行tuple的事務id。
PostgreSQL Page的物理結構相比Oracle的數據塊來說簡單很多了,源代碼開放也便于學習和研究,pg是個很好很強大的數據庫,值得好好學習。
以上是“怎么從零學習PostgreSQL Page結構”這篇文章的所有內容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內容對大家有所幫助,如果還想學習更多知識,歡迎關注億速云行業(yè)資訊頻道!
免責聲明:本站發(fā)布的內容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。