From: | MichaelDBA <MichaelDBA(at)sqlexec(dot)com> |
---|---|
To: | Murthy Nunna <mnunna(at)fnal(dot)gov> |
Cc: | "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Vacuum Tuning Question |
Date: | 2023-05-19 15:48:20 |
Message-ID: | 16139c34-a68b-f9ea-dd43-c6d84300d112@sqlexec.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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 <mailto:michaelvitale(at)sqlexec(dot)com>
703-600-9343
From | Date | Subject | |
---|---|---|---|
Next Message | Murthy Nunna | 2023-05-19 16:47:46 | RE: Vacuum Tuning Question |
Previous Message | Murthy Nunna | 2023-05-19 15:35:57 | Vacuum Tuning Question |