我在 Windows Server 2003 64 位上安装了 PostgreSQL 9.0 64 位。该系统有 8 个 3GHz CPU 和 8GB 内存。
我应该如何配置以下设置？：
- 共享缓冲区
- 工作内存
- 维护工作内存
该数据库用于分析。在任何给定时间，只有两三个用户连接运行查询。我认为数据集的数量可以在 1M 到 15M 行之间。
底层存储是 EMC CX 存储阵列，采用光纤通道连接。性能非常好。
答案1
您可以在以下网址找到这三个问题的详细答案调整你的 PostgreSQL 服务器，以及您可能想要调整的其他一些参数的建议。在 Windows 上，您无法使用较大的 shared_buffers 设置，当它停止帮助大约 512MB 时，就会出现持续下降。打开 log_temp_files 并查看显示的内容以确定是否真的需要提高 work_mem。根据您对数据集的说法，它听起来不像会发出大型单个查询，您甚至可能不需要担心这一点。适度提高 Maintenance_work_mem 可能对后台自动清理工作有帮助，但除非这对您来说是个问题，否则调整得太高并不重要。
答案2
正确的值取决于使用模式。不过这里有一些指导原则
shared_buffers：专用 postgresql 服务器内存大小的 25%。work_mem：用于排序等操作。单个连接可以使用此数量多次，因此如果您同时运行大量查询，请谨慎使用。这需要大量测试才能确定它是否能提高性能但不会让系统使用太多内存。因此，如果您增加此值，请确保您的系统不会开始使用太多内存。我个人经常从 4MB 左右开始。
Maintenance_work_mem：这个是针对某些维护操作，比如清理和索引，设置得相当高一般比较安全。64M 或 128M 通常就足够了。
还设置有效缓存大小。这是对规划器的一个提示，应设置为操作系统用作磁盘缓存的内存量 + 共享缓冲区大小。
如果你想进行广泛的调整，我建议你读一本关于它的好书，例如：PostgreSQL 9.0 高性能。
答案3
对于非常大的数据集，您可能会发现 SAN 并非最佳选择。SAN 非常擅长处理大量小型 ios。除非您与它们之间有非常快的互连，否则它们通常只是在顺序吞吐量方面表现一般，即使这样，它们通常也没有针对顺序吞吐量进行优化。我已经在我的计算机上测试了顺序和随机读写性能，这些计算机使用 Areca 和 LSI RAID 卡，带有电池备份缓存、带有 Linux 软件 RAID 的本机 SAS 接口以及后端的 SAN。随机访问速度最快的是 SAN 和 RAID 卡，但对于顺序吞吐量，Linux 软件 RAID 将它们碾压得落花流水。硬件 RAID 可以达到 350M/s，SAN 在 100M/s 范围内（它连接到千兆以太网），带有 SW RAID 的本机 SAS 的读取速度约为 1G/s，写入速度约为 80%。当然，都是顺序的。不要以为您的 SAN 对于您正在做的事情来说速度超快，它可能很快，也可能不是。使用 bonnie++ 或 dd 或其他程序进行测试，了解它的实际速度。如果您的连续速度约为 100MB/s，那么与一台更便宜的机器相比，它的速度会慢得令人难以忍受，该机器有 4 个或 8 个 7200RPM SATA 驱动器，运行 RAID-10 进行分析。
当您说 8x 3GHz CPU 时，您是指 8x 个插槽，每个插槽有 4 或 8 个内核吗？还是 8 个内核？还是 4 个内核加超线程？对于您的工作，超过 4 个内核可能就是浪费。超过 8 个内核肯定是浪费。对于 OLAP/Analytics，如果可以的话，您希望使用更少、更快的 CPU。
关于您的设置。shared_mem 不需要很大。在 Windows 上，共享内存实现对于大值来说不是最优的，并且将其增大很少有助于提高性能。也就是说，我会测试各种值来查看，但几百兆可能就是最快的了。维护工作内存可以在千兆范围内，但最大的收获是将其提高到 100M 左右。work_mem 是 postgresql 的脚枪。如果您要将其提高，我建议您在机器上将其至少提高到 16 或 32M，请确保您将 postgresql 的 max_connections 参数限制为最多几十个连接。如果有人以某种方式同时启动一堆查询，您很快就会耗尽 RAM。不好。另一方面，一些测试可能会表明超过一百左右的任何东西都没有太大帮助。
将 work_mem 调得太高会带来危险，因为最终会将操作系统缓存的数据从缓存中推出，而需要重新加载。访问磁盘获取数据的成本通常高于真正调高它的收益。
一个好的经验法则是保持 work_mem*max_connections*2 < 内存的 1/4。因此，在具有 64G 内存和 100 个连接的机器上，您需要 work_mem*200 < 16G 或大约 80 Megs 最大值。这可确保所有连接运行大量排序的任何病态行为不会轻易导致机器崩溃。
如果您发现 1G 的 work_mem 比 100M 等的效果好得多，那么您可以妥协，将常规 work_mem 保持在较低的水平以确保安全，并让运行大查询的单个线程在连接上设置自己的 work_mem。
我同意前面的发帖人的观点，即 Windows 对于 pgsql 来说不是最理想的，强调一下，对于 OLAP 来说，它更糟糕，而能够分配更多的共享内存对于 pg / linux 来说可能是一个优势。
答案4
好的。你说“IO 性能非常好”。这并不意味着很多，但我认为这种硬件具有良好的顺序 IO 吞吐量...
您的查询似乎属于“处理大量数据以返回一些汇总结果”的风格，并且并行性较低。
关于此的建议将取决于数据有多大。
如果您的数据库（或者至少是经常访问的部分）足够小，可以很好地缓存在 RAM 中，那么您的 IO 性能就不会有太大影响（写入除外）；但是，如果您的数据库很大，并且您想要快速浏览它，那么顺序 IO 性能就很重要了。
无论如何，首先是最简单的：
工作内存
当您执行包含少量排序和哈希（用于连接和聚合）或物化元组存储的查询时，每个查询最多可使用 work_mem。排序可以溢出到磁盘，但哈希不能。请注意，如果您的查询有 N 个排序，它将使用 N 次 work_mem。对于大量用户，这一点很重要。对于您来说，用户很少，您可以将其设置得相当高，也许 128MB。这样，即使对于较大的数据集，哈希仍将被允许，这比排序快得多。如果需要，您也可以在执行查询之前更改它。
维护工作内存
对于索引创建等操作，情况也一样。创建 btree 索引需要进行大量排序，因此，如果您在大型表上创建索引，则将 Maintenance_work_mem 设置为 1-2GB 之类的大值将需要更少的排序过程（即临时文件）。请记住，在恢复备份时不要同时启动 10 个索引创建...
更多详细信息 -> 请参阅文档
至于 shared_buffers，在 Windows 上我不知道。你应该询问邮件列表。
还要记住，pg 每个查询只能使用一个核心，因此请禁用超线程。不过，并行扫描同一张表的多个查询将同步，仅读取一次数据。
顺便问一下，您没有在该机器上运行 Linux 有什么原因吗？ PG 在 Linux 上更“原生”。