譯者 | 李睿

審校 | 重樓

如今，數(shù)據(jù)庫領(lǐng)域正在迅速向人工智能（AI）和機器學(xué)習(xí)（ML）邁進，數(shù)據(jù)庫的工作量將會大幅增加。對于數(shù)據(jù)庫管理員來說，提前預(yù)測數(shù)據(jù)庫基礎(chǔ)設(shè)施的工作負載并滿足需求將是一項額外的責(zé)任。

隨著數(shù)據(jù)庫規(guī)模的擴展和資源管理變得越來越重要，傳統(tǒng)的容量規(guī)劃方法往往難以滿足需求，從而導(dǎo)致性能問題和計劃外停機。作為使用最廣泛的開源關(guān)系數(shù)據(jù)庫之一，PostgreSQL也不例外。由于對CPU、內(nèi)存和磁盤空間的需求不斷增加，數(shù)據(jù)庫管理員必須采取主動的方法來防止瓶頸并提高效率。

本文將探討如何將長短期記憶網(wǎng)絡(luò)（LSTM）機器學(xué)習(xí)模型應(yīng)用于預(yù)測PostgreSQL數(shù)據(jù)庫中的資源消耗。這種方法使數(shù)據(jù)庫管理員能夠從被動容量規(guī)劃轉(zhuǎn)向預(yù)測性容量規(guī)劃，從而減少停機時間，改進資源分配，并最大限度地降低過度配置的成本。

為什么預(yù)測性容量規(guī)劃至關(guān)重要

通過利用機器學(xué)習(xí)，數(shù)據(jù)庫管理員可以預(yù)測未來的資源需求，并在問題變得嚴重之前加以解決，從而實現(xiàn)：

• 減少停機時間：提前發(fā)現(xiàn)資源短缺，有助于避免中斷。
• 提高效率：根據(jù)實際需求分配資源，避免過度配置。
• 節(jié)約成本：在云計算環(huán)境中，準確預(yù)測資源使用情況可以降低過度配置的成本。

機器學(xué)習(xí)如何優(yōu)化PostgreSQL資源規(guī)劃

為了準確預(yù)測PostgreSQL的資源使用情況，采用了一種優(yōu)化的LSTM模型，這是一種遞歸神經(jīng)網(wǎng)絡(luò)（RNN），擅長捕捉時間序列數(shù)據(jù)中的時間模式。LSTM模型非常適合理解復(fù)雜的依賴關(guān)系和序列，使其成為預(yù)測PostgreSQL環(huán)境中CPU、內(nèi)存和磁盤使用情況的一個理想選擇。

方法

數(shù)據(jù)收集

（1）選項1

為了構(gòu)建LSTM模型，需要從各種PostgreSQL系統(tǒng)服務(wù)器操作系統(tǒng)命令和數(shù)據(jù)庫視圖中收集性能數(shù)據(jù)，例如：

• pg_stat_activity （Postgres數(shù)據(jù)庫中活動連接的詳細信息）
• vmstat
• free
• df

這些數(shù)據(jù)可以在六個月內(nèi)每隔幾分鐘捕獲一次，為訓(xùn)練模型提供一個全面的數(shù)據(jù)集。收集的指標可以存儲在名為capacity_metrics的專用表中。

示例表架構(gòu)：

SQL 
 CREATE TABLE capacity_metrics (
 time TIMESTAMPTZ PRIMARY KEY,
 cpu_usage DECIMAL,
 memory_usage DECIMAL,
 disk_usage BIGINT,
 active_connections INTEGER
 );

有多種方法可以將這個系統(tǒng)數(shù)據(jù)捕獲到歷史表中。其中一種方法是編寫Python腳本，并通過crontab每隔幾分鐘調(diào)度一次。

（2）選項2

為了測試靈活性，可以使用代碼（合成數(shù)據(jù)生成）生成CPU、內(nèi)存和磁盤利用率指標，并在Google Colab Notebook中執(zhí)行。對于本文的測試分析，使用了這個選項。以下章節(jié)將解釋這些步驟。

機器學(xué)習(xí)模型：優(yōu)化LSTM

選擇LSTM模型是因為它能夠?qū)W習(xí)時間序列數(shù)據(jù)中的長期依賴關(guān)系。為了提高其性能，對其進行了多項優(yōu)化：

• 堆疊LSTM層：將兩個LSTM層堆疊起來，以捕獲資源使用數(shù)據(jù)中的復(fù)雜模式。
• Dropout正則化：在每個LSTM層之后加入Dropout層，防止過擬合，提高泛化能力。
• 雙向LSTM：將模型設(shè)置為雙向，以捕獲數(shù)據(jù)中的前向和后向模式。
• 學(xué)習(xí)率優(yōu)化：選擇0.001的學(xué)習(xí)率來微調(diào)模型的學(xué)習(xí)過程。

該模型被訓(xùn)練了20個迭代周期，批量大小為64，并在CPU、內(nèi)存和存儲（磁盤）使用率的看不見的測試數(shù)據(jù)上測量了性能。

以下是數(shù)據(jù)設(shè)置和機器學(xué)習(xí)實驗中使用的步驟摘要以及Google Colab Notebook的截圖：

步驟1：數(shù)據(jù)設(shè)置（模擬6個月的CPU、內(nèi)存、磁盤使用數(shù)據(jù)）

步驟2：為數(shù)據(jù)添加更多變化

步驟3：為可視化或進一步使用創(chuàng)建數(shù)據(jù)框架

步驟4：準備LSTM數(shù)據(jù)、訓(xùn)練、預(yù)測和繪圖的功能

步驟5：運行CPU、內(nèi)存和存儲的模型

結(jié)果

優(yōu)化后的LSTM模型在預(yù)測CPU、內(nèi)存和磁盤使用率優(yōu)于ARIMA和線性回歸等傳統(tǒng)方法。該模型的預(yù)測密切跟蹤實際的資源使用情況，有效地捕獲短期和長期的使用模式。

以下是LSTM預(yù)測的可視化結(jié)果：

圖1：優(yōu)化后的LSTM CPU使用率預(yù)測

圖2：優(yōu)化的LSTM內(nèi)存使用率預(yù)測

圖3：優(yōu)化的LSTM磁盤使用率預(yù)測

與PostgreSQL監(jiān)控工具的實際集成

為了最大限度地利用LSTM模型，可以探索PostgreSQL監(jiān)控生態(tài)系統(tǒng)中的各種實際實現(xiàn)：

• pgAdmin集成：可以將pgAdmin功能擴展為可視化實時資源預(yù)測和實際指標，從而使數(shù)據(jù)庫管理員能夠主動應(yīng)對潛在的資源短缺問題。
• Grafana儀表板：PostgreSQL指標可以與Grafana集成，在性能圖表上疊加LSTM預(yù)測結(jié)果。還可以配置警報，以便在預(yù)計使用量超過預(yù)定義閾值時通知數(shù)據(jù)庫管理員。
• Prometheus監(jiān)控：Prometheus可以抓取PostgreSQL指標，并使用LSTM預(yù)測來發(fā)出警報，生成預(yù)測，并根據(jù)預(yù)測的資源消耗設(shè)置通知。
• 云計算環(huán)境中的自動擴展：在云托管的PostgreSQL實例中（例如，AWS RDS、Google Cloud SQL），LSTM模型可以根據(jù)預(yù)測的資源需求增長觸發(fā)自動擴展服務(wù)。
• CI/CD管道：機器學(xué)習(xí)模型可以通過CI/CD管道不斷更新新數(shù)據(jù)，重新訓(xùn)練并實時部署，確保預(yù)測在工作負載變化時保持準確。

結(jié)論

通過應(yīng)用LSTM機器學(xué)習(xí)模型來預(yù)測CPU、內(nèi)存和磁盤的使用情況，PostgreSQL的容量規(guī)劃可以從被動的方式轉(zhuǎn)變?yōu)橹鲃拥姆绞?。研究結(jié)果表明，優(yōu)化后的LSTM模型提供了準確的預(yù)測，實現(xiàn)了更有效的資源管理和成本節(jié)約，特別是在云計算托管環(huán)境中。

隨著數(shù)據(jù)庫生態(tài)系統(tǒng)變得越來越復(fù)雜，這些預(yù)測工具對于希望優(yōu)化資源利用率、防止停機和確?？蓴U展性的數(shù)據(jù)庫管理員來說變得至關(guān)重要。如果數(shù)據(jù)庫管理員正在大規(guī)模管理PostgreSQL數(shù)據(jù)庫，那么現(xiàn)在是利用機器學(xué)習(xí)進行預(yù)測容量規(guī)劃，并在性能問題出現(xiàn)之前優(yōu)化資源管理的時候了。

未來的工作

未來的改進可能包括：

• 嘗試使用額外的神經(jīng)網(wǎng)絡(luò)架構(gòu)（例如GRU或Transformer模型）來處理更不穩(wěn)定的工作負載。
• 將該方法擴展到多節(jié)點和分布式PostgreSQL部署，其中網(wǎng)絡(luò)流量和存儲優(yōu)化也起著重要作用。
• 實現(xiàn)實時警報，并進一步將預(yù)測集成到PostgreSQL的操作堆棧中，以實現(xiàn)更加自動化的管理。
• 使用Oracle自動工作負載存儲庫（AWR）數(shù)據(jù)進行Oracle數(shù)據(jù)庫工作負載預(yù)測試驗。

原文標題：Applying Machine Learning for Predictive Capacity Planning in PostgreSQL Databases，作者：Elango Muthusamy