Re: Freeze avoidance of very large table.

From: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, Fujii Masao <masao(dot)fujii(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Petr Jelinek <petr(at)2ndquadrant(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>, Greg Stark <stark(at)mit(dot)edu>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Freeze avoidance of very large table.
Date: 2015-11-22 16:16:34
Message-ID: CAD21AoBsupFYE2oHxa5fPHV3d3Co=q54y1Q_iyZvs65kg92mvA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On Sat, Nov 21, 2015 at 6:50 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Thu, Nov 19, 2015 at 6:44 AM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>> On Thu, Nov 19, 2015 at 5:54 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>> On Wed, Nov 18, 2015 at 11:18 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>>>
>>>> I get an error when running pg_upgrade from 9.4 to 9.6-this
>>>>
>>>> error while copying relation "mediawiki.archive"
>>>> ("/tmp/data/base/16414/21043_vm" to
>>>> "/tmp/data_fm/base/16400/21043_vm"): No such file or directory
>>>
>>> OK, so the problem seems to be that rewriteVisibilitymap can get
>>> called with errno already set to a nonzero value.
>>>
>>> It never clears it, and then fails at the end despite that no error
>>> has actually occurred.
>>>
>>> Just setting it to 0 at the top of the function seems to be correct
>>> thing to do. Or does it need to save the old value and restore it?
>>
>> Thank you for testing!
>> I think that the former is better, so attached latest patch.
>>
>>> But now when I want to do the upgrade faster, I run into this:
>>>
>>> "This utility cannot upgrade from PostgreSQL version from 9.5 or
>>> before to 9.6 or later with link mode."
>>>
>>> Is this really an acceptable a tradeoff? Surely we can arrange to
>>> link everything else and rewrite just the _vm, which is a tiny portion
>>> of the data directory. I don't think that -k promises to link
>>> everything it possibly can.
>>
>> I agree.
>> I've changed the patch so that.
>> pg_upgarde creates new _vm file and rewrites it even if upgrading to
>> 9.6 with link mode.
>
>
> The rewrite code thinks that only the first page of a vm has a header
> of size SizeOfPageHeaderData, and the rest of the pages have a zero
> size header. So the resulting _vm is corrupt.
>
> After pg_upgrade, doing a vacuum freeze verbose gives:
>
>
> WARNING: invalid page in block 1 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 1 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 2 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 2 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 3 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 3 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 4 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 4 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 5 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 5 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 6 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 6 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 7 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 7 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 8 of relation base/16402/22430_vm;
> zeroing out page
> WARNING: invalid page in block 8 of relation base/16402/22430_vm;
> zeroing out page
>

Thank you for taking the time to review this patch!
The updated version patch is attached.

Regards,

--
Masahiko Sawada

Attachment Content-Type Size
000_add_frozen_bit_into_visibilitymap_v26.patch application/octet-stream 91.0 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jim Nasby 2015-11-22 17:29:20 Re: count_nulls(VARIADIC "any")
Previous Message Andrew Dunstan 2015-11-22 15:37:08 Re: [Pgbuildfarm-members] latest buildfarm client release