pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
How much memory is PostgreSQL using 4+ messages / 3 participants [nested] [flat]
* How much memory is PostgreSQL using @ 2010-03-29 14:59 Campbell, Lance <lance@illinois.edu> 0 siblings, 1 reply; 4+ messages in thread From: Campbell, Lance @ 2010-03-29 14:59 UTC (permalink / raw) To: pgsql-performance PostgreSQL 8.4.3 OS: Linux Red Hat 4.x I changed my strategy with PostgreSQL recently to use a large segment of memory for shared buffers with the idea of caching disk blocks. How can I see how much memory PostgreSQL is using for this? I tried: ps aux | grep post | sort -k4 This lists the processes using memory at the bottom. Are they sharing memory or using individual their own blocks of memory? When I do top I see that VIRT is the value of my shared buffers plus a tiny bit. I see %MEM is only 2.4%, 2.6%, 1.0%,1.5%, and 1.1% for all of the running processes. Do I add these percentages up to see what amount of VIRT I am really using? Or is there some way to ask PostgreSQL how much memory are you using to cache disk blocks currently? When you do a PG_DUMP does PostgreSQL put the disk blocks into shared buffers as it runs? Thanks, Lance Campbell Software Architect/DBA/Project Manager Web Services at Public Affairs 217-333-0382 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How much memory is PostgreSQL using @ 2010-03-29 16:53 Greg Smith <greg@2ndquadrant.com> parent: Campbell, Lance <lance@illinois.edu> 0 siblings, 1 reply; 4+ messages in thread From: Greg Smith @ 2010-03-29 16:53 UTC (permalink / raw) To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-performance 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How much memory is PostgreSQL using @ 2010-04-02 20:10 Campbell, Lance <lance@illinois.edu> parent: Greg Smith <greg@2ndquadrant.com> 0 siblings, 1 reply; 4+ messages in thread From: Campbell, Lance @ 2010-04-02 20:10 UTC (permalink / raw) To: Greg Smith <greg@2ndquadrant.com>; +Cc: pgsql-performance 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How much memory is PostgreSQL using @ 2010-04-03 11:58 Guillaume Lelarge <guillaume@lelarge.info> parent: Campbell, Lance <lance@illinois.edu> 0 siblings, 0 replies; 4+ messages in thread From: Guillaume Lelarge @ 2010-04-03 11:58 UTC (permalink / raw) To: Campbell, Lance <lance@illinois.edu>; +Cc: Greg Smith <greg@2ndquadrant.com>; pgsql-performance 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-04-03 11:58 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2010-03-29 14:59 How much memory is PostgreSQL using Campbell, Lance <lance@illinois.edu> 2010-03-29 16:53 ` Greg Smith <greg@2ndquadrant.com> 2010-04-02 20:10 ` Campbell, Lance <lance@illinois.edu> 2010-04-03 11:58 ` Guillaume Lelarge <guillaume@lelarge.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox