大型的數(shù)據(jù)庫系統(tǒng)，往往都需要消耗大量的內(nèi)存資源，如果資源配置不合理，很容易出現(xiàn)系統(tǒng)可用內(nèi)存不足的問題。

內(nèi)存使用是數(shù)據(jù)庫系統(tǒng)最重要的方面之一。內(nèi)存不足會(huì)直接影響每個(gè)性能指標(biāo)，并對(duì)性能產(chǎn)生負(fù)面影響。這反過來又會(huì)影響我們的用戶和業(yè)務(wù)。在本文中，我們將了解 PostgreSQL 數(shù)據(jù)庫如何管理內(nèi)存，以及如何排查可用內(nèi)存不足的問題。

數(shù)據(jù)庫如何讀取數(shù)據(jù)

要了解如何處理內(nèi)存，我們需要了解事情是如何運(yùn)作的。讓我們來看一些基本的數(shù)據(jù)庫和操作系統(tǒng)機(jī)制。

內(nèi)存頁

數(shù)據(jù)庫組織數(shù)據(jù)的方式，需要能提高性能，并使讀取和寫入更易于處理。為了實(shí)現(xiàn)最佳性能，業(yè)務(wù)負(fù)載必須是可預(yù)測(cè)的，并劃分為可管理的塊。數(shù)據(jù)庫通過頁面來實(shí)現(xiàn)這一點(diǎn)。

數(shù)據(jù)庫上下文中的頁是一個(gè)固定長度的數(shù)據(jù)塊，它表示數(shù)據(jù)庫系統(tǒng)處理的最小存儲(chǔ)單位。頁面允許數(shù)據(jù)庫有效地組織磁盤上的數(shù)據(jù)，這反過來又可以提高性能，并使業(yè)務(wù)負(fù)載可預(yù)測(cè)。

頁面大小參數(shù)決定頁面的大小。通常，它設(shè)置為 8192 字節(jié)（8kB），某些數(shù)據(jù)庫允許用戶對(duì)其進(jìn)行配置，例如，PostgreSQL 中的 block_size。頁面可以分組到所謂的區(qū)中，這使得頁面管理更容易。

數(shù)據(jù)庫有效地存儲(chǔ)在頁面中。每次磁盤讀取或?qū)懭?，最終都會(huì)讀取或?qū)懭胝麄€(gè)頁面，這意味著會(huì)寫入 8 KB 的數(shù)據(jù)。即使我們想讀取一個(gè)字節(jié)的數(shù)據(jù)（比如表中一行的單個(gè)比特位的列），我們也需要將整個(gè)頁面加載到內(nèi)存中。

碎片化

通常，數(shù)據(jù)庫將一個(gè)表行僅存儲(chǔ)在一個(gè)頁面上。它們不允許將行存儲(chǔ)在多個(gè)頁面上。這會(huì)導(dǎo)致一種稱為碎片化的現(xiàn)象。每行可能具有不同的長度，特別是如果我們使用可變長度類型，例如varchar。為了有效地利用內(nèi)存，數(shù)據(jù)庫會(huì)嘗試一行接一行地進(jìn)行存儲(chǔ)。如果我們現(xiàn)在修改位于其他兩行之間的一行，則修改后的行可能會(huì)變大，并且無法再存儲(chǔ)在頁面上。要解決此問題，數(shù)據(jù)庫必須將行移動(dòng)到其他位置，并且原始的空間將變?yōu)榭眨ɑ驅(qū)⒕哂兄赶蛐挛恢玫闹羔槪?。此空間現(xiàn)在被浪費(fèi)并產(chǎn)生了碎片化。

預(yù)讀和性能

當(dāng)訪問的頁面構(gòu)成一個(gè)連續(xù)的內(nèi)存塊時(shí)，讀取和寫入的速度要快得多。這利用了內(nèi)存預(yù)取機(jī)制，其中內(nèi)存管理單元（MMU）會(huì)進(jìn)行預(yù)測(cè)，在不久的將來可能訪問哪些內(nèi)存頁，并更早地加載它們。如果預(yù)測(cè)正確，則整體性能會(huì)隨著 MMU 更早地讀取數(shù)據(jù)而提高。為了更好地進(jìn)行預(yù)測(cè)，內(nèi)存頁必須構(gòu)成一個(gè)連續(xù)的塊（因此頁面必須一個(gè)接一個(gè)地出現(xiàn)在物理內(nèi)存中）。

典型的頁面大小為 8 KB。這在大多數(shù)情況下都很有效，但是，有時(shí)我們希望擁有更大的頁面，以減少讀寫操作的次數(shù)。頁面大小主要由操作系統(tǒng)和 CPU 架構(gòu)決定。由于操作系統(tǒng)通常使用 8kB 頁面，因此數(shù)據(jù)庫系統(tǒng)希望使用相同的設(shè)置。為了減少 I/O 操作的次數(shù)，我們需要使頁面更大。為此，我們可以使用長度為 2MB、1GB 甚至 16GB 的所謂大頁面。我們需要在操作系統(tǒng)中啟用它們，然后在數(shù)據(jù)庫中啟用它們。例如，PostgreSQL 為此提供了 huge_page_size 參數(shù)。通過啟用大頁面，數(shù)據(jù)庫可以獲得更大的頁面，從而減少數(shù)據(jù)庫必須執(zhí)行的 I/O 讀取和寫入次數(shù)。這也使頁面能構(gòu)成更大的連續(xù)塊，從而提高預(yù)取和整體性能。

但是，操作系統(tǒng)可能會(huì)動(dòng)態(tài)調(diào)整頁面大小。例如，Linux 支持透明大頁面（THP），它會(huì)自動(dòng)提升和降低頁面大小。這會(huì)對(duì)應(yīng)用程序隱藏大頁面，理論上可以在應(yīng)用程序沒有使用大頁面時(shí)提高性能，因?yàn)椴僮飨到y(tǒng)可能會(huì)將多個(gè)應(yīng)用程序的頁面合并為一個(gè)大頁面。遺憾的是，當(dāng)應(yīng)用程序顯式使用大頁面，而操作系統(tǒng)在后臺(tái)將大頁面表示為常規(guī)頁面時(shí)，性能會(huì)很快下降。如果您在數(shù)據(jù)庫中啟用了大頁面，請(qǐng)?jiān)诓僮飨到y(tǒng)中禁用 THP。

內(nèi)存過量使用

在為應(yīng)用程序分配的內(nèi)存量方面，操作系統(tǒng)也可能會(huì)作弊。當(dāng)應(yīng)用程序嘗試分配內(nèi)存時(shí)，即使沒有可用的內(nèi)存，操作系統(tǒng)也始終會(huì)確認(rèn)內(nèi)存已分配。這稱為內(nèi)存過量使用。

此方法可在許多應(yīng)用程序運(yùn)行時(shí)提高系統(tǒng)的可用性。在準(zhǔn)備處理輸入的數(shù)據(jù)時(shí)，應(yīng)用程序分配的緩沖區(qū)通常比所需的緩沖區(qū)更大。即使應(yīng)用程序不使用這些內(nèi)存，操作系統(tǒng)也需要分配大塊內(nèi)存，這會(huì)很快耗盡資源，并且能夠運(yùn)行的應(yīng)用程序更少。

為避免此問題，操作系統(tǒng)假裝所有內(nèi)存分配都已完成，并且內(nèi)存是可用的。只有當(dāng)應(yīng)用程序嘗試訪問數(shù)據(jù)時(shí)，操作系統(tǒng)才會(huì)引發(fā)內(nèi)存不足的異常。在這種情況下，可能會(huì)啟動(dòng) Out-of-Memory-Killer，并殺死其中一個(gè)進(jìn)程。當(dāng)應(yīng)用程序不想使用比機(jī)器物理內(nèi)存更多的內(nèi)存時(shí)，這一切都很好。如果他們想使用它，那么問題就開始了。

數(shù)據(jù)庫通常就是這種情況。他們希望在開始時(shí)分配大塊內(nèi)存，以便為任何業(yè)務(wù)負(fù)載做好準(zhǔn)備。不幸的是，即使內(nèi)存不可用，操作系統(tǒng)也會(huì)簡單地假裝內(nèi)存已分配。因此，請(qǐng)?jiān)谀牟僮飨到y(tǒng)中禁用內(nèi)存過量使用。在 Linux 中，您可以使用 vm.overcommit_memory 參數(shù)。

PostgreSQL 如何分配內(nèi)存

當(dāng) PostgreSQL 服務(wù)器啟動(dòng)時(shí)，它會(huì)分配許多不同的內(nèi)存塊。讓我們一一看看。

共享緩沖區(qū)

最重要的內(nèi)存塊稱為共享緩沖區(qū)。它是用于緩存最常用的頁面的內(nèi)存塊（涵蓋了數(shù)據(jù)庫中的行、索引和其他內(nèi)容）。數(shù)據(jù)庫會(huì)使用幾個(gè)指標(biāo)，來識(shí)別最受歡迎的頁面，但它們主要?dú)w結(jié)為對(duì)讀取和寫入進(jìn)行計(jì)數(shù)。有趣的是，您甚至可以讀取頁面，并將其重定向到/dev/null，使其緩存在共享緩沖區(qū)中，因?yàn)?PostgreSQL 還會(huì)檢查操作系統(tǒng)指標(biāo)。

共享緩沖區(qū)在開始時(shí)分配，在運(yùn)行期間無法更改它們的大小。因此，要更改大小，您需要重新啟動(dòng)數(shù)據(jù)庫。

共享緩沖區(qū)是數(shù)據(jù)庫內(nèi)存中最重要的部分。默認(rèn)大小設(shè)置為 128MB，一般建議將其設(shè)置為機(jī)器內(nèi)存的 25%。但是，這是一個(gè)非常古老且不準(zhǔn)確的建議，因此請(qǐng)繼續(xù)閱讀以了解如何調(diào)整它，也可參閱我們另一篇文章，關(guān)于如何處理緩存命中率低的問題，以了解更多信息。

工作內(nèi)存

另一個(gè)內(nèi)存塊是，為每個(gè)查詢中的每個(gè)執(zhí)行節(jié)點(diǎn)分配的工作內(nèi)存。此內(nèi)存用于處理節(jié)點(diǎn)的輸出，并生成結(jié)果。因此，我們擁有的查詢和節(jié)點(diǎn)越多，我們使用的內(nèi)存就會(huì)越多。

要了解其工作原理，我們需要了解查詢是如何執(zhí)行的。每當(dāng)我們運(yùn)行查詢時(shí)，數(shù)據(jù)庫必須分階段執(zhí)行它。首先，它從表中提取數(shù)據(jù)并進(jìn)行連接。接下來，數(shù)據(jù)庫執(zhí)行過濾和其他處理。最后，對(duì)結(jié)果進(jìn)行排序。在每個(gè)階段，數(shù)據(jù)庫可能需要生成一大塊數(shù)據(jù)（比如一個(gè)表的內(nèi)容），這會(huì)消耗大量內(nèi)存。

無需過多的論述，每一個(gè)此類型的操作都可能是執(zhí)行計(jì)劃中的一個(gè)節(jié)點(diǎn)。因此，要從多個(gè)表進(jìn)行讀取的一個(gè)查詢，可能具有多個(gè)執(zhí)行計(jì)劃節(jié)點(diǎn)。對(duì)于每個(gè)這樣的節(jié)點(diǎn)，數(shù)據(jù)庫都會(huì)分配工作內(nèi)存。如果節(jié)點(diǎn)的結(jié)果集大于工作內(nèi)存，則會(huì)溢出到磁盤（這比將數(shù)據(jù)保存在內(nèi)存中要慢得多）。

PostgreSQL 中的 work_mem 設(shè)置，控制為每個(gè)查詢中的每個(gè)執(zhí)行節(jié)點(diǎn)分配的內(nèi)存量。默認(rèn)情況下，它設(shè)置為 4MB。通常建議將此參數(shù)設(shè)置為總內(nèi)存量除以連接數(shù)，然后再除以 4 或 16。這取決于您正在運(yùn)行的業(yè)務(wù)負(fù)載，因此請(qǐng)繼續(xù)閱讀以了解如何對(duì)其進(jìn)行優(yōu)化。

維護(hù)工作內(nèi)存

我們要考慮的下一個(gè)內(nèi)存塊是維護(hù)工作內(nèi)存。該內(nèi)存塊用于執(zhí)行后臺(tái)操作，如清理（碎片整理）、索引創(chuàng)建或 DDL 操作（比如添加外鍵）。

每個(gè)后臺(tái)任務(wù)都有自己的內(nèi)存塊，因此，如果有許多 autovacuum 進(jìn)程正在運(yùn)行，則每個(gè)進(jìn)程都會(huì)有自己的內(nèi)存塊。默認(rèn)情況下，該內(nèi)存塊的大小設(shè)置為 64MB。如果您的服務(wù)器有足夠的內(nèi)存，通常建議將其設(shè)置為更高的值，例如 1GB。

臨時(shí)緩沖區(qū)

每個(gè)會(huì)話還會(huì)獲得另一個(gè)用于會(huì)話本地緩沖區(qū)的內(nèi)存塊。該內(nèi)存塊用于創(chuàng)建臨時(shí)數(shù)據(jù)，如臨時(shí)表。會(huì)話會(huì)根據(jù)需要分配臨時(shí)緩沖區(qū)。

默認(rèn)情況下，允許每個(gè)會(huì)話分配 8MB 的臨時(shí)緩沖區(qū)。此內(nèi)存不與其他會(huì)話共享，并且是會(huì)話專用的。如果您處理的會(huì)話需要分配許多臨時(shí)表，則可以考慮調(diào)整此參數(shù)。

內(nèi)存調(diào)優(yōu)

現(xiàn)在讓我們看看，如何對(duì) PostgreSQL 服務(wù)器中的內(nèi)存進(jìn)行調(diào)優(yōu)。

靜態(tài)配置

首先，您需要做配置的調(diào)優(yōu)。

shared_buffers

對(duì)shared_buffers參數(shù)的典型建議是，將其設(shè)置為內(nèi)存的 25%。這是一個(gè)很好的起點(diǎn)，但它并不能說明問題。

內(nèi)存利用率在很大程度上取決于您與數(shù)據(jù)的交互方式。如果您運(yùn)行的是一個(gè) OLTP 系統(tǒng)，那么我們可以假設(shè)，許多事務(wù)將在短時(shí)間內(nèi)觸及相同的行。在這種情況下，緩存這些行而不是一遍又一遍地從磁盤中檢索它們會(huì)是有益的。在這種情況下，增加緩存大小是一個(gè)好主意。

但是，如果您運(yùn)行的是數(shù)倉或報(bào)表分析數(shù)據(jù)庫，則不太可能在短時(shí)間內(nèi)讀取任何行兩次。這意味著緩存數(shù)據(jù)沒有意義。相反，我們應(yīng)該讓緩存更小！

如果運(yùn)行的是 OLTP 業(yè)務(wù)，則優(yōu)化 shared_buffers 參數(shù)的實(shí)際過程應(yīng)如下所示：

• ? 從數(shù)據(jù)庫的共享緩沖區(qū)內(nèi)存初始值開始，比如 1GB

• ? 使用 pg_buffercache 檢查命中率和未命中率

• ? 再添加 1GB 的共享緩沖區(qū)內(nèi)存

• ? 再次檢查命中率和未命中率。此時(shí)命中率應(yīng)該會(huì)增加

• ? 繼續(xù)添加內(nèi)存，直到您看到命中率不再增加

如果您運(yùn)行 OLAP 或數(shù)倉，則可以使用相同的策略，來減小緩存的內(nèi)存大小，而不會(huì)降低系統(tǒng)的性能。請(qǐng)參閱我們關(guān)于如何處理緩存命中率低的問題的文章，以了解更多信息。

work_mem

應(yīng)根據(jù)您在數(shù)據(jù)庫中配置的連接數(shù)，來設(shè)置 work_mem 參數(shù)。在獲得連接數(shù)后，可這樣計(jì)算

work_mem = TOTAL_RAM / #connections / 16

如果您觀察到仍然有許多查詢會(huì)溢出到磁盤，請(qǐng)將 work_mem 參數(shù)值繼續(xù)加倍，直到您的查詢不再經(jīng)常溢出。

maintenance_work_mem

將maintenace_work_mem參數(shù)初始設(shè)置為 1GB。如果您發(fā)現(xiàn)清理操作或其他后臺(tái)進(jìn)程太慢，請(qǐng)將大小加倍。

temp_buffers

將temp_buffers參數(shù)設(shè)置為 2GB 除以連接數(shù)的值，作為初始值。如果您觀察到有許多查詢?cè)趧?chuàng)建臨時(shí)表，則將大小加倍。

連接

每個(gè)連接都會(huì)消耗一些內(nèi)存。連接過多會(huì)降低系統(tǒng)性能，并消耗大量內(nèi)存。因此，您應(yīng)該限制連接數(shù)，并盡可能使用連接池。

請(qǐng)參閱我們的配置連接池指南，了解如何配置它們。

查詢優(yōu)化

很明顯，慢查詢可能會(huì)影響到可用的內(nèi)存量。低效的查詢可能會(huì)讀取過多數(shù)據(jù)（通過掃描表而不是使用索引）、溢出到磁盤（通過使用低效的連接策略）或降低緩存命中率（通過更新未使用的索引）。

因此，請(qǐng)始終優(yōu)化好查詢。分析他們的連接策略、查詢參數(shù)、過濾器、溢出到磁盤，以及會(huì)降低性能的其他方面。

索引

未使用的索引可能會(huì)降低您的可用內(nèi)存。每次更新表中的數(shù)據(jù)時(shí)，可能還需要更新索引。即使未使用的索引，它們也需要與表保持同步。這意味著更新表中的行，可能會(huì)導(dǎo)致執(zhí)行更多的更新。

此處的一般建議是，刪除所有未使用的索引。在查找未使用的索引時(shí)，請(qǐng)考慮以下事項(xiàng)：

• ? 為什么索引未被使用？也許應(yīng)該使用它，但您的查詢有問題。在這種情況下，請(qǐng)不要修改索引，而是修復(fù)查詢

• ? 索引是否在所有地方都未被使用？也許它在只讀副本上用到了？保持副本之間的數(shù)據(jù)庫配置一致是有益的，因此在這種情況下不要?jiǎng)h除索引。

• ? 索引是否未被使用？也許它用于一些罕見的場(chǎng)景，比如月度的報(bào)告。在這種情況下，請(qǐng)考慮刪除索引，并且僅在有幫助的情況下，每月為報(bào)表重新創(chuàng)建一次索引。

如果您確定該索引未被使用，則只需將其刪除即可。如果您觀察到某些查詢的性能下降，請(qǐng)分析其歷史性能（如果它們?cè)趧h除索引時(shí)，速度會(huì)變慢）。如果是這樣的話，那么也許他們還是使用了索引。

操作系統(tǒng)配置

如前所述，您的操作系統(tǒng)配置可能會(huì)影響數(shù)據(jù)庫的性能。一般建議禁用內(nèi)存過量使用和透明大頁面。請(qǐng)查閱您的操作系統(tǒng)的文檔，以了解如何執(zhí)行此操作。

緩存命中率

通常，提高緩存命中率的所有步驟，也都可能改善可用內(nèi)存不足的情況。請(qǐng)參閱我們關(guān)于如何處理緩存命中率低的問題的指南。

表分區(qū)

請(qǐng)對(duì)表進(jìn)行分析，是否可以對(duì)表進(jìn)行分區(qū)。有很多方法可以進(jìn)行分區(qū)，您可以按照我們的表分區(qū)指南，了解更多信息。

擴(kuò)展

如果都沒有什么幫助，您可以考慮擴(kuò)展服務(wù)器。您可以使機(jī)器更大（垂直擴(kuò)展）或?qū)⒇?fù)載分布到多臺(tái)機(jī)器（水平擴(kuò)展）。可以從垂直擴(kuò)展開始，因?yàn)樗菀椎枚?。如果這沒有幫助，請(qǐng)考慮水平擴(kuò)展，但請(qǐng)記住，這可能需要更改數(shù)據(jù)庫客戶端。

總結(jié)

可用內(nèi)存低的問題可能會(huì)很難處理。我們需要明白，許多活動(dòng)的部分是相互關(guān)聯(lián)的。我們需要分析我們的操作系統(tǒng)配置、數(shù)據(jù)庫配置和我們應(yīng)對(duì)的業(yè)務(wù)負(fù)載。OLTP 和 OLAP 系統(tǒng)的情況不同，沒有通用的解藥。