译者 | 李睿
审校 | 重楼
如今，数据库领域正在迅速向人工智能（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
