Thread: Per-Table vacuum_freeze_min_age
Hi all!
I have several large tables (1-2Tb) that are 99.9% writes (small number of updates) with a decent commit rate (20K/sec). The basic idea is that it’s generating a lot of data continuously. When the table would reach the thresholds for autovacuum a vacuum would start and would start generating wal write lock wait events. Once I set the freeze age to 500,000 (default is 50,000,000) the vacuums have to touch many fewer pages and is significantly faster without causing any write lock wait events.
The only downside I’ve seen is that this is a global setting and my understanding is that this would cause decreased performance when used with tables with a lot of writes and deletes. Is there a technical reason this setting cannot be applied at the database or table context like other autovacuum settings?
- chris
Chris Bisnett <cbisnett@gmail.com> writes:
> The only downside I’ve seen is that this is a global setting and my
> understanding is that this would cause decreased performance when used with
> tables with a lot of writes and deletes. Is there a technical reason this
> setting cannot be applied at the database or table context like other
> autovacuum settings?
There's an autovacuum_freeze_min_age reloption, isn't that
what you need?
regards, tom lane
On 4/6/22 3:13 PM, Chris Bisnett wrote: > Hi all! > > I have several large tables (1-2Tb) that are 99.9% writes (small number > of updates) with a decent commit rate (20K/sec). The basic idea is that > it’s generating a lot of data continuously. When the table would reach > the thresholds for autovacuum a vacuum would start and would start > generating wal write lock wait events. Once I set the freeze age to > 500,000 (default is 50,000,000) the vacuums have to touch many fewer > pages and is significantly faster without causing any write lock wait > events. > > The only downside I’ve seen is that this is a global setting and my > understanding is that this would cause decreased performance when used > with tables with a lot of writes and deletes. Is there a technical > reason this setting cannot be applied at the database or table context > like other autovacuum settings? It can: https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS Per-table value for vacuum_freeze_min_age parameter. > > - chris > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Apr 6, 2022 at 6:24 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/6/22 3:13 PM, Chris Bisnett wrote:
> Hi all!
>
> I have several large tables (1-2Tb) that are 99.9% writes (small number
> of updates) with a decent commit rate (20K/sec). The basic idea is that
> it’s generating a lot of data continuously. When the table would reach
> the thresholds for autovacuum a vacuum would start and would start
> generating wal write lock wait events. Once I set the freeze age to
> 500,000 (default is 50,000,000) the vacuums have to touch many fewer
> pages and is significantly faster without causing any write lock wait
> events.
>
> The only downside I’ve seen is that this is a global setting and my
> understanding is that this would cause decreased performance when used
> with tables with a lot of writes and deletes. Is there a technical
> reason this setting cannot be applied at the database or table context
> like other autovacuum settings?
It can:
https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
Per-table value for vacuum_freeze_min_age parameter.
>
> - chris
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Oh I must have missed this. Is this missing from the documentation here (
I can try again, but I’m pretty sure this option was rejected when I attempted to set it via an alter table command.
On 4/6/22 3:28 PM, Chris Bisnett wrote: > On Wed, Apr 6, 2022 at 6:24 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > It can: > > https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS > <https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS> > > Per-table value for vacuum_freeze_min_age parameter. > > > > - chris > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > Oh I must have missed this. Is this missing from the documentation here ( > https://www.postgresql.org/docs/current/runtime-config-autovacuum.html > <https://www.postgresql.org/docs/current/runtime-config-autovacuum.html>)? That describes autovacuum in general. The per table options are attributes of a table. > > I can try again, but I’m pretty sure this option was rejected when I > attempted to set it via an alter table command. What was the command you used? What was the error you got? > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Apr 6, 2022 at 6:31 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/6/22 3:28 PM, Chris Bisnett wrote:
> On Wed, Apr 6, 2022 at 6:24 PM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
> It can:
>
> https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
> <https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS>
>
> Per-table value for vacuum_freeze_min_age parameter.
> >
> > - chris
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
> Oh I must have missed this. Is this missing from the documentation here (
> https://www.postgresql.org/docs/current/runtime-config-autovacuum.html
> <https://www.postgresql.org/docs/current/runtime-config-autovacuum.html>)?
That describes autovacuum in general. The per table options are
attributes of a table.
>
> I can try again, but I’m pretty sure this option was rejected when I
> attempted to set it via an alter table command.
What was the command you used?
What was the error you got?
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Either I was trying to use vacuum_freeze_min_age on the table or I forgot the parenthesis around the options in the alter table command. Either way, I just tried it and it worked. Thanks for the help!
- chris