From: Laurenz Albe <laurenz.albe@cybertec.at>
To: senor <frio_cervesa@hotmail.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: vacuum visibility relevance
Date: Mon, 04 Dec 2023 03:56:49 +0100
Message-ID: <28bb8938984b0d97f25f413d794efa782286a440.camel@cybertec.at> (raw)
In-Reply-To: <SN4P221MB0683F647B1C33B363A92FF37F786A@SN4P221MB0683.NAMP221.PROD.OUTLOOK.COM>
References: <SN4P221MB0683F647B1C33B363A92FF37F786A@SN4P221MB0683.NAMP221.PROD.OUTLOOK.COM>
On Mon, 2023-12-04 at 02:08 +0000, senor wrote:
> Simplified Scenario:
> 40+ gig table with 20+ indexes receiving log event type records in an append only fashion.
Log tables typically receive tons of INSERTs. Over twenty indexes are too many.
> One of the columns is a sequence ID.
> PG version 11.4
>
>
> If I start a vacuum on this table when sequence ID is 1 million, and the table continues
> to accumulate new events, when vacuum completes, should I be thinking about the state of
> the table and indexes as being vacuumed up to 1 million? Or is vacuum also operating on
> records inserted after vacuum started?
VACUUM processes the whole table, but it determines an "xmin horizon" when it starts,
that is, it determines which tuples are safe to remove. Rows added after VACUUM started
won't qualify, so they won't be removed.
> Is there any reason to think about this differently when the vacuum is manual as opposed to auto?
No, the behavior is the same.
> I attempted to deal with the append only issue corrected in PG13 by setting
> autovacuum_freeze_max_age low enough to trigger vacuum but that has been such a wild card
> I abandoned it. I'm now trying to find a formula to trigger a manual vacuum. There are
> stats gathering processes pulling from the most recently added data all the time so my
> priority is to keep performance high for those. Secondly, there's a ton of memory allotted
> so running vacuum freeze before table pages age out of memory seems like a good idea.
You can monitor the "n_tup_ins" column in "pg_stat_user_tables" and run VACUUM whebever
it has increased enough.
But the simple solution is to upgrade. Any version older than v13 is due for an upgrade soon.
> I am having trouble with autovacuum keeping up and I suspect there's a communication
> problem with the stats collector but have put off looking into that because updating
> to PG15 was "right around the corner". Meanwhile, I have had to run multiple-thread
> scripts to keep up vacuum. Manual vacuums always work and updates stats correctly but
> autovacuums hang and occupies workers doing nothing.
Autovacuum deliberately runs slower than a manual VACUUM.
If you are running a version older than v12, the default value for
"autovacuum_vacuum_cost_delay" is 20ms, which makes autovacuum really slow. You should
change that parameter to 2ms, and if that is still too slow, use ALTER TABLE to set it
to 0 on the affected table. Then autovacuum will run as fast as manual VACUUM.
In addition, see that "maintenance_work_mem" is set high to make autovacuum fast.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: laurenz.albe@cybertec.at, frio_cervesa@hotmail.com
Subject: Re: vacuum visibility relevance
In-Reply-To: <28bb8938984b0d97f25f413d794efa782286a440.camel@cybertec.at>
References: <SN4P221MB0683F647B1C33B363A92FF37F786A@SN4P221MB0683.NAMP221.PROD.OUTLOOK.COM> <28bb8938984b0d97f25f413d794efa782286a440.camel@cybertec.at>
* 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