|
Thank you so much Keith for sharing your opinion again.
I will have a discussion this afternoon with my superior.
And i will propose vacuum full as soon as possible, will follow your blog's tuning information and promote upgrade PG version.
De: Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx>
Enviado: terça-feira, 4 de agosto de 2020 13:34 Para: Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx> Cc: Guillaume Lelarge <guillaume@xxxxxxxxxxxx>; Ron <ronljohnsonjr@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx> Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows On Tue, Aug 4, 2020 at 11:46 AM Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx> wrote:
You stated in your opening email that you just had the default autovacuum settings in place. The defaults are often nowhere near sufficient for most production use cases. But everyone's production use case is different, so common settings that work with
the least issues are the default. If autovacuum is not running frequently enough, especially on larger tables with frequent updates, you will see exactly what happened here over time: autovacuum will not keep up with the write rate and take longer and longer
to run over time.
If autovacuum, or manual vacuums, are scheduled often enough to keep up with marking old rows as reusable space, then any new writes will use that empty space vs allocating additional pages and constantly growing your table's size. This includes indexes
as well. If you're adding NEW rows, of course it will continue to grow, but that is not autovacuum's problem. The trick is, for now, figuring out how often autovac will need to run to keep up with your write rate. You may need to adjust your cost settings
as well to ensure autovac runs a little more aggressively, at least on this table. The blog I just shared goes over how to figure out how to ensure autovacuum is run at least once per day based on a consistent avg row change per day. If you have occasions
where higher writes than normal are occurring, autovac may run more often in that case or you can even schedule a manual vacuum.
Thankfully you are on at least PG 9.6 as well, where vacuum has been made to run much more efficiently. Please just make sure you are on the latest minor release. Also, more recent major versions of PostgreSQL have improved upon this even more, so I would
highly suggest starting to plan for a major version upgrade. PG9.6 is scheduled to be EOL next year as well, so you want to be looking into that not just for vacuum efficiency.
If you get vacuum tuned properly, on a table that isn't as massively bloated as that one was, I think you will be fine.
|
