From: | Darafei "Komяpa" Praliaskouski <me(at)komzpa(dot)net> |
---|---|
To: | PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Berserk Autovacuum (let's save next Mandrill) |
Date: | 2019-03-27 20:54:57 |
Message-ID: | CAC8Q8t+j36G_bLF=+0iMo6jGNWnLnWb1tujXuJr-+x8ZCCTqoQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi hackers,
Attached is sketch of small patch that fixes several edge cases with
autovacuum. Long story short autovacuum never comes to append only tables,
killing large productions.
First case, mine.
We had a table we were appending and wanted Index Only Scan to work. For it
to work, you need to call VACUUM manually, since VACUUM is the only way to
mark pages all visible, and autovacuum never comes to append only tables.
We were clever to invent a workflow without dead tuples and it painfully
bit us.
Second case, just read in the news.
https://mailchimp.com/what-we-learned-from-the-recent-mandrill-outage/
Mandrill has 6TB append only table that autovacuum probably never vacuumed.
Then anti-wraparound came and production went down. If autovacuum did its
job before that last moment, it would probably be okay.
Idea: look not on dead tuples, but on changes, just like ANALYZE does.
It's my first patch on Postgres, it's probably all wrong but I hope it
helps you get the idea.
--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa
Attachment | Content-Type | Size |
---|---|---|
autovacuum_berserk_v1.patch | text/x-patch | 2.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Gavin Flower | 2019-03-27 20:57:41 | Re: PostgreSQL pollutes the file system |
Previous Message | Ants Aasma | 2019-03-27 20:51:16 | Re: Enable data checksums by default |