Re: Add a log message on recovery startup before syncing datadir

Hi,

Am Mittwoch, den 07.10.2020, 21:11 +1300 schrieb Thomas Munro:
> On Wed, Oct 7, 2020 at 8:58 PM Michael Banck <michael(dot)banck(at)credativ(dot)de> wrote:
> > we had a customer incident recently where they needed to do a PITR.
> > Their data directory is on a NetApp NFS and they have several hundred
> > databases in their instance. The startup sync (i.e. before the message
> > "starting archive recovery" appears) took 20 minutes and during the
>
> Nice data point.

In the thread you pointed to below, Robert also mentions "tens of
minutes".

> > first try[1] they were wondering what's going on because there is just
> > one log message ("database system was interrupted; last known up at
> > ...") and the postmaster process is in state 'D'. Attaching strace
> > revealed that it was syncing files and due to the NFS performance that
> > took a long time.
>
> No objection to adding a message, but see also this other thread,
> about potential ways to get rid of that sync completely, or at least
> the phase where you have to open all the files one by one:
>
> https://www.postgresql.org/message-id/flat/CAEET0ZHGnbXmi8yF3ywsDZvb3m9CbdsGZgfTXscQ6agcbzcZAw%40mail.gmail.com

Thanks, I've re-read that now. As a summary, Tom said that the syncs
can't go away on the relations files/tablespaces. Robert suggested some
periodic status update that we're still doing work. You proposed using
syncfs. There was a discussion about ommitting some files/directories,
but I think those don't matter much for people who see extreme delays
here because those are very likely due to relation files.

I had a quick look yesterday and it seems to me the code that actually
does the syncing has no notion of how many files we synced already or
will sync in total, and adding that along with book-keeping would
complicate the calling pattern considerably. So I don't think a status
update like "finished syncing 10000 out of 23054 files" is going to (i)
be back-patchable or (ii) would not be even slower than now due to
having to figure out how much work is to be done first.

So I would suggest to go with a minimal message before starting to sync
as proposed, which I think should be back-patchable, and try to (also)
make it faster (e.g. via syncfs) for v14.

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael(dot)banck(at)credativ(dot)de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz