Обсуждение: How much memory is PostgreSQL using

Campbell, Lance wrote:
>
> Or is there some way to ask PostgreSQL how much memory are you using
> to cache disk blocks currently?
>

You can install contrib/pg_buffercache into each database and count how
many used blocks are there.  Note that running queries using that
diagnostic tool is really intensive due to the locks it takes, so be
careful not to do that often on a production system.


> When you do a PG_DUMP does PostgreSQL put the disk blocks into shared
> buffers as it runs?
>

To some extent.  Most pg_dump activity involves sequential scans that
are reading an entire table.  Those are no different from any other
process that will put disk blocks into shared_buffers.  However, that
usage pattern makes pg_dump particularly likely to run into an
optimization in 8.3 and later that limits how much of shared_buffers is
used when sequentially scanning a large table.  See P10 of
http://www.westnet.com/~gsmith/content/postgresql/InsideBufferCache.pdf
for the exact implementation.  Basically, anything bigger than
shared_buffers / 4 uses a 256K ring to limit its cache use, but it's a
little more complicated than that.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Greg,
Thanks for your help.

1) How does the number of buffers provided by pg_buffercache compare to
memory (buffers * X = Y meg)?
2) Is there a way to tell how many total buffers I have available/max?

Thanks,

Lance Campbell
Software Architect/DBA/Project Manager
Web Services at Public Affairs
217-333-0382


-----Original Message-----
From: Greg Smith [mailto:greg@2ndquadrant.com]
Sent: Monday, March 29, 2010 11:54 AM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How much memory is PostgreSQL using

Campbell, Lance wrote:
>
> Or is there some way to ask PostgreSQL how much memory are you using
> to cache disk blocks currently?
>

You can install contrib/pg_buffercache into each database and count how
many used blocks are there.  Note that running queries using that
diagnostic tool is really intensive due to the locks it takes, so be
careful not to do that often on a production system.


> When you do a PG_DUMP does PostgreSQL put the disk blocks into shared
> buffers as it runs?
>

To some extent.  Most pg_dump activity involves sequential scans that
are reading an entire table.  Those are no different from any other
process that will put disk blocks into shared_buffers.  However, that
usage pattern makes pg_dump particularly likely to run into an
optimization in 8.3 and later that limits how much of shared_buffers is
used when sequentially scanning a large table.  See P10 of
http://www.westnet.com/~gsmith/content/postgresql/InsideBufferCache.pdf
for the exact implementation.  Basically, anything bigger than
shared_buffers / 4 uses a 256K ring to limit its cache use, but it's a
little more complicated than that.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Le 02/04/2010 22:10, Campbell, Lance a écrit :
> Greg,
> Thanks for your help.
>
> 1) How does the number of buffers provided by pg_buffercache compare to
> memory (buffers * X = Y meg)?

1 buffer is 8 KB.

> 2) Is there a way to tell how many total buffers I have available/max?

With pg_buffercache, yes.

SELECT count(*)
FROM pg_buffercache
WHERE relfilenode IS NOT NULL;

should give you the number of non-empty buffers.


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com