pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTooling for per table autovacuum tuning
11+ messages / 7 participants
[nested] [flat]
* Tooling for per table autovacuum tuning
@ 2023-03-11 18:49 Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-11 19:07 ` Re: Tooling for per table autovacuum tuning Ron <ronljohnsonjr@gmail.com>
2023-03-12 13:47 ` Re: Tooling for per table autovacuum tuning Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 11+ messages in thread
From: Joseph Hammerman @ 2023-03-11 18:49 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi pgsql-admins,
I would like to define t-shirt sizes and have an autovacuuming profile
associated with each t-shirt size.
Is there any tooling out there that assists in the execution side of this?
Or are all of you rolling your own?
Thanks in advance for any assistance anyone can provide,
Joe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tooling for per table autovacuum tuning
2023-03-11 18:49 Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
@ 2023-03-11 19:07 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Ron @ 2023-03-11 19:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
T-shirts?
On 3/11/23 12:49, Joseph Hammerman wrote:
> Hi pgsql-admins,
>
> I would like to define t-shirt sizes and have an autovacuuming profile
> associated with each t-shirt size.
>
> Is there any tooling out there that assists in the execution side of this?
> Or are all of you rolling your own?
>
> Thanks in advance for any assistance anyone can provide,
> Joe
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tooling for per table autovacuum tuning
2023-03-11 18:49 Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
@ 2023-03-12 13:47 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 16:56 ` Re: Tooling for per table autovacuum tuning Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
1 sibling, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2023-03-12 13:47 UTC (permalink / raw)
To: Joseph Hammerman <joe.hammerman@datadoghq.com>; pgsql-admin@lists.postgresql.org
On Sat, 2023-03-11 at 10:49 -0800, Joseph Hammerman wrote:
> I would like to define t-shirt sizes and have an autovacuuming profile associated with each t-shirt size.
>
> Is there any tooling out there that assists in the execution side of this? Or are all of you rolling your own?
Isn't that question 21 days early?
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tooling for per table autovacuum tuning
2023-03-11 18:49 Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 13:47 ` Re: Tooling for per table autovacuum tuning Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-03-12 16:56 ` Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
2023-03-12 20:34 ` Re: Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
0 siblings, 1 reply; 11+ messages in thread
From: Wolfgang Wilhelm @ 2023-03-12 16:56 UTC (permalink / raw)
To: Joseph Hammerman <joe.hammerman@datadoghq.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>
I think Mr Hammerman is referring to T-shirts for user stories.
But even when I'm right I don't get what is meant with that. I don't get what Joe means with "autovacuuming profiles per table". Joe, can you elaborate on that?
YoursWolfgang
Am Sonntag, 12. März 2023 um 14:47:42 MEZ hat Laurenz Albe <laurenz.albe@cybertec.at> Folgendes geschrieben:
On Sat, 2023-03-11 at 10:49 -0800, Joseph Hammerman wrote:
> I would like to define t-shirt sizes and have an autovacuuming profile associated with each t-shirt size.
>
> Is there any tooling out there that assists in the execution side of this? Or are all of you rolling your own?
Isn't that question 21 days early?
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tooling for per table autovacuum tuning
2023-03-11 18:49 Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 13:47 ` Re: Tooling for per table autovacuum tuning Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 16:56 ` Re: Tooling for per table autovacuum tuning Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
@ 2023-03-12 20:34 ` Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 20:43 ` Re: Tooling for per table autovacuum tuning MichaelDBA <MichaelDBA@sqlexec.com>
2023-03-12 21:03 ` Re: Tooling for per table autovacuum tuning Ron <ronljohnsonjr@gmail.com>
2023-03-13 05:58 ` Re: Tooling for per table autovacuum tuning Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 11+ messages in thread
From: Joseph Hammerman @ 2023-03-12 20:34 UTC (permalink / raw)
To: Wolfgang Wilhelm <wolfgang20121964@yahoo.de>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>
Hi all,
Apologies for any confusion I may have caused. What I am imagining is per
table tuning that buckets the tables based on their relative sizes.
Something like:
Up to 1Gb - Small
Up to 4Gb - Medium
Up to 8Gb - L
Bigger - XL
And an accordant autovacuum_scale_factor associated with each size.
The motivation for this is to make sure large tables get regularly vacuumed.
I hope that clears thing up!
Joe
On Sun, Mar 12, 2023 at 9:56 AM Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
wrote:
> I think Mr Hammerman is referring to T-shirts for user stories.
>
> But even when I'm right I don't get what is meant with that. I don't get
> what Joe means with "autovacuuming profiles per table". Joe, can you
> elaborate on that?
>
> Yours
> Wolfgang
>
> Am Sonntag, 12. März 2023 um 14:47:42 MEZ hat Laurenz Albe <
> laurenz.albe@cybertec.at> Folgendes geschrieben:
>
>
> On Sat, 2023-03-11 at 10:49 -0800, Joseph Hammerman wrote:
>
> > I would like to define t-shirt sizes and have an autovacuuming profile
> associated with each t-shirt size.
> >
> > Is there any tooling out there that assists in the execution side of
> this? Or are all of you rolling your own?
>
>
> Isn't that question 21 days early?
>
> Yours,
> Laurenz Albe
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tooling for per table autovacuum tuning
2023-03-11 18:49 Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 13:47 ` Re: Tooling for per table autovacuum tuning Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 16:56 ` Re: Tooling for per table autovacuum tuning Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
2023-03-12 20:34 ` Re: Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
@ 2023-03-12 20:43 ` MichaelDBA <MichaelDBA@sqlexec.com>
2023-03-12 21:36 ` Re: Tooling for per table autovacuum tuning Matt Pearson <mpearson2@pythian.com>
2 siblings, 1 reply; 11+ messages in thread
From: MichaelDBA @ 2023-03-12 20:43 UTC (permalink / raw)
To: Joseph Hammerman <joe.hammerman@datadoghq.com>; +Cc: Wolfgang Wilhelm <wolfgang20121964@yahoo.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>
Why don't you just monitor pg_stat_user_tables.n_dead_tup on a regular
basis and increase autovacuum aggressiveness based on that at the global
level (postgresql.conf - thresholds) or set autovacuum parms at the
table level for customized cases.
Regards,
Michael Vitale
Joseph Hammerman wrote on 3/12/2023 4:34 PM:
> Hi all,
>
> Apologies for any confusion I may have caused. What I am imagining is
> per table tuning that buckets the tables based on their relative sizes.
>
> Something like:
>
> Up to 1Gb - Small
> Up to 4Gb - Medium
> Up to 8Gb - L
> Bigger - XL
>
> And an accordant autovacuum_scale_factor associated with each size.
>
> The motivation for this is to make sure large tables get regularly
> vacuumed.
>
> I hope that clears thing up!
> Joe
>
> On Sun, Mar 12, 2023 at 9:56 AM Wolfgang Wilhelm
> <wolfgang20121964@yahoo.de <mailto:wolfgang20121964@yahoo.de>> wrote:
>
> I think Mr Hammerman is referring to T-shirts for user stories.
>
> But even when I'm right I don't get what is meant with that. I
> don't get what Joe means with "autovacuuming profiles per table".
> Joe, can you elaborate on that?
>
> Yours
> Wolfgang
>
> Am Sonntag, 12. März 2023 um 14:47:42 MEZ hat Laurenz Albe
> <laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>>
> Folgendes geschrieben:
>
>
> On Sat, 2023-03-11 at 10:49 -0800, Joseph Hammerman wrote:
>
> > I would like to define t-shirt sizes and have an
> autovacuuming profile associated with each t-shirt size.
> >
> > Is there any tooling out there that assists in the execution
> side of this? Or are all of you rolling your own?
>
>
> Isn't that question 21 days early?
>
> Yours,
> Laurenz Albe
>
>
>
Regards,
Michael Vitale
Michaeldba@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../23bac98a-30aa-4305-6dcd-b914d733e45e@sqlexec.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tooling for per table autovacuum tuning
2023-03-11 18:49 Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 13:47 ` Re: Tooling for per table autovacuum tuning Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 16:56 ` Re: Tooling for per table autovacuum tuning Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
2023-03-12 20:34 ` Re: Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 20:43 ` Re: Tooling for per table autovacuum tuning MichaelDBA <MichaelDBA@sqlexec.com>
@ 2023-03-12 21:36 ` Matt Pearson <mpearson2@pythian.com>
0 siblings, 0 replies; 11+ messages in thread
From: Matt Pearson @ 2023-03-12 21:36 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; Joseph Hammerman <joe.hammerman@datadoghq.com>; +Cc: Wolfgang Wilhelm <wolfgang20121964@yahoo.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>
Hi,
Following on from Michael's idea, you could write a function to split up
the tables based upon size. As an example, you could do this (using the
pg_stat_all_tables):
DO
$$
DECLARE
tab_rec RECORD;
BEGIN
FOR tab_rec IN SELECT schemaname,
relname tablename,
pg_catalog.pg_table_size(relid) bytes,
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(relid)) size
FROM pg_stat_all_tables
WHERE schemaname NOT IN
('pg_catalog','information_schema')
AND schemaname = 'public'
ORDER BY bytes
LOOP
RAISE NOTICE 'schemaname % tablename % bytes: %
Table_size: %', tab_rec.schemaname, tab_rec.tablename, tab_rec.bytes,
tab_rec.size;
IF tab_rec.bytes < 10000 THEN
RAISE NOTICE 'ALTER TABLE %.% SET
(autovacuum_vacuum_cost_limit = 1000);', tab_rec.schemaname,
tab_rec.tablename;
ELSE
RAISE NOTICE 'ALTER TABLE %.% SET
(autovacuum_vacuum_cost_limit = 10000);', tab_rec.schemaname,
tab_rec.tablename;
END IF;
END LOOP;
END;
$$
NOTICE: schemaname public tablename event_check3 bytes: 0 Table_size: 0
bytes
NOTICE: ALTER TABLE public.event_check3 SET
(autovacuum_vacuum_cost_limit = 1000);
NOTICE: schemaname public tablename event_check bytes: 0 Table_size: 0
bytes
NOTICE: ALTER TABLE public.track_ddl SET (autovacuum_vacuum_cost_limit
= 10000);
NOTICE: schemaname public tablename audit_ddl_cmds bytes: 16384
Table_size: 16 kB
NOTICE: ALTER TABLE public.audit_ddl_cmds SET
(autovacuum_vacuum_cost_limit = 10000);
NOTICE: schemaname public tablename c1 bytes: 16384 Table_size: 16 kB
You'd have to define the parameters in the IF statements but it could be
split up into the "t-shirt" sizes that you want. The pg_catalog table
could also be changed to something else but this is the general idea.
KR,
Matt
On 12/03/2023 20:43, MichaelDBA wrote:
> Why don't you just monitor pg_stat_user_tables.n_dead_tup on a
> regular basis and increase autovacuum aggressiveness based on that at
> the global level (postgresql.conf - thresholds) or set autovacuum
> parms at the table level for customized cases.
>
> Regards,
> Michael Vitale
>
>
> Joseph Hammerman wrote on 3/12/2023 4:34 PM:
>> Hi all,
>>
>> Apologies for any confusion I may have caused. What I am imagining is
>> per table tuning that buckets the tables based on their relative sizes.
>>
>> Something like:
>>
>> Up to 1Gb - Small
>> Up to 4Gb - Medium
>> Up to 8Gb - L
>> Bigger - XL
>>
>> And an accordant autovacuum_scale_factor associated with each size.
>>
>> The motivation for this is to make sure large tables get regularly
>> vacuumed.
>>
>> I hope that clears thing up!
>> Joe
>>
>> On Sun, Mar 12, 2023 at 9:56 AM Wolfgang Wilhelm
>> <wolfgang20121964@yahoo.de> wrote:
>>
>> I think Mr Hammerman is referring to T-shirts for user stories.
>>
>> But even when I'm right I don't get what is meant with that. I
>> don't get what Joe means with "autovacuuming profiles per table".
>> Joe, can you elaborate on that?
>>
>> Yours
>> Wolfgang
>>
>> Am Sonntag, 12. März 2023 um 14:47:42 MEZ hat Laurenz Albe
>> <laurenz.albe@cybertec.at> Folgendes geschrieben:
>>
>>
>> On Sat, 2023-03-11 at 10:49 -0800, Joseph Hammerman wrote:
>>
>> > I would like to define t-shirt sizes and have an
>> autovacuuming profile associated with each t-shirt size.
>> >
>> > Is there any tooling out there that assists in the execution
>> side of this? Or are all of you rolling your own?
>>
>>
>> Isn't that question 21 days early?
>>
>> Yours,
>> Laurenz Albe
>>
>>
>>
>
>
> Regards,
>
> Michael Vitale
>
> Michaeldba@sqlexec.com <mailto:michaelvitale@sqlexec.com>
>
> 703-600-9343
>
>
>
>
--
Pythian
Matt Pearson | Database Consultant - PostgreSQL & Oracle | LinkedIn
mpearson2@pythian.com
www.pythian.com
Pythian
--
--
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../8b782c01-1243-d0dd-ad78-cd0e4111f91b@pythian.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tooling for per table autovacuum tuning
2023-03-11 18:49 Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 13:47 ` Re: Tooling for per table autovacuum tuning Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 16:56 ` Re: Tooling for per table autovacuum tuning Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
2023-03-12 20:34 ` Re: Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
@ 2023-03-12 21:03 ` Ron <ronljohnsonjr@gmail.com>
2023-03-13 10:09 ` Re: Tooling for per table autovacuum tuning Alvaro Herrera <alvherre@alvh.no-ip.org>
2 siblings, 1 reply; 11+ messages in thread
From: Ron @ 2023-03-12 21:03 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
A giant table which is never updated (or deleted from) doesn't need to be
vacuumed.
That same giant table which isn't added to anymore doesn't need to be
analyzed, either.
On 3/12/23 15:34, Joseph Hammerman wrote:
> Hi all,
>
> Apologies for any confusion I may have caused. What I am imagining is per
> table tuning that buckets the tables based on their relative sizes.
>
> Something like:
>
> Up to 1Gb - Small
> Up to 4Gb - Medium
> Up to 8Gb - L
> Bigger - XL
>
> And an accordant autovacuum_scale_factor associated with each size.
>
> The motivation for this is to make sure large tables get regularly vacuumed.
>
> I hope that clears thing up!
> Joe
>
> On Sun, Mar 12, 2023 at 9:56 AM Wolfgang Wilhelm
> <wolfgang20121964@yahoo.de> wrote:
>
> I think Mr Hammerman is referring to T-shirts for user stories.
>
> But even when I'm right I don't get what is meant with that. I don't
> get what Joe means with "autovacuuming profiles per table". Joe, can
> you elaborate on that?
>
> Yours
> Wolfgang
>
> Am Sonntag, 12. März 2023 um 14:47:42 MEZ hat Laurenz Albe
> <laurenz.albe@cybertec.at> Folgendes geschrieben:
>
>
> On Sat, 2023-03-11 at 10:49 -0800, Joseph Hammerman wrote:
>
> > I would like to define t-shirt sizes and have an
> autovacuuming profile associated with each t-shirt size.
> >
> > Is there any tooling out there that assists in the execution side of
> this? Or are all of you rolling your own?
>
>
> Isn't that question 21 days early?
>
> Yours,
> Laurenz Albe
>
>
>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tooling for per table autovacuum tuning
2023-03-11 18:49 Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 13:47 ` Re: Tooling for per table autovacuum tuning Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 16:56 ` Re: Tooling for per table autovacuum tuning Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
2023-03-12 20:34 ` Re: Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 21:03 ` Re: Tooling for per table autovacuum tuning Ron <ronljohnsonjr@gmail.com>
@ 2023-03-13 10:09 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-13 12:56 ` Re: Tooling for per table autovacuum tuning Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Alvaro Herrera @ 2023-03-13 10:09 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On 2023-Mar-12, Ron wrote:
> A giant table which is never updated (or deleted from) doesn't need to be
> vacuumed.
Actually, this bit is wrong, because vacuuming is also used for things
such as setting up the visibility map, which feeds index-only scans. So
insert-only tables also benefit from vacuuming, and indeed Postgres 13
introduced this as an autovacuum feature, in this commit:
commit b07642dbcd8d5de05f0ee1dbb72dd6760dd30436
Author: David Rowley <drowley@postgresql.org> []
AuthorDate: Sat Mar 28 19:20:12 2020 +1300
CommitDate: Sat Mar 28 19:20:12 2020 +1300
Trigger autovacuum based on number of INSERTs
Traditionally autovacuum has only ever invoked a worker based on the
estimated number of dead tuples in a table and for anti-wraparound
purposes. For the latter, with certain classes of tables such as
insert-only tables, anti-wraparound vacuums could be the first vacuum that
the table ever receives. This could often lead to autovacuum workers being
busy for extended periods of time due to having to potentially freeze
every page in the table. This could be particularly bad for very large
tables. New clusters, or recently pg_restored clusters could suffer even
more as many large tables may have the same relfrozenxid, which could
result in large numbers of tables requiring an anti-wraparound vacuum all
at once.
Here we aim to reduce the work required by anti-wraparound and aggressive
vacuums in general, by triggering autovacuum when the table has received
enough INSERTs. This is controlled by adding two new GUCs and reloptions;
autovacuum_vacuum_insert_threshold and
autovacuum_vacuum_insert_scale_factor. These work exactly the same as the
existing scale factor and threshold controls, only base themselves off the
number of inserts since the last vacuum, rather than the number of dead
tuples. New controls were added rather than reusing the existing
controls, to allow these new vacuums to be tuned independently and perhaps
even completely disabled altogether, which can be done by setting
autovacuum_vacuum_insert_threshold to -1.
We make no attempt to skip index cleanup operations on these vacuums as
they may trigger for an insert-mostly table which continually doesn't have
enough dead tuples to trigger an autovacuum for the purpose of removing
those dead tuples. If we were to skip cleaning the indexes in this case,
then it is possible for the index(es) to become bloated over time.
There are additional benefits to triggering autovacuums based on inserts,
as tables which never contain enough dead tuples to trigger an autovacuum
are now more likely to receive a vacuum, which can mark more of the table
as "allvisible" and encourage the query planner to make use of Index Only
Scans.
Currently, we still obey vacuum_freeze_min_age when triggering these new
autovacuums based on INSERTs. For large insert-only tables, it may be
beneficial to lower the table's autovacuum_freeze_min_age so that tuples
are eligible to be frozen sooner. Here we've opted not to zero that for
these types of vacuums, since the table may just be insert-mostly and we
may otherwise freeze tuples that are still destined to be updated or
removed in the near future.
There was some debate to what exactly the new scale factor and threshold
should default to. For now, these are set to 0.2 and 1000, respectively.
There may be some motivation to adjust these before the release.
Author: Laurenz Albe, Darafei Praliaskouski
Reviewed-by: Alvaro Herrera, Masahiko Sawada, Chris Travers, Andres Freund, Justin Pryzby
Discussion: https://postgr.es/m/CAC8Q8t%2Bj36G_bLF%3D%2B0iMo6jGNWnLnWb1tujXuJr-%2Bx8ZCCTqoQ%40mail.gmail.com
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tooling for per table autovacuum tuning
2023-03-11 18:49 Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 13:47 ` Re: Tooling for per table autovacuum tuning Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 16:56 ` Re: Tooling for per table autovacuum tuning Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
2023-03-12 20:34 ` Re: Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 21:03 ` Re: Tooling for per table autovacuum tuning Ron <ronljohnsonjr@gmail.com>
2023-03-13 10:09 ` Re: Tooling for per table autovacuum tuning Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-03-13 12:56 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Ron @ 2023-03-13 12:56 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
An excellent write up.
And seems to be for the same reason why one should VACUUM after
On 3/13/23 05:09, Alvaro Herrera wrote:
> On 2023-Mar-12, Ron wrote:
>
>> A giant table which is never updated (or deleted from) doesn't need to be
>> vacuumed.
> Actually, this bit is wrong, because vacuuming is also used for things
> such as setting up the visibility map, which feeds index-only scans. So
> insert-only tables also benefit from vacuuming, and indeed Postgres 13
> introduced this as an autovacuum feature, in this commit:
>
>
> commit b07642dbcd8d5de05f0ee1dbb72dd6760dd30436
> Author: David Rowley <drowley@postgresql.org> []
> AuthorDate: Sat Mar 28 19:20:12 2020 +1300
> CommitDate: Sat Mar 28 19:20:12 2020 +1300
>
> Trigger autovacuum based on number of INSERTs
>
> Traditionally autovacuum has only ever invoked a worker based on the
> estimated number of dead tuples in a table and for anti-wraparound
> purposes. For the latter, with certain classes of tables such as
> insert-only tables, anti-wraparound vacuums could be the first vacuum that
> the table ever receives. This could often lead to autovacuum workers being
> busy for extended periods of time due to having to potentially freeze
> every page in the table. This could be particularly bad for very large
> tables. New clusters, or recently pg_restored clusters could suffer even
> more as many large tables may have the same relfrozenxid, which could
> result in large numbers of tables requiring an anti-wraparound vacuum all
> at once.
>
> Here we aim to reduce the work required by anti-wraparound and aggressive
> vacuums in general, by triggering autovacuum when the table has received
> enough INSERTs. This is controlled by adding two new GUCs and reloptions;
> autovacuum_vacuum_insert_threshold and
> autovacuum_vacuum_insert_scale_factor. These work exactly the same as the
> existing scale factor and threshold controls, only base themselves off the
> number of inserts since the last vacuum, rather than the number of dead
> tuples. New controls were added rather than reusing the existing
> controls, to allow these new vacuums to be tuned independently and perhaps
> even completely disabled altogether, which can be done by setting
> autovacuum_vacuum_insert_threshold to -1.
>
> We make no attempt to skip index cleanup operations on these vacuums as
> they may trigger for an insert-mostly table which continually doesn't have
> enough dead tuples to trigger an autovacuum for the purpose of removing
> those dead tuples. If we were to skip cleaning the indexes in this case,
> then it is possible for the index(es) to become bloated over time.
>
> There are additional benefits to triggering autovacuums based on inserts,
> as tables which never contain enough dead tuples to trigger an autovacuum
> are now more likely to receive a vacuum, which can mark more of the table
> as "allvisible" and encourage the query planner to make use of Index Only
> Scans.
>
> Currently, we still obey vacuum_freeze_min_age when triggering these new
> autovacuums based on INSERTs. For large insert-only tables, it may be
> beneficial to lower the table's autovacuum_freeze_min_age so that tuples
> are eligible to be frozen sooner. Here we've opted not to zero that for
> these types of vacuums, since the table may just be insert-mostly and we
> may otherwise freeze tuples that are still destined to be updated or
> removed in the near future.
>
> There was some debate to what exactly the new scale factor and threshold
> should default to. For now, these are set to 0.2 and 1000, respectively.
> There may be some motivation to adjust these before the release.
>
> Author: Laurenz Albe, Darafei Praliaskouski
> Reviewed-by: Alvaro Herrera, Masahiko Sawada, Chris Travers, Andres Freund, Justin Pryzby
> Discussion: https://postgr.es/m/CAC8Q8t%2Bj36G_bLF%3D%2B0iMo6jGNWnLnWb1tujXuJr-%2Bx8ZCCTqoQ%40mail.gmail.com
>
>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tooling for per table autovacuum tuning
2023-03-11 18:49 Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 13:47 ` Re: Tooling for per table autovacuum tuning Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 16:56 ` Re: Tooling for per table autovacuum tuning Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
2023-03-12 20:34 ` Re: Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
@ 2023-03-13 05:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 11+ messages in thread
From: Laurenz Albe @ 2023-03-13 05:58 UTC (permalink / raw)
To: Joseph Hammerman <joe.hammerman@datadoghq.com>; Wolfgang Wilhelm <wolfgang20121964@yahoo.de>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Sun, 2023-03-12 at 13:34 -0700, Joseph Hammerman wrote:
> What I am imagining is per table tuning that buckets the tables based on their relative sizes.
>
> Something like:
>
> Up to 1Gb - Small
> Up to 4Gb - Medium
> Up to 8Gb - L
> Bigger - XL
>
> And an accordant autovacuum_scale_factor associated with each size.
>
> The motivation for this is to make sure large tables get regularly vacuumed.
I understand.
There is no such option currently. Perhaps you can use "autovacuum_vacuum_threshold"
for something like that: if you set it to a certain (high) value and set "autovacuum_vacuum_scale_factor"
to 0, autovacuum is triggered based on the absolute number of dead tuples.
But I would say that the standard configuration makes sense in this case: normally,
large tables don't need to be vacuumed that often, and vacuum on large tables is
nore expensive too.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2023-03-13 12:56 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-11 18:49 Tooling for per table autovacuum tuning Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-11 19:07 ` Ron <ronljohnsonjr@gmail.com>
2023-03-12 13:47 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-03-12 16:56 ` Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
2023-03-12 20:34 ` Joseph Hammerman <joe.hammerman@datadoghq.com>
2023-03-12 20:43 ` MichaelDBA <MichaelDBA@sqlexec.com>
2023-03-12 21:36 ` Matt Pearson <mpearson2@pythian.com>
2023-03-12 21:03 ` Ron <ronljohnsonjr@gmail.com>
2023-03-13 10:09 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-13 12:56 ` Ron <ronljohnsonjr@gmail.com>
2023-03-13 05:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox