[PG] Postgres Tuning
Environment Setup
在這篇文章中,我們會使用 docker 來進行示範 :
docker run --name postgres-tuning -e POSTGRES_PASSWORD=postgres -d postgres:16-alpine
docker exec -it --user root postgres-tuning bash
如果想要在容器中使用 nano (容器中預設可以使用 vi),可以執行以下指令 :
apk update && apk add nano
接著進入到 postgres 使用者中 :
su postgres
如果要離開容器,可以執行 exit
指令。
Postgres Architecture
Process manager
postgres 是 client-server 的架構,client 端可以透過 TCP/IP 連線到 server 端,server 端會執行 client 端發送的指令,並回傳結果。 對於每一個 client 端連線,postgres 都會啟動一個 process 來處理這個連線。
Shared Memory
postgres 並不會直接讀取磁碟上的資料,而是先從 shared buffer 中讀取來減少磁碟 I/O 的次數。
Query processor
Query processor 分為五個步驟
- Connection manager : 管理 client 端的連線
- Query parser : 解析 client 端發送的指令
- Query rewriter : 將 view 之類的物件轉換成基本的 SQL 語法
- Query planner : 依據統計資料,選擇最佳的執行計畫
- Query executor : 執行指令
Checkpoint
當要執行 checkpoint 時,會將 shared buffer 中的資料寫入到磁碟上, 並且將 shared buffer 和 WAL 中的資料標記為乾淨。
這樣當資料庫出現問題需要 recovery 時,可以從 checkpoint 的位置開始進行 recovery。
常見的參數包含以下,我們需要依據實際的狀況進行調整 :
checkpoint_flush_after
: 每寫入多少資料後就進行 flushcheckpoint_timeout
: 設定 checkpoint 的時間間隔checkpoint_completion_target
: 設定寫入必須在 checkpoint 的幾趴內完成,預設為 0.5
WAL writer
在資料被修改時,會先寫入到 WAL 的 buffer 中, 直到被 commit 後才會寫入到磁碟上。
同時,WAL 也會被用來進行 crash recovery 和 replication。
Utility process
在 Postgres 中有很多 utility process 會在背景執行,例如 :
Vacuum
: 清理 dead tuplesCheckpointer
: 執行 checkpointWAL Writer
: 紀錄 WAL 的資料Archiver
: 將 WAL 中的資料寫入到磁碟Stats Collector
: 收集統計資料
Vacuum
在 postgres 中,如果我們使用 DELETE
或 UPDATE
指令來刪除或修改資料,
實際上並不會立即將資料從磁碟上刪除,而是將資料標記為 dead tuples (因為需要在失敗時進行 rollback),
這樣的資料會佔用磁碟空間,並且影響查詢效能,
而 VACUUM
指令可以將被標記為已刪除或是過期的 tuple 標記為可重用,
這樣當下一次有資料需要寫入時,會覆蓋掉原本的 dead tuples,
因此如果我們再次執行 UPDATE
,資料表的大小也不會增加。
如果我們想要刪除所有 dead tuples,可以執行 VACUUM FULL
指令,
這樣會將整張表的資料重新寫入到磁碟上,並且釋放磁碟空間,
但是這樣會使用排他鎖阻塞其他指令的執行。
我們可以透過以下的範例來觀察 VACUUM
的影響 :
首先我們先創建 10 萬筆資料並關閉 autovacuum
psql -U postgres -d postgres
CREATE TABLE test (id int) with (autovacuum_enabled = off);
INSERT INTO test SELECT * FROM generate_series(1, 100000);
接著我們查詢資料表的大小
SELECT pg_size_pretty(pg_relation_size('test'));
-- result
pg_size_pretty
----------------
3544 kB
(1 row)
更新之後再觀察資料表的大小,發現大小大約增加了兩倍
UPDATE test SET id = id + 1;
SELECT pg_size_pretty(pg_relation_size('test'));
-- result
pg_size_pretty
----------------
7080 kB
(1 row)
接著執行 VACUUM
指令,大小並不會減少,而是將 tuple 標記為可重用
VACUUM test;
SELECT pg_size_pretty(pg_relation_size('test'));
-- result
pg_size_pretty
----------------
7080 kB
(1 row)
再執行一次 UPDATE
指令,大小並不會增加,因為他覆蓋掉原本的 dead tuples
UPDATE test SET id = id + 1;
SELECT pg_size_pretty(pg_relation_size('test'));
-- result
pg_size_pretty
----------------
7080 kB
(1 row)
最後執行 VACUUM FULL
指令,大小會縮小到原本的大小
VACUUM FULL test;
SELECT pg_size_pretty(pg_relation_size('test'));
-- result
pg_size_pretty
----------------
3544 kB
(1 row)
如果想要檢查 table 中有多少 dead tuples 可以執行以下指令 :
SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'test';
-- result
relname | n_live_tup | n_dead_tup
---------+------------+------------
test | 100000 | 100000
(1 row)
也可以透過以下指令來檢查 VACUUM 的參數
select name,setting
from pg_settings
where name in ('autovacuum_max_workers','autovacuum_naptime','autovacuum_vacuum_scale_factor','autovacuum_vacuum_threshold');
-- result
name | setting
--------------------------------+---------
autovacuum_max_workers | 3
autovacuum_naptime | 60
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 50
(4 rows)
我們可以透過調整參數來控制 VACUUM 的行為,例如 :
autovacuum_max_workers
: 最大可以同時運行 vacuum 的 process 數量autovacuum_naptime
: 每多少秒需要檢查是否需要執行 vacuumautovacuum_vacuum_scale_factor
: 決定觸發 vacuum 的條件autovacuum_vacuum_threshold
: 決定觸發 vacuum 的條件
而觸發 vacuum 的公式是 : dead tuples > autovacuum_vacuum_scale_factor * tuple count + autovacuum_vacuum_threshold
如果我們的資料量過大,VACUUM
在執行一段時間後就會進入到 delay 的時間,讓其他操作可以執行。
如果一直無法完成,我們就需要去調整 vacuum_cost_delay
的時間。
Index
Index introduction
索引通常是資料庫效能的瓶頸,因此我們需要適當的建立索引來提高查詢效能。
我們先建立範例資料表
CREATE TABLE test_index (id serial, name text);
INSERT INTO test_index (name) SELECT 'alice' FROM generate_series(1, 2500000);
INSERT INTO test_index (name) SELECT 'bob' FROM generate_series(1, 2500000);
-- 開啟計時
\timing
接著嘗試在沒有索引的情況下進行 查詢
EXPLAIN ANALYZE SELECT * FROM test_index WHERE id = 1000000;
-- result
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..43392.60 rows=15606 width=36) (actual time=100.488..103.282 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test_index (cost=0.00..40832.00 rows=6502 width=36) (actual time=72.973..97.820 rows=0 loops=3)
Filter: (id = 1000000)
Rows Removed by Filter: 1666666
Planning Time: 0.286 ms
Execution Time: 103.303 ms
(8 rows)
Time: 104.655 ms
經由上面的查詢計畫可以看到,postgres 使用兩個 Workers 和 Parallel Seq Scan 來進行查詢。
CREATE INDEX idx_id ON test_index (id);
EXPLAIN ANALYZE SELECT * FROM test_index WHERE id = 1000000;
-- result
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id on test_index (cost=0.43..8.45 rows=1 width=9) (actual time=0.653..0.671 rows=1 loops=1)
Index Cond: (id = 1000000)
Planning Time: 1.190 ms
Execution Time: 0.739 ms
(4 rows)
Time: 3.408 ms
可以看到,使用 index 之後速度加快了許多, 但是要注意的是 index 會佔用硬碟空間,並且在寫入時會增加額外的開銷, 因此我們需要觀察 index 和 table 的大小,並且在適當的時機使用索引。
\di+
-- result
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+----------+-------+----------+------------+-------------+---------------+--------+-------------
public | idx_id | index | postgres | test_index | permanent | btree | 107 MB |
(1 row)
\dt+
-- result
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------+-------+----------+-------------+---------------+--------+-------------
public | test_index | table | postgres | permanent | heap | 192 MB |
(1 row)
Index use case
除了在查詢的時候可以使用 index 來提高查詢性能,在做 join 的時候也可以使用 index 來提高性能, 我們可以在 foreign key 上建立 index , 這樣除了可以提高 join 的效率外, 在 parent table 變動時,也可以提高效能。
至於 index 的下法,我們除了針對全表建立 index 外,也可以針對某些特定的值來建立 index (partial index)
CREATE INDEX idx_name_res ON test_index(name) WHERE name not in ('alice', 'bob');
還有一種 index 的模式叫做 combination index,這種 index 可以將多個欄位組合起來建立 index, 這樣可以提高查詢效能,但同時也會增加 index 的大小, 並且如果我們不對第一個欄位搜尋時,這個 index 就會失效。
CREATE INDEX idx_name_res2 ON test_index(name, id);
Index cluster
除此之外 correlation 也可以用來提高查詢效能, 由於資料的物理位置接近,可以減少 I/O 的次數,進而提高查詢效能。
接下來我們透過兩個範例來說明 correlation 的影響。
CREATE TABLE t_test (id serial, name text);
INSERT INTO t_test (name) SELECT 'alice' FROM generate_series(1, 500000);
CREATE INDEX t_test_idx ON t_test (id);
VACUUM ANALYZE t_test;
EXPLAIN (ANALYZE true, BUFFERS true, TIMING true) SELECT * FROM t_test WHERE id < 10000;
-- result
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using t_test_idx on t_test (cost=0.42..321.59 rows=9324 width=10) (actual time=0.054..1.826 rows=9999 loops=1)
Index Cond: (id < 10000)
Buffers: shared hit=55 read=30
Planning:
Buffers: shared hit=17 read=1
Planning Time: 0.321 ms
Execution Time: 2.325 ms
(7 rows)
Time: 3.179 ms
CREATE TABLE t_random as SELECT * FROM t_test ORDER BY random();
CREATE INDEX t_random_idx ON t_random (id);
VACUUM ANALYZE t_random;
EXPLAIN (ANALYZE true, BUFFERS true, TIMING true) SELECT * FROM t_random WHERE id < 10000;
-- result
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t_random (cost=191.22..3070.30 rows=10167 width=10) (actual time=1.065..3.621 rows=9999 loops=1)
Recheck Cond: (id < 10000)
Heap Blocks: exact=2636
Buffers: shared hit=2636 read=30
-> Bitmap Index Scan on t_random_idx (cost=0.00..188.68 rows=10167 width=0) (actual time=0.789..0.789 rows=9999 loops=1)
Index Cond: (id < 10000)
Buffers: shared read=30
Planning:
Buffers: shared hit=9
Planning Time: 0.095 ms
Execution Time: 4.446 ms
(11 rows)
Time: 4.959 ms
可以看到 Buffer 的 shared hit 相差很多,進而影響了效能。
我們可以透過以下的範例來查看 correlation 的數值
SELECT tablename, attname, correlation FROM pg_stats WHERE tablename IN ('t_test', 't_random') ORDER BY 1, 2;
-- result
tablename | attname | correlation
-----------+---------+-------------
t_random | id | 0.012932359
t_random | name | 1
t_test | id | 1
t_test | name | 1
(4 rows)
如果要解決這個問題,我們可以使用 CLUSTER
指令來將資料按照 index 的順序重新排列,來提高查詢效能,
但這個方式會造成鎖表,且只能依據一個 index 來進行排序,同時當新的資料進入時,也不會自動重新排序。
CLUSTER t_random USING t_random_idx;
ANALYZE;
SELECT tablename, attname, correlation FROM pg_stats WHERE tablename IN ('t_test', 't_random') ORDER BY 1, 2;
-- result
tablename | attname | correlation
-----------+---------+-------------
t_random | id | 1
t_random | name | 1
t_test | id | 1
t_test | name | 1
在 postgres 中,有一個叫做 fill factor 的參數,預設是 100%, 這個參數會決定 page 被填滿的程度,如果我們將 fill factor 設定為 90%,那麼當 page 被填滿 90% 時, 就不會再寫入新的資料, 把空間預留給更新和刪除的資料, 使之變成 HOT update。
我們可以透過以下範例來宣告 fill factor 的值
CREATE TABLE test_fillfactor (id serial, name text) WITH (fillfactor = 90);
Statistics
在 postgres 中,統計資料是非常重要的,它可以幫助我們了解資料庫的狀況,並且提供最佳化的建議。
我們可以透過 pg_stat_statements
來查看統計資料,這個 view 會記錄所有執行的 SQL 語句,並且提供執行時間、執行次數、讀取的資料量等資訊。
首先我們需要啟動這個 extension,我們先創建一個新的資料庫並使用 pgbench 生成一些假資料
su postgres
createdb benchdb
pgbench -i benchdb
pgbench -c 10 -t 100000 benchdb
接著需要調整 postgresql.conf 來啟動這個 extension
cd /var/lib/postgresql/data
nano postgresql.conf
# change shared_preload_libraries = 'pg_stat_statements'
# restart docker
docker restart postgres-tuning
docker exec -it --user root postgres-tuning bash
psql -U postgres -d benchdb
CREATE EXTENSION pg_stat_statements;
接著我們可能需要調整一下顯示方式,輸入 \x
來顯示單行,
接著查看 pg_stat_statements
的資料
SELECT * FROM pg_stat_statements;
-- result
-[ RECORD 1 ]----------+------------------------------------
userid | 10
dbid | 16411
toplevel | t
queryid | -8553890797533947962
query | CREATE EXTENSION pg_stat_statements
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 1
total_exec_time | 36.871701
min_exec_time | 36.871701
max_exec_time | 36.871701
mean_exec_time | 36.871701
stddev_exec_time | 0
rows | 0
shared_blks_hit | 2653
shared_blks_read | 131
shared_blks_dirtied | 60
shared_blks_written | 7
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
--More--