建站
咨詢
售后
建站咨詢
新聞中心
數(shù)據(jù)庫優(yōu)化是一個綜合工程，不僅僅是需要DBA參與，更重要的是研發(fā)設計人員針對PG數(shù)據(jù)庫的特點來進行相關的優(yōu)化設計。不過對于DBA來說，一旦接到上線和運維任務，基本上都是木已成舟，軟件設計方面留下的坑已經挖好，DBA的作為已經十分有限了。不過既然要干運維，那么少不了就要參與優(yōu)化。PG的優(yōu)化工作該如何開展呢？今天我從幾個主要的方面聊聊PG優(yōu)化的幾個常見的角度。針對PG數(shù)據(jù)庫，只要做好了下面幾個方面的優(yōu)化工作，那么運維起來也就比較省心了。
金山ssl適用于網站、小程序/APP、API接口等需要進行數(shù)據(jù)傳輸應用場景，ssl證書未來市場廣闊！成為成都創(chuàng)新互聯(lián)公司的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18982081108（備注：SSL證書合作）期待與您的合作！
- 硬件資源問題：如果數(shù)據(jù)庫服務器硬件資源不足，例如 CPU、內存、磁盤 IO 等，會導致系統(tǒng)性能下降，響應時間變慢。
- 操作系統(tǒng)配置不合理：如果操作系統(tǒng)沒有針對PG數(shù)據(jù)庫進行優(yōu)化，那么PG數(shù)據(jù)庫也無法發(fā)揮最佳的效能，因此針對PG數(shù)據(jù)庫的優(yōu)化，從操作系統(tǒng)參數(shù)調整入手永遠是不會錯的。
- 文件系統(tǒng)配置不合理：對于一些負載較高的大型數(shù)據(jù)庫來說，如果無法發(fā)揮后端存儲的IO能力，或者說讓后端磁盤出現(xiàn)了性能問題，那么就會嚴重影響PG數(shù)據(jù)庫的性能甚至穩(wěn)定性。對于大型數(shù)據(jù)庫來說，文件系統(tǒng)設計與配置一定要十分用心。
- SQL不夠優(yōu)化：如果應用沒有經過優(yōu)化，可能會導致查詢效率低下，索引設計不合理，缺少必要的索引，過多的單列索引以及索引類型使用不合理等都會帶來性能問題。最后不合理多表的 JOIN、WHERE 子句和大表并行掃碼都可能成為性能殺手。
- 數(shù)據(jù)庫結構設計不合理：如果數(shù)據(jù)庫結構設計不合理，可能會導致查詢效率低下，例如表過度歸一化、大表未分區(qū)或者分區(qū)設置不合理，表或者索引的的FILL FACTOR參數(shù)設置不合理導致的熱塊沖突。索引設計不合理產生的不必要的寫成本過高。應該存儲到對象存儲中的非結構化數(shù)據(jù)存儲到PG數(shù)據(jù)庫中等。表分區(qū)設計不合理，時序數(shù)據(jù)沒有使用timescaledb的自動分區(qū)與自動壓縮特性也會導致時序數(shù)據(jù)訪問的性能不佳。
- 數(shù)據(jù)庫參數(shù)設置不合理：如果 PostgreSQL 數(shù)據(jù)庫參數(shù)設置不合理，可能會導致數(shù)據(jù)庫性能低下，例如 shared_buffers、work_mem、WAL/Checkpoint 等參數(shù)的設置等。
- 并發(fā)控制不合理：如果數(shù)據(jù)庫并發(fā)控制不合理，可能會導致性能下降，這方面包含事務隔離級別設置不合理，并發(fā)度相關參數(shù)設置不合理等。
- 緩存命中率低：如果緩存命中率低，會導致頻繁的磁盤 IO 操作，從而降低數(shù)據(jù)庫性能。
- 訪問冷數(shù)據(jù)的性能不足：PG數(shù)據(jù)庫是采用DOUBLE CACHE機制的，冷數(shù)據(jù)是指在SHARED BUFFERS和OS CACHE中都不存在的數(shù)據(jù)，這些數(shù)據(jù)一旦要訪問，要產生大量的物理IO，訪問性能較差。
- 自動化任務沖突：如果數(shù)據(jù)庫中存在大量的自動化任務，例如備份、VACUUM、定時任務等，可能會導致任務之間的沖突，從而影響系統(tǒng)性能。
硬件資源不足的問題我們就不多加討論了，這種情況一般會出現(xiàn)在CPU、IO等方面，在分析這方面問題的時候，需要關注R隊列的長度是否超過CPU邏輯核數(shù)的2倍以上，對于IO來說，不僅僅要看IOPS/IO吞吐量等指標，更重要的是要看IO延時是否合理。
操作系統(tǒng)配置不合理是絕大多數(shù)PG數(shù)據(jù)庫都存在的問題，這方面實際上是有一些最佳實踐的。
[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數(shù)據(jù)庫RHEL參數(shù)優(yōu)化的建議，大家可以參考，對于絕大多數(shù)高負載的系統(tǒng)來說，都是有效的。大家要注意的是，關于臟塊回寫的設置，對于不同的寫IO負載以及不同的底層IO硬件，可能調整會有不同，甚至會有截然相反的配置策略。要注意的是，絕對不能因為不合理的臟塊刷新策略導致了OS IO負載的過載。在此前提下，縮短IO寫盤的周期對于提高并發(fā)負載是有幫助的。
文件系統(tǒng)的設計對于大型系統(tǒng)來說十分關鍵，除了使用XFS與EXT4等帶日志的文件系統(tǒng)并且打開日志功能外，設置文件系統(tǒng)的mount參數(shù)對性能也有很大影響。文件系統(tǒng)的條帶大小、塊大小要與PG數(shù)據(jù)庫匹配，MOUNT時也要加入nobarrier、noatime,nodiratime等參數(shù)，并做好扇區(qū)對齊，除此之外就是文件存儲方面的性能優(yōu)化了。
很多DBA都只會設置一個$PGDATA，整個數(shù)據(jù)庫都放在同一個文件系統(tǒng)上，這需要對文件系統(tǒng)底層的卷做十分細致的優(yōu)化，確保整個卷的IO能力是優(yōu)秀的，這一點總是無法做到的。因此在數(shù)據(jù)庫設計的時候就通過WAL與數(shù)據(jù)文件分離，熱數(shù)據(jù)與冷數(shù)據(jù)分離，通過表空間隔離熱點IO等方式規(guī)劃PG數(shù)據(jù)庫的文件存儲。如果應用系統(tǒng)已經無法通過表空間來隔離IO熱點，那么通過軟連接將部分庫的目錄遷移到其他文件系統(tǒng)也是一個可行的方案。
對于數(shù)據(jù)庫參數(shù)來說，實際上不同的應用場景下的最佳調整方案是不同的，一般來說，設置合理的shared_buffers，以及優(yōu)化好相關的而bgwriter，WAL，checkpoint，work_mem，VACUUM等相關的參數(shù)，就能夠滿足大多數(shù)應用的需求了。在這里我們就不做過多的討論了。在這方面我以前寫過十多篇文章，有興趣的朋友可以到公眾號通過搜索“性能優(yōu)化”或者通過公眾號的菜單去查找。
并發(fā)控制不合理方面的問題是比較容易被忽視的問題，事務隔離級別用錯對于性能的影響極大，不過一般情況下我們都是使用read committed，不要輕易去修改數(shù)據(jù)庫級的事務隔離級別。
并發(fā)的另外一個方面是系統(tǒng)中的各類并發(fā)訪問的控制，特別是并行執(zhí)行的設置。max_worker_processes、max_parallel_workers、max_parallel_maintenance_workers和max_parallel_workers_per_gather等參數(shù)對數(shù)據(jù)庫的并發(fā)度控制都至關重要。
如果并發(fā)相關的設置過小，那么當活躍會話數(shù)量不高的時候，無法充分發(fā)揮服務器硬件的資源優(yōu)勢，造成巨大的浪費。PG數(shù)據(jù)庫可以支撐巨大的數(shù)據(jù)庫與極高的并發(fā)，因此如果服務器的配置足夠好，系統(tǒng)資源使用率不高，但是應用性能無法達到設計要求，那么我們就應該關注一下是否并發(fā)控制相關的參數(shù)設置過低了。默認的PG參數(shù)里,max_worker_processes是偏小的，僅僅是8,對于有上百甚至上千個邏輯核數(shù)的服務器來說是完全不夠用的。
當然如果因為并發(fā)控制參數(shù)設置的過高而導致了CPU等資源出現(xiàn)了不足，因為IOPS過大或者IO吞吐量過大，底層存儲能力不足導致的IO延時過大等現(xiàn)象，那么適當調低這些參數(shù)對數(shù)據(jù)庫的整體性能提升是有幫助的。
PG的SHARED_BUFFERS設置不合理可能會導致緩沖區(qū)命中率不高，從而影響SQL的執(zhí)行性能。不過PG數(shù)據(jù)庫是使用DOUBLE BUFFER機制的，要想為你的應用調整好緩沖區(qū)并不容易。再怎么調整都無法滿足不同場景的應用，有些時候DBA真的很難通過調整來優(yōu)化這方面的性能。對于一些定期的報表等應用，在跑批之前做數(shù)據(jù)預熱可能是DBA能夠控制的優(yōu)化方法，也是最為有效的提升統(tǒng)計報表性能的方法。
最后一點，自動化任務沖突是所有數(shù)據(jù)庫都會遇到的性能問題，如果數(shù)據(jù)庫備份，大批量統(tǒng)計作業(yè)與大數(shù)據(jù)量導入導出同時發(fā)生，再好的硬件也可能撐不住，因此在設計這些定期任務的時候，一定要通過算法將這些作業(yè)分開，千萬不要讓這些大型操作存在最大公約數(shù)。否則哪怕現(xiàn)在你的系統(tǒng)沒問題，幾年后，還是會出問題的。
當前標題：Postgresql數(shù)據(jù)庫優(yōu)化上該考慮些什么
URL分享：http://m.5511xx.com/article/djgdjgp.html
