关于如何在PostgreSQL中调整数据库参数和配置的综合指南

关于如何在PostgreSQL中调整数据库参数和配置的综合指南

PostgreSQL是一个非常通用的数据库系统，能够在低资源环境和与各种其他应用程序共享的环境中高效运行。为了确保它将在许多不同的环境中正常运行，默认配置非常保守，不太适合高性能生产数据库。加上地理空间数据库具有不同使用模式的事实，数据往往由比非地理空间数据库更少、更大的记录组成，您可以看到默认配置不会完全适合我们的目的。

我想我们可以同意，每个人都想要一个快速的数据库。问题是：在哪些方面快速？

在数据库方面，“快”至少有两个不同的方向：

• 每秒的事务数
• 吞吐量或数据处理量

这些是相互关联的，但绝对不一样。两者在I/O方面的要求完全不同。一般来说，您希望不惜一切代价避免I/O。这是因为与访问内存中的数据、不同级别的CPU缓存甚至CPU寄存器相比，I/O总是很慢。根据经验，每一层都会减慢大约1:1000的访问速度。

对于每秒大量事务的高需求系统，您需要尽可能多的并发IO。对于具有高吞吐量的系统，您需要一个每秒可以交付尽可能多字节的IO子系统。

这导致需要在CPU附近拥有尽可能多的数据——例如，在RAM中。至少工作集，即在可接受的时间内给出答案所需的数据集，应该合适。

每个数据库引擎都有一个特定的内存布局，并为不同的目的处理不同的内存区域。

总结一下：我们需要避免IO，我们需要调整内存布局的大小，以便数据库能够有效地工作（并且，我假设所有其他任务都是根据正确的模式设计完成的）。

所有这些配置参数都可以在postgresql. conf数据库配置文件中进行编辑。这是一个常规文本文件，可以使用记事本或任何其他文本编辑器进行编辑。在服务器重新启动之前，更改不会生效。

注意：这些值仅为建议值。每个环境都会有所不同，需要进行测试以确定最佳配置。但是这一部分应该会让你有一个好的开始。

数据库参数

以下是一些参数，可以根据您的系统和工作负载进行调整以获得最佳性能。

shared_buffer

PostgreSQL缓冲区称为shared_buffer，是大多数操作系统中最有效的可调参数，该参数设置PostgreSQL将为缓存使用多少专用内存。

shared_buffer的默认值设置得很低，你不会从中得到多少好处。它设置得很低是因为某些机器和操作系统不支持更高的值。但是在大多数现代机器中，你需要增加这个值以获得最佳性能。

如果您有一个1GB或更大内存的专用数据库服务器，shared_buffers的合理起始值是系统内存的25%。您应该尝试一些较低和较高的值，因为在某些情况下，要获得最佳性能，您需要超过25%的设置。Windows系统上shared_buffers的有用范围通常从64MB到512MB。配置取决于您的机器、工作数据集和机器上的工作负载。

在生产环境中，可以观察到shared_buffer的较大值会提供非常好的性能，尽管您应该始终进行基准测试以找到正确的平衡。

# show shared_buffers;
shared_buffers
----------------
128MB

wal_buffers

PostgreSQL将其WAL（预写日志）记录写入缓冲区，然后这些缓冲区被刷新到磁盘。缓冲区的默认大小（由wal_buffers定义）为16MB，但如果您有多个并发连接，则更高的值可以提供更好的性能。

effective_cache_size

effective_cache_size参数提供了磁盘缓存可用存储器的估计值。它只是一个指南，而不是确切的分配内存或缓存大小。它不分配实际内存，而是告诉优化器内核中可用的缓存量。这是使用索引成本估计的一个因素；值越高，使用索引扫描的可能性越大，而值越低，使用顺序扫描的可能性越大。如果值设置得太低，查询规划器可以决定不使用某些索引，即使它们会有帮助。还应该考虑不同表上并发查询的预期数量，因为它们必须共享可用空间。因此，设置一个大的值总是有益的。

让我们看看effective_cache_size的一些实际含义。<