postgresql 查看并清除 Postgres 缓存/缓冲区？

See and clear Postgres caches/buffers?

提问by User1

Sometimes I run a Postgres query 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?

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

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

注意：我基本上是在寻找以下 SQL Server 命令的 postgres 版本：

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

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

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

Thanks for any help.

谢谢你的帮助。

采纳答案by Greg Smith

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_buffercache 模块查看 PostgreSQL 缓冲区缓存中的内容。我做了一个名为“ PostgreSQL 缓冲区缓存内部”的演示文稿，它解释了您所看到的内容，我展示了一些更复杂的查询，以帮助解释随之而来的信息。

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

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

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

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

回答by Leeeroy

I haven't seen any commands to flush the caches in PostgreSQL. What you see is likely just normal index and data caches being read from disk and held in memory. by both postgresql and the caches in the OS. To get rid of all that, the only way I know of:

我还没有看到任何命令来刷新 PostgreSQL 中的缓存。您看到的可能只是从磁盘读取并保存在内存中的普通索引和数据缓存。通过 postgresql 和操作系统中的缓存。为了摆脱这一切，我所知道的唯一方法：

What you should do is:

你应该做的是：

1. Shutdown the database server (pg_ctl, sudo service postgresql stop, etc.)
2. echo 3 > /proc/sys/vm/drop_caches This will clear out the OS file/block caches - very important though I don't know how to do that on other OSs.
3. Start the database server

1. 关闭数据库服务器（pg_ctl、sudo service postgresql stop等）
2. echo 3 > /proc/sys/vm/drop_caches 这将清除操作系统文件/块缓存 - 非常重要，尽管我不知道如何在其他操作系统上执行此操作。
3. 启动数据库服务器

回答by Steve Saporta

Greg Smith's answer about drop_caches was very helpful. I did find it necessary to stop and start the postgresql service, in addition to dropping the caches. Here's a shell script that does the trick. (My environment is Ubuntu 14.04 and PostgreSQL 9.3.)

Greg Smith 关于 drop_caches 的回答非常有帮助。除了删除缓存之外，我确实发现有必要停止和启动 postgresql 服务。这是一个可以解决问题的shell脚本。（我的环境是 Ubuntu 14.04 和 PostgreSQL 9.3。）

#!/usr/bin/sudo bash

service postgresql stop
sync
echo 3 > /proc/sys/vm/drop_caches
service postgresql start

I tested with a query that took 19 seconds the first time, and less than 2 seconds on subsequent attempts. After running this script, the query once again took 19 seconds.

我测试了一个查询，第一次需要 19 秒，随后的尝试不到 2 秒。运行此脚本后，查询再次耗时 19 秒。

回答by Mike Starov

I use this command on my linux box:

我在我的 linux 机器上使用这个命令：

sync; /etc/init.d/postgresql-9.0 stop; echo 1 > /proc/sys/vm/drop_caches; /etc/init.d/postgresql-9.0 start

It completely gets rid of the cache.

它完全摆脱了缓存。

回答by Magnus Hagander

Yes, postgresql certainly has caching. The size is controlled by the setting shared_buffers. Other than that, there is as the previous answer mentions, the OS file cache which is also used.

是的，postgresql 肯定有缓存。大小由设置shared_buffers控制。除此之外，正如前面的答案所提到的，还使用了操作系统文件缓存。

If you want to look at what's in the cache, there is a contrib module called pg_buffercacheavailable (in contrib/ in the source tree, in the contrib RPM, or wherever is appropriate for how you installed it). How to use it is listed in the standard PostgreSQL documentation.

如果您想查看缓存中的内容，有一个名为pg_buffercache的 contrib 模块可用（在源树中的 contrib/ 中，在 contrib RPM 中，或任何适合您安装方式的地方）。标准 PostgreSQL 文档中列出了如何使用它。

There are no ways to clear out the buffer cache, other than to restart the server. You can drop the OS cache with the command mentioned in the other answer - provided your OS is Linux.

除了重新启动服务器之外，没有其他方法可以清除缓冲区缓存。您可以使用另一个答案中提到的命令删除操作系统缓存 - 前提是您的操作系统是 Linux。

回答by Luc M

I had this error.

我有这个错误。

psql:/cygdrive/e/test_insertion.sql:9: ERROR: type of parameter 53 (t_stat_gardien) does not match that when preparing the plan (t_stat_avant)

psql:/cygdrive/e/test_insertion.sql:9: 错误：参数 53 (t_stat_gardien) 的类型与准备计划 (t_stat_avant) 时不匹配

I was looking for flushing the current plan and a found this:

我正在寻找刷新当前计划并找到了这个：

DISCARD PLANS

丢弃计划

I had this between my inserts and it solves my problem.

我的插入物之间有这个，它解决了我的问题。

回答by Thiago Linhares de Oliveira

Yes, it is possible to clear both the shared buffers postgres cache ANDthe OS cache. Solution bellow is for Windows... others have already given the linux solution.

是的，可以同时清除共享缓冲区 postgres 缓存和操作系统缓存。下面的解决方案适用于Windows...其他人已经给出了 linux 解决方案。

As many people already said, to clear the shared buffers you can just restart Postgres (no need to restart the server). But just doing this won't clear the OS cache.

正如许多人所说，要清除共享缓冲区，您只需重新启动 Postgres（无需重新启动服务器）。但仅仅这样做不会清除操作系统缓存。

To clear the OS cache used by Postgres, after stopping the service, use the excelent RamMap(https://technet.microsoft.com/en-us/sysinternals/rammap), from the excelent Sysinternals Suite. Once you execute RamMap, just click "Empty"->"Empty Standby List" in the main menu.

要清除 Postgres 使用的操作系统缓存，请在停止服务后使用来自优秀 Sysinternals Suite的优秀RamMap( https://technet.microsoft.com/en-us/sysinternals/rammap)。执行 RamMap 后，只需单击主菜单中的“Empty”->“Empty Standby List”。

Restart Postgres and you'll see now your next query will be damm slow due to no cache at all.

重新启动 Postgres，您现在会看到由于根本没有缓存，您的下一个查询将非常缓慢。

You can also execute the RamMap without closing Postgres, and probably will have the "no cache" results you want, since as people already said, shared buffers usually gives little impact compared to the OS cache. But for a reliable test, I would rather stop postgres as all before clearing the OS cache to make sure.

您也可以在不关闭 Postgres 的情况下执行 RamMap，并且可能会得到您想要的“无缓存”结果，因为正如人们已经说过的，与操作系统缓存相比，共享缓冲区通常几乎没有影响。但是为了可靠的测试，我宁愿在清除操作系统缓存之前停止 postgres 以确保。

Note: AFAIK, I don't recommend clearing the other things besides "Standby list" when using RamMap, because the other data is somehow being used, and you can potentially cause problems/loose data if you do that. Remember that you are clearing memory not only used by postgres files, but any other app and OS as well.

注意：AFAIK，我不建议在使用 RamMap 时清除“备用列表”之外的其他内容，因为其他数据正在以某种方式被使用，如果这样做可能会导致问题/数据丢失。请记住，您不仅要清除 postgres 文件使用的内存，还要清除任何其他应用程序和操作系统使用的内存。

Regards, Thiago L.

问候，蒂亚戈L。

回答by wutzebaer

this is my shortcut

这是我的捷径

echo 1 > /proc/sys/vm/drop_caches; echo 2 > /proc/sys/vm/drop_caches; echo 3 > /proc/sys/vm/drop_caches; rcpostgresql stop; rcpostgresql start;

回答by Ildar Musin

There is pg_buffercachemodule to look into shared_bufferscache. And at some point I needed to drop cache to make some performance tests on 'cold' cache so I wrote an pg_dropcacheextension that does exactly this. Please check it out.

有pg_buffercache模块可以查看shared_buffers缓存。在某些时候，我需要删除缓存以对“冷”缓存进行一些性能测试，因此我编写了一个pg_dropcache扩展来执行此操作。请检查一下。