Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] PostgreSQL Cache

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Greg Smith <gsmith(at)gregsmith(dot)com>
Cc: Matthew Pulis <mpulis(at)gmail(dot)com>, pgsql-admin(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] PostgreSQL Cache
Date: 2008-09-29 07:51:52
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-adminpgsql-general
A while ago I wrote a script based on Dave Plonka work

My script monitors system buffers and shared buffers 
(if pg_buffercache installed) and I found it's almost useless to 
check system buffers, since I got rather ridiculous numbers.

> I use it to investigate OS cacheing of PostgreSQL files and was
> surprized on 24 Gb server, total cache was about 30 Gb. How this is
> possible ?

I can send script and perl module if you want to play with.


On Mon, 29 Sep 2008, Greg Smith wrote:

> On Mon, 29 Sep 2008, Matthew Pulis wrote:
>> I need to perform some timed testing, thus need to make sure that disk 
>> cache
>> does not affect me. Is clearing the OS (Ubuntu) disk cache, ( by running:
>> sudo echo 3 | sudo tee /proc/sys/vm/drop_caches ) enough to do this?
> What you should do is:
> 1) Shutdown the database server (pg_ctl, sudo service postgresql stop, etc.)
> 2) sync
> 3) sudo echo 3 > /proc/sys/vm/drop_caches
> 4) Start the database server
> That will clear both the database and OS cache with a minimum of junk left 
> behind in the process; clearing the cache without a sync is a bad idea.
> Note that all of this will still leave behind whatever cache is in your disk 
> controller card or on the disk themselves available.  There are some other 
> techniques you could consider.  Add a setp 2.5 that generates a bunch of data 
> unused by the test, then sync again, and you've turned most of that into 
> useless caching.
> Ideally, your test should be running against a large enough data set that the 
> dozens or couple of hundred megabytes that might be in those will only add a 
> bit of noise to whatever you're testing.  If you're not running a larger test 
> or going through tasts to make the caches clear, the only easy way to make 
> things more clear is to reboot the whole server.
> --
> * Greg Smith gsmith(at)gregsmith(dot)com Baltimore, MD

Oleg Bartunov, Research Scientist, Head of AstroNet (,
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su,
phone: +007(495)939-16-83, +007(495)939-23-83

In response to

pgsql-admin by date

Next:From: Peter KovacsDate: 2008-09-29 09:37:36
Subject: Do we need vacuuming when tables are regularly dropped?
Previous:From: Joris DobbelsteenDate: 2008-09-29 07:01:12
Subject: Re: [GENERAL] PostgreSQL Cache

pgsql-general by date

Next:From: Ivan ZolotukhinDate: 2008-09-29 09:39:58
Subject: Re: pg_start_backup() takes too long
Previous:From: Abdul RahmanDate: 2008-09-29 07:49:06
Subject: Re: Replication using slony-I

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group