查看并清除 Postgres 缓存/缓冲区? [英] See and clear Postgres caches/buffers?

问题描述

有时我运行 Postgres 查询需要 30 秒.然后，我立即运行相同的查询，它需要 2 秒.Postgres 似乎有某种缓存.我能以某种方式看到缓存中保存的内容吗?我可以强制清除所有缓存以进行调整吗?

Sometimes I run a Postgres query and it takes 30 seconds. Then, I immediately run the same query and it takes 2 seconds. It appears that Postgres has some sort of caching. Can I somehow see what that cache is holding? Can I force all caches to be cleared for tuning purposes?

我基本上是在寻找以下 SQL Server 命令的 Postgres 版本:

I'm basically looking for a Postgres version of the following SQL Server command:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

但我也想知道如何查看该缓冲区中实际包含的内容.

But I would also like to know how to see what is actually contained in that buffer.

推荐答案

您可以使用 pg_buffercache 模块查看 PostgreSQL 缓冲区缓存中的内容.我做过一个名为Inside the PostgreSQL Buffer Cache"的演讲；这解释了您所看到的内容，我展示了一些更复杂的查询，以帮助解释随之而来的信息.

You can see what's in the PostgreSQL buffer cache using the pg_buffercache module. I've done a presentation called "Inside the PostgreSQL Buffer Cache" that explains what you're seeing, and I show some more complicated queries to help interpret that information that go along with that.

在某些系统上也可以查看操作系统缓存，请参阅 [pg_osmem.py] 以获得一个粗略的示例.

It's also possible to look at the operating system cache too on some systems, see [pg_osmem.py] for one somewhat rough example.

没有办法轻松清除缓存.在 Linux 上，您可以停止数据库服务器并使用 drop_caches 工具来清除操作系统缓存；请务必注意那里的警告，先运行同步.

There's no way to clear the caches easily. On Linux you can stop the database server and use the drop_caches facility to clear the OS cache; be sure to heed the warning there to run sync first.

这篇关于查看并清除 Postgres 缓存/缓冲区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！