PostgreSQL内存表

Question

我创建了一个包含3个表的数据库，目的是特定的。所有表的总大小约为850 MB，其中单个表包含大约800 MB（包括索引）的数据和500万条记录（每天增加约6000条记录）。

系统是PG-Windows，配备8 GB RAM Windows 7笔记本电脑和SSD。我将2048MB分配为共享缓冲区，256MB分配为临时缓冲区，128MB分配为工作内存。我多次执行针对单个表的查询-希望该表保留在RAM中（因此上述参数）。但是，尽管我在执行期间看到内存使用量急剧上升（约200 MB），但我没有看到内存消耗至少保持在500 MB以上（使数据保留在内存中）。所有postgres exe在任务管理器中显示2-6 MB的大小。因此，我怀疑LRU不会将数据保留在内存中。

平均查询执行时间约为2秒（非常简单的单表查询）...但我需要将其降低到10-20毫秒或更少，纯粹是因为同样的查询将被执行太多次，只有通过将内容保留在内存中才能实现。有什么建议吗？

顺祝商祺， Kapil

- Kapil

---

有些相关：http://dba.stackexchange.com/q/53415/7788 - Craig Ringer

请查看 pg_fincore（如果它在Windows上可用）。它可能会很有用。我通常发现Windows缓存行为不太友好。 - Craig Ringer

我查看了这两个评论。但它们并不是真正相关的。我的问题很简单。尽管将2 GB RAM作为shared_buffers和足够大小的temp和work mems，为什么内存使用量不会增加呢？我仍然看到所有postgres.exe的内存使用量总共只有不到200 MB。我不确定表是否完全保存在内存中。 - Kapil

Answer 1

即使整个数据库被缓存在RAM中，您也不应该期望postgres进程显示大量的内存使用情况。

这是因为PostgreSQL依赖于来自操作系统缓冲区缓存的缓冲读取。简单来说，当PostgreSQL执行read()时，操作系统会查看请求的块是否缓存在它用于磁盘缓存的"free" RAM中。如果块在缓存中，则操作系统几乎立即返回它。如果块不在缓存中，则操作系统从磁盘读取它，将其添加到磁盘缓存中并返回该块。随后的读取将从缓存中获取它，除非它被其他块替换出缓存。

这意味着，如果您有足够的空闲内存可将整个数据库放入"free"操作系统内存中，则不太可能遇到读取磁盘的情况。

根据操作系统的不同，写入磁盘的行为可能会有所不同。Linux将回写高速缓存中的"dirty"缓冲区，并且即使已经写入，仍将从缓存中返回块。除非通过Pg在COMMIT时间使用的fsync()立即强制将它们写入，否则它会懒惰地将其写回磁盘。当它这样做时，它标记缓存块为干净的，但不刷新它们。我不知道Windows在这里的行为。

重点是，即使没有看到任何PostgreSQL进程使用大量RAM，使用1GB数据库时，PostgreSQL可以完全在RAM中运行。将shared_buffers设置过高只会导致双重缓存，并可能减少用于操作系统缓存块的可用RAM。

很难准确地查看RAM中缓存的内容，因为Pg依赖于操作系统缓存。这就是我向您介绍pg_fincore的原因。

如果您在Windows上无法使用此方法，那么您只能依靠观察磁盘活动情况。性能监视器是否显示大量未缓存的磁盘读取？操作系统内存监视器是否显示操作系统中用于磁盘缓存的大量内存使用情况？

确保effective_cache_size正确反映用于磁盘缓存的RAM。它将帮助PostgreSQL选择适当的查询计划。

您假设查询性能受到磁盘读取延迟的影响，并且可以通过内存缓存来改善，但这可能根本不是事实。您需要查看explain analyze输出和系统性能指标来确定情况。

- Craig Ringer