From:
Greg Smith <greg(at)2ndquadrant(dot)com>
To:
Bruce Momjian <bruce(at)momjian(dot)us>
Cc:
Dimitri Fontaine <dfontaine(at)hi-media(dot)com>,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,
Greg Stark <gsstark(at)mit(dot)edu>, Josh Berkus <josh(at)agliodbs(dot)com>,
pgsql-hackers(at)postgresql(dot)org
Subject:
Re: Hot Standby query cancellation and Streaming Replication
integration
Date:
2010-02-27 02:43:58
Message-ID:
4B8886EE.8010407@2ndquadrant.com (view raw or flat )
Thread:
2010-02-26 08:33:35 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-26 12:10:07 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-26 18:53:16 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 19:01:30 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-26 19:06:51 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-02-26 19:16:40 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 20:02:08 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-02-26 20:22:56 from "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
2010-02-26 20:04:23 from Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
2010-02-26 20:21:05 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 20:10:11 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-26 20:25:33 from Aidan Van Dyk <aidan(at)highrise(dot)ca>
2010-02-26 20:30:01 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 21:06:10 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-02-26 21:11:32 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-26 21:19:39 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 21:22:47 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-26 21:44:20 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-27 04:02:24 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-28 21:58:51 from Simon Riggs <simon(at)2ndQuadrant(dot)com>
2010-02-27 06:53:24 from Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
2010-02-26 21:39:12 from Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
2010-02-26 21:48:38 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-26 22:11:28 from Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
2010-02-26 22:20:37 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-27 09:07:32 from Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
2010-02-27 09:33:34 from Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
2010-02-26 23:56:55 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 00:36:57 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-27 02:43:58 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 03:40:57 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-27 04:38:38 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 04:53:48 from Aidan Van Dyk <aidan(at)highrise(dot)ca>
2010-02-27 05:08:35 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 20:31:06 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-02-27 22:09:59 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 22:26:22 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-02-28 02:14:28 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-28 05:28:17 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-28 06:01:49 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-28 06:18:00 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 00:43:48 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-27 01:53:13 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 02:30:56 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-27 03:11:49 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 22:28:49 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-02-27 23:49:09 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 03:59:41 from "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
2010-02-27 04:31:12 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 14:34:08 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-26 23:33:57 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 00:21:11 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-02-27 06:59:10 from Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
2010-02-27 14:56:51 from Tatsuo Ishii <ishii(at)postgresql(dot)org>
2010-02-28 01:00:52 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-28 06:07:20 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-28 13:54:48 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-28 15:56:28 from Joachim Wieland <joe(at)mcknight(dot)de>
2010-02-28 19:47:38 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-02-28 20:48:51 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-28 22:38:28 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-03-01 03:00:09 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-03-01 17:50:52 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-03-01 18:26:09 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-03-01 18:46:29 from Greg Stark <gsstark(at)mit(dot)edu>
2010-03-01 19:21:48 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-03-01 19:32:24 from Greg Stark <gsstark(at)mit(dot)edu>
2010-03-01 19:35:37 from Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>
2010-03-01 19:43:13 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-03-01 20:04:41 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-03-02 01:34:53 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-03-02 20:57:39 from Simon Riggs <simon(at)2ndQuadrant(dot)com>
2010-03-02 20:58:03 from Simon Riggs <simon(at)2ndQuadrant(dot)com>
2010-03-02 21:36:52 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-03-02 23:39:10 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-03-01 03:12:22 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-03-01 22:32:01 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-03-01 22:52:50 from "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
2010-03-02 00:46:22 from Greg Stark <stark(at)mit(dot)edu>
2010-03-02 01:04:11 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-03-02 02:08:27 from Aidan Van Dyk <aidan(at)highrise(dot)ca>
2010-03-02 01:50:28 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-03-02 06:21:10 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-03-02 04:56:45 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-28 20:54:52 from Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
2010-02-28 21:42:00 from Joachim Wieland <joe(at)mcknight(dot)de>
2010-02-28 20:21:23 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-03-02 04:54:04 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-03-02 05:50:08 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-03-02 18:30:15 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-03-02 18:34:33 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-03-02 20:11:00 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-03-02 23:44:16 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-03-10 06:29:19 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-03-10 11:38:12 from Greg Stark <gsstark(at)mit(dot)edu>
2010-03-10 18:59:50 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-03-11 00:51:29 from Fujii Masao <masao(dot)fujii(at)gmail(dot)com>
2010-03-11 01:39:37 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-03-11 05:58:35 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-03-02 19:03:14 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-03-02 21:00:24 from Simon Riggs <simon(at)2ndQuadrant(dot)com>
2010-02-26 14:10:26 from Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
2010-02-26 14:24:30 from Richard Huxton <dev(at)archonet(dot)com>
2010-02-26 18:47:09 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-27 00:11:36 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 00:54:57 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-27 02:52:12 from Greg Smith <greg(at)2ndquadrant(dot)com>
2010-02-27 02:56:01 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-26 14:17:23 from Richard Huxton <dev(at)archonet(dot)com>
2010-02-26 14:45:07 from Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
2010-02-26 14:57:16 from Richard Huxton <dev(at)archonet(dot)com>
2010-02-26 15:21:03 from Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
2010-02-26 17:45:33 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-26 19:02:12 from Josh Berkus <josh(at)agliodbs(dot)com>
2010-02-26 16:43:24 from Richard Huxton <dev(at)archonet(dot)com>
2010-02-26 17:46:15 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-28 22:15:18 from Simon Riggs <simon(at)2ndQuadrant(dot)com>
Lists:
pgsql-hackers
Bruce Momjian wrote:
> Well, I think the choice is either you delay vacuum on the master for 8
> hours or pile up 8 hours of WAL files on the slave, and delay
> application, and make recovery much slower. It is not clear to me which
> option a user would prefer because the bloat on the master might be
> permanent.
>
But if you're running the 8 hour report on the master right now, aren't
you already exposed to a similar pile of bloat issues while it's going?
If I have the choice between "sometimes queries will get canceled" vs.
"sometimes the master will experience the same long-running transaction
bloat issues as in earlier versions even if the query runs on the
standby", I feel like leaning toward the latter at least leads to a
problem people are used to.
This falls into the principle of least astonishment category to me.
Testing the final design for how transactions get canceled here led me
to some really unexpected situations, and the downside for a mistake is
"your query is lost". Had I instead discovered that sometimes
long-running transactions on the standby can ripple back to cause a
maintenance slowdown on the master, that's not great. But it would not
have been so surprising, and it won't result in lost query results.
I think people will expect that their queries cancel because of things
like DDL changes. And the existing knobs allow inserting some slack for
things like locks taking a little bit of time to acquire sometimes.
What I don't think people will see coming is that a routine update on an
unrelated table is going to kill a query they might have been waiting
hours for the result of, just because that update crossed an autovacuum
threshold for the other table and introduced a dead row cleanup.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us
In response to
Responses
pgsql-hackers by date
Next :From: Michael GlaesemannDate: 2010-02-27 02:44:09Subject : Re: Correcting Error message Previous :From : Greg StarkDate : 2010-02-27 02:30:56Subject : Re: Hot Standby query cancellation and Streaming Replication integration
