From: Craig Ringer <craig@postnewspapers.com.au>
To: 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 11:03:37 +0800
Message-ID: <4D4F6109.1090209@postnewspapers.com.au> (raw)
In-Reply-To: <AANLkTimnFc-hKG+PFEfwBPLAoK6q9kx=nc_4Fr++C_QB@mail.gmail.com>
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>
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
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: craig@postnewspapers.com.au, crucialfelix@gmail.com
Subject: Re: Really really slow select count(*)
In-Reply-To: <4D4F6109.1090209@postnewspapers.com.au>
* 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