From:
Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To:
Christopher Browne <cbbrowne(at)gmail(dot)com>
Cc:
Andres Freund <andres(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>,Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject:
Re: autovacuum not prioritising for-wraparound tables
Date:
2013-01-31 19:36:47
Message-ID:
20130131193647.GB4883@alvh.no-ip.org (view raw or flat )
Thread:
2013-01-24 21:57:15 from Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
2013-01-24 22:22:50 from Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>
2013-01-24 23:25:47 from Christopher Browne <cbbrowne(at)gmail(dot)com>
2013-01-24 23:48:26 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2013-01-25 15:29:15 from Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
2013-01-25 15:49:25 from Peter Eisentraut <peter_e(at)gmx(dot)net>
2013-01-25 15:59:56 from Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
2013-01-25 16:44:49 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2013-01-25 16:51:33 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2013-01-25 17:00:51 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-01-25 17:56:46 from Christopher Browne <cbbrowne(at)gmail(dot)com>
2013-01-30 21:05:52 from Jim Nasby <jim(at)nasby(dot)net>
2013-01-30 21:28:36 from Kevin Grittner <kgrittn(at)ymail(dot)com>
2013-01-30 22:44:34 from Jim Nasby <jim(at)nasby(dot)net>
2013-01-30 22:37:33 from Christopher Browne <cbbrowne(at)gmail(dot)com>
2013-01-30 22:50:22 from Jim Nasby <jim(at)nasby(dot)net>
2013-01-31 19:36:47 from Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
2013-01-31 19:40:14 from Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
2013-01-31 19:54:31 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-01-31 20:18:24 from Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
2013-01-31 20:28:58 from Christopher Browne <cbbrowne(at)gmail(dot)com>
2013-02-01 21:59:52 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-02-01 22:43:04 from Christopher Browne <cbbrowne(at)gmail(dot)com>
2013-02-01 23:08:21 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2013-03-21 22:15:47 from Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
2013-02-02 13:41:35 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-02-02 16:25:01 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-02-02 18:49:13 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-02-02 19:54:10 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-02-02 21:03:54 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-02-03 02:08:34 from Christopher Browne <cbbrowne(at)gmail(dot)com>
2013-02-03 02:47:34 from Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
2013-02-28 17:21:58 from Jim Nasby <jim(at)nasby(dot)net>
2013-01-31 19:55:06 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-01-31 19:58:13 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2013-01-31 20:07:07 from Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
2013-01-25 17:19:25 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-01-25 17:35:25 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-01-25 17:52:46 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2013-01-25 18:01:16 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-01-25 18:02:19 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-01-25 18:17:20 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2013-01-25 19:22:50 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-01-27 19:33:18 from Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
2013-01-27 09:17:27 from Simon Riggs <simon(at)2ndQuadrant(dot)com>
2013-01-27 17:11:53 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-01-27 21:29:56 from Simon Riggs <simon(at)2ndQuadrant(dot)com>
2013-01-27 19:17:24 from Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
2013-01-29 03:03:19 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-01-25 00:18:32 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-01-28 13:11:12 from Josh Berkus <josh(at)agliodbs(dot)com>
2013-01-28 14:10:24 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-01-28 16:15:29 from Kevin Grittner <kgrittn(at)ymail(dot)com>
2013-01-28 16:21:15 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-02-02 04:48:48 from Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
2013-01-29 05:09:52 from Josh Berkus <josh(at)agliodbs(dot)com>
2013-01-30 09:48:40 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-01-30 13:39:29 from Kevin Grittner <kgrittn(at)ymail(dot)com>
2013-01-30 13:58:24 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-01-30 14:55:21 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-01-30 18:21:07 from Kevin Grittner <kgrittn(at)ymail(dot)com>
2013-01-30 18:26:06 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-01-30 18:26:37 from Kevin Grittner <kgrittn(at)ymail(dot)com>
2013-02-01 15:17:25 from Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
2013-02-01 22:05:46 from Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
2013-02-01 22:34:18 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-02-01 23:09:34 from Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
2013-02-02 13:25:11 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-02-03 17:25:37 from Kevin Grittner <kgrittn(at)ymail(dot)com>
2013-02-03 22:58:43 from Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
2013-02-04 21:59:57 from Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
2013-02-04 22:16:02 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2013-02-02 15:43:43 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-02-03 01:40:04 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-02-03 01:41:42 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-02-03 16:17:42 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2013-02-03 16:41:20 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-02-03 18:26:25 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2013-02-03 21:08:35 from Andres Freund <andres(at)2ndquadrant(dot)com>
2013-01-30 15:58:07 from Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
2013-01-30 18:31:50 from Kevin Grittner <kgrittn(at)ymail(dot)com>
2013-01-30 15:35:46 from Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
2013-01-31 03:41:38 from Josh Berkus <josh(at)agliodbs(dot)com>
2013-01-29 03:35:22 from Robert Haas <robertmhaas(at)gmail(dot)com>
2013-01-27 20:47:20 from Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Lists:
pgsql-hackers
Christopher Browne escribió:
> On Fri, Jan 25, 2013 at 12:00 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> I'd be inclined to do something a bit more sophisticated than just
> age(relfrozenxid) for wraparound; I'd be inclined to kick off large tables'
> wraparound vacuums earlier than those for smaller tables.
>
> With a little bit of noodling around, here's a thought for a joint function
> that I *think* has reasonably common scales:
>
> f(deadtuples, relpages, age) =
> deadtuples/relpages + e ^ (age*ln(relpages)/2^32)
Okay, here's a patch along these lines. I haven't considered Jim's
suggestion downthread about discounting dead tuples from relpages; maybe
we can do that by subtracting the pages attributed to dead ones,
estimating via tuple density (reltuples/relpages). But that's no my
main concern here.
Instead, what I propose (and is not really in the patch), as a
backpatchable item, is an approach in which the functions to compute
each rel's Browne strength and sort are hooks. Normal behavior is not
to sort at all, as currently, and sites that have a problem with the
current random order can install a custom module that provide hooks to
change ordering as they see fit. So behavior won't change for people
who have no problem today.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
In response to
Responses
pgsql-hackers by date
Next :From: Alvaro HerreraDate: 2013-01-31 19:40:14Subject : Re: autovacuum not prioritising for-wraparound tablesPrevious :From : Simon RiggsDate : 2013-01-31 19:33:53Subject : Re: pgsql: Fast promote mode skips checkpoint at end of recovery.
