Re: [GENERAL] PostgreSQL Cache

A while ago I wrote a script based on Dave Plonka work
http://net.doit.wisc.edu/~plonka/fincore/

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.

Oleg

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 http://www.gregsmith.com Baltimore, MD
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83