RE: How can i be certain autovacuum is causing reuse if table still grows - Mailing list pgsql-admin
| From | Sidney Aloisio Ferreira Pryor |
|---|---|
| Subject | RE: How can i be certain autovacuum is causing reuse if table still grows |
| Date | |
| Msg-id | CP2PR80MB070746BB714F54099B9CFE998C4A0@CP2PR80MB0707.lamprd80.prod.outlook.com Whole thread Raw |
| In response to | Re: How can i be certain autovacuum is causing reuse if table still grows (Keith Fiske <keith.fiske@crunchydata.com>) |
| Responses |
RE: How can i be certain autovacuum is causing reuse if table still grows
(Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>)
|
| List | pgsql-admin |
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@crunchydata.com>
Enviado: terça-feira, 4 de agosto de 2020 13:34
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
Enviado: terça-feira, 4 de agosto de 2020 13:34
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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@tjrj.jus.br> wrote:
Thank you very much Guillaume and Keith for your straight and quick answers.I was looking this database size and tup_update history.It never shrank even with autovacuum enabled (growing 100GB a month).The number of updates was always this high.But on last 2 months its size started to grow a lot (growing 50GB a day).We will schedule a vacuum full to stop adding disks.But i am truly afraid postgres is not suitable for this application.We are talkiing with dev team for the last three weeks.And so far no answer about optimizing the number of updates.It is true we have never executed a vacuum full on this database.But its size even growing was not so much to worry about.And if postgres was never able to follow the number of updates and reuse efficiently.I am afraid an autovacuum tuning may not be sufficente to reuse after we finish vacuum full and downsize the database from 4tb to 200gb.
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.
pgsql-admin by date: