PostgreSQL 效能最佳化簡介

檢視資料庫統計資訊

pg_stat_database 表格包含每個資料庫的統計資訊

SELECT * FROM pg_stat_database \gx

. . .
-[ RECORD 2 ]------------+------------------------------
datid                    | 13921
datname                  | postgres
numbackends              | 1
xact_commit              | 266
xact_rollback            | 9
blks_read                | 229
blks_hit                 | 11263
tup_returned             | 118708
tup_fetched              | 3563
tup_inserted             | 0
tup_updated              | 0
tup_deleted              | 0
conflicts                | 0
temp_files               | 0
temp_bytes               | 0
deadlocks                | 0
checksum_failures        |
checksum_last_failure    |
blk_read_time            | 0
blk_write_time           | 0
session_time             | 5303626.534
active_time              | 200.906
idle_in_transaction_time | 0
sessions                 | 2
sessions_abandoned       | 0
sessions_fatal           | 0
sessions_killed          | 0
stats_reset              | 2022-11-06 20:20:18.279798+01
. . .

對於我們的目的而言，一些有趣的欄位包括

• blks_read：資料庫中讀取的磁碟區塊數。
• blks_hit：在緩衝快取中找到磁碟區塊的次數（避免從磁碟進行慢速讀取）。
• xact_commit：已提交的交易數。
• xact_rollback：已回滾的交易數。

如Data Egret 團隊在其部落格上所示，您可以使用這些原始值來計算有趣的統計資訊，例如快取命中率

SELECT
    datname,
    100 * blks_hit / (blks_hit + blks_read) as cache_hit_ratio
FROM
    pg_stat_database
WHERE
    (blks_hit + blks_read) > 0;

  datname  | cache_hit_ratio
-----------+-----------------
           |              99
 postgres  |              98
 template1 |              99
(3 rows)

這可能是寶貴的資訊，可以幫助您評估是否可以從向資料庫叢集新增 RAM 中受益，以便可以有效地快取最常見的查詢。

檢視表格統計資訊

另一個有用的檢視表系列是 pg_stat_all_tables、pg_stat_user_tables 和 pg_stat_sys_tables。pg_stat_all_tables 檢視表顯示所有資料庫的存取統計資訊，而其他兩個檢視表則根據表格是使用者表格還是系統表格來篩選表格。

SELECT * FROM pg_stat_all_tables \gx

. . .
-[ RECORD 104 ]-----+------------------------
relid               | 1262
schemaname          | pg_catalog
relname             | pg_database
seq_scan            | 5168
seq_tup_read        | 20655
idx_scan            | 20539
idx_tup_fetch       | 20539
n_tup_ins           | 0
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 0
n_dead_tup          | 0
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

這些檢視表中的一些有趣欄位包括

• seq_scan：在表格上執行的循序掃描次數。
• seq_tup_read：從循序掃描傳回的列數。
• idx_scan：針對表格執行的索引掃描次數。
• idx_tup_fetch：透過索引擷取的列數。

這些欄位中的數字可以幫助您評估索引的效能，以及您執行的查詢是否有效地使用它們。如果您發現表格有許多循序掃描，您可能會受益於建立額外的索引，供您最常見的查詢使用。

檢視索引命中次數

如果您需要更多關於目前擁有的索引的資訊，您可以查看 pg_stat_all_indexes、pg_stat_user_indexes 和 pg_stat_sys_indexes 檢視表

SELECT * FROM pg_stat_all_indexes \gx

. . .
-[ RECORD 6 ]-+----------------------------------------------
relid         | 1249
indexrelid    | 2659
schemaname    | pg_catalog
relname       | pg_attribute
indexrelname  | pg_attribute_relid_attnum_index
idx_scan      | 822
idx_tup_read  | 1670
idx_tup_fetch | 1670
. . .

這些檢視表為您提供每個索引使用頻率的資訊。idx_scan 欄顯示索引已掃描的次數。idx_tup_read 欄顯示掃描傳回的項目數，而 idx_tup_fetch 顯示索引掃描傳回的總列數。

此資訊對於幫助您了解何時擁有查詢未使用的索引非常有用。一旦您識別出這些索引，您可以重寫查詢以利用索引，或者您可以移除未使用的索引以提高寫入效能。

檢視鎖定資訊

您收集到的一些關於慢速查詢的資訊可能指向鎖定問題。您可以透過查詢 pg_locks 檢視表來找到更多關於目前持有的所有鎖定的資訊

SELECT * FROM pg_locks \gx

-[ RECORD 1 ]------+----------------
locktype           | relation
database           | 13921
relation           | 12290
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/3920
pid                | 967262
mode               | AccessShareLock
granted            | t
fastpath           | t
waitstart          |
-[ RECORD 2 ]------+----------------
locktype           | virtualxid
database           |
relation           |
page               |
tuple              |
virtualxid         | 3/3920
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/3920
pid                | 967262
mode               | ExclusiveLock
granted            | t
fastpath           | t
waitstart          |

輸出將提供關於 PostgreSQL 中所有鎖定的資訊。這可以幫助您診斷在不同程序請求控制相同物件時可能發生的爭用問題。

一些可能有助於您調查有問題鎖定的欄位包括

• locktype：可鎖定物件的類型
• database/relation/page/tuple：鎖定項目的物件 ID。對於資料庫和關聯，這些可以在 pg_database 和 pg_class 中交叉參照。
• mode：已實作或請求的鎖定模式。
• granted：一個布林值，表示是否已授予鎖定。

檢查 PostgreSQL 是否已記錄慢速查詢

您應該做的第一件事是驗證慢速查詢記錄的目前狀態。如果已啟用慢速查詢記錄，則您無需執行任何操作。

您可以透過輸入以下內容來檢查是否已啟用慢速查詢記錄

SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx

-[ RECORD 1 ]---+---------------------------------------------------------------------------
name            | log_min_duration_statement
setting         | -1
unit            | ms
category        | Reporting and Logging / When to Log
short_desc      | Sets the minimum execution time above which all statements will be logged.
extra_desc      | Zero prints all queries. -1 turns this feature off.
context         | superuser
vartype         | integer
source          | default
min_val         | -1
max_val         | 2147483647
enumvals        |
boot_val        | -1
reset_val       | -1
sourcefile      |
sourceline      |
pending_restart | f

如果您檢查 short_desc 和 extra_desc 欄位的值，您將找到允許我們評估目前是否已啟用記錄的資訊。我們可以發現目前未啟用慢速查詢記錄，因為 setting 欄目前設定為 -1。

現在您已知道目前狀態，您可以根據需要變更它。

設定 PostgreSQL 以記錄慢速查詢

在我們繼續之前，務必注意，雖然慢速查詢記錄非常有用，但它可能會對效能產生額外的影響。PostgreSQL 必須執行額外的操作來計時每個查詢，並將結果記錄到日誌中。這可能會影響效能並意外填滿硬碟空間。

在所有時間都記錄慢速查詢可能不是一個好主意。相反地，當您主動調查問題時啟用此功能，並在完成後停用它。

SHOW config_file;

               config_file
-----------------------------------------
 /etc/postgresql/14/main/postgresql.conf
(1 row)

. . .
# Query logging configuration

#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
                                 # and their durations, > 0 logs only
                                 # statements running at least this number
                                 # of milliseconds

#log_min_duration_sample = -1    # -1 is disabled, 0 logs a sample of statements
                                 # and their durations, > 0 logs only a sample of
                                 # statements running at least this number
                                 # of milliseconds;
                                 # sample fraction is determined by log_statement_sample_rate

#log_statement_sample_rate = 1.0 # fraction of logged statements exceeding
                                 # log_min_duration_sample to be logged;
                                 # 1.0 logs all such statements, 0.0 never logs

#log_transaction_sample_rate = 0.0 # fraction of transactions whose statements
                                   # are logged regardless of their duration; 1.0 logs all
                                   # statements from all transactions, 0.0 never logs
. . .

log_min_duration_statement = 5s

SELECT pg_reload_conf();

SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx

-[ RECORD 1 ]---+---------------------------------------------------------------------------
name            | log_min_duration_statement
setting         | 5000
unit            | ms
category        | Reporting and Logging / When to Log
short_desc      | Sets the minimum execution time above which all statements will be logged.
extra_desc      | Zero prints all queries. -1 turns this feature off.
context         | superuser
vartype         | integer
source          | configuration file
min_val         | -1
max_val         | 2147483647
enumvals        |
boot_val        | -1
reset_val       | 5000
sourcefile      | /etc/postgresql/14/main/postgresql.conf
sourceline      | 506
pending_restart | f

ALTER DATABASE helloprisma SET 'log_min_duration_statement' = 2000;

ALTER DATABASE

\drds

                  List of settings
 Role |  Database   |           Settings
------+-------------+-------------------------------
      | helloprisma | log_min_duration_statement=2000
(1 row)

SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx

-[ RECORD 1 ]---+---------------------------------------------------------------------------
name            | log_min_duration_statement
setting         | 5000
unit            | ms
category        | Reporting and Logging / When to Log
short_desc      | Sets the minimum execution time above which all statements will be logged.
extra_desc      | Zero prints all queries. -1 turns this feature off.
context         | superuser
vartype         | integer
source          | configuration file
min_val         | -1
max_val         | 2147483647
enumvals        |
boot_val        | -1
reset_val       | 5000
sourcefile      | /etc/postgresql/14/main/postgresql.conf
sourceline      | 506
pending_restart | f

測試慢速查詢記錄

透過發出一個超過最小記錄持續時間的語句來測試設定

SELECT pg_sleep(10);

 pg_sleep
----------

(1 row)

檢查日誌，您應該會找到指示發生長時間執行查詢的語句

2022-11-11 17:58:04.719 CET [1121088] postgres@postgres STATEMENT:  select sleep(10);
2022-11-11 17:58:42.635 CET [1121088] postgres@postgres LOG:  duration: 10017.171 ms  statement: select pg_sleep(10);

由於我們對全域限制和特定表格有不同的閾值，因此我們可以透過使用應觸發其中一個但不觸發另一個的查詢時間來測試是否正確套用每個閾值。

例如，我們可以連線到具有較低閾值的資料庫並休眠 4 秒，這應該會觸發日誌行

\c helloprisma
SELECT pg_sleep(4);

我們的日誌顯示

2022-11-13 14:46:07.361 CET [1252789] postgres@helloprisma STATEMENT:  alter database helloprisma set log_min_duration_statement=2s;
2022-11-13 14:53:05.027 CET [1309069] postgres@helloprisma LOG:  duration: 4022.546 ms  statement: select pg_sleep(4);

現在，我們可以切換到另一個僅受全域設定影響的資料庫。相同的休眠語句不應觸發日誌行

\c postgres
SELECT pg_sleep(4);

不應記錄新的日誌行。