Обсуждение: How clear the cache on postgresql?

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?

On Fri, Nov 24, 2017 at 6:54 AM, hmidi slim 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 : > bricklen writes: > > On Fri, Nov 24, 2017 at 6:54 AM, hmidi slim > 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 >

On Fri, Nov 24, 2017 at 8:54 AM, hmidi slim 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 : > bricklen 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 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/