How clear the cache on postgresql?

On Fri, Nov 24, 2017 at 6:54 AM, hmidi slim <hmidi.slim2@gmail.com> wrote:

I'm trying to analyze some queries using the explain instructions and the
option analyze and buffers. I realized that the query refers to the cache
memory to return the results. Is there any solution to clear the cache in
postgresql inorder to get execution time of the query when it get data from
disk and not from cache memory?

​
If you are on a (non-production) *nix server you can use:
sync && echo 3 > /proc/sys/vm/drop_caches​

bricklen <bricklen@gmail.com> writes:

On Fri, Nov 24, 2017 at 6:54 AM, hmidi slim <hmidi.slim2@gmail.com> wrote:

I'm trying to analyze some queries using the explain instructions and the
option analyze and buffers. I realized that the query refers to the cache
memory to return the results. Is there any solution to clear the cache in
postgresql inorder to get execution time of the query when it get data from
disk and not from cache memory?

If you are on a (non-production) *nix server you can use:
sync && echo 3 > /proc/sys/vm/drop_caches​

You would also need to restart the postmaster, to get rid of whatever
is in Postgres' shared buffers.

regards, tom lane

I execute the command sync && echo 3 > /proc/sys/vm/drop_caches​ but I
still got buffers hit added to that buffers read now.Why I got the buffers
hit?

2017-11-24 17:55 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

On Fri, Nov 24, 2017 at 8:54 AM, hmidi slim <hmidi.slim2@gmail.com> wrote:

I'm trying to analyze some queries using the explain instructions and the
option analyze and buffers. I realized that the query refers to the cache
memory to return the results. Is there any solution to clear the cache in
postgresql inorder to get execution time of the query when it get data from
disk and not from cache memory?

There are so many different levels of caching going on--within Postgresql,
within the OS, within a disk array or SAN, and at the individual
drive--that there may no longer be a meaningful way to perform this
measurement.
--
Mike Nolan

On 11/24/2017 11:43 AM, Michael Nolan wrote:

There are so many different levels of caching going on--within
Postgresql, within the OS, within a disk array or SAN, and at the
individual drive--that there may no longer be a meaningful way to
perform this measurement.

generally, power cycling the server will flush all the hardware caches
AND the OS cache.

--
john r pierce, recycling bits in santa cruz

2017-11-24 17:55 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

bricklen <bricklen@gmail.com> writes:

If you are on a (non-production) *nix server you can use:
sync && echo 3 > /proc/sys/vm/drop_caches​

You would also need to restart the postmaster, to get rid of whatever
is in Postgres' shared buffers.

And restart postgres first, otherwise the shutdown checkpoint will
repopulate
some of the buffers you just dropped via drop_caches.

On Fri, Nov 24, 2017 at 9:09 AM, hmidi slim <hmidi.slim2@gmail.com> wrote:

I execute the command sync && echo 3 > /proc/sys/vm/drop_caches​ but I
still got buffers hit added to that buffers read now.Why I got the buffers
hit?

Please don't top post in this mailling list.

If the same buffer is accessed repeatedly in a query, then some of those
accesses will be from the cache even if it were completely cold to start
with.

Cheers,

Jeff

On Fri, 2017-11-24 at 11:58 -0800, John R Pierce wrote:

On 11/24/2017 11:43 AM, Michael Nolan wrote:

There are so many different levels of caching going on--within�
Postgresql, within the OS, within a disk array or SAN, and at the�
individual drive--that there may no longer be a meaningful way to�
perform this measurement.

generally, power cycling the server will flush all the hardware
caches AND the OS cache.

Given that a real-world application will almost never experience an
empty-cache scenario I question the usefulness of clearing the
cache(s). � I would capture transactions from a production database in
order to create a load test that mimics real-world load.

--
Meetings Coordinator, Michigan Association of Railroad Passengers
537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010
E-mail: awilliam@whitemice.org GPG#D95ED383 Web: http://www.marp.org

Hi,

A way to trick EXPLAIN/EXPLAIN ANALYZE to do what you mean is to play
with the optimisation variables:

https://www.postgresql.org/docs/9.6/static/runtime-config-query.html

J.

On 25 November 2017 at 15:01, Adam Tauno Williams
<awilliam@whitemice.org> wrote:

On Fri, 2017-11-24 at 11:58 -0800, John R Pierce wrote:

On 11/24/2017 11:43 AM, Michael Nolan wrote:

There are so many different levels of caching going on--within
Postgresql, within the OS, within a disk array or SAN, and at the
individual drive--that there may no longer be a meaningful way to
perform this measurement.

generally, power cycling the server will flush all the hardware
caches AND the OS cache.

Given that a real-world application will almost never experience an
empty-cache scenario I question the usefulness of clearing the
cache(s). I would capture transactions from a production database in
order to create a load test that mimics real-world load.

--
Meetings Coordinator, Michigan Association of Railroad Passengers
537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010
E-mail: awilliam@whitemice.org GPG#D95ED383 Web: http://www.marp.org

--
Jerome Eteve
http://www.justgiving.com/jetevesober
+44(0)7738864546
http://www.eteve.net/