Обсуждение: Debian Bug#506196: postgresql: consume too much power when idle (>10 wakeups/second)

Hello PostgreSQL developers,

first, happy new year to you all!

I recently got this bug report through Debian. I can confirm that on
an otherwise idle system, and with no connections to PostgreSQL at
all, I get

     8,5% ( 11,5)          postgres : schedule_hrtimeout_range (hrtimer_wak=
eup)=20

in powertop. Now, 11 wakeups per minute is not dramatic, and with
PostgreSQL being a server application, perfect power management is
certainly the least concern for you.=20

However, it would be interesting to know whether those wakeups are
intended and necessary, or if they would be easy or hard to fix. (I
have no problem with closing the bug as wontfix, but I'd like to give
a rationale).

Thank you!

Martin


----- Forwarded message from Xavier Bestel <xavier.bestel@free.fr> -----

Subject: Bug#506196: postgresql: consume too much power when idle (>10 wake=
ups/second)
Reply-To: Xavier Bestel <xavier.bestel@free.fr>, 506196@bugs.debian.org
From: Xavier Bestel <xavier.bestel@free.fr>
To: Debian Bug Tracking System <submit@bugs.debian.org>
Date: Wed, 19 Nov 2008 10:12:05 +0100

Package: postgresql
Version: 8.3.5-1
Severity: minor


Hi,

postgresql is installed on my machine because it was pulled by another pack=
age
(can't remember which one). It doesn't do anything special, but it still do=
es more
then 10 wakeups/second on that system, uselessly. If idle, it shouldn't eve=
n appear
in powertop's profile.

Thanks,

    Xav

----- End forwarded message -----

--=20
Martin Pitt                        | http://www.piware.de
Ubuntu Developer (www.ubuntu.com)  | Debian Developer  (www.debian.org)

Xavier Bestel wrote:

> postgresql is installed on my machine because it was pulled by another package
> (can't remember which one). It doesn't do anything special, but it still does more
> then 10 wakeups/second on that system, uselessly. If idle, it shouldn't even appear
> in powertop's profile.

How many databases are there?  I'm wondering if the wakeups are caused
by autovacuum.  It could also be bgwriter.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Hi Alvaro,

Alvaro Herrera [2009-01-04 18:05 -0300]:
> How many databases are there?

It's a freshly created instance, thus just "template[01]" and
"postgres". Standard 8.3 autovacuum is enabled.

Thanks,

Martin

--
Martin Pitt                        | http://www.piware.de
Ubuntu Developer (www.ubuntu.com)  | Debian Developer  (www.debian.org)

Martin Pitt wrote:
> Hi Alvaro,
>
> Alvaro Herrera [2009-01-04 18:05 -0300]:
> > How many databases are there?
>
> It's a freshly created instance, thus just "template[01]" and
> "postgres". Standard 8.3 autovacuum is enabled.

I think we must blame bgwriter then.  I had a look at it some time ago
to how hard would it be to remove the useless wakeups and concluded that
it wasn't really worth the trouble.  Not that I looked that hard;
probably someone smarter (and/or with more time) can find ways to
optimize it.  See BgWriterNap in

http://git.postgresql.org/?p=postgresql.git;a=blob;f=src/backend/postmaster/bgwriter.c

(Note that with the current code, you can put it to rest for 10 seconds
at a time by setting bgwriter_lru_maxpages to 0 in postgresql.conf)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> I think we must blame bgwriter then.  I had a look at it some time ago
> to how hard would it be to remove the useless wakeups and concluded that
> it wasn't really worth the trouble.

I've got similar complaints in the RH/Fedora bugzilla.  It's hard to fix
in a portable way because of the lack of consistent select/poll
semantics across various platforms.  See my previous notes about the
lack of consistent EINTR behavior and whether we should really be using
SA_RESTART.

The short answer is "don't hold your breath".  It'd be nice to have a
plan for improving things though.

            regards, tom lane

On Sun, 2009-01-04 at 21:52 +0100, Martin Pitt wrote:

> 11 wakeups per minute is not dramatic, and with
> PostgreSQL being a server application, perfect power management is
> certainly the least concern for you.

Is this 11 per minute, or 11 per second?

> ----- Forwarded message from Xavier Bestel <xavier.bestel@free.fr> -----
>
> Subject: Bug#506196: postgresql: consume too much power when idle (>10 wakeups/second)
> Reply-To: Xavier Bestel <xavier.bestel@free.fr>, 506196@bugs.debian.org
> From: Xavier Bestel <xavier.bestel@free.fr>
> To: Debian Bug Tracking System <submit@bugs.debian.org>
> Date: Wed, 19 Nov 2008 10:12:05 +0100
>
> Package: postgresql
> Version: 8.3.5-1
> Severity: minor

> postgresql is installed on my machine because it was pulled by another package
> (can't remember which one). It doesn't do anything special, but it still does more
> then 10 wakeups/second on that system, uselessly. If idle, it shouldn't even appear
> in powertop's profile.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

Hi Simon,

Simon Riggs [2009-01-05 12:13 +0000]:
> Seems consistent with wal_writer_delay = 200ms and bgwriter_delay =
> 200ms, plus some other minor noise.

Ah, thanks.

> So its not a "bug" and won't get "fixed".

Right, it's not a bug in the sense of "does not behave as intended".
Purely a wishlist thingy.

Thanks for your investigations,

Martin
--
Martin Pitt                        | http://www.piware.de
Ubuntu Developer (www.ubuntu.com)  | Debian Developer  (www.debian.org)

On Mon, 2009-01-05 at 12:21 +0100, Martin Pitt wrote:
> Hi Simon,
>
> Simon Riggs [2009-01-05 10:57 +0000]:
> > Is this 11 per minute, or 11 per second?
>
> Per second.

Seems consistent with wal_writer_delay = 200ms and bgwriter_delay =
200ms, plus some other minor noise.

So its not a "bug" and won't get "fixed".

Seems possible to put some progressive delays in there, but we'd need to
discuss how things could snap back into action when required.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

Hi Simon,

Simon Riggs [2009-01-05 10:57 +0000]:
> Is this 11 per minute, or 11 per second?

Per second.

Martin
--
Martin Pitt                        | http://www.piware.de
Ubuntu Developer (www.ubuntu.com)  | Debian Developer  (www.debian.org)

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > I think we must blame bgwriter then.  I had a look at it some time ago
> > to how hard would it be to remove the useless wakeups and concluded that
> > it wasn't really worth the trouble.
>
> I've got similar complaints in the RH/Fedora bugzilla.  It's hard to fix
> in a portable way because of the lack of consistent select/poll
> semantics across various platforms.  See my previous notes about the
> lack of consistent EINTR behavior and whether we should really be using
> SA_RESTART.
>
> The short answer is "don't hold your breath".  It'd be nice to have a
> plan for improving things though.

Is this a TODO?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +