數據庫優化是一個綜合工程，不僅僅是需要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能夠控制的優化方法，也是最為有效的提升統計報表性能的方法。

最后一點，自動化任務沖突是所有數據庫都會遇到的性能問題，如果數據庫備份，大批量統計作業與大數據量導入導出同時發生，再好的硬件也可能撐不住，因此在設計這些定期任務的時候，一定要通過算法將這些作業分開，千萬不要讓這些大型操作存在最大公約數。否則哪怕現在你的系統沒問題，幾年后，還是會出問題的。