From: Murthy Nunna <mnunna@fnal.gov>
To: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: RE: Vacuum Tuning Question
Date: Fri, 19 May 2023 16:47:46 +0000
Message-ID: <DM8PR09MB6677C4A05D5AC529ECC6785DB87C9@DM8PR09MB6677.namprd09.prod.outlook.com> (raw)
In-Reply-To: <16139c34-a68b-f9ea-dd43-c6d84300d112@sqlexec.com>
References: <DM8PR09MB667745E57CD0351317FAA785B87C9@DM8PR09MB6677.namprd09.prod.outlook.com>
<16139c34-a68b-f9ea-dd43-c6d84300d112@sqlexec.com>
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@sqlexec.com>
Sent: Friday, May 19, 2023 10:48 AM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@lists.postgresql.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@sqlexec.com<mailto:michaelvitale@sqlexec.com>
703-600-9343
[cid:image001.jpg@01D98A47.B9C273E0]
Attachments:
[image/jpeg] image001.jpg (20.6K, ../DM8PR09MB6677C4A05D5AC529ECC6785DB87C9@DM8PR09MB6677.namprd09.prod.outlook.com/3-image001.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: mnunna@fnal.gov, MichaelDBA@sqlexec.com, pgsql-admin@lists.postgresql.org
Subject: RE: Vacuum Tuning Question
In-Reply-To: <DM8PR09MB6677C4A05D5AC529ECC6785DB87C9@DM8PR09MB6677.namprd09.prod.outlook.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