From: | Darafei "Komяpa" Praliaskouski <me(at)komzpa(dot)net> |
---|---|
To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
Cc: | PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Berserk Autovacuum (let's save next Mandrill) |
Date: | 2019-03-28 05:34:34 |
Message-ID: | CAC8Q8tJMzs8fh4Ene0_oLOqmTpPkQM_mYBG7LBWcGJwn23FUqQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
чт, 28 мар. 2019 г. в 01:01, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:
> On 2019-Mar-28, Darafei "Komяpa" Praliaskouski wrote:
>
>
> > чт, 28 мар. 2019 г. в 00:32, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:
> >
> > > On 2019-Mar-27, Darafei "Komяpa" Praliaskouski wrote:
>
> > > * certain tables would have some sort of partial scan that sets the
> > > visibility map. There's no reason to invoke the whole vacuuming
> > > machinery. I don't think this is limited to append-only tables, but
> > > rather those are just the ones that are affected the most.
> >
> > What other machinery runs on VACUUM invocation that is not wanted there?
> > Since Postgres 11 index cleanup is already skipped on append-only tables.
>
> Well, I think it would be useful to set all-visible earlier than waiting
> for a vacuum to be necessary, even for tables that are not append-only.
> So if you think about this just for the append-only table, you leave
> money on the table.
>
Thing is, problem does not exist for non-append-only tables, they're going
to be vacuumed after 50 rows got updated, automatically.
>
> > > * tables nearing wraparound danger should use the (yet to be committed)
> > > option to skip index cleaning, which makes the cleanup action faster.
> > > Again, no need for complete vacuuming.
> >
> > "Nearing wraparound" is too late already. In Amazon, reading table from
> gp2
> > after you exhausted your IOPS burst budget is like reading a floppy
> drive,
> > you have to freeze a lot earlier than you hit several terabytes of
> unfrozen
> > data, or you're dead like Mandrill's Search and Url tables from the link
> I
> > shared.
>
> OK, then start freezing tuples in the cheap mode (skip index updates)
> earlier than that. I suppose a good question is when to start.
>
Attached (autovacuum_berserk_v1.patch)
code achieves that. For append-only tables since
https://commitfest.postgresql.org/16/952/ vacuum skips index cleanup if no
updates happened. You just need to trigger it, and it already will be
"cheap".
--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa
From | Date | Subject | |
---|---|---|---|
Next Message | Tsunakawa, Takayuki | 2019-03-28 05:47:10 | RE: Timeout parameters |
Previous Message | Thomas Munro | 2019-03-28 05:29:47 | Re: Usage of epoch in txid_current |