Message-ID: <4D4F6109.1090209@postnewspapers.com.au>
Date: Mon, 07 Feb 2011 11:03:37 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9) Gecko/20100423 Thunderbird/3.0.4
MIME-Version: 1.0
To: felix <crucialfelix@gmail.com>
CC: "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>
In-Reply-To: <AANLkTimnFc-hKG+PFEfwBPLAoK6q9kx=nc_4Fr++C_QB@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

On 07/02/11 09:52, felix wrote:

> So is it normal for postgres to report that it failed to shut down,
> operate for an hour and then go ahead and restart itself ?

That's pretty wacky. Did you shut it down via  pg_ctl or using an init
script / "service" command in your OS?

It shouldn't matter, but it'd be good to know. If the problem is with an
init script, then knowing which OS and version you're on would help. If
it was with psql directly, that's something that can be looked into.

> this was changing one configuration parameter. something I was advised
> to do, read about quite a bit, tested on my development server (mac) and
> then proceeded to do at 6 am on Sunday morning, our slowest time.

System V shared memory is awful - but it's really the only reasonable
alternative for a multi-process (rather than multi-threaded) server.

PostgreSQL could use mmap()ed temp files, but that'd add additional
overheads and they'd potentially get flushed from main memory unless the
memory was mlock()ed. As mlock() has similar limits and configuration
methods to system V shared memory, you get back to the same problem in a
slightly different form.

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. OTOH, many people don't check the logs, so they'd think their
new setting had taken effect and it hadn't - you've traded one usability
problem for another. Even if Pg issued WARNING messages to each client
that connected, lots of (non-psql) clients don't display them, so many
users would never know.

Do you have a suggestion about how to do this better? The current
approach is known to be rather unlovely, but nobody's come up with a
better one that works reasonably and doesn't trample on other System V
shared memory users that may exist on the system.

> so does the above mean that I don't have to restart the entire server,
> just postgres ?  I assumed that changing kernel settings means rebooting
> the server.

Nope. sysctl settings like shmmax may be changed on the fly.

-- 
System & Network Administrator
POST Newspapers