Thread: How to find how much postgresql use the memory?
Hi all,
I wonder how to find how much postgresql memory consumption is (real, not allocated) ?
thx.
> I wonder how to find how much postgresql memory consumption
> is (real, not allocated) ?
You could install the pg_buffercache contrib and
SELECT pg_size_pretty(
CAST(current_setting('block_size') AS integer) * max(bufferid)
)
FROM pg_buffercache WHERE relfilenode IS NOT NULL;
to find out how much of the shared memory in the buffer pool is
actually in use.
There is some extra shared memory that is used for other purposes,
and there is private memory of the backends (the latter could be
a significant number).
Why do you want that number?
If it is for tuning of shared_buffers, that is probably a bad way.
Even if shared_buffers is set much higher than you'd actually need
it, sooner or later all of your shared_buffers will be used, unless
your database is tiny.
Yours,
Laurenz Albe
Ok I see. So what's the best way to find optimum value for various memory-related setting of postgresql ?
On Tue, Jan 20, 2009 at 3:27 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> I wonder how to find how much postgresql memory consumptionYou could install the pg_buffercache contrib and
> is (real, not allocated) ?
SELECT pg_size_pretty(
CAST(current_setting('block_size') AS integer) * max(bufferid)
)
FROM pg_buffercache WHERE relfilenode IS NOT NULL;
to find out how much of the shared memory in the buffer pool is
actually in use.
There is some extra shared memory that is used for other purposes,
and there is private memory of the backends (the latter could be
a significant number).
Why do you want that number?
If it is for tuning of shared_buffers, that is probably a bad way.
Even if shared_buffers is set much higher than you'd actually need
it, sooner or later all of your shared_buffers will be used, unless
your database is tiny.
Yours,
Laurenz Albe
Luki Rustianto wrote: > Ok I see. So what's the best way to find optimum value for > various memory-related setting of postgresql ? How much memory is there in the machine? Are there other programs running or is the machine dedicated to the database? Are the queries you run complicated (order / hash large amounts of data) or simple? Maybe a rough guideline would be: Set work_mem to the amount of memory a query will regularly need for sorting and such, set max_connections to the maximum number of connections you need. Then figure out how much memory you want to dedicate to the database, subtract work_mem * max_connections from that value and set shared_buffers to the result value. Make sure you adjust the kernel parameters accordingly, see http://www.postgresql.org/docs/current/static/kernel-resources.html Tom Lane suggested in http://archives.postgresql.org/pgsql-general/2004-02/msg00471.php that it might be better to set shared_buffers "relatively small" and let the filesystem cache do the buffering, so that's another way you can go. His advice is usually good. Yours, Laurenz Albe
On Wed, Jan 21, 2009 at 10:15 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > > Tom Lane suggested in > http://archives.postgresql.org/pgsql-general/2004-02/msg00471.php > that it might be better to set shared_buffers "relatively > small" and let the filesystem cache do the buffering, so that's > another way you can go. His advice is usually good. Note that for transactional databases that are too large to fit the dataset into memory (think a 16Gig machine running a 100G transactional database) this is very true. The shared_buffers seldom get reused and you'll get faster throughput with lower shared_buffers. In testing a 40Gig db on a 32Gig machine, I got the best pgbench numbers with a shared_buffers setting in the hundreds of megs range.