Обсуждение: shared_buffers advice
Hi there, I'm after a little bit of advice on the shared_buffers setting (I have read the various docs on/linked from the performance tuning wiki page, some very helpful stuff there so thanks to those people). I am setting up a 64bit Linux server running Postgresql 8.3, the server has 64gigs of memory and Postgres is the only major application running on it. (This server is to go alongside some existing 8.3 servers, we will look at 8.4/9 migration later) I'm basically wondering how the postgresql cache (ie shared_buffers) and the OS page_cache interact. The general advice seems to be to assign 1/4 of RAM to shared buffers. I don't have a good knowledge of the internals but I'm wondering if this will effectively mean that roughly the same amount of RAM being used for the OS page cache will be used for redundantly caching something the Postgres is caching as well? IE when Postgres reads something from disk it will go into both the OS page cache and the Postgresql shared_buffers and the OS page cache copy is unlikely to be useful for anything. If that is the case what are the downsides to having less overlap between the caches, IE heavily favouring one or the other, such as allocating shared_buffers to a much larger percentage (such as 90-95% of expected 'free' memory). Paul (Apologies if two copies of this email arrive, I sent the first from an email address that wasn't directly subscribed to the list so it was blocked).
On Mar 10, 2010, at 6:22 PM, Paul McGarry wrote: > Hi there, > > I'm after a little bit of advice on the shared_buffers setting (I have > read the various docs on/linked from the performance tuning wiki page, > some very helpful stuff there so thanks to those people). > > I am setting up a 64bit Linux server running Postgresql 8.3, the > server has 64gigs of memory and Postgres is the only major application > running on it. (This server is to go alongside some existing 8.3 > servers, we will look at 8.4/9 migration later) > > I'm basically wondering how the postgresql cache (ie shared_buffers) > and the OS page_cache interact. The general advice seems to be to > assign 1/4 of RAM to shared buffers. > > I don't have a good knowledge of the internals but I'm wondering if > this will effectively mean that roughly the same amount of RAM being > used for the OS page cache will be used for redundantly caching > something the Postgres is caching as well? > > IE when Postgres reads something from disk it will go into both the OS > page cache and the Postgresql shared_buffers and the OS page cache > copy is unlikely to be useful for anything. > > If that is the case what are the downsides to having less overlap > between the caches, IE heavily favouring one or the other, such as > allocating shared_buffers to a much larger percentage (such as 90-95% > of expected 'free' memory). Cache isn't all you have to worry about. There's also work_mem and the number of concurrent queries that you expect, andthose may end up leaving you less than 25% of ram for shared_buffers - though probably not in your case. Also, I've readthat 10GB is the upper end of where shared_buffers becomes useful, though I'm not entirely sure why. I think that ruleof thumb has its roots in some heuristics around the double buffering effects you're asking about. I *can* say a 10GB shared_buffer value is working "well" with my 128GB of RAM..... whether or not it's "optimal," I couldn'tsay without a lot of experimentation I can't afford to do right now. You might have a look at the pg_buffercachecontrib module. It can tell you how utilized your shared buffers are.
Paul McGarry wrote: > IE when Postgres reads something from disk it will go into both the OS > page cache and the Postgresql shared_buffers and the OS page cache > copy is unlikely to be useful for anything. > That's correct. However, what should happen over time is that the popular blocks in PostgreSQL's buffer cache, the hot ones that are used all the time for things like index blocks, will stay in the PG buffer cache, while being evicted from the OS. And now you've got a win over the situation where you'd have used a smaller buffer cache. A typical OS buffering scheme will not quite be smart enough to prioritize those blocks over the rest so that they are likely to stay there. So for any given system, the question is whether the gain in performance from buffers that get a high usage count and stay there, something you only get from the PG buffer cache, outweighs the overhead of the double-buffering that shows up in order to reach that state. If you oversize the buffer cache, and look inside it with pg_buffercache considering the usage count distribution, you can actually estimate how likely that is to be true. > If that is the case what are the downsides to having less overlap > between the caches, IE heavily favouring one or the other, such as > allocating shared_buffers to a much larger percentage (such as 90-95% > of expected 'free' memory). > Giving all the buffers to the database doesn't work for many reasons: -Need a bunch leftover for clients to use (i.e. work_mem) -Won't be enough OS cache for non-buffer data the database expects cached reads and writes will perform well onto (some of the non-database files it uses) -Database checkpoints will turn into a nightmare, because there will be so much more dirty data that could have been spooled regularly out to the OS and then to disk by backends that doesn't ever happen. -Not having enough writes for buffering backend writes means less chanes to do write combining and elevator seek sorting, which means average I/O will drop. The alternate idea is to make shared_buffers small. I see people happilly running away in the 128MB - 256MB range sometimes. The benefit over just using the default of <32MB is obvious, but you're already past a good bit of the diminishing marginal returns just by the 8X increase. Improves keep coming as shared_buffers cache size increases for many workloads, but eventually you can expect to go to far if you try to push everything in there. Only question is whether that happens at 40%, 60%, or something higher. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
On Mar 11, 2010, at 12:39 AM, Greg Smith wrote: > > Giving all the buffers to the database doesn't work for many reasons: > -Need a bunch leftover for clients to use (i.e. work_mem) > -Won't be enough OS cache for non-buffer data the database expects > cached reads and writes will perform well onto (some of the non-database > files it uses) > -Database checkpoints will turn into a nightmare, because there will be > so much more dirty data that could have been spooled regularly out to > the OS and then to disk by backends that doesn't ever happen. > -Not having enough writes for buffering backend writes means less chanes > to do write combining and elevator seek sorting, which means average I/O > will drop. > > The alternate idea is to make shared_buffers small. I see people > happilly running away in the 128MB - 256MB range sometimes. The benefit > over just using the default of <32MB is obvious, but you're already past > a good bit of the diminishing marginal returns just by the 8X increase. > The DB usage pattern influences this sort of decision too. One that does large bulk inserts can prefer larger shared buffers,provided its bg_writer is tuned well (10GB - 16GB for a 64GB server). Temp table usage benefits from it as well -- I believe that one created as "ON COMMIT DROP" has a better chance of not beingwritten to the data disk before being dropped with more work_mem. If you have a mixed read workload that has occasional very large sequential scans, you will want to make sure shared_buffersis large enough to hold the most important index and randomly accessed data. Linux is more sensitive to letting sequential scans kick out data from page cache than Postgres. ---------- Lastly, a word of caution on Linux. Before the recent changes to memory accounting and paging (~ kernel 2.28 ish?). Shared_buffersare only accounted for in part of the equations for paging. On one hand, the system sees shared memory asavailable to be swapped out (even though it won't) and on the other hand it senses memory pressure from it. So if youfor example, set shared_mem to 75% of your RAM the system will completely freak out and kswapd and other processes willgo through long periods of 100% CPU utilization. An example: 32GB RAM, 16GB shared_buffers, CentOS 5.4: With the default os 'swappiness' of '60' the system will note that less than 60% is used by pagecache and favor swappingout postgres backends aggressively. If either by turning down the swappiness or opening enough processes to consumemore RAM on the system (to ~ 80% or so) the kernel will start spending a LOT of CPU, often minutes at a time, tryingto free up memory. From my understanding, it will keep searching the postgres shared_buffers space for pages to swapout even though it can't do so. So for example, there might be 16GB shared mem (which it won't page out), 10GB otherprocess memory, and 6GB actual cahced files in page cache. It sees the ratio of 6GB files to 26GB processes and heavilyfavors attacking the 26GB -- but scans the whole set of process memory and finds all pages are recently used or can'tbe paged out. Anyhow, the latest linux kernels claim to fix this, and Solaris/OpenSolaris or BSD's don't have this problem. On OpenSolaristhere are some benchmarks out there that showing that 90% of memory allocated to shared_buffers can work well. On Linux, that is dangerous. Combine the poor memory management when there is a lot of shared memory with the factthat 50% is bad for double-buffering, and the Linux suggestion becomes the typical 'at least 128MB, but never more than25% of RAM'. > Improves keep coming as shared_buffers cache size increases for many > workloads, but eventually you can expect to go to far if you try to push > everything in there. Only question is whether that happens at 40%, 60%, > or something higher. > > -- > Greg Smith 2ndQuadrant US Baltimore, MD > PostgreSQL Training, Services and Support > greg@2ndQuadrant.com www.2ndQuadrant.us > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
On 11 March 2010 16:16, Ben Chobot <bench@silentmedia.com> wrote: > I *can* say a 10GB shared_buffer value is working "well" with my 128GB of RAM..... whether or not it's "optimal," I couldn'tsay without a lot of experimentation I can't afford to do right now. You might have a look at the pg_buffercachecontrib module. It can tell you how utilized your shared buffers are. Thanks Ben and Greg, I shall start with something relatively sane (such as 10GB) and then see how we go from there. Once this server has brought online and bedded in I will be updating our other three servers which are identical in hardware spec and all have the same replicated data so I'll be able to do some real world tests with different settings withn the same load. (Currently one is currently running postgresql 8.1 on 32bit OS under a VM, the other two running 8.3 on 64bit OS with 64gig of memory but with Postgres still tuned for the 8 gigs the servers originally had and under a VM). Paul
On Thu, Mar 11, 2010 at 5:19 PM, Paul McGarry <paul@paulmcgarry.com> wrote: > On 11 March 2010 16:16, Ben Chobot <bench@silentmedia.com> wrote: > >> I *can* say a 10GB shared_buffer value is working "well" with my 128GB of RAM..... whether or not it's "optimal," I couldn'tsay without a lot of experimentation I can't afford to do right now. You might have a look at the pg_buffercachecontrib module. It can tell you how utilized your shared buffers are. > > Thanks Ben and Greg, > > I shall start with something relatively sane (such as 10GB) and then > see how we go from there. > > Once this server has brought online and bedded in I will be updating > our other three servers which are identical in hardware spec and all > have the same replicated data so I'll be able to do some real world > tests with different settings withn the same load. > > (Currently one is currently running postgresql 8.1 on 32bit OS under a > VM, the other two running 8.3 on 64bit OS with 64gig of memory but > with Postgres still tuned for the 8 gigs the servers originally had > and under a VM). Definitely look at lowering the swappiness setting. On a db server I go for a swappiness of 1