PostgreSQL 元組統計與 pgstattuple 最佳化

pgccc發表於2023-11-29
SQL
PostgreSQL 元組統計與 pgstattuple 最佳化
新增圖片註釋，不超過 140 字（可選）

第1章 簡介

1.1 參考文件

https://www.percona.com/blog/postgresql-tuple-level-statistics-with-pgstattuple/

1.2 關於pgstattuble

由於Postgres表膨脹會降低資料庫效能，因此我們可以透過消除表膨脹來提高其效能。我們可以使用pgstattuple擴充套件來識別膨脹的表。
這個擴充套件提供了幾個函式來獲取元級統計資訊。因為pgstattuple函式產生大量的頁面級資訊，所以預設情況下對它們的訪問是有限的。預設情況下，只有pg_stat_scan_tables角色有能力執行pgstattuple函式。
使用pgstattuple函式，我們可以列出死元組百分比高的表，並執行手動VACUUM來回收死元組佔用的空間。

第2章 pgstattuble 外掛安裝 & 測試

2.1 PG15 & pg_tde編譯

2.1.1 作業系統資訊

我的作業系統是CentOS 8.5，其核心資訊如下
[root@pg-server01 ~]# uname -aLinux pg-server01 4.18.0-348.el8.x86_64 #1 SMP Tue Oct 19 15:14:17 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux[root@pg-server01 ~]# cat /etc/redhat-release CentOS Linux release 8.5.2111

2.1.2 配置阿里雲 yum 源

# 參考如下連結 # 操作步驟略
https://www.cnblogs.com/hunttown/p/16287988.html

2.1.3 安裝相關依賴

yum -y install gcc readline readline-devel zlib-devel openssl-devel libicu-devel make json-c-devel git wget

2.1.4 下載PG15 原始碼&編譯

mkdir -p /data/software/pg/;cd /data/software/pg/;wget \--prefix=/opt/pgsql \--with-openssl make && make install

2.1.5 編譯 pgstattuble 外掛

cd /data/software/pg/postgresql-15.4/;cd ./contrib/pgstattuple;make && make install

2.1.6 編譯 btree_gin 外掛

cd /data/software/pg/postgresql-15.4/;cd ./contrib/btree_gin;make && make install

2.2 安裝配置PG

2.2.1 新增 postgres使用者並授權

useradd postgres chown -R postgres:postgres /home/postgres;chown -R postgres:postgres /opt/pgsql;

2.2.2 配置環境變數

vi /etc/profile # 新增如下內容# pg envexport PGHOME=/opt/pgsqlexport PATH=$PATH:$PGHOME/bin

2.2.3 初始化資料庫

# 1. 建立相關目錄，並授權
mkdir -p /data/pgdata/;chown postgres:postgres /data/pgdata/;
# 2. 切換到 postgres 使用者
su - postgres
# 3. 初始化資料庫
initdb -D /data/pgdata/ -U postgres --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8
# 4. 啟動資料庫
pg_ctl -D /data/pgdata/ start

2.3 測試 pgstattuble 外掛

2.3.1 開啟 pgstattuble 外掛

su - postgrespsqlselect * from pg_available_extensions where name='pgstattuple';create extension pgstattuple;select * from pg_available_extensions where name='pgstattuple'; # 我的操作輸出類似如下：[root@pg-server01 opt]# su - postgres[postgres@pg-server01 ~]$ psqlpsql (15.4)Type "help" for help. postgres=# select * from pg_available_extensions where name='pgstattuple';    name     | default_version | installed_version |           comment           -------------+-----------------+-------------------+----------------------------- pgstattuple | 1.5             |                   | show tuple-level statistics(1 row) postgres=# create extension pgstattuple;CREATE EXTENSIONpostgres=# select * from pg_available_extensions where name='pgstattuple';    name     | default_version | installed_version |           comment           -------------+-----------------+-------------------+----------------------------- pgstattuple | 1.5             | 1.5               | show tuple-level statistics(1 row) postgres=#
# 注意：預設情況下，只有超級使用者可以訪問 pgstattuple 函式; 但是，您可以透過將 pg_stat_scan_tables 角色授予非超級使用者來授予非超級使用者訪問許可權。
grant pg_stat_scan_tables to <nonsuperuser>;

2.3.2 pgstattuble測試前準備

接下來，在使用 pgstattuple 函式之前，讓我們建立一個表和索引來進行演示。
psqlcreate table workshop (jobno int);insert into workshop values (generate_series(1,80000));create index workshop_index on workshop (jobno);
# 我的操作輸出類似如下：
[postgres@pg-server01 ~]$ psqlpsql (15.4)Type "help" for help.postgres=# create table workshop (jobno int);CREATE TABLEpostgres=# insert into workshop values (generate_series(1,80000));INSERT 0 80000postgres=# create index workshop_index on workshop (jobno);CREATE INDEXpostgres=#

2.3.3 pgstattuple 功能

2.3.3.1 pgstattuple(regclass)

這個函式返回一個關係的物理長度，“死亡”元組的百分比，以及其他資訊。這可以幫助使用者確定是否需要真空。引數是目標關係的名稱(可選的模式限定)或OID。例如：
# 顯示 pg_catalog.pg_proc 表 的元組統計資訊。
SELECT * FROM pgstattuple('pg_catalog.pg_proc');
# 顯示 workshop 表 的元組統計資訊。
SELECT * FROM pgstattuple('workshop');
# 注意： Pgstattuple 只獲得關係上的讀鎖。因此，pgstattuple 輸出並不表示瞬時快照。併發更新將改變 pgstattuple 的輸出。
# 我的操作輸出類似如下：
postgres=# \xExpanded display is on.postgres=# SELECT * FROM pgstattuple('pg_catalog.pg_proc');-[ RECORD 1 ]------+-------table_len          | 786432tuple_count        | 3253tuple_len          | 731586tuple_percent      | 93.03dead_tuple_count   | 12dead_tuple_len     | 4790dead_tuple_percent | 0.61free_space         | 22564free_percent       | 2.87 postgres=# SELECT * FROM pgstattuple('workshop'); -[ RECORD 1 ]------+--------table_len          | 2899968tuple_count        | 80000tuple_len          | 2240000tuple_percent      | 77.24dead_tuple_count   | 0dead_tuple_len     | 0dead_tuple_percent | 0free_space         | 10056free_percent       | 0.35

2.3.3.2 下面是pgstattuple輸出列及其說明

-------------------------------------------------------------------|        欄位名       |   型別   |              說明                |-------------------------------------------------------------------| table_len          | bigint  | 物理關係長度，以位元組為單位         |-------------------------------------------------------------------| tuple_count        | bigint  | 活動元組的數量                    |-------------------------------------------------------------------| tuple_len          | bigint  | 活元組的總長度(以位元組為單位)       |-------------------------------------------------------------------| tuple_percent      | float8  | 活元組的百分比                    |-------------------------------------------------------------------| dead_tuple_count   | bigint  | 無效(死)元組的數量                |-------------------------------------------------------------------| dead_tuple_len     | bigint  | 無效(死)元組的總長度(以位元組為單位) |-------------------------------------------------------------------| dead_tuple_percent | float8  | 無效(死)元組的百分比              |-------------------------------------------------------------------| free_space         | bigint  | 總可用空間(以位元組為單位)          |------------------------------------------------------------------| free_percent       | float8  | 可用空間百分比                   |------------------------------------------------------------------

2.3.3.3 讓我們在刪除或更新行時檢查元組統計資訊

# 1. 下面是引用的當前元組統計資訊。
postgres=# SELECT * FROM pgstattuple('workshop');-[ RECORD 1 ]------+--------table_len          | 2899968tuple_count        | 80000tuple_len          | 2240000tuple_percent      | 77.24dead_tuple_count   | 0dead_tuple_len     | 0dead_tuple_percent | 0free_space         | 10056free_percent       | 0.35 # 2. Delete the few ROWS from the table.DELETE FROM workshop WHERE jobno % 8 = 0;
# 3. 在下面的輸出中，dead_tuple_count 顯示 Postgres 將這些行標記為已刪除，但沒有從表中刪除它們，因為刪除這些行後表的長度是相同的。
postgres=# SELECT * FROM pgstattuple('workshop');-[ RECORD 1 ]------+--------table_len          | 2899968tuple_count        | 70000tuple_len          | 1960000tuple_percent      | 67.59dead_tuple_count   | 10000dead_tuple_len     | 280000dead_tuple_percent | 9.66free_space         | 10056free_percent       | 0.35

2.3.3.4 在表上執行VACUUM命令

# 1. 執行普通VACUUM後，我們看到:
vacuum workshop;SELECT * FROM pgstattuple('workshop');
# 2. 在執行VACUUM FULL命令後，我們可以看到 table_len 減少了。顯示執行VACUUM FULL後，os級空間被回收。
vacuum full workshop;SELECT * FROM pgstattuple('workshop');
# 我的操作輸出類似如下：
postgres=# vacuum workshop;VACUUMpostgres=# SELECT * FROM pgstattuple('workshop');-[ RECORD 1 ]------+--------table_len          | 2899968tuple_count        | 70000tuple_len          | 1960000tuple_percent      | 67.59dead_tuple_count   | 0dead_tuple_len     | 0dead_tuple_percent | 0free_space         | 330412free_percent       | 11.39 postgres=# vacuum full workshop;VACUUMpostgres=# SELECT * FROM pgstattuple('workshop');-[ RECORD 1 ]------+--------table_len          | 2539520tuple_count        | 70000tuple_len          | 1960000tuple_percent      | 77.18dead_tuple_count   | 0dead_tuple_len     | 0dead_tuple_percent | 0free_space         | 10840free_percent       | 0.43

2.3.3.5 pgstattuple 查詢檢查表膨脹

# 1. 我們可以使用下面的查詢列出死元組百分比高的表。
## 注意：如果pg16.0 版本的話， 下面查詢語句將報錯 "ERROR:  only heap AM is supported"，是pg16 的一個 bug，參考：%40alvherre.pgsql
select relname,(pgstattuple(oid)).dead_tuple_percent from pg_class where relkind = 'r' order by dead_tuple_percent desc limit 10;
## 我的查詢輸出類似如下：
postgres=# \xExpanded display is off.postgres=# postgres=# select relname,(pgstattuple(oid)).dead_tuple_percent postgres-# from pg_class postgres-# where relkind = 'r' postgres-# order by dead_tuple_percent desc limit 10;        relname        | dead_tuple_percent -----------------------+-------------------- pg_init_privs         |               2.56 pg_extension          |               1.28 pg_class              |               0.75 pg_proc               |               0.61 pg_depend             |               0.47 pg_statistic          |               0.36 pg_type               |               0.29 pg_attribute          |               0.06 pg_user_mapping       |                  0 pg_statistic_ext_data |                  0(10 rows)
# 2. 下面的查詢將向您詳細顯示死元組百分比高的表的元組統計資訊。
SELECT relname, oid, relowner,  (pgstattuple(oid)).* FROM pg_class WHERE relkind = 'r' order by dead_tuple_percent desc limit 5;
## 我的操作輸出類似如下：
postgres=# SELECT relname, oid, relowner,postgres-#   (pgstattuple(oid)).* postgres-# FROM pg_class postgres-# WHERE relkind = 'r' order by dead_tuple_percent desc limit 5;-[ RECORD 1 ]------+--------------relname            | pg_init_privsoid                | 3394relowner           | 10table_len          | 24576tuple_count        | 222tuple_len          | 17316tuple_percent      | 70.46dead_tuple_count   | 9dead_tuple_len     | 630dead_tuple_percent | 2.56free_space         | 4568free_percent       | 18.59-[ RECORD 2 ]------+--------------relname            | pg_extensionoid                | 3079relowner           | 10table_len          | 8192tuple_count        | 2tuple_len          | 210tuple_percent      | 2.56dead_tuple_count   | 1dead_tuple_len     | 105dead_tuple_percent | 1.28free_space         | 7816free_percent       | 95.41-[ RECORD 3 ]------+--------------relname            | pg_classoid                | 1259relowner           | 10table_len          | 114688tuple_count        | 412tuple_len          | 79912tuple_percent      | 69.68dead_tuple_count   | 5dead_tuple_len     | 860dead_tuple_percent | 0.75free_space         | 28656free_percent       | 24.99-[ RECORD 4 ]------+--------------relname            | pg_procoid                | 1255relowner           | 10table_len          | 786432tuple_count        | 3253tuple_len          | 731586tuple_percent      | 93.03dead_tuple_count   | 12dead_tuple_len     | 4790dead_tuple_percent | 0.61free_space         | 22564free_percent       | 2.87-[ RECORD 5 ]------+--------------relname            | pg_dependoid                | 2608relowner           | 10table_len          | 114688tuple_count        | 1854tuple_len          | 90846tuple_percent      | 79.21dead_tuple_count   | 11dead_tuple_len     | 539dead_tuple_percent | 0.47free_space         | 2288free_percent       | 1.99

2.3.4 pgstatindex(regclass)

2.3.4.1 這個函式返回一條顯示b樹索引資訊的記錄

# 1. 例如
SELECT * FROM pgstatindex ('workshop_index');
## 我的查詢輸出類似如下：
postgres=# SELECT * FROM pgstatindex ('workshop_index');-[ RECORD 1 ]------+--------version            | 4tree_level         | 1index_size         | 1589248root_block_no      | 3internal_pages     | 1leaf_pages         | 192empty_pages        | 0deleted_pages      | 0avg_leaf_density   | 89.74leaf_fragmentation | 0

2.3.4.2 輸出列及其描述

-------------------------------------------------------------------|        欄位名       |   型別   |              說明                |-------------------------------------------------------------------| version           | integer  | b樹版本號                        |-------------------------------------------------------------------| tree_level        | integer  | 根頁面的樹級別                    |-------------------------------------------------------------------| index_size        | bigint   | 總索引大小(以位元組為單位)          |-------------------------------------------------------------------| root_block_no     | bigint   | 根頁面的位置(如果沒有則為零)       |-------------------------------------------------------------------| internal_pages    | bigint   | “內部”(上層)頁面的數量            |-------------------------------------------------------------------| leaf_pages        | bigint   | 葉子結點的個數                    |-------------------------------------------------------------------| empty_pages       | bigint   | 空頁的個數                       |-------------------------------------------------------------------| deleted_pages     | bigint   | 刪除頁面數                       |-------------------------------------------------------------------

2.3.5 pgstatginindex(regclass)

2.3.5.1 這個函式返回一條顯示GIN索引資訊的記錄

  GIN索引參考： https://pganalyze.com/blog/gin-index
# 1. 例1
## 1.1 前期 建表、索引等相關 SQL
-- DROP TABLE IF EXISTS test;CREATE TABLE test (  id bigserial PRIMARY KEY,  data jsonb);INSERT INTO test(data) VALUES ('{"field": "value1"}');INSERT INTO test(data) VALUES ('{"field": "value2"}');INSERT INTO test(data) VALUES ('{"other_field": "value42"}');-- CREATE INDEX ON test USING gin(data jsonb_path_ops);CREATE INDEX test_data_gin_idx ON test USING gin(data jsonb_path_ops);EXPLAIN SELECT * FROM test WHERE data @> '{"field": "value1"}';
## 1.2 測量GIN掛起列表開銷和大小
### 1.2.1 首先，我們可以將pgstatginindex函式與類似psql的\watch命令一起使用，以密切關注特定索引
SELECT * FROM pgstatginindex('test_data_gin_idx');
# 我的操作輸出類似如下：
postgres=# SELECT * FROM pgstatginindex('test_data_gin_idx'); version | pending_pages | pending_tuples ---------+---------------+----------------       2 |             0 |              0(1 row)
## 注意：如果在建立索引的時候不指定”索引名稱”，類似語句 "CREATE INDEX ON test USING gin(data jsonb_path_ops);"，需要先執行類似如下查詢，查到其資訊（包括索引名稱），再根據其查詢到的索引名稱，將其傳參到上面查詢（當然：其實如果不指定索引名稱的話，其索引命名也是有規律的）。
select *from pg_indexeswhere tablename = 'test';
### 1.2.2 其次，如果您執行自己的資料庫伺服器，您可以使用 “perf”動態跟蹤點 來測量對 Postgres 中 ginInsertCleanup 函式的呼叫
dnf install perfsudo perf probe -x /opt/pgsql/bin/postgres ginInsertCleanupsudo perf stat -a -e probe_postgres:ginInsertCleanup -- sleep 60
# 2. 例2
## 2.1 前期 建表、索引等相關 SQL
-- DROP TABLE IF EXISTS records;CREATE TABLE records (  id bigserial PRIMARY KEY,  customer_id int4,  data jsonb); CREATE EXTENSION btree_gin;CREATE INDEX ON records USING gin (data, customer_id); EXPLAIN SELECT * FROM records WHERE customer_id = 123 AND data @> '{ "location": "New York" }';EXPLAIN SELECT * FROM records WHERE customer_id = 123; select *from pg_indexeswhere tablename =  'records';
## 注意：如下查詢中的 "records_data_customer_id_idx" 就是 上面查詢結果的 indexname 欄位值
SELECT * FROM pgstatginindex('records_data_customer_id_idx');
# 3. pganalyze索引顧問中的GIN索引支援
## 注意：在 頁面測試
## 現在，我們已經向pganalyze index Advisor新增了對GIN和GIST索引建議的初始支援
## 下面是一個為現有的tsvector列推薦GIN索引的示例
CREATE TABLE post(  id SERIAL PRIMARY KEY,  title TEXT NOT NULL,  content TEXT NOT NULL,  author_id INT NOT NULL,  language text NOT NULL DEFAULT('english'),  document tsvector); SELECT id, title FROM postWHERE post.document @@ to_tsquery('english', 'Endangered & Species')ORDER BY ts_rank(post.document, to_tsquery('english', 'Endangered & Species')) DESC

2.3.6 pgstathashindex(regclass)

2.3.6.1 參考文件

https://postgrespro.com/blog/pgsql/4161321

2.3.6.2 測試

# 1. 例如
## 1.1 前期準備
### 1.1.1 從 下載 demo-small-en.zip 檔案到 /data/software/pg/demo-data/ 並解壓（解壓後生成 demo-small-en-20170815.sql 檔案
### 1.1.2 postgres 使用者下執行如下命令
cd /data/software/pg/demo-data/;
psql -f demo-small-en-20170815.sql
## 1.2 為 demo 資料庫的 flights 表的 flight_no 欄位 建立 hash 索引 & 相關測試
### 1.2.1 建立 hash 索引
psql
\c demo
create index on flights using hash(flight_no);
### 1.2.2 建立 pgstattuple 擴充套件
#### 因為切換到 demo 資料庫了，所以需要重新建立 pgstattuple 擴充套件
create extension pgstattuple;select * from pg_available_extensions where name='pgstattuple';
### 1.2.3 查詢剛才建立的索引
select *from pg_indexeswhere tablename =  'flights';
### 1.2.4 檢視相關 sql 執行計劃 & 檢視相關索引資訊
explain (costs off) select * from flights where flight_no = 'PG0001';select * from pgstathashindex('flights_flight_no_idx');
# 最後：我的查詢輸出類似如下
demo=# explain (costs off) select * from flights where flight_no = 'PG0001';                     QUERY PLAN                     ---------------------------------------------------- Bitmap Heap Scan on flights   Recheck Cond: (flight_no = 'PG0001'::bpchar)   ->  Bitmap Index Scan on flights_flight_no_idx         Index Cond: (flight_no = 'PG0001'::bpchar)(4 rows) demo=# select * from pgstathashindex('flights_flight_no_idx'); version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent    ---------+--------------+----------------+--------------+--------------+------------+------------+-------------------       4 |          128 |             13 |            1 |            0 |      33121 |          0 | 42.36979656038809(1 row)

#PG資料庫工程師的搖籃#PostgreSQL考試#PostgreSQL培訓
PostgreSQL 元組統計與 pgstattuple 最佳化
新增圖片註釋，不超過 140 字（可選）
PostgreSQL 元組統計與 pgstattuple 最佳化
新增圖片註釋，不超過 140 字（可選）


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69998444/viewspace-2997910/，如需轉載，請註明出處，否則將追究法律責任。

相關文章