PostgreSQL數(shù)據(jù)庫優(yōu)化一數(shù)據(jù)庫配置調(diào)優(yōu)

數(shù)據(jù)庫配置調(diào)優(yōu)

Configuration Files

使用可配置的WAL段大小

/opt/pgsql13.2/bin/initdb --pgdata=/home/postgres/pgsql/data \
--waldir=/home/postgres/pgsql/wal \
--wal-segsize=64 \
--encoding=UTF8 \
--allow-group-access \
--data-checksums \
--username=postgres \
--pwprompt

當(dāng)運(yùn)行著一個寫密集型工作負(fù)載的系統(tǒng)，才會看到效果，這樣改變WAL段大小才有價(jià)值。

了解配置參數(shù)等級

• portgresql.conf（global）
• postgresql.auto.conf（alter system）
• command line options（-o options）
• all role（all role applicable）
• database（per-database applicable）
• role（per-role applicable）
• session（per session with set applicable）
• transaction（per function with set local）

理解配置參數(shù)更改的上下文（Context）

重新加載配置文件

• 重新加載配置參數(shù)

  1. pg_reload_conf函數(shù)
  2. pg_ctl reload命令
  3. kill -hup命令

• 檢查配置參數(shù)

  pg_settings視圖

  current_setting函數(shù)

  show 命令

postgres=# select * from pg_settings where name='shared_buffers';
-[ RECORD 1 ]---+-------------------------------------------------------------
name            | shared_buffers
setting         | 16384		--初始啟動時(shí)默認(rèn)值的設(shè)置
unit            | 8kB
category        | Resource Usage / Memory
short_desc      | Sets the number of shared memory buffers used by the server.
extra_desc      | 
context         | postmaster
vartype         | integer
source          | configuration file
min_val         | 16
max_val         | 1073741823
enumvals        | 
boot_val        | 1024
reset_val       | 16384		--重新加載的設(shè)置
sourcefile      | /home/postgres/pgsql/data/postgresql.conf
sourceline      | 122
pending_restart | f			--t表示值的設(shè)置需要重啟才能生效，f則相反

postgres=# 

Configuration Tuning

Connections Related

• listen_addresses = ‘0.0.0.0’
• port = 5433
• unix_socket_directories = ‘$PGDATA’
• unix_socket_group = ‘dba’
• unix_socket_permissions = ‘0700’
• max_connections = 100
• idle_in_transaction_session_timeout = 5min
• idle_session_timeout --version 14

Memtory Related

shared_buffers

shared_buffers = 8GB ( RAM 1/4 )

共享緩存區(qū)的大小 PG：雙緩沖

命中率查詢：

postgres=# select blks_hit::float/(blks_read+blks_hit) as cache_hit_ratio 
postgres-# from pg_stat_database 
postgres-# where datname=current_database();
  cache_hit_ratio   
--------------------
 0.9602566108590205
(1 row)

postgres=# explain (analyze ,buffers) select sum(sal) from emp;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Aggregate  (cost=14.75..14.76 rows=1 width=32) (actual time=0.101..0.103 rows=1 loops=1)
   Buffers: shared hit=1		--表明在共享內(nèi)存中直接讀到了一行
   ->  Seq Scan on emp  (cost=0.00..13.80 rows=380 width=14) (actual time=0.014..0.018 rows=14 loops=1)
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=8 read=1
 Planning Time: 0.250 ms
 Execution Time: 0.250 ms
(8 rows)

postgres=# 

work_mem

work_mem=32MB ( RAM * 0.25 / max_connections )

指定在寫入磁盤上的臨時(shí)文件之前，ORDER BY 、DISTINCT 、JOIN和哈希表的內(nèi)部操作將使用的內(nèi)存量

如果有并發(fā)的M個進(jìn)程，每個進(jìn)程中有N個HASH操作，則需要分配的內(nèi)存是 M * N * work_mem

一個連接將占用一個work_mem空間

maintenance_work_mem

maintenance_work_mem=2GB ( RAM * 0.15 / autovacuum_max_workers )

進(jìn)行維護(hù)操作時(shí)需要的內(nèi)存

如VACUUM 、CREATE INDEX 、ALTER TABLE ADD FOREIGN KEY 等操作需要的內(nèi)存

wal_buffers

wal_buffers=16MB ( shared_buffers / 32 )

指定WAL日志緩存大小，默認(rèn)值 -1

64KB <= wal_buffers <= WAL文件尺寸

Planner / Cost Related

random_page_cost

random_page_cost = 1.1 ~ 3 （ > seq_page_cost :1)

隨即磁盤訪問時(shí)，單個頁面的讀取開銷，默認(rèn)為 4.0

work_mem

work_mem=32MB ( RAM * 0.25 / max_connections )

effective_cache_seze

effective_cache_size = 24GB （ RAM 3/4 )

提供了可以用于磁盤緩存存儲器的估計(jì)

WAL Related

wal_level

wal_level = replica ( minimal, replica, or logical )

決定多少信息寫入WAL日志中。

pg 10 及以上版本默認(rèn)是replica，pg 9.6及之前版本默認(rèn)值是minimal；

9.6以前：

archive：增加wal歸檔所需的日志（最常用）；

hot_standby：在備用服務(wù)器上增加了運(yùn)行只讀查詢所需的信息（流復(fù)制使用）。

9.6以后：

minimal：值寫入數(shù)據(jù)庫崩潰或突然關(guān)機(jī)后，進(jìn)行恢復(fù)所需要的信息； （如果使用minimal，需要把max_wal_senders設(shè)置為0）

replica：增加wal歸檔信息同時(shí)包括只讀服務(wù)器需要的信息；

logical：支持邏輯解碼，用于邏輯復(fù)制時(shí)，需要配合為logical。

archive_mode

archive_mode = on （off， on， always）

on：在主庫歸檔

always：可以在從庫歸檔

當(dāng)wal_level設(shè)置為minimal時(shí)，無法設(shè)置改參數(shù)。

fsync

改參數(shù)直接控制WAL日志文件是否先寫入硬盤。

默認(rèn)值為on（先寫入），表示當(dāng)更新數(shù)據(jù)寫入硬盤時(shí)，操作系統(tǒng)必須等待WAL日志文件寫入完成。

當(dāng)設(shè)置為off 時(shí)，表示在更新數(shù)據(jù)寫入硬盤時(shí)，操作系統(tǒng)無須等待WAL日志文件寫入完成。

synchronous_commit

synchronous_commit = on ( off, local, remote_write, remote_apply, or on )

單實(shí)例環(huán)境：

• off：當(dāng)數(shù)據(jù)庫提交事務(wù)時(shí)不需要等待本地wal buffer 寫入wal 日志，隨即向客戶端返回成功。適用對數(shù)據(jù)庫準(zhǔn)確性要求不高同時(shí)追求數(shù)據(jù)庫性能的場景。
• on：表示提交事務(wù)時(shí)需等待本地wal 寫入wal 日志后才向客戶端返回成功。on為默認(rèn)設(shè)置，數(shù)據(jù)庫非常安全，但性能有所損耗。
• local：含義與on類似，表示提交事務(wù)時(shí)需要等待本地wal寫入后才向客戶端返回成功。

流復(fù)制環(huán)境：

• off：不必等wal 日志被本地持久化，也不管是否被傳到遠(yuǎn)程，事務(wù)commit都可以立即返回。
• local：wal 日志被本地持久哈后（不用管遠(yuǎn)程），事務(wù)commit就可以返回。
• remote_write：wal 日志被備庫內(nèi)存中，事務(wù)commit才返回。
• on：wal日志被傳入備庫并持久化，事務(wù)commit才返回。
• remote_apply：wal日志被傳到備庫并apply，事務(wù)commit才返回。

full_page_writes

在檢查點(diǎn)之后首次修改一個頁面時(shí)，PostgreSQL服務(wù)器會將該頁面的全部內(nèi)容刷寫到WAL日志文件中。會增加WAL日志文件的寫入量。

wal_compression

full_page_writes = on 、wal_compression = on 時(shí)，PostgreSQL服務(wù)器會將CheckPoint第一次修改的數(shù)據(jù)頁壓縮后寫到WAL日志文件中

effective_io_concurrency

effective_io_concurrency = 4 ( > 100 : SSDs and Memory-backed storages )

設(shè)置同時(shí)被執(zhí)行的并發(fā)磁盤I/O操作的數(shù)量。0表示禁用異步I/O請求，默認(rèn)是1。對位圖索引掃描有效。

checkpoint_timeout

checkpoint_timeout = 5min ~ 15min

系統(tǒng)自動執(zhí)行checkpoint之間的最大時(shí)間間隔。系統(tǒng)默認(rèn)值是5分鐘

checkpoint_completion_target

checkpoint_completion_target = 0.7 ~ 0.9

checkpoint調(diào)度系統(tǒng)，它可以讓檢查點(diǎn)在我們設(shè)置的checkpoint_timeout時(shí)間周期做的更分散，從而降低IO的影響。

checkpoint_warning

如果檢查點(diǎn)發(fā)生的時(shí)間間隔接近c(diǎn)heckpoint_warning秒，就會在服務(wù)器日志中輸出一條信息。

wal_write_delay

wal_write_delay = 10ms

指定WAL寫入器刷寫WAL的頻繁程度，以時(shí)間為單位。

在刷新WAL之后，寫入器將根據(jù)wal_write_delay所給出的實(shí)際長度進(jìn)行睡眠。

commit_delay

commit_delay = 10ms

事務(wù)提交后，日志寫到wal_buffer上到wal_buffer寫到磁盤的時(shí)間間隔。

需要和commit_sibling配合使用。

commit_siblings

commit_siblings = 1000

觸發(fā)commit_delay 等待的并發(fā)事務(wù)數(shù)

若系統(tǒng)中并發(fā)活躍事務(wù)達(dá)不到該值，commit_delay將不起作用，為防止在系統(tǒng)并發(fā)壓力較小的情況下事務(wù)提交后空等其他事務(wù)，不宜設(shè)置過大。

min_wal_size（收縮WAL尺寸的最低限制值）

只要WAL磁盤使用率低于這個設(shè)置，那個發(fā)生檢查點(diǎn)時(shí)，舊的WAL文件總是被循環(huán)復(fù)用，而不是刪除。這可以用來確保預(yù)留足夠的空間應(yīng)對WAL使用高峰，比如當(dāng)運(yùn)行批處理任務(wù)時(shí)。

max_wal_size

WAL日志文件增大到該參數(shù)指定大小后，會自動進(jìn)行checkpoint。

特殊情況下，如符合過高、archive_command歸檔失敗、wal_keep_segments設(shè)置過大，WAL文件大小會超過設(shè)置的值。增加該參數(shù)的值會延長崩潰恢復(fù)所需要的時(shí)間。

wal_keep_segments

wal_keep_segments（pg13：wal_keep_size)

改參數(shù)獨(dú)立于其他參數(shù)設(shè)置，保留最少wal_keep_segments個wal段文件。

archive_command

如果配置archive_mode = on，當(dāng)wal段文件未及時(shí)被歸檔時(shí)，即使?jié)M足了其他清理?xiàng)l件，wal段文件也不能被清理。

archive_timeout

如果配置archive_mode=on，archive_timeout用來強(qiáng)制服務(wù)器周期性地切換到一個新的wal段文件。

max_slot_keep_wal_size

該值表示如果復(fù)制槽被感知到失聯(lián)，保留WAL文件的最大數(shù)量。如果超過該值，PostgreSQL將開始刪除最早的WAL文件。

Parallel Related

eg：Linux 64Bit x86-64 SSD CPU:16 Memory:32GB Connections:1000 OLTP

PG：13

max_worker_processes

• max_worker_processes = 16 （ < CPU core )

  數(shù)據(jù)庫允許的最大后臺進(jìn)程數(shù)，并行進(jìn)程屬于后臺進(jìn)程的一種。

max_parallel_workers

• max_parallel_workers = 16 （ <= max_worker_process ）

  數(shù)據(jù)庫循序的最大并行進(jìn)程數(shù)。

max_parallel_workers_per_gather

• max_parallel_workers_per_gather = 8 （ 1/2 max_parallel_workers ）

  并行查詢進(jìn)程數(shù)。

max_parallel_maintenance_workers

• max_parallel_maintenance_workers = 8 （ 1/2 max_parallel_workers ）

  維護(hù)并行進(jìn)程數(shù)

max_parallel_workers_per_gather + max_parallel_maintenance_works <= max_parallel_workers

Autovacuum Related

autovacuum

autovacuum = on

autovacuum_max_workers

autovacuum_max_workers = 6 ~ 12

一次可以運(yùn)行多少個autovacuum進(jìn)程。

autovacuum_naptime

autovacuum_naptime = 1min （autovacuum_natime / database）

autovacuum worker進(jìn)程休息間隔。

log_autovacuum_min_duration

log_autovacuum_min_duration （默認(rèn)ms）

運(yùn)行時(shí)間超過此值的任何autovacuum都會記錄到PostgreSQL日志文件中。

autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold

autovacuum_analyze_scale_factor * number of tuples + autovacuum_analyze_threshold

antovacuum_vacuum_scale_factor / autovacuum_analyze_scale_factor

將添加到公式中的表記錄的分?jǐn)?shù)。例如，值0.2等于表記錄的20%

autovacuum_vacuum_threshold / autovacuum_analyze_threshold

觸發(fā)autovacuum所需的過時(shí)記錄或dml的最小數(shù)量

autovacuum_vacuum_cost_limit

autovacuum可達(dá)到的總成本限制（結(jié)合所有autovacuum作業(yè)）

autovacuum_vacuum_cost_delay

autovacuum_vacuum_cost_delay = 2ms ( < 20 ms )

當(dāng)一個清理工作達(dá)到autovacuum_vacuum_cost_limit 指定的成本限制時(shí)，autovacuum將休眠數(shù)毫秒。

autovacuum_vacuum_insert_threshold ( PG13 )

autovacuum_vacuum_insert_scale_factor ( PG13 )

Autovacuum最佳實(shí)踐

死元組清理調(diào)優(yōu)

idle_in_transaction_session_timeout

長查詢：statement_timeout

加速autovacuum運(yùn)行

調(diào)大autovacuum_vacuum_cost_limit參數(shù)的值，以及降低autovacuum_vacuum_cost_delay的值。

如果autovacuum_vacuum_cost_delay=0，相當(dāng)于立即手動執(zhí)行autovacuum。

降低死元組生成速度

多條update語句合并為一條；使用填充因子（HOT）特性；使用upsert特性（insert on conflict）

基于單表數(shù)據(jù)修改調(diào)優(yōu)

autovacuum_vacuum_scale_factor

基于單表數(shù)據(jù)插入調(diào)優(yōu)

13：降低autovacuum_vacuum_insert_scale_factor的值來加速autovacuum操作。

低于13：可以降低autovacuum_freeze_max_age的值，該值默認(rèn)是2億。

避免事務(wù)ID回卷

檢查是否有長session手動處理事務(wù)或者使用臨時(shí)表；檢查是否有數(shù)據(jù)文件壞塊。

表分區(qū)增大并發(fā)workers

增大autovacuum_max_workers的值。

Logging Related

• logging_collector = on

• log_destination = ‘csvlog’

• log_line_prefix = ‘%m %p’

  log_line_prefix需要與log_destination = ‘stderr’ 同時(shí)使用

• log_directory = ‘log’

• log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’

• log_file_mode = 0600

• log_truncate_on_rotation = on

• log_rotation_age = 1d

• log_rotation_size = 0

• log_checkpoints = on

• log_lock_waits = on

• log_duration = on

• log_statement_sample_rate = 0.2

• log_min_turation_sample = 100ms

• log_min_turation_statement = 500ms

• log_statement = ‘none | ddl | mod | all’

1. 方案一：每天生成一個新的日志文件

   log_filename = 'postgresql-%Y-%m-%d.log'
   log_truncate_on_rotation = off
   log_rotation_age = 1d
   log_rotation_size = 0
   

2. 方案二：每當(dāng)日志寫滿一定的大小（10MB），則切換一個日志

   log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
   log_truncate_on_rotation = off
   log_rotation_age = 0
   log_rotation_size = 10MB
   

3. 方案三

   log_filename = 'postgresql-%u.log'
   log_truncate_on_rotation = on
   log_rotation_age = 1d
   log_rotation_size = 0
   

other

old_snapshot_threshold

強(qiáng)制刪除過老的事務(wù)快照保留的死元組

temp_file_limit

限制臨時(shí)文件使用量

track_io_timing

跟蹤IO消耗的時(shí)間

track_activity_query_size

activity_query列存儲的大小，默認(rèn)是1kb。

huge_pages

改參數(shù)用于啟用或禁用巨型內(nèi)存頁面，對于一些連接數(shù)很大或內(nèi)存很大的數(shù)據(jù)庫，強(qiáng)烈建議配置大頁。

try：表示讓postgresql嘗試使用大頁，分配大頁失敗后，會使用普通內(nèi)存。

on：分配大頁失敗后，postgresql也會啟動失敗。

檢查頁表大小：

[postgres@lyp ~]$ cat /proc/meminfo |grep PageTables
PageTables:        22872 kB
[postgres@lyp ~]$ 

default_statistics_target

查看執(zhí)行計(jì)劃時(shí)，生成執(zhí)行計(jì)劃時(shí)間遠(yuǎn)大于執(zhí)行時(shí)間時(shí)，可以通過調(diào)整default_statistics_target參數(shù)進(jìn)行優(yōu)化

default_statistics_target過大：會影響analyze操作執(zhí)行的時(shí)間，掃描的數(shù)據(jù)行的個數(shù)就會更多，得到的優(yōu)化器的統(tǒng)計(jì)數(shù)據(jù)就會越準(zhǔn)確。

建議根據(jù)業(yè)務(wù)的情況，進(jìn)行表級別的設(shè)置。

Tuning tools

PGTune

https://pgtune.leopard.in.ua/#/

PostgreSQL Configuration Tool

https://www.pgconfig.org/#/tuning

postgresqltuner

https://github.com/jfcoz/postgresqltuner

pgBadger

https://github.com/darold/pgbadger