From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Simon Riggs <simon(at)2ndquadrant(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>, Magnus Hagander <magnus(at)hagander(dot)net>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Claudio Freire <klaussfreire(at)gmail(dot)com>, Merlin Moncure <mmoncure(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers |
Date: | 2014-05-07 14:24:34 |
Message-ID: | 27075.1399472674@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, May 7, 2014 at 3:18 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> If we believe that 25% of shared_buffers worth of heap blocks would
>> flush the cache doing a SeqScan, why should we allow 400% of
>> shared_buffers worth of index blocks?
> I think you're comparing apples and oranges. The 25% threshold is
> answering the question "How big does a sequential scan have to be
> before it's likely to flush so much so much unrelated data out of
> shared_buffers that it hurts the performance of other things running
> on the system?". So it's not really about whether or not things will
> *fit* in the cache, but rather a judgement about at what point caching
> that stuff is going to be less value than continuing to cache other
> things. Also, it's specifically a judgement about shared_buffers, not
> system memory.
> But effective_cache_size is used to estimate the likelihood that an
> index scan which accesses the same heap or index block twice will
> still be in cache on the second hit, and thus need to be faulted in
> only once. So this *is* a judgment about what will fit - generally
> over a very short time scale. And, since bringing a page into
> shared_buffers from the OS cache is much less expensive than bringing
> a page into memory from disk, it's really about what will fit in
> overall system memory, not just shared_buffers.
Another point is that the 25% seqscan threshold actually controls some
specific caching decisions, which effective_cache_size does not. Raising
effective_cache_size "too high" is unlikely to result in cache trashing;
in fact I'd guess the opposite. What that would do is cause the planner
to prefer indexscans over seqscans in more cases involving large tables.
But if you've got a table+index that's bigger than RAM, seqscans are
probably going to be worse for the OS cache than indexscans, because
they're going to require bringing in more data.
So I still think this whole argument is founded on shaky hypotheses
with a complete lack of hard data showing that a smaller default for
effective_cache_size would be better. The evidence we have points
in the other direction.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Petr Jelinek | 2014-05-07 14:25:46 | Re: bgworker crashed or not? |
Previous Message | Magnus Hagander | 2014-05-07 14:20:08 | Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers |