PostgreSQL 如何计算服务器配置参数的大小

作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net



文章目录

大家好,我是只谈技术不剪发的 Tony 老师。

PostgreSQL 的默认配置参数通常都偏小,不太适合生产环境。因此,我们在安装数据库之后需要修改服务器的配置参数。上一篇文章中介绍了如何设置服务器的配置参数。今天给大家介绍一个快速计算 PostgreSQL 主要参数大小的方法。

我们可以使用 PGTune 在线工具根据给定硬件配置的最大性能计算出 PostgreSQL 的推荐配置,它的设置界面如下:


这个工具的使用非常简单,我们可以输入的参数包括:

    PostgreSQL 数据库版本,可以通过SELECT version();命令查看。
    数据库服务器主机的操作系统,包括 Linux、Windows 以及 OS X。
    数据库应用的类型,包括:网站(Web Application)、在线交易处理系统(Online Transaction Processing)、数据仓库(Data Warehouse)、桌面应用(Desktop application)以及混合型应用(Mixed type of application)。配置页面右侧给出了不同应用类型的介绍。
    服务器的内存大小。
    服务器的 CPU 核数,可选参数。
    最大的数据库连接数,可选参数。
    数据存储设备的类型,包括 SSD、SAN 以及机械硬盘(HDD)。

输入必填参数之后点击“Generate”按钮。


PGTune 在页面右侧生成了推荐的配置参数,并且提供了修改配置文件 postgresql.conf 以及使用 ALTER SYSTEM 命令进行参数设置两者方法。例如,我们输入的硬件参数产生的配置文件内容如下:

# WARNING
# this tool not being optimal
# for very high memory systems

# DB Version: 13
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 128 GB
# CPUs num: 63
# Connections num: 500
# Data Storage: san

max_connections = 500
shared_buffers = 32GB
effective_cache_size = 96GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 300
work_mem = 16777kB
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 63
max_parallel_workers_per_gather = 4
max_parallel_workers = 63
max_parallel_maintenance_workers = 4

 

或者,生成的 ALTER SYSTEM 命令如下:

# WARNING
# this tool not being optimal
# for very high memory systems

# DB Version: 13
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 128 GB
# CPUs num: 63
# Connections num: 500
# Data Storage: san

ALTER SYSTEM SET
 max_connections = '500';
ALTER SYSTEM SET
 shared_buffers = '32GB';
ALTER SYSTEM SET
 effective_cache_size = '96GB';
ALTER SYSTEM SET
 maintenance_work_mem = '2GB';
ALTER SYSTEM SET
 checkpoint_completion_target = '0.9';
ALTER SYSTEM SET
 wal_buffers = '16MB';
ALTER SYSTEM SET
 default_statistics_target = '100';
ALTER SYSTEM SET
 random_page_cost = '1.1';
ALTER SYSTEM SET
 effective_io_concurrency = '300';
ALTER SYSTEM SET
 work_mem = '16777kB';
ALTER SYSTEM SET
 min_wal_size = '2GB';
ALTER SYSTEM SET
 max_wal_size = '8GB';
ALTER SYSTEM SET
 max_worker_processes = '63';
ALTER SYSTEM SET
 max_parallel_workers_per_gather = '4';
ALTER SYSTEM SET
 max_parallel_workers = '63';
ALTER SYSTEM SET
 max_parallel_maintenance_workers = '4';

 

关于这些参数的介绍和作用,可以参考官方文档。

PostgreSQL 数据库的优化不仅取决于硬件配置,而且还取决于数据库的大小、客户端的数量以及查询的复杂性等各种因素,因此 PGTune 的推荐值不一定是最优设置。不过,我们可以将它作为一个初始配置,然后再根据实际情况进一步进行优化。

PGTune 是一个开源项目,托管在 GitHub,我们可以下载并部署一个自己的版本。