Adrian, thanks for pointing out the fix. We are just about to update to 11.18 next month.
Mikhael On Thu, 16 Feb 2023 at 23:44, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > 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.kla...@aklaver.com > >
