Re: Vacuum Tuning Question

You can set your analyze and vacuum scale factor at table level -- normally
I do for big tables
For example:

-- Find current setting (this is at database level)

*select* * *from* pg_settings *where* *name* *in* ('autovacuum',
'autovacuum_analyze_scale_factor','autovacuum_analyze_threshold',
'autovacuum_vacuum_scale_factor');

*select* *current_setting*('autovacuum_vacuum_scale_factor') *as*
"analyze_scale_factor",*current_setting*('autovacuum_vacuum_threshold') *as*
"vacuum_threshold";

*select* *current_setting*('autovacuum_analyze_scale_factor') *as*
"analyze_scale_factor", *current_setting*('autovacuum_analyze_threshold')
*as* "analyze_threshold";

-- Note: The smaller number = more aggressive = vacuum more frequence

-- Current:

-- autovacuum_analyze_scale_factor = 0.05 ---> 0.002

-- autovacuum_vacuum_scale_factor = 0.1 ---> 0.001

-- Fine Tune at table level = ALTER TABLE mytable SET
(autovacuum_analyze_scale_factor = 0.02);

*ALTER* *TABLE* your_schema.your_table *SET* (autovacuum_enabled =
*true*,autovacuum_analyze_scale_factor
= 0.002,autovacuum_vacuum_scale_factor = 0.001);

-- Put it back to use global setting

*ALTER* *TABLE* your_schema.your_table *RESET*
(autovacuum_enabled,autovacuum_analyze_scale_factor,autovacuum_vacuum_scale_factor)
;

On Fri, May 19, 2023 at 12:48 PM Murthy Nunna <mnunna(at)fnal(dot)gov> wrote:

> Thanks! But if I set autovacuum_vacuum_scale_factor I am afraid it will
> make autovacuum less aggressive. It is already not aggressive enough. I am
> trying to make it more aggressive.
>
>
>
> Formula:
>
> Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor *
> number of tuples + autovacuum_vacuum_threshold
>
>
>
>
>
>
>
>
>
> *From:* MichaelDBA <MichaelDBA(at)sqlexec(dot)com>
> *Sent:* Friday, May 19, 2023 10:48 AM
> *To:* Murthy Nunna <mnunna(at)fnal(dot)gov>
> *Cc:* pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* Re: Vacuum Tuning Question
>
>
>
> You turned off autovacuum_vacuum_scale_factor!
>
> Murthy Nunna wrote on 5/19/2023 11:35 AM:
>
> Hi,
>
>
>
> I have a fairly large database (several TBs) where auto vacuum is enabled.
> My focus is to keep the datfrozenxid reasonably low and avoid manual
> vacuum. When I run following query, I see the percentage creeping up every
> day. Right after running vacuum manually on tables with large relfrozenxid
> the following query would return 7% on pgprd1 database but after 5 months
> it increased to 40%. So, eventually I am afraid I have to vacuum the tables
> manually which has its own problems like creating massive WALs in a short
> time etc. I would like to avoid manual vacuuming for this reason.
>
>
>
>
>
>
>
> SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname
>
> FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname
>
> FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE
> name = 'autovacuum_freeze_max_age') AS foo
>
> ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;
>
>
>
> 1500000000 | 599260139 | 40 | pgprd1
>
> 1500000000 | 50138249 | 3 | postgres
>
> 1500000000 | 50138249 | 3 | template1
>
>
>
>
>
> Auto vacuum is working as I can see from logs it is repeatedly vacuuming
> same tables, but I don’t see that as a problem because those are the tables
> that get updated/inserted continuously.
>
> Following are the settings I have. I am wondering if there is a way
> autovacuum can keep the above “datfrozenxid” low and not keep increasing.
> Thank you ahead time for reading my post.
>
>
>
> postgres=# select name, setting, unit from pg_settings where name like
> '%autovacuum%';
>
> name | setting | unit
>
> ---------------------------------------+------------+------
>
> autovacuum | on |
>
> autovacuum_analyze_scale_factor | 0.1 |
>
> autovacuum_analyze_threshold | 5000 |
>
> autovacuum_freeze_max_age | 1500000000 |
>
> autovacuum_max_workers | 5 |
>
> autovacuum_multixact_freeze_max_age | 400000000 |
>
> autovacuum_naptime | 60 | s
>
> autovacuum_vacuum_cost_delay | 2 | ms
>
> autovacuum_vacuum_cost_limit | -1 |
>
> autovacuum_vacuum_insert_scale_factor | 0 |
>
> autovacuum_vacuum_insert_threshold | 5000 |
>
> autovacuum_vacuum_scale_factor | 0 |
>
> autovacuum_vacuum_threshold | 5000 |
>
> autovacuum_work_mem | -1 | kB
>
> log_autovacuum_min_duration | 0 | ms
>
> (15 rows)
>
>
>
>
>
>
>
> Regards,
>
> Michael Vitale
>
> Michaeldba(at)sqlexec(dot)com <michaelvitale(at)sqlexec(dot)com>
>
> 703-600-9343
>
>
>