10個常見的 PostgreSQL 錯誤及避坑指南!

10個常見的 PostgreSQL 錯誤及避坑指南!

【CSDN 編者按】PostgreSQL 作為當下流行的資料庫,不少開發者因其開源、可靠、可擴充套件等特性把它應用到實際的生產環境中,幫助無數 PostgreSQL 廠商的 Percona 編制了一個最常見錯誤的列表。即使你認為自己已經正確地安裝配置 PostgreSQL,或許仍會發現此列表對於驗證你的安裝配置大有裨益。

原文連結:https://www。infoworld。com/article/3681655/10-common-postgresql-mistakes-and-how-to-avoid-them。html

作者 | Hamid Akhtar 編譯 | 王雪迎

出品 | CSDN(ID:CSDNnews)

PostgreSQL 旨在應對廣泛的使用場景,但具有極大靈活性的同時也有不利的一面。使用時應注意不要犯本文所列舉的這些十分常見的設計、配置、調整或其他相關錯誤。

在安裝 PostgreSQL 時可能會忽略很多問題,其中某些問題也許因潛伏而未被發現。隨著時間的推移,它們可能突然爆發併產生重大影響,使其成為大家關注的焦點,這種情況尤其糟糕。

無論是效能明顯下降,還是資源消耗或使用成本的急劇上升,在出現這些情況前儘早發現問題都很重要。而更好的做法是,實施時透過配置以適應所需的工作負載來避免這些問題。

錯誤1:執行預設配置

PostgreSQL 可以做到開箱即用,但其配置通常不能很好地滿足需求。預設配置沒有針對任何特定的工作負載進行調整,具有極大的侷限性。這種過於保守的配置,目的在於允許 PostgreSQL 執行在任何環境下,並期望使用者根據自己的需要進行配置。

pgtune 工具提供了基於硬體資源和工作負載型別的配置子集,是根據工作負載需要配置 PostgreSQL 叢集的良好起點。此外,還可能需要配置 autovacuum、日誌、檢查點和 WAL(預寫日誌)保留策略等變數。

為伺服器進行最佳化配置,以滿足全部近期需求,同時避免任何不必要的重啟操作,這一點非常重要。所以有必要看一下 pg_settings 系統檢視中所有具有 “postmaster” 上下文的 GUC。

SELECT name, setting, boot_valFROM pg_settingsWHERE context = ‘postmaster’;

尤其在設定高可用(HA)群集時,這點更為重要,因為主伺服器的任何停機都會降低群集效能,並導致將備用伺服器提升為主伺服器角色。

錯誤2:未最佳化的資料庫設計和架構

關於這點怎麼強調都不為過。我曾親眼看到,僅僅因為未最佳化的資料庫設計和架構,使用者付出的成本是他們所需成本的五倍多。

這裡最好的建議之一是看看現在和近期的工作負載需求時什麼,而不是六個月到一年後可能需要什麼。向前看得太遠可能意味著你的表是為永遠無法實現的未來需求而設計的。這只是其中的一個方面。

除此之外,過度依賴物件關係對映(ORM)也是效能不佳的主要原因之一。ORM 主要用於使用面向物件的程式語言將應用程式連線到資料庫,久而久之它們會逐漸簡化開發人員的工作。然而,瞭解 ORM 提供什麼功能以及它引入了什麼樣的效能影響至關重要。ORM 可能正在後臺執行多個查詢,不管是連線多個表、執行聚合,還是拆分查詢資料。總的來說,使用 ORM 時會引起更高的延遲和更低的事務吞吐量。

另外,改進資料庫架構還為了使資料更加結構化,以便對錶或索引進行最佳的讀寫操作。另一種有用的方法是對資料庫進行反規範化,因為這會降低 SQL 查詢的複雜性,減少相關的表連線,進而可以從更少的表中獲取資料。

簡單來說最終驅動高效能的,是對具體環境中的應用程式和工作負載執行三步過程,即“定義、測量、最佳化”。

錯誤3:沒有針對工作負載調整資料庫

根據工作負載調整資料庫,需要深入瞭解要儲存的資料量、應用程式的性質,以及要執行的查詢型別。可以隨時修改配置並進行基準測試,直到對高負載下的資源使用滿意為止。

例如,考慮是否可以將整個資料庫放入計算機的可用記憶體中。如果是,那麼顯然希望增加資料庫的 shared_buffers 值。類似地,瞭解工作負載是如何正確配置檢查點和 autovacuum 程序的關鍵。例如,與滿足事務處理效能委員會 C 類基準的混合線上事務處理工作負載相比,為 append-only 型別工作負載進行的這些配置將非常不同。

有很多有用的工具提供了檢視查詢效能的功能。關於更多檢視查詢效能的說明,可以瀏覽我的部落格文章,其中討論了一些可選的開源工具。還可以看下我在 YouTube 上的演示。

在 Percona,我們的兩個工具可以極大地幫助理解查詢效能狀況:

lPMM - Percona監控和管理,是一個免費的、完全開源的專案,提供了一個帶有詳細系統統計和查詢分析的圖形介面。可隨意試用適合MySQL、MongoDB或PostgreSQL的PMM演示程式。

lpg_stat_monitor - 這是pg_stat_statements的增強版本,可以藉此更詳細地瞭解查詢效能狀況、實際的查詢計劃和帶有引數值的查詢文字。可以從我們的下載頁獲得它在Linux上的可用包,也可以從PostgreSQL社群的yum儲存庫獲得RPM包。

錯誤4:連線管理不當

乍一看連線配置似乎沒問題,但是我見過太大的 max_connections 值導致記憶體不足錯誤的情況,所以配置 max_connection 還需要注意一下。

配置 max_connections 時必須考慮核心數、可用記憶體量和儲存型別。誰都不希望讓可能永遠不會使用的連線致使伺服器資源過載,況且還要為每個連線分配核心資源。PostgreSQL 核心文件有更多詳細資訊。

當客戶端執行花費很少時間的查詢時,連線池能顯著提高效能,因為在這種型別的工作負載中,生成連線的開銷相對變得很大。

錯誤5:Vacuum 工作異常

希望 autovacuum 沒有被禁用。我們已經在許多生產環境中看到,使用者完全禁用了 autovacuum,這通常是由於一些潛在的問題所導致。如果 autovacuum 在具體環境中不起作用,那麼只可能有以下三個原因:

1。vacuum 過程沒有被觸發,或者至少沒有像應該的那樣頻繁。

2。Vacuuming 太慢。

3。vacuum 沒有清理沒用的舊版本資料。

其中 1 和 2 都與配置選項直接相關。可以透過查詢 pg_settings 系統檢視來檢視vacuum相關選項。

SELECT name, short_desc, setting, unit, CASEWHEN context = ‘postmaster’ THEN ‘restart’WHEN context = ‘sighup’ THEN ‘reload’ELSE contextEND “server requires”FROM pg_settingsWHERE name LIKE ‘%vacuum%’;

透過調整 autovacuum_work_mem 和並行工作執行緒的數量,可以潛在提高速度。vacuum 過程的觸發可以透過配置比例因子或閾值來調節。

當 vacuum 過程沒有清理沒用的舊版本資料時,表明有某種東西阻礙了獲取關鍵資源,罪魁禍首可能是以下一項或多項:

長時間執行的查詢或事務;

複製環境中的備用伺服器啟用了 hot_Standby_feedback 選項;

大於所需的 vacuum_defer_cleanup_age 值;

保持 xmin 值的複製槽阻止了 vacuum 清理沒用的舊版本資料。

如果想手動管理表的 vacuum 過程,那麼請遵循帕累託定律(即80/20法則)。將叢集調整到適合 80% 的表的最佳配置,然後專門針對剩下 20% 的表進行調整。記住,可以透過在 create 或 alter 語句中指定相關的儲存選項,來為特定表禁用 autovacuum 或 toast。autovacuum。

錯誤6:惡意連線和長時間執行的事務

PostgreSQL 叢集可能會受到很多因素的影響,而惡意連線就是其中之一。除了佔用可能被其他應用程式使用的連線槽外,惡意連線和長時間執行的事務佔用關鍵資源,這可能會對整個系統造成嚴重破壞。看一個較小程度的影響:在啟用了 hot_standby_feedback 的複製環境中,備用伺服器上的長時間事務可能會阻止主伺服器上的 vacuum 完成其工作。

試想一個有問題的應用程式,它開啟一個事務,然後停止響應。程式可能會持有鎖,或者只是阻止 vacuum 清理舊版本資料,因為這些資料在此事務中仍然可見。如果該應用程式打開了大量此類事務怎麼辦?

通常情況下,可以透過將 idle_in_transaction_session_timeout 配置為針對查詢調整的值來消除此類事務。當然每次修改引數時,都要記住應用程式的行為。

除了調整 idle_in_transaction_session_timeout 之外,還要監控 pg_stat_activity 系統檢視以檢視任何長時間執行的查詢,或等待客戶端相關事件的時間超過預期時間的會話。注意時間戳、等待事件和狀態列。

backend_start | 2022-10-25 09:25:07。934633+00xact_start | 2022-10-25 09:25:11。238065+00query_start | 2022-10-25 09:25:11。238065+00state_change | 2022-10-25 09:25:11。238381+00wait_event_type | Clientwait_event | ClientReadstate | idle in transaction

此外,準備事務(尤其是孤立的準備事務)也可能持有關鍵系統資源(如鎖或xmin值等)。我建議為準備事務設定一個命名法來定義它們的存在期限。比如,一個最長存在時間為 5 分鐘的準備事務可以建立為 PREPARE TRANSACTION ‘foo_prepared 5m’。

SELECT gid, prepared, REGEXP_REPLACE(gid, ‘。* ’, ‘’) AS ageFROM pg_prepared_xactsWHERE prepared + CAST(regexp_replace(gid, ‘。* ’, ‘’) AS INTERVAL) < NOW();

這為應用程式提供了一種定義其準備事務的期限的方案。繼而使用 cronjob 或計劃作業,就可以監控或回滾任何在其預期期限之後仍保持活動狀態的準備事務。

錯誤7:索引過度或索引不足

對錶進行過度索引究竟有沒有問題呢?必須要了解 PostgreSQL 如何管理索引,才能使 PostgreSQL 例項獲得最佳效能。

PostgreSQL 中有多種型別的索引,每種都有不同的使用場景和開銷。B 樹是最常用的索引型別,也用於主鍵。在過去的幾個主要版本中,B樹索引中出現了許多與效能相關(或剝離)的改進。這裡是我的一篇博文,討論了 PostgreSQL 14 中的重複版本變動。

當對錶執行索引掃描時,對於每個匹配的行,索引會回表訪問以獲取資料和可見性資訊,以便只選擇當前事務可見的版本的資料。過度索引將導致更多的索引更新,因此會消耗更多資源而得不到預期的好處。

同樣,索引不足將導致更多的表掃描,這可能導致更多的 I/O 操作,從而導致效能下降。

建立索引時不僅要考慮表上的索引數量,還要考慮這些索引在所針對的查詢上如何進行最佳化。理想情況下,希望每次查詢只掃描一個索引,但有一些限制。儘管 B 樹索引支援所有運算子的索引掃描,但 GiST 和 SP GiST 索引僅支援某些運算子。有關詳細資訊,請參閱文件。

以下簡單的檢查項,可以幫助驗證是否為系統進行了最佳索引設定:

確保配置正確(例如,為相關硬體調整了隨機頁訪問成本)。

檢查統計資料是否最新,或者至少檢查執行analyze或vacuum命令的表上是否有索引。確保統計資料最新或接近最新,以便查詢計劃更有可能選擇索引掃描。

建立正確型別的索引(B樹、雜湊或其他型別)。

在正確的列上使用索引。不要忘記,在索引中包含查詢所需列可以避免回表訪問,即所謂索引覆蓋。並非所有索引型別都允許索引覆蓋,因此使用時請檢查文件。

去除不必要的索引。請參閱pg_statio_user_indexes,瞭解有關索引和塊命中的更多資訊。

瞭解索引覆蓋對重複資料消除、重複版本變動和僅索引掃描等功能的影響。

錯誤8:備份和 HA 不足

HA 的作用不僅是保持服務的正常執行,還要確保服務在定義的驗收標準內進行響應,並滿足 RPO(恢復點目標)和 RTO(恢復時間目標)目標。要達到系統正常執行時間要求的9的個數(正常使用時間與總時間之比),請參閱此wiki頁面以計算百分比。

為了滿足 RPO 和 RTO,必須考慮許多因素,包括計劃內停機時間、任何自動或手動操作及其頻率和持續時間,當然還有與計劃外停機相關的成本。

擁有準確和及時的備份,以及有效恢復備份的能力,在定義 RPO 和 RTO 這兩個引數方面起著關鍵作用,其中涉及資料備份的頻率是多少,怎樣管理 WAL 檔案,如何驗證備份和 WAL 檔案等諸多問題。

根據工作負載和可用的維護時間視窗,通常應至少每七天進行一次備份。除此之外,還應該定期測試恢復過程,以便確認這些備份是有效的。事實上,只有應用程式能夠恢復並進行處理,備份才算成功。不應信任未經測試的備份。

錯誤9:錯誤管理擴充套件模組

PostgreSQL 自帶 50 多個擴充套件模組,而後還有個人或組織提供的第三方擴充套件模組。PostgreSQL 核心提供了一些常用的擴充套件模組,如 pg_stat_statements,此外還有一些著名的擴充套件模組,例如 PostGIS,它們不是核心的一部分。

首先應該確保所部署的任何一組擴充套件模組都能夠一起工作,而不會相互影響。此外還有效能方面的考慮。有些擴充套件模組只是簡單的 SQL 擴充套件,而另一些擴充套件模組帶有共享物件或 DLL,這會消耗更多資源並影響整體效能,一定要了解這些擴充套件模組將消耗哪些資源。

更重要的是,任何預載入的擴充套件模組都會成為伺服器的一部分。無論是否透過發出 CREATEEXTENSION… 語句建立了 SQL 介面,這些預載入擴充套件模組都將在後臺工作。例如,無論是否建立了 SQL 介面,將 pg_stat_statements 新增到共享預載入庫中都會導致效能下降。這裡的總體經驗是仔細考慮是否真的需要這些擴充套件模組。

下面是一些很有用的關於對擴充套件模組的查詢。

可以查詢系統檢視pg_extension以獲取有關已安裝擴充套件模組的資訊。

SELECT * FROM pg_extension;

類似地,可以找出系統上所有可用的擴充套件模組。

SELECT * FROM pg_available_extensions();

還可以查詢所擁有的擴充套件模組的可用版本。

SELECT * FROM pg_available_extension_versions();

錯誤10:忽略支援工具

除 PostgreSQL 叢集本身以外,還應該考慮需要哪些其他支援工具以改善 PostgreSQL 的使用體驗,因此有必要了解可用的工具。由於舊版本存在嚴重問題,人們對某些工具存有誤解,所以應看下新版本、各個社群的活躍性以及釋出的頻率。

例如,讓我們回顧一下 PostgreSQL 生態系統中用於連線池和負載均衡的幾個工具:PgBouncer、HAProxy 和 Pgpoo II。

HAProxy 是一個負載均衡器。請注意,與各種作業系統發行版一起打包的 HAProxy 版本很舊。如 CentOS 7 中版本為 1。5,CentOS 8 中版本為 1。8,而 HAProxy 的最新版本是 2。6。作為參考,HAProxy 2。4 有 1687 個新提交程式碼。雖然使用作業系統發行版提供的包更容易,但這些包可能太舊了。

PgBouncer 是一款輕量級連線池。雖然它是單執行緒的,但如果執行多個 PgBouncer 例項並監聽同一埠,則支援 SO_REUSEPORT 選項的核心可能會允許負載均衡。需要檢查核心文件,看看它是否支援負載均衡或輪詢,也許根本不支援。使用 systemd 模板,可以以非常簡單和優雅的方式執行多個 PgBouncer 例項。只需建立檔案 /etc/systemd/system/pgbouncer@。service,並使用 systemctl start pgbouncer@1、systemctl start pgbouncer@2 等命令執行任意數量的 PgBouncer 例項。

Pgpool II 在過去幾年中取得了很大進步,添加了很多功能,包括監控和仲裁,所以它提供的不僅僅是連線池。

要選擇那種工具呢?PgBouncer、HAProxy、Pgpool II,還是 PgBouncer 和 HAProxy?答案取決於多種因素,例如要使用 HAProxy,需要考慮是否配置流複製,是否要為讀取和寫入設定單獨的埠等。最後的選擇將取決於具體的使用場景(在某些情況下還有誤用案例!)。

多種原因使 PostgreSQL 成為了一個非常流行的開源資料庫。它被設計為易於使用和可擴充套件,以滿足廣泛的使用者需求。然而,這種靈活性同時也意味著在使用它時必須審視用法,並在安裝時就考慮有針對性地進行相應的配置。這樣會使應用程式的效能更好,更能使使用者怡然自得,而且從長遠看,還可以節省大量成本。

10個常見的 PostgreSQL 錯誤及避坑指南!

☞☞

頂部