On 2/15/23 22:57, Mikhail Balayan wrote:
> Hello,
>
> I have a big table in the actively working system, in which nothing is
> written for a long time, and nothing is read from it. Table size is 15GB
> (data only), indexes 150GB.
> Since the table does not change, after a while it crosses the
> autovacuum_freeze_max_age and an aggressive vacuum is triggered. And it
> would be OK, but vacuuming of the table takes a long time, despite the
> fact that exactly the same scan was made a few days before and almost
> all pages are marked as frozen, which is confirmed by data from the log:
> automatic aggressive vacuum of table
> "appdbname.appschemaname.applications": index scans: 1
> pages: 0 removed, 2013128 remain, 0 skipped due to pins,
> 2008230 skipped frozen
> tuples: 2120 removed, 32616340 remain, 0 are dead but not
> yet removable, oldest xmin: 4111875427
> buffer usage: 2005318781 hits, 19536511 misses, 23903 dirtied
> avg read rate: 4.621 MB/s, avg write rate: 0.006 MB/s
> system usage: CPU: user: 26398.27 s, system: 335.27 s,
> elapsed: 33029.00 s
>
> That is, if I understand it correctly, it says that there were (and
> actually are) 2013128 pages of which 2008230 were skipped, which leaves
> 4898 blocks to be scanned. I.e. it seems that the allocated 1GB
> (autovacuum_work_mem) should be enough to handle that amount of blocks
> and to avoid multiple scans of the indexes.
> But, based on buffer usage, one can see that a huge amount of data is
> read, greatly exceeding not only the number of remaining unfrozen
> blocks, but also the size of the table and indexes taken together: 2
> billion blocks, more than 15TB.
>
> Is this a bug in Postgresql or am I interpreting the log data wrong?
Not sure if this applies but from:
https://www.postgresql.org/docs/11/release-11-18.html
Release 11.18
Avoid long-term memory leakage in the autovacuum launcher process (Reid
Thompson)
The lack of field reports suggests that this problem is only latent in
pre-v15 branches; but it's not very clear why, so back-patch the fix anyway.
>
> Just in case, I'm using Postgresql version: 11.11.
Besides the above you are missing 8 releases of other fixes.
> autovacuum_vacuum_cost_delay: 2ms
> autovacuum_vacuum_cost_limit: 8000
>
> Thank you.
>
> BR,
> Mikhael
--
Adrian Klaver
adrian.klaver@aklaver.com
