From: Matt Pearson <mpearson2@pythian.com>
To: MichaelDBA <MichaelDBA@sqlexec.com>
To: Joseph Hammerman <joe.hammerman@datadoghq.com>
Cc: Wolfgang Wilhelm <wolfgang20121964@yahoo.de>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Subject: Re: Tooling for per table autovacuum tuning
Date: Sun, 12 Mar 2023 21:36:45 +0000
Message-ID: <8b782c01-1243-d0dd-ad78-cd0e4111f91b@pythian.com> (raw)
In-Reply-To: <23bac98a-30aa-4305-6dcd-b914d733e45e@sqlexec.com>
References: <CAHs7QM_scGEh8uhsCUoc_BEimyYsUTVZxpE10rUiqnShd8nTkQ@mail.gmail.com>
<5c0a6ab9ba801e0fa7c2217e49ce804837c64cf1.camel@cybertec.at>
<14072753.2216234.1678640212317@mail.yahoo.com>
<CAHs7QM_dDN3M=G0vEEi7V_wZaWmtJO5OK6ruh7bBfQ4BNo4QxQ@mail.gmail.com>
<23bac98a-30aa-4305-6dcd-b914d733e45e@sqlexec.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mpearson2@pythian.com, MichaelDBA@sqlexec.com, joe.hammerman@datadoghq.com, wolfgang20121964@yahoo.de, pgsql-admin@lists.postgresql.org, laurenz.albe@cybertec.at
Subject: Re: Tooling for per table autovacuum tuning
In-Reply-To: <8b782c01-1243-d0dd-ad78-cd0e4111f91b@pythian.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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