Postgresql資料庫優化上該考慮些什麼

来源:https://www.cnblogs.com/88223100/archive/2023/03/21/What-should-be-considered-for-Postgresql-database-optimization.html
-Advertisement-
Play Games

資料庫優化是一個綜合工程,不僅僅是需要DBA參與,更重要的是研發設計人員針對PG資料庫的特點來進行相關的優化設計。不過對於DBA來說,一旦接到上線和運維任務,基本上都是木已成舟,軟體設計方面留下的坑已經挖好,DBA的作為已經十分有限了。不過既然要乾運維,那麼少不了就要參與優化。PG的優化工作該如何開... ...


資料庫優化是一個綜合工程,不僅僅是需要DBA參與,更重要的是研發設計人員針對PG資料庫的特點來進行相關的優化設計。不過對於DBA來說,一旦接到上線和運維任務,基本上都是木已成舟,軟體設計方面留下的坑已經挖好,DBA的作為已經十分有限了。不過既然要乾運維,那麼少不了就要參與優化。PG的優化工作該如何開展呢?今天我從幾個主要的方面聊聊PG優化的幾個常見的角度。針對PG資料庫,只要做好了下麵幾個方面的優化工作,那麼運維起來也就比較省心了。

         

硬體資源問題:如果資料庫伺服器硬體資源不足,例如 CPU、記憶體、磁碟 IO 等,會導致系統性能下降,響應時間變慢。

 

操作系統配置不合理:如果操作系統沒有針對PG資料庫進行優化,那麼PG資料庫也無法發揮最佳的效能,因此針對PG資料庫的優化,從操作系統參數調整入手永遠是不會錯的。

 

文件系統配置不合理:對於一些負載較高的大型資料庫來說,如果無法發揮後端存儲的IO能力,或者說讓後端磁碟出現了性能問題,那麼就會嚴重影響PG資料庫的性能甚至穩定性。對於大型資料庫來說,文件系統設計與配置一定要十分用心。

 

SQL不夠優化:如果應用沒有經過優化,可能會導致查詢效率低下,索引設計不合理,缺少必要的索引,過多的單列索引以及索引類型使用不合理等都會帶來性能問題。最後不合理多表的 JOIN、WHERE 子句和大表並行掃碼都可能成為性能殺手。

 

資料庫結構設計不合理:如果資料庫結構設計不合理,可能會導致查詢效率低下,例如表過度歸一化、大表未分區或者分區設置不合理,表或者索引的的FILL FACTOR參數設置不合理導致的熱塊衝突。索引設計不合理產生的不必要的寫成本過高。應該存儲到對象存儲中的非結構化數據存儲到PG資料庫中等。表分區設計不合理,時序數據沒有使用timescaledb的自動分區與自動壓縮特性也會導致時序數據訪問的性能不佳。

 

資料庫參數設置不合理:如果 PostgreSQL 資料庫參數設置不合理,可能會導致資料庫性能低下,例如 shared_buffers、work_mem、WAL/Checkpoint 等參數的設置等。

 

併發控制不合理:如果資料庫併發控制不合理,可能會導致性能下降,這方麵包含事務隔離級別設置不合理,併發度相關參數設置不合理等。

 

緩存命中率低:如果緩存命中率低,會導致頻繁的磁碟 IO 操作,從而降低資料庫性能。

 

訪問冷數據的性能不足:PG資料庫是採用DOUBLE CACHE機制的,冷數據是指在SHARED BUFFERS和OS CACHE中都不存在的數據,這些數據一旦要訪問,要產生大量的物理IO,訪問性能較差。

 

自動化任務衝突:如果資料庫中存在大量的自動化任務,例如備份、VACUUM、定時任務等,可能會導致任務之間的衝突,從而影響系統性能。

         

硬體資源不足的問題我們就不多加討論了,這種情況一般會出現在CPU、IO等方面,在分析這方面問題的時候,需要關註R隊列的長度是否超過CPU邏輯核數的2倍以上,對於IO來說,不僅僅要看IOPS/IO吞吐量等指標,更重要的是要看IO延時是否合理。
操作系統配置不合理是絕大多數PG資料庫都存在的問題,這方面實際上是有一些最佳實踐的。
[sysctl]

vm.swappiness = 1

vm.dirty_background_ratio = 10

vm.dirty_ratio = 40

vm.dirty_expire_centisecs = 3000

vm.dirty_writeback_centisecs = 500

kernel.shmmax = 18446744073692700000

kernel.shmall = 18446744073692700000

kernel.shmmni = 4096

kernel.sem = 250 512000 100 2048

fs.file-max = 312139770

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 2048 65499

# Permits sockets in the time-wait state to be reused for new connections:

net.ipv4.tcp_tw_reuse = 1

net.core.netdev_budget = 1024

net.core.netdev_max_backlog = 2048

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

kernel.panic_on_oops = 1

# We don't need NUMA balancing in this box:

kernel.numa_balancing = 0

# Used if not defined by the service:

net.core.somaxconn = 4096

# Other parameters to override throughput-performance template

net.ipv4.tcp_rmem = 4096 87380 16777216

net.ipv4.tcp_wmem = 4096 65536 16777216

net.ipv4.tcp_window_scaling = 1

net.netfilter.nf_conntrack_max = 250000

net.ipv4.tcp_max_syn_backlog=4096

[vm]

transparent_hugepages=never

         

上面是一個紅帽公司對於PG資料庫RHEL參數優化的建議,大家可以參考,對於絕大多數高負載的系統來說,都是有效的。大家要註意的是,關於臟塊回寫的設置,對於不同的寫IO負載以及不同的底層IO硬體,可能調整會有不同,甚至會有截然相反的配置策略。要註意的是,絕對不能因為不合理的臟塊刷新策略導致了OS IO負載的過載。在此前提下,縮短IO寫盤的周期對於提高併發負載是有幫助的。
文件系統的設計對於大型系統來說十分關鍵,除了使用XFS與EXT4等帶日誌的文件系統並且打開日誌功能外,設置文件系統的mount參數對性能也有很大影響。文件系統的條帶大小、塊大小要與PG資料庫匹配,MOUNT時也要加入nobarrier、noatime,nodiratime等參數,並做好扇區對齊,除此之外就是文件存儲方面的性能優化了。
很多DBA都只會設置一個$PGDATA,整個資料庫都放在同一個文件系統上,這需要對文件系統底層的捲做十分細緻的優化,確保整個捲的IO能力是優秀的,這一點總是無法做到的。因此在資料庫設計的時候就通過WAL與數據文件分離,熱數據與冷數據分離,通過表空間隔離熱點IO等方式規劃PG資料庫的文件存儲。如果應用系統已經無法通過表空間來隔離IO熱點,那麼通過軟連接將部分庫的目錄遷移到其他文件系統也是一個可行的方案。
對於資料庫參數來說,實際上不同的應用場景下的最佳調整方案是不同的,一般來說,設置合理的shared_buffers,以及優化好相關的而bgwriter,WAL,checkpoint,work_mem,VACUUM等相關的參數,就能夠滿足大多數應用的需求了。在這裡我們就不做過多的討論了。在這方面我以前寫過十多篇文章,有興趣的朋友可以到公眾號通過搜索“性能優化”或者通過公眾號的菜單去查找。
併發控制不合理方面的問題是比較容易被忽視的問題,事務隔離級別用錯對於性能的影響極大,不過一般情況下我們都是使用read committed,不要輕易去修改資料庫級的事務隔離級別。
併發的另外一個方面是系統中的各類併發訪問的控制,特別是並行執行的設置。max_worker_processes、max_parallel_workers、max_parallel_maintenance_workers和max_parallel_workers_per_gather等參數對資料庫的併發度控制都至關重要。
如果併發相關的設置過小,那麼當活躍會話數量不高的時候,無法充分發揮伺服器硬體的資源優勢,造成巨大的浪費。PG資料庫可以支撐巨大的資料庫與極高的併發,因此如果伺服器的配置足夠好,系統資源使用率不高,但是應用性能無法達到設計要求,那麼我們就應該關註一下是否併發控制相關的參數設置過低了。預設的PG參數里,max_worker_processes是偏小的,僅僅是8,對於有上百甚至上千個邏輯核數的伺服器來說是完全不夠用的。
當然如果因為併發控制參數設置的過高而導致了CPU等資源出現了不足,因為IOPS過大或者IO吞吐量過大,底層存儲能力不足導致的IO延時過大等現象,那麼適當調低這些參數對資料庫的整體性能提升是有幫助的。
PG的SHARED_BUFFERS設置不合理可能會導致緩衝區命中率不高,從而影響SQL的執行性能。不過PG資料庫是使用DOUBLE BUFFER機制的,要想為你的應用調整好緩衝區並不容易。再怎麼調整都無法滿足不同場景的應用,有些時候DBA真的很難通過調整來優化這方面的性能。對於一些定期的報表等應用,在跑批之前做數據預熱可能是DBA能夠控制的優化方法,也是最為有效的提升統計報表性能的方法。
最後一點,自動化任務衝突是所有資料庫都會遇到的性能問題,如果資料庫備份,大批量統計作業與大數據量導入導出同時發生,再好的硬體也可能撐不住,因此在設計這些定期任務的時候,一定要通過演算法將這些作業分開,千萬不要讓這些大型操作存在最大公約數。否則哪怕現在你的系統沒問題,幾年後,還是會出問題的。
作者| 白鱔

本文來自博客園,作者:古道輕風,轉載請註明原文鏈接:https://www.cnblogs.com/88223100/p/What-should-be-considered-for-Postgresql-database-optimization.html


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 20230321 1.進程和線程的區別 進程(Process)和線程(Thread)是操作系統中的兩個重要概念。 一個進程可以看作是一個正在運行的程式實例,它擁有自己的地址空間、記憶體、數據棧和其他系統資源。一個進程可以包含多個線程,每個線程都是進程中獨立的執行流,每個線程共用進程的地址空間和系統資源 ...
  • 一、項目代碼 #vim /usr/local/src/mail_api_flask/run.py """ mail_api_flask 為基於Flask web框架開發的線上發送郵件api,實現功能復用。支持html模板郵件。 """ from flask import Flask from fla ...
  • Linux 中的 Bash 腳本支持對變數的操作,下麵鹹魚將介紹 Linux Bash Shell 中關於變數的 5 個易錯點 因為編程習慣,這類現象往往發生在大多數使用過其他流行編程語言的程式員身上 變數賦值 對於許多編程語言(例如 Python),變數賦值的時候在等號兩邊添加空格是一個好的習慣 ...
  • MySQL基礎:函數 函數是指一段可以直接被另一段程式調用的程式或代碼。 字元串函數 MySQL中內置了很多字元串函數,常用的幾個如下: | 函數 | 功能 | | : : | : : | | CONCAT(S1,S2,...Sn) | 字元串拼接,將S1,S2,...Sn拼接成一個字元串 | | ...
  • SQL:DML、DQL、DCL DML:Data Manipulation Language(數據操作語言) DML用來對資料庫中的數據記錄進行增刪改操作。 DML-添加數據 給指定欄位添加數據(一條數據) INSERT INTO 表名(欄位名1,欄位名2,...) VALUES(值1,值2,... ...
  • 摘要:本文就針對因USING子句的書寫方式可能導致MERGE INTO語句的執行不下推的場景,對USING子句的SQL語句進行改寫一遍,整個SQL語句可以下推。 本文分享自華為雲社區《GaussDB(DWS)運維 -- values子句做MERGE數據源導致SQL執行不下推的改寫方案》,作者: 譡里 ...
  • 什麼是主數據? 主數據是一組用於提供有關業務數據(如位置、客戶、產品、資產等)情境的標識符。它是企業或單位內運行業務必不可少的核心數據。否則,將無法統一比較系統之間的數據。但是,並非所有主數據都是一樣的。被指定為主數據的數據類型可能因行業而異。即使在同一行業的不同業務實體中,主數據的示例也可能是離散 ...
  • 眾所周知Redis有以下幾種常見的數據類型 String(字元串)、List(列表)、Set(集合)、Hash(哈希)、Sorted set(有序集合)、Stream(流)、Geo(地理空間索引)、Bitmap(點陣圖)、HyperLogLog(基數統計)等。 我們最常用的就是String(字元串)... ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...