Search Postgresql Archives

Re: Automatic aggressive vacuum on almost frozen table takes too long

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux