From: Greg Smith <greg@2ndquadrant.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: felix <crucialfelix@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Really really slow select count(*)
Date: Mon, 07 Feb 2011 14:05:25 -0500
Message-ID: <4D504275.5040103@2ndquadrant.com> (raw)
In-Reply-To: <4D4F6109.1090209@postnewspapers.com.au>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, craig@postnewspapers.com.au, crucialfelix@gmail.com
Subject: Re: Really really slow select count(*)
In-Reply-To: <4D504275.5040103@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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