Обсуждение: how to clean the cache from databases and operating system

Hi,

On 26 September 2018 at 08:25, jimmy <mpokky@126.com> wrote:

1、When I execute the firse sql query, like below:

     select * from tablename;

     there are some datas that will be loaded into the database cache.

     How to clean the data from cache.

2、When I execute second sql query like below:

     SELECT pg_prewarm('tablename', 'buffer') . Data will be loaded into the  database cache.

     How to clean the data from cache.

3、When I execute the third sql query like below:

     SELECT pg_prewarm('tablename', 'main') . Data will be loaded into the  os  cache.

     How to clean the data from cache.

To drop the system cache, as per [0] : echo 3 > /proc/sys/vm/drop_caches

To drop postgres' cache, afaik the easiest is to restart postgres.

If you're like me and too lazy to do all that (and don't care about potentially losing data), you can also install an extension rjuju and I wrote that allows you to do that simply with `SELECT pg_drop_caches`.

See [1] for the extension and [2] for how to use it.

[0] https://www.kernel.org/doc/Documentation/sysctl/vm.txt search for "drop_caches" in that page

[1] https://github.com/rjuju/pg_dropbuffers

[2] https://maahl.net/pg_dropbuffers