performance - Windows 上的 PostgreSQL shared_buffers

Question

我在 Windows Server 上运行 64 位 PostgreSQL 9.1。我正在努力提高它的性能，尤其是在处理繁重的写作方面。我过去常常将 shared_buffer 增加到 RAM 的 %25，而当我得到 32GB RAM 时，我决定将 shared_buffers 设置为 8GB。在搜索更多信息时，我看到了这篇文章:http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html

它说:但在 Linux 上不超过 8GB，在 Windows 上不超过 512MB，有时甚至更少。

现在我很困惑。如果增加 RAM 无助于提高 PostgreSQL 性能，那又有什么意义呢？!

其他值如下:

work_mem: 160MB
maintenance_work_mem = 1920MB
checkpoint_segments = 100
checkpoint_completion_target = 0.9
checkpoint_timeout = 1h
wal_buffers = 64MB
effective_cache_size = 22GB

Answer 1

对于写入量大的Windows服务器，最重要的设置是调整checkpoint_segments。您的值已经相当高了，但您可能想要试验最大 256 的值。

来自 postgresql 性能调整指南(找到 here):

PostgreSQL writes new transactions to the database in files called WAL segments that are 16MB in size. Every time checkpoint_segments worth of these files have been written, by default 3, a checkpoint occurs. Checkpoints can be resource intensive, and on a modern system doing one every 48MB will be a serious performance bottleneck. Setting checkpoint_segments to a much larger value improves that. Unless you're running on a very small configuration, you'll almost certainly be better setting this to at least 10, which also allows usefully increasing the completion target.

For more write-heavy systems, values from 32 (checkpoint every 512MB) to 256 (every 4GB) are popular nowadays. Very large settings use a lot more disk and will cause your database to take longer to recover, so make sure you're comfortable with both those things before large increases. Normally the large settings (>64/1GB) are only used for bulk loading. Note that whatever you choose for the segments, you'll still get a checkpoint at least every 5 minutes unless you also increase checkpoint_timeout (which isn't necessary on most systems).