Re: CLOG contention

On Thu, Jan 5, 2012 at 10:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Thu, Jan 5, 2012 at 2:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I would be in favor of that, or perhaps some other formula (eg, maybe
>>> the minimum should be less than 8 for when you've got very little shmem).
>
>> I have some results that show that, under the right set of
>> circumstances, 8->32 is a win, and I can quantify by how much it wins.
>>  I don't have any data at all to quantify the cost of dropping the
>> minimum from 8->6, or from 8->4, and therefore I'm reluctant to do it.
>>  My guess is that it's a bad idea, anyway.  Even on a system where
>> shared_buffers is just 8MB, we have 1024 regular buffers and 8 CLOG
>> buffers.  If we reduce the number of CLOG buffers from 8 to 4 (i.e. by
>> 50%), we can increase the number of regular buffers from 1024 to 1028
>> (i.e. by <0.5%).  Maybe you can find a case where that comes out to a
>> win, but you might have to look pretty hard.
>
> I think you're rejecting the concept too easily.  A setup with very
> little shmem is only going to be suitable for low-velocity systems that
> are not pushing too many transactions through per second, so it's not
> likely to need so many CLOG buffers.  And frankly I'm not that concerned
> about what the performance is like: I'm more concerned about whether
> PG will start up at all without modifying the system shmem limits,
> on systems with legacy values for SHMMAX etc.  Shaving a few
> single-purpose buffers to make back what we spent on SSI, for example,
> seems like a good idea to me.

Having 32 clog buffers is important at the high end. I don't think
that other complexities should mask that truth and lead to us not
doing anything on this topic for this release.

Please can we either make it user configurable? prepared transactions
require config, lock table size is configurable also, so having SSI
and clog require config is not too much of a stretch. We can then
discuss intelligent autotuning behaviour when we have more time and
more evidence.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services