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 (
https://www.postgresql.org/docs/current/runtime-config-autovacuum.html)?
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