I have a small database PostgreSQL (v9.3) on Centos 6 x64 ( RAM: 8 GB ).
max_connections = 512 shared_buffers = 3000MB temp_buffers = 8MB work_mem = 2MB maintenance_work_mem = 128MB effective_cache_size = 3000MB
About 150 connections, PostgreSQL takes more than 6 GB of RAM (of course, other applications use about 200 MB of RAM), here my info:
Mem: 7062.945M total, 6892.410M used, 170.535M free, 6644.000k buffers Swap: 0.000k total, 0.000k used, 0.000k free, 5378.922M cached
Why does PG takes a lot of RAM ?
How can I reduce cached buffers of PG ?
PostgreSQL isn’t using lots of RAM, the kernel is using it for disk cache.
This is exactly what you want to happen. There is nothing to fix here.
“Free” RAM is wasted RAM. The best thing for a database server is for as much RAM as possible to be used as disk cache.
shared_buffers = 3000MB, Postgres does take “a lot” of RAM (relatively speaking for a system with 8 GB) – even if not all the RAM, like you may have been thinking. And the “why” is obvious: because you instructed it with your setting for
shared_buffers. The setting is high (default is 128MB), for a dedicated DB server the setting seems still reasonable, though. (More than ~ 40% of total RAM is typically overkill.)
Postgres uses both the system cache and its own shared buffers. Shared buffers are dedicated to Postgres and the memory is not shared with other processes. They are used for short-term buffering of data and vital to fast processing. The system cache is generally smarter and faster for long-term handling of what to keep in cache and what to push out if pressed for resources. @Craig’s answer and the site you found explain why the system cache seems to eat all your RAM. All of this is for Unix / Linux. Windows works differently in some aspects, I am not familiar with the details.
- The manual has detailed information and instructions for various scenarios: Managing Kernel Resources
- The Postgres Wiki has more advice on how to set
To “reduce cached buffers of PG” (and you really might want to do that for a “small database”) lower the setting for
shared_buffers and restart Postgres:
This parameter can only be set at server start.