Обсуждение: 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/