數(shù)據(jù)庫優(yōu)化是一個(gè)綜合工程，不僅僅是需要DBA參與，更重要的是研發(fā)設(shè)計(jì)人員針對(duì)PG數(shù)據(jù)庫的特點(diǎn)來進(jìn)行相關(guān)的優(yōu)化設(shè)計(jì)。不過對(duì)于DBA來說，一旦接到上線和運(yùn)維任務(wù)，基本上都是木已成舟，軟件設(shè)計(jì)方面留下的坑已經(jīng)挖好，DBA的作為已經(jīng)十分有限了。不過既然要干運(yùn)維，那么少不了就要參與優(yōu)化。PG的優(yōu)化工作該如何開展呢？今天我從幾個(gè)主要的方面聊聊PG優(yōu)化的幾個(gè)常見的角度。針對(duì)PG數(shù)據(jù)庫，只要做好了下面幾個(gè)方面的優(yōu)化工作，那么運(yùn)維起來也就比較省心了。

• 硬件資源問題：如果數(shù)據(jù)庫服務(wù)器硬件資源不足，例如 CPU、內(nèi)存、磁盤 IO 等，會(huì)導(dǎo)致系統(tǒng)性能下降，響應(yīng)時(shí)間變慢。
• 操作系統(tǒng)配置不合理：如果操作系統(tǒng)沒有針對(duì)PG數(shù)據(jù)庫進(jìn)行優(yōu)化，那么PG數(shù)據(jù)庫也無法發(fā)揮最佳的效能，因此針對(duì)PG數(shù)據(jù)庫的優(yōu)化，從操作系統(tǒng)參數(shù)調(diào)整入手永遠(yuǎn)是不會(huì)錯(cuò)的。
• 文件系統(tǒng)配置不合理：對(duì)于一些負(fù)載較高的大型數(shù)據(jù)庫來說，如果無法發(fā)揮后端存儲(chǔ)的IO能力，或者說讓后端磁盤出現(xiàn)了性能問題，那么就會(huì)嚴(yán)重影響PG數(shù)據(jù)庫的性能甚至穩(wěn)定性。對(duì)于大型數(shù)據(jù)庫來說，文件系統(tǒng)設(shè)計(jì)與配置一定要十分用心。
• SQL不夠優(yōu)化：如果應(yīng)用沒有經(jīng)過優(yōu)化，可能會(huì)導(dǎo)致查詢效率低下，索引設(shè)計(jì)不合理，缺少必要的索引，過多的單列索引以及索引類型使用不合理等都會(huì)帶來性能問題。最后不合理多表的 JOIN、WHERE 子句和大表并行掃碼都可能成為性能殺手。
• 數(shù)據(jù)庫結(jié)構(gòu)設(shè)計(jì)不合理：如果數(shù)據(jù)庫結(jié)構(gòu)設(shè)計(jì)不合理，可能會(huì)導(dǎo)致查詢效率低下，例如表過度歸一化、大表未分區(qū)或者分區(qū)設(shè)置不合理，表或者索引的的FILL FACTOR參數(shù)設(shè)置不合理導(dǎo)致的熱塊沖突。索引設(shè)計(jì)不合理產(chǎn)生的不必要的寫成本過高。應(yīng)該存儲(chǔ)到對(duì)象存儲(chǔ)中的非結(jié)構(gòu)化數(shù)據(jù)存儲(chǔ)到PG數(shù)據(jù)庫中等。表分區(qū)設(shè)計(jì)不合理，時(shí)序數(shù)據(jù)沒有使用timescaledb的自動(dòng)分區(qū)與自動(dòng)壓縮特性也會(huì)導(dǎo)致時(shí)序數(shù)據(jù)訪問的性能不佳。
• 數(shù)據(jù)庫參數(shù)設(shè)置不合理：如果 PostgreSQL 數(shù)據(jù)庫參數(shù)設(shè)置不合理，可能會(huì)導(dǎo)致數(shù)據(jù)庫性能低下，例如 shared_buffers、work_mem、WAL/Checkpoint 等參數(shù)的設(shè)置等。
• 并發(fā)控制不合理：如果數(shù)據(jù)庫并發(fā)控制不合理，可能會(huì)導(dǎo)致性能下降，這方面包含事務(wù)隔離級(jí)別設(shè)置不合理，并發(fā)度相關(guān)參數(shù)設(shè)置不合理等。
• 緩存命中率低：如果緩存命中率低，會(huì)導(dǎo)致頻繁的磁盤 IO 操作，從而降低數(shù)據(jù)庫性能。
• 訪問冷數(shù)據(jù)的性能不足：PG數(shù)據(jù)庫是采用DOUBLE CACHE機(jī)制的，冷數(shù)據(jù)是指在SHARED BUFFERS和OS CACHE中都不存在的數(shù)據(jù)，這些數(shù)據(jù)一旦要訪問，要產(chǎn)生大量的物理IO，訪問性能較差。
• 自動(dòng)化任務(wù)沖突：如果數(shù)據(jù)庫中存在大量的自動(dòng)化任務(wù)，例如備份、VACUUM、定時(shí)任務(wù)等，可能會(huì)導(dǎo)致任務(wù)之間的沖突，從而影響系統(tǒng)性能。

硬件資源不足的問題我們就不多加討論了，這種情況一般會(huì)出現(xiàn)在CPU、IO等方面，在分析這方面問題的時(shí)候，需要關(guān)注R隊(duì)列的長(zhǎng)度是否超過CPU邏輯核數(shù)的2倍以上，對(duì)于IO來說，不僅僅要看IOPS/IO吞吐量等指標(biāo)，更重要的是要看IO延時(shí)是否合理。

操作系統(tǒng)配置不合理是絕大多數(shù)PG數(shù)據(jù)庫都存在的問題，這方面實(shí)際上是有一些最佳實(shí)踐的。

[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

上面是一個(gè)紅帽公司對(duì)于PG數(shù)據(jù)庫RHEL參數(shù)優(yōu)化的建議，大家可以參考，對(duì)于絕大多數(shù)高負(fù)載的系統(tǒng)來說，都是有效的。大家要注意的是，關(guān)于臟塊回寫的設(shè)置，對(duì)于不同的寫IO負(fù)載以及不同的底層IO硬件，可能調(diào)整會(huì)有不同，甚至?xí)薪厝幌喾吹呐渲貌呗?。要注意的是，絕對(duì)不能因?yàn)椴缓侠淼呐K塊刷新策略導(dǎo)致了OS IO負(fù)載的過載。在此前提下，縮短IO寫盤的周期對(duì)于提高并發(fā)負(fù)載是有幫助的。

文件系統(tǒng)的設(shè)計(jì)對(duì)于大型系統(tǒng)來說十分關(guān)鍵，除了使用XFS與EXT4等帶日志的文件系統(tǒng)并且打開日志功能外，設(shè)置文件系統(tǒng)的mount參數(shù)對(duì)性能也有很大影響。文件系統(tǒng)的條帶大小、塊大小要與PG數(shù)據(jù)庫匹配，MOUNT時(shí)也要加入nobarrier、noatime,nodiratime等參數(shù)，并做好扇區(qū)對(duì)齊，除此之外就是文件存儲(chǔ)方面的性能優(yōu)化了。

很多DBA都只會(huì)設(shè)置一個(gè)$PGDATA，整個(gè)數(shù)據(jù)庫都放在同一個(gè)文件系統(tǒng)上，這需要對(duì)文件系統(tǒng)底層的卷做十分細(xì)致的優(yōu)化，確保整個(gè)卷的IO能力是優(yōu)秀的，這一點(diǎn)總是無法做到的。因此在數(shù)據(jù)庫設(shè)計(jì)的時(shí)候就通過WAL與數(shù)據(jù)文件分離，熱數(shù)據(jù)與冷數(shù)據(jù)分離，通過表空間隔離熱點(diǎn)IO等方式規(guī)劃PG數(shù)據(jù)庫的文件存儲(chǔ)。如果應(yīng)用系統(tǒng)已經(jīng)無法通過表空間來隔離IO熱點(diǎn)，那么通過軟連接將部分庫的目錄遷移到其他文件系統(tǒng)也是一個(gè)可行的方案。

對(duì)于數(shù)據(jù)庫參數(shù)來說，實(shí)際上不同的應(yīng)用場(chǎng)景下的最佳調(diào)整方案是不同的，一般來說，設(shè)置合理的shared_buffers，以及優(yōu)化好相關(guān)的而bgwriter，WAL，checkpoint，work_mem，VACUUM等相關(guān)的參數(shù)，就能夠滿足大多數(shù)應(yīng)用的需求了。在這里我們就不做過多的討論了。在這方面我以前寫過十多篇文章，有興趣的朋友可以到公眾號(hào)通過搜索“性能優(yōu)化”或者通過公眾號(hào)的菜單去查找。

并發(fā)控制不合理方面的問題是比較容易被忽視的問題，事務(wù)隔離級(jí)別用錯(cuò)對(duì)于性能的影響極大，不過一般情況下我們都是使用read committed，不要輕易去修改數(shù)據(jù)庫級(jí)的事務(wù)隔離級(jí)別。

并發(fā)的另外一個(gè)方面是系統(tǒng)中的各類并發(fā)訪問的控制，特別是并行執(zhí)行的設(shè)置。max_worker_processes、max_parallel_workers、max_parallel_maintenance_workers和max_parallel_workers_per_gather等參數(shù)對(duì)數(shù)據(jù)庫的并發(fā)度控制都至關(guān)重要。

如果并發(fā)相關(guān)的設(shè)置過小，那么當(dāng)活躍會(huì)話數(shù)量不高的時(shí)候，無法充分發(fā)揮服務(wù)器硬件的資源優(yōu)勢(shì)，造成巨大的浪費(fèi)。PG數(shù)據(jù)庫可以支撐巨大的數(shù)據(jù)庫與極高的并發(fā)，因此如果服務(wù)器的配置足夠好，系統(tǒng)資源使用率不高，但是應(yīng)用性能無法達(dá)到設(shè)計(jì)要求，那么我們就應(yīng)該關(guān)注一下是否并發(fā)控制相關(guān)的參數(shù)設(shè)置過低了。默認(rèn)的PG參數(shù)里,max_worker_processes是偏小的，僅僅是8,對(duì)于有上百甚至上千個(gè)邏輯核數(shù)的服務(wù)器來說是完全不夠用的。

當(dāng)然如果因?yàn)椴l(fā)控制參數(shù)設(shè)置的過高而導(dǎo)致了CPU等資源出現(xiàn)了不足，因?yàn)镮OPS過大或者IO吞吐量過大，底層存儲(chǔ)能力不足導(dǎo)致的IO延時(shí)過大等現(xiàn)象，那么適當(dāng)調(diào)低這些參數(shù)對(duì)數(shù)據(jù)庫的整體性能提升是有幫助的。

PG的SHARED_BUFFERS設(shè)置不合理可能會(huì)導(dǎo)致緩沖區(qū)命中率不高，從而影響SQL的執(zhí)行性能。不過PG數(shù)據(jù)庫是使用DOUBLE BUFFER機(jī)制的，要想為你的應(yīng)用調(diào)整好緩沖區(qū)并不容易。再怎么調(diào)整都無法滿足不同場(chǎng)景的應(yīng)用，有些時(shí)候DBA真的很難通過調(diào)整來優(yōu)化這方面的性能。對(duì)于一些定期的報(bào)表等應(yīng)用，在跑批之前做數(shù)據(jù)預(yù)熱可能是DBA能夠控制的優(yōu)化方法，也是最為有效的提升統(tǒng)計(jì)報(bào)表性能的方法。

最后一點(diǎn)，自動(dòng)化任務(wù)沖突是所有數(shù)據(jù)庫都會(huì)遇到的性能問題，如果數(shù)據(jù)庫備份，大批量統(tǒng)計(jì)作業(yè)與大數(shù)據(jù)量導(dǎo)入導(dǎo)出同時(shí)發(fā)生，再好的硬件也可能撐不住，因此在設(shè)計(jì)這些定期任務(wù)的時(shí)候，一定要通過算法將這些作業(yè)分開，千萬不要讓這些大型操作存在最大公約數(shù)。否則哪怕現(xiàn)在你的系統(tǒng)沒問題，幾年后，還是會(huì)出問題的。