Message-ID: <4D504275.5040103@2ndquadrant.com>
Date: Mon, 07 Feb 2011 14:05:25 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Craig Ringer <craig@postnewspapers.com.au>
CC: felix <crucialfelix@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Really really slow select count(*)
References: <AANLkTinAJcm5A+i1O_R87aiYyu8N2OWgoaL9RfR=tUR9@mail.gmail.com>
	<4D4C138C.7010304@2ndquadrant.com>	<4D4C1555.40209@peak6.com>
	<AANLkTikrS5JkJDMjAXpcRWRnuLbX+MHB=CWb_mFa9qb-@mail.gmail.com>
	<4D4C2AC3.5020804@peak6.com>
	<AANLkTin8ndBSU9ZJ2uAdnvYQ7oLBg2S04bqAXn-s5qv+@mail.gmail.com>
	<AANLkTim+aLaihKbNSsiiL4exgtCeRqCh_50j48jHFDQm@mail.gmail.com>
	<AANLkTimnFc-hKG+PFEfwBPLAoK6q9kx=nc_4Fr++C_QB@mail.gmail.com>
	<4D4F6109.1090209@postnewspapers.com.au>
In-Reply-To: <4D4F6109.1090209@postnewspapers.com.au>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Craig Ringer wrote:
> What would possibly help would be if Pg could fall back to lower
> shared_buffers automatically, screaming about it in the logs but still
> launching.

This is exactly what initdb does when it produces an initial setting for 
shared_buffers that goes into the postgresql.conf file.  It wouldn't be 
hard to move that same logic into a loop that executed when startup 
failed to allocated enough memory.

There are two problems here, one almost solved, the other more 
philosphical.  It used to be that max_fsm_pages and wal_buffers could be 
large enough components to the allocation that reducing them might 
actually be a necessary fix, too.  With the removal of the former and a 
method to automatically set the latter now available, the remaining 
components to the shared memory sizing computation are probably possible 
to try and fix automatically if the kernel limits are too low.

But it's unclear whether running in a degraded mode, where performance 
might be terrible, with only a log message is preferrable to stopping 
and forcing the DBA's attention toward the mistake that was made 
immediately.  Log files get rotated out, and it's not hard to imagine 
this problem coming to haunt someone only a month or two later--by which 
time the change to shared_buffers is long forgotten, and the log message 
complaining about it lost too.  Accordingly I would expect any serious 
attempt to add some auto-reduction behavior to be beset with argument, 
and I'd never consider writing such a thing as a result.  Too many 
non-controversial things I could work on instead.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books