Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

> > I also find others's ideas woth considering -- WAL-logging the stats files, > type-specific stats files, etc. -- but I'm afraid those ideas would only be > employed in a new major release, not in released versions. I'm asking for a > remedy for a user (and potential users) who use older

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Thu, Nov 24, 2016 at 12:41 AM, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-ow...@postgresql.org >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane >> Robert Haas writes: >> > I agree. However, in many cases, the

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > Maybe a workable compromise would be to leave the file present, and have > the stats collector re-write it every (say) five minutes. Then I'd be okay > with having an immediate shutdown

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > Robert Haas writes: > > I agree. However, in many cases, the major cost of a fast shutdown is > > getting the dirty data already in the operating system buffers

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Tomas Vondra writes: > This would also reduce the amount of data that we need to write to WAL, > although I'm not sure the amount is actually a problem. I've seen > instances with ~500MB stat files, but those were instances with hundreds > of databases, each with

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On 11/23/2016 12:24 PM, Michael Paquier wrote: On Wed, Nov 23, 2016 at 6:15 PM, Magnus Hagander wrote: There's also the consideration of what to do with stats *on the standby*. If we WAL log the stats file, then when it replays onthe standby, the stats there will be

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Wed, Nov 23, 2016 at 6:15 PM, Magnus Hagander wrote: > There's also the consideration of what to do with stats *on the standby*. If > we WAL log the stats file, then when it replays onthe standby, the stats > there will be overwritten. And stats like number of index vs seq

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Nov 22, 2016 at 10:26 PM, Alvaro Herrera wrote: > Andres Freund wrote: > > On 2016-11-22 16:15:58 -0500, Tom Lane wrote: > > > > Maybe a workable compromise would be to leave the file present, and > have > > > the stats collector re-write it every (say) five

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Andres Freund wrote: > On 2016-11-22 16:15:58 -0500, Tom Lane wrote: > > Maybe a workable compromise would be to leave the file present, and have > > the stats collector re-write it every (say) five minutes. Then I'd be > > okay with having an immediate shutdown skip writing the file; you'd be >

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On 2016-11-22 16:15:58 -0500, Tom Lane wrote: > Alvaro Herrera writes: > > Well, the problem is that the stats data is not on disk while the system > > is in operation, as far as I recall -- it's only in the collector's > > local memory. On shutdown we tell it to write

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Nov 22, 2016 at 4:15 PM, Tom Lane wrote: > Alvaro Herrera writes: >> Well, the problem is that the stats data is not on disk while the system >> is in operation, as far as I recall -- it's only in the collector's >> local memory. On shutdown

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Alvaro Herrera writes: > Well, the problem is that the stats data is not on disk while the system > is in operation, as far as I recall -- it's only in the collector's > local memory. On shutdown we tell it to write it down to a file, and on > startup we tell it to read

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Nov 22, 2016 at 3:52 PM, Tom Lane wrote: > Robert Haas writes: >> I agree. However, in many cases, the major cost of a fast shutdown is >> getting the dirty data already in the operating system buffers down to >> disk, not in writing out

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Andres Freund writes: > But I'm a bit confused too - does this make any sort of difference? > Because the startup path for crash recovery is like this: > pgstat_reset_all(); > so it seems quite inconsequential whether we write out pgstat, because > we're going to

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Hi, On 2016-11-22 15:49:27 -0500, Robert Haas wrote: > I think you are almost right. When the server is running, there are > files in pg_stat_tmp but not pg_stat; when it is shut down, there are > files in pg_stat but not pg_stat_tmp. Of course the data can never be > ONLY in the collector's

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Robert Haas writes: > I agree. However, in many cases, the major cost of a fast shutdown is > getting the dirty data already in the operating system buffers down to > disk, not in writing out shared_buffers itself. The latter is > probably a single-digit number of

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Nov 22, 2016 at 3:34 PM, Alvaro Herrera wrote: >> OK, that's possible, but I'm not sure. I think there are two separate >> issues here. One is whether we should nuke the stats file on >> recovery, and the other is whether we should force a final write of >> the

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Robert Haas wrote: > On Tue, Nov 22, 2016 at 1:37 PM, Alvaro Herrera > wrote: > >> > Yes, I am, and I disagree with you. The current decision on this point > >> > was made ages ago, before autovacuum even existed let alone relied on > >> > the stats for proper

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Nov 22, 2016 at 1:37 PM, Alvaro Herrera wrote: >> > Yes, I am, and I disagree with you. The current decision on this point >> > was made ages ago, before autovacuum even existed let alone relied on >> > the stats for proper functioning. The tradeoff you're

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Robert Haas wrote: > On Tue, Nov 22, 2016 at 12:54 PM, Tom Lane wrote: > > Robert Haas writes: > >> But that's not what is at issue here. The issue is whether, when > >> asked to exit immediately, all processes should exit immediately, or > >> whether

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Nov 22, 2016 at 12:54 PM, Tom Lane wrote: > Robert Haas writes: >> But that's not what is at issue here. The issue is whether, when >> asked to exit immediately, all processes should exit immediately, or >> whether it would be better for all

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Robert Haas writes: > But that's not what is at issue here. The issue is whether, when > asked to exit immediately, all processes should exit immediately, or > whether it would be better for all processes except one to exit > immediately and the last one exit

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Nov 22, 2016 at 10:59 AM, Tom Lane wrote: > It's already the case that the pgstats code writes the stats data under a > temporary file name and then renames it into place atomically. So the > prospects for corrupt data are not large, and I do not think that the >

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

"Tsunakawa, Takayuki" writes: > From: Tom Lane [mailto:t...@sss.pgh.pa.us] >> The point I was trying to make is that I think the forced-removal behavior >> is not desirable, and therefore committing a patch that makes it be graven >> in stone is not desirable

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Nov 22, 2016 at 12:26 PM, Tsunakawa, Takayuki wrote: > From: Tom Lane [mailto:t...@sss.pgh.pa.us] >> The point I was trying to make is that I think the forced-removal behavior >> is not desirable, and therefore committing a patch that makes it be graven >>

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: Tom Lane [mailto:t...@sss.pgh.pa.us] > The point I was trying to make is that I think the forced-removal behavior > is not desirable, and therefore committing a patch that makes it be graven > in stone is not desirable either. I totally agree that we should pursue the direction for escaping

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: Robert Haas [mailto:robertmh...@gmail.com] > So there are two questions here: > > 1. Should we try to avoid having the stats collector write a stats file > during an immediate shutdown? The file will be removed anyway during crash > recovery, so writing it is pointless. I think you are

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Robert Haas writes: > 1. Should we try to avoid having the stats collector write a stats > file during an immediate shutdown? The file will be removed anyway > during crash recovery, so writing it is pointless. The point I was trying to make is that I think the

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Sun, Nov 20, 2016 at 10:20 PM, Tsunakawa, Takayuki wrote: > The reasons why I proposed this patch are: > > * It happened in a highly mission-critical production system of a customer > who uses 9.2. > > * 9.4's solution is not perfect, because it wastes 5

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: Robert Haas [mailto:robertmh...@gmail.com] > On Fri, Nov 18, 2016 at 4:12 PM, Tom Lane wrote: > > Alvaro Herrera writes: > >> Tom Lane wrote: > >>> IMO it's not, and closer analysis says that this patch series is an > >>> attempt to solve

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Alvaro Herrera writes: > Robert Haas wrote: >> IIUC, MauMau = Tsunakawa Takayuki. > Right, > https://www.postgresql.org/message-id/964413269E3A41409EA53EE0D813E48C@tunaPC Ah! I'd forgotten. regards, tom lane -- Sent via pgsql-hackers

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Robert Haas wrote: > On Fri, Nov 18, 2016 at 4:12 PM, Tom Lane wrote: > > Alvaro Herrera writes: > >> Tom Lane wrote: > >>> IMO it's not, and closer analysis says that this patch series is an > >>> attempt to solve something we already fixed, better,

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Fri, Nov 18, 2016 at 4:12 PM, Tom Lane wrote: > Alvaro Herrera writes: >> Tom Lane wrote: >>> IMO it's not, and closer analysis says that this patch series is an >>> attempt to solve something we already fixed, better, in 9.4. > >> ... by the same

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Alvaro Herrera writes: > Tom Lane wrote: >> IMO it's not, and closer analysis says that this patch series is an >> attempt to solve something we already fixed, better, in 9.4. > ... by the same patch submitter. [ confused ] The commit log credits 82233ce7e to MauMau

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Tom Lane wrote: > Peter Eisentraut writes: > > On 11/14/16 4:38 AM, Ashutosh Bapat wrote: > >> The patch 02_close_listen... closes the listen sockets > >> explicitly when it's known that postmaster is going to stop all the > >> children and then die. I have tried

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On 11/18/16 12:00 PM, Tom Lane wrote: > My feeling is that 82233ce7e has obsoleted all of the proposals made so > far in this thread, and that we should reject them all. Yes, it seems that very similar concerns were already addressed there. -- Peter Eisentraut

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Peter Eisentraut writes: > On 11/14/16 4:38 AM, Ashutosh Bapat wrote: >> The patch 02_close_listen... closes the listen sockets >> explicitly when it's known that postmaster is going to stop all the >> children and then die. I have tried to see, if there's a

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

I wrote: > Peter Eisentraut writes: >> ISTM that this would change the "immediate shutdown" to not save stats >> files anymore. So far, all the shutdown modes are equivalent in terms >> of how they preserve data and system state. They differ only in when >> the

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On 11/14/16 4:38 AM, Ashutosh Bapat wrote: > The patch 02_close_listen... closes the listen sockets > explicitly when it's known that postmaster is going to stop all the > children and then die. I have tried to see, if there's a possibility > that it closes the listen sockets but do not actually

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Peter Eisentraut writes: > ISTM that this would change the "immediate shutdown" to not save stats > files anymore. So far, all the shutdown modes are equivalent in terms > of how they preserve data and system state. They differ only in when > the hard work

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On 9/27/16 11:07 PM, Tsunakawa, Takayuki wrote: >> From: pgsql-hackers-ow...@postgresql.org >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane >> Allowing SIGQUIT to prompt fast shutdown of the stats collector seems sane, >> though. Try to make sure it doesn't leave

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Mon, Nov 14, 2016 at 5:16 AM, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-ow...@postgresql.org >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ashutosh Bapat >> I have changed some comments around this block. See attached patch. >> Let me know

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ashutosh Bapat > I have changed some comments around this block. See attached patch. > Let me know if that looks good. Thanks, it looks good. Regards Takayuki Tsunakawa -- Sent via

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Mon, Nov 7, 2016 at 10:14 AM, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-ow...@postgresql.org >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ashutosh Bapat >> I am not sure if following condition is a good idea in ServerLoop() >> 1650

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ashutosh Bapat > I am not sure if following condition is a good idea in ServerLoop() > 1650 if (pmState == PM_WAIT_DEAD_END || ClosedSockets) > > There are no sockets to listen on, so

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Oct 4, 2016 at 2:35 PM, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-ow...@postgresql.org >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > FWIW, I'm pretty much -1 on messing with the timing of the socket close >> actions. I broke

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: Ashutosh Bapat [mailto:ashutosh.ba...@enterprisedb.com] > Ok. In that case, I think we shouldn't even call PG_SETMASK() similar to > pgarch_exit(). Attached patch removes PG_SETMASK(). Let me know if it looks > good. It looks good. Thanks. Regards Takayuki Tsunakawa -- Sent via

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Thu, Oct 27, 2016 at 7:29 AM, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-ow...@postgresql.org >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ashutosh Bapat >> In pgstat_quickdie(), I think a call to sigaddset(, SIGQUIT) is >> missing before

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ashutosh Bapat > In pgstat_quickdie(), I think a call to sigaddset(, SIGQUIT) is > missing before PG_SETMASK(). Although there are some SIGQUIT handlers which > do not have that call. But I guess,

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Robert Haas writes: > On Wed, Oct 26, 2016 at 7:12 AM, Ashutosh Bapat > wrote: >> Also, many other SIGQUIT handlers like bgworker_quickdie() call >> on_exit_reset() >> followed by exit(2) instead of just exit(1) in pgstat_quickdie(). Why

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Wed, Oct 26, 2016 at 7:12 AM, Ashutosh Bapat wrote: > Also, many other SIGQUIT handlers like bgworker_quickdie() call > on_exit_reset() > followed by exit(2) instead of just exit(1) in pgstat_quickdie(). Why is this > difference? Well, for that, you'd need to

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Wed, Sep 28, 2016 at 8:37 AM, Tsunakawa, Takayuki wrote: >> From: pgsql-hackers-ow...@postgresql.org >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane >> Allowing SIGQUIT to prompt fast shutdown of the stats collector seems sane, >> though.

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: Robert Haas [mailto:robertmh...@gmail.com] > I have no opinion on this patch, because I haven't reviewed it, but note > recent commit 3b90e38c5d592ea8ec8236287dd5c749fc041728, which appears to > be semi-related. Thank you for interesting information. Maybe Tom-san experienced some trouble

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Oct 4, 2016 at 5:05 AM, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-ow...@postgresql.org >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > FWIW, I'm pretty much -1 on messing with the timing of the socket close >> actions. I broke

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane FWIW, I'm pretty much -1 on messing with the timing of the socket close > actions. I broke that once within recent memory, so maybe I'm gun-shy, > but I think that the odds of unpleasant

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

> From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > Allowing SIGQUIT to prompt fast shutdown of the stats collector seems sane, > though. Try to make sure it doesn't leave partly-written stats files > behind. The attached patch based

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

"Tsunakawa, Takayuki" writes: >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas >> I think that we shouldn't start changing things based on guesses about what >> the problem is, even if they're fairly smart guesses. The thing to do would >>

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

> From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas > On Tue, Sep 20, 2016 at 2:20 AM, Tsunakawa, Takayuki > wrote: > > There's no apparent evidence to indicate the cause, but I could guess > > a few

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Sep 20, 2016 at 2:20 AM, Tsunakawa, Takayuki wrote: > There's no apparent evidence to indicate the cause, but I could guess a few > reasons. What do you think these are correct and should fix PostgreSQL? (I > think so) I think that we shouldn't start

[HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Hello, Please let me ask you about possible causes of a certain problem, slow shutdown of postmaster when a backend crashes, and whether to fix PostgreSQL. Our customer is using 64-bit PostgreSQL 9.2.8 on RHEL 6.4. Yes, the PostgreSQL version is rather old but there's no relevant bug fix in