譯者 | 李睿

審校 | 重樓

如今，數據庫領域正在迅速向人工智能（AI）和機器學習（ML）邁進，數據庫的工作量將會大幅增加。對于數據庫管理員來說，提前預測數據庫基礎設施的工作負載并滿足需求將是一項額外的責任。

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

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

為什么預測性容量規劃至關重要

通過利用機器學習，數據庫管理員可以預測未來的資源需求，并在問題變得嚴重之前加以解決，從而實現：

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

機器學習如何優化PostgreSQL資源規劃

為了準確預測PostgreSQL的資源使用情況，采用了一種優化的LSTM模型，這是一種遞歸神經網絡（RNN），擅長捕捉時間序列數據中的時間模式。LSTM模型非常適合理解復雜的依賴關系和序列，使其成為預測PostgreSQL環境中CPU、內存和磁盤使用情況的一個理想選擇。

方法

數據收集

（1）選項1

為了構建LSTM模型，需要從各種PostgreSQL系統服務器操作系統命令和數據庫視圖中收集性能數據，例如：

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

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

示例表架構：

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

有多種方法可以將這個系統數據捕獲到歷史表中。其中一種方法是編寫Python腳本，并通過crontab每隔幾分鐘調度一次。

（2）選項2

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

機器學習模型：優化LSTM

選擇LSTM模型是因為它能夠學習時間序列數據中的長期依賴關系。為了提高其性能，對其進行了多項優化：

• 堆疊LSTM層：將兩個LSTM層堆疊起來，以捕獲資源使用數據中的復雜模式。
• Dropout正則化：在每個LSTM層之后加入Dropout層，防止過擬合，提高泛化能力。
• 雙向LSTM：將模型設置為雙向，以捕獲數據中的前向和后向模式。
• 學習率優化：選擇0.001的學習率來微調模型的學習過程。

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

以下是數據設置和機器學習實驗中使用的步驟摘要以及Google Colab Notebook的截圖：

步驟1：數據設置（模擬6個月的CPU、內存、磁盤使用數據）

步驟2：為數據添加更多變化

步驟3：為可視化或進一步使用創建數據框架

步驟4：準備LSTM數據、訓練、預測和繪圖的功能

步驟5：運行CPU、內存和存儲的模型

結果

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

以下是LSTM預測的可視化結果：

圖1：優化后的LSTM CPU使用率預測

圖2：優化的LSTM內存使用率預測

圖3：優化的LSTM磁盤使用率預測

與PostgreSQL監控工具的實際集成

為了最大限度地利用LSTM模型，可以探索PostgreSQL監控生態系統中的各種實際實現：

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

結論

通過應用LSTM機器學習模型來預測CPU、內存和磁盤的使用情況，PostgreSQL的容量規劃可以從被動的方式轉變為主動的方式。研究結果表明，優化后的LSTM模型提供了準確的預測，實現了更有效的資源管理和成本節約，特別是在云計算托管環境中。

隨著數據庫生態系統變得越來越復雜，這些預測工具對于希望優化資源利用率、防止停機和確保可擴展性的數據庫管理員來說變得至關重要。如果數據庫管理員正在大規模管理PostgreSQL數據庫，那么現在是利用機器學習進行預測容量規劃，并在性能問題出現之前優化資源管理的時候了。

未來的工作

未來的改進可能包括：

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

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