Free Cache Memory (Linux) and Postgresql

On Tue, Sep 30, 2008 at 3:33 AM, Denis Gasparin <denis@edistar.com> wrote:

Hi.

I'm evaluating to issue the drop_caches kernel command (echo 3 >
/proc/sys/vm/drop_caches) in order to free unused pagecache, directory
entries and inodes.

I'm thinking to schedule the command during low load moments after
forcing a sync command.

I wonder if this can cause pgsql problems of any kind. Any idea?

Yes, it can. Postgres relies heavily on the OS' file system cache, if
you wipe it out, you're going to have quite an I/O storm on a large
database.

What are you trying to accomplish? By itself, sync will flush all
dirty file system blocks to disk and leave them in memory.

--
Jonah H. Harris, Senior DBA
myYearbook.com

Denis Gasparin <denis@edistar.com> writes:

I'm evaluating to issue the drop_caches kernel command (echo 3 >
/proc/sys/vm/drop_caches) in order to free unused pagecache, directory
entries and inodes.

Why in the world would you think that's a good idea?

regards, tom lane

Tom Lane ha scritto:

Denis Gasparin <denis@edistar.com> writes:

I'm evaluating to issue the drop_caches kernel command (echo 3 >
/proc/sys/vm/drop_caches) in order to free unused pagecache, directory
entries and inodes.

Why in the world would you think that's a good idea?

regards, tom lane

We see cached memory growing on constant base, even if there are no
connections to database.

We have some tables that are truncated and reloaded with updated data on
regular basis (3,4 days).

It seems like postgres or the operating system (linux) is keeping in
cache that old data even if it has been deleted.

We're searching a way to free that memory without shutting down pgsql.

Thank you for your help,
Denis

On Tue, Sep 30, 2008 at 7:51 AM, Denis Gasparin <denis@edistar.com> wrote:

Tom Lane ha scritto:

Denis Gasparin <denis@edistar.com> writes:

I'm evaluating to issue the drop_caches kernel command (echo 3 >
/proc/sys/vm/drop_caches) in order to free unused pagecache, directory
entries and inodes.

Why in the world would you think that's a good idea?

regards, tom lane

We see cached memory growing on constant base, even if there are no
connections to database.

We have some tables that are truncated and reloaded with updated data on
regular basis (3,4 days).

It seems like postgres or the operating system (linux) is keeping in
cache that old data even if it has been deleted.

We're searching a way to free that memory without shutting down pgsql.

You're fixing a problem that isn't there. The OS allocates cache (the
cache shown under top). And it uses all free memory it can get its
hands on to do so. The second postgres or any other program asks for
memory, the kernel throws away the oldest bits of cache to provide
memory to the application.

What you are doing is counter-productive.

If its the OS cache the kernel ought to free the memory when there is
something else worth caching. Its not a big deal if the cache is full so
long as the system still performs well.

On Tue, Sep 30, 2008 at 9:51 AM, Denis Gasparin <denis@edistar.com> wrote:

On Tue, Sep 30, 2008 at 03:51:44PM +0200, Denis Gasparin wrote:

It seems like postgres or the operating system (linux) is keeping in
cache that old data even if it has been deleted.

Just remember: "free memory" is "memory you paid for and are not
using" == "wasted memory". The OS knows damn well it's not important
and will throw it out if necessary, but it costs nothing to keep it.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Tue, Sep 30, 2008 at 4:02 PM, Martijn van Oosterhout
<kleptog@svana.org> wrote:

On Tue, Sep 30, 2008 at 03:51:44PM +0200, Denis Gasparin wrote:

It seems like postgres or the operating system (linux) is keeping in
cache that old data even if it has been deleted.

Just remember: "free memory" is "memory you paid for and are not
using" == "wasted memory". The OS knows damn well it's not important
and will throw it out if necessary, but it costs nothing to keep it.

free
total used free shared buffers cached
Mem: 33031252 24901824 8129428 0 380492 21991100

shhhhh. don't tell me boss we've got 8 gig free in the db servers,
he'll want to re-purpose it.

Still 20+ Gig of cache is awfully nice.