pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow can i be certain autovacuum is causing reuse if table still grows
22+ messages / 6 participants
[nested] [flat]
* How can i be certain autovacuum is causing reuse if table still grows
@ 2020-07-27 20:08 Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-07-27 20:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi, we have a 9.6.5 postgres database with one table that is growing a lot both in length and in number of new lines.
Each day we have an average of table growing 30GB, indexes growing 15GB and 400.000 new lines.
Autovacuum is set with default parameters and is running on this table for days without naping or stopping.
flip=# select schemaname as table_schema,
flip-# relname as table_name,
flip-# pg_size_pretty(pg_total_relation_size(relid)) as total_size,
flip-# pg_size_pretty(pg_relation_size(relid)) as data_size,
flip-# pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
flip-# as external_size
flip-# from pg_catalog.pg_statio_user_tables
flip-# order by pg_total_relation_size(relid) desc,
flip-# pg_relation_size(relid) desc
flip-# limit 1;
table_schema | table_name | total_size | data_size | external_size
--------------+--------------------+------------+-----------+---------------
public | flip_pagina_edicao | 4072 GB | 2526 GB | 1546 GB
(1 row
flip=# SELECT pid,
flip-# Age(query_start, Clock_timestamp()),
flip-# usename,
flip-# query
flip-# FROM pg_stat_activity
flip-# WHERE query != '<IDLE>'
flip-# AND query ilike '%vacuum%'
flip-# ORDER BY query_start ASC;
pid | age | usename | query
------+--------------------------+----------+------------------------------------------------------------------------------
3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE public.flip_pagina_edicao (to prevent wraparound)
(1 rows)
There is a high number of updates each day but we are not able to be sure what is really going on.
We are assuming autovacuum is marking dead tuples for reuse but we still see table allocating a lot of more gbytes each day.
We see live tuple length, dead tuple length and free space all growing according to pgstattuple. So no reuse??
But according with pg_freespace the information is very different. So there is reuse??
flip=# SELECT * FROM pgstattuple('public.flip_pagina_edicao');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
---------------+-------------+-------------+---------------+------------------+----------------+--------------------+---------------+--------------
2713168764928 | 103935134 | 95056542505 | 3.5 | 61449232 | 37711185236 | 1.39 | 2546185255732 | 93.85
(1 row)
flip=# select pg_size_pretty(sum(avail)) from pg_freespace('flip_pagina_edicao');
pg_size_pretty
----------------
98 GB
(1 row)
We would appreciate some help to find what can be happening:
1) How can we be sure postgres is reusing if relation is still allocating a lot of new space?
2) Is vacuum achieving marking dead tuples even if it is not ending?
3) We killed a autovacuum execution to see if pgstattuple show some different information, but no changing on its output. Is there a more reliable query or function to map what is really going on?
Thank you.
Sidney Pryor.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
@ 2020-07-27 20:17 ` David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 1 reply; 22+ messages in thread
From: David G. Johnston @ 2020-07-27 20:17 UTC (permalink / raw)
To: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
wrote:
> Hi, we have a 9.6.5 postgres
>
> Autovacuum is set with default parameters and is running on this table for
> days without naping or stopping.
>
> 3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE
> public.flip_pagina_edicao (to prevent wraparound)
> (1 rows)
>
>
Its probably best avoid drawing conclusions here until the anti-wraparound
vacuum has completed and you’ve updated to the latest minor version of
PostgreSQL.
David J.
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
@ 2020-07-27 20:36 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:27 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 22+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-07-27 20:36 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you, David.
So can i assume that autovacuum only release dead tuples when it ends its execution?
Autovacuum is not finishing and is running for days.
We did not find a parameter to limit autovacuum executing time.
Do you have any recomendation?
Thank you.
Sidney.
________________________________
De: David G. Johnston <david.g.johnston@gmail.com>
Enviado: segunda-feira, 27 de julho de 2020 17:17
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: 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 Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>> wrote:
Hi, we have a 9.6.5 postgres
Autovacuum is set with default parameters and is running on this table for days without naping or stopping.
3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE public.flip_pagina_edicao (to prevent wraparound)
(1 rows)
Its probably best avoid drawing conclusions here until the anti-wraparound vacuum has completed and you’ve updated to the latest minor version of PostgreSQL.
David J.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
@ 2020-07-27 20:59 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
1 sibling, 1 reply; 22+ messages in thread
From: Keith Fiske @ 2020-07-27 20:59 UTC (permalink / raw)
To: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Mon, Jul 27, 2020 at 4:36 PM Sidney Aloisio Ferreira Pryor <
sidney@tjrj.jus.br> wrote:
> Thank you, David.
> So can i assume that autovacuum only release dead tuples when it ends its
> execution?
>
> Autovacuum is not finishing and is running for days.
> We did not find a parameter to limit autovacuum executing time.
> Do you have any recomendation?
>
> Thank you.
> Sidney.
> ------------------------------
> *De:* David G. Johnston <david.g.johnston@gmail.com>
> *Enviado:* segunda-feira, 27 de julho de 2020 17:17
> *Para:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
> *Cc:* 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 Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor <
> sidney@tjrj.jus.br> wrote:
>
> Hi, we have a 9.6.5 postgres
>
>
>
> Autovacuum is set with default parameters and is running on this table for
> days without naping or stopping.
>
> 3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE
> public.flip_pagina_edicao (to prevent wraparound)
> (1 rows)
>
>
> Its probably best avoid drawing conclusions here until the anti-wraparound
> vacuum has completed and you’ve updated to the latest minor version of
> PostgreSQL.
>
> David J.
>
>
Correct, the tuples will only be released once the autovacuum transaction
has committed.
If it's been running for days without being committed, I would recommend
cancelling it and immediately running a manual vacuum before it starts up
again. You'll have to be quick and double-check that your manual vacuum is
the one running and not getting blocked by the wraparound vac starting up
again. You can check this by looking at pg_stat_activity. You may see the
wraparound vacuum try and start up again as well, but it should be blocked
by yours and typically cancel out once yours finishes.
A manual vacuum runs with different, more aggressive settings than
autovacuum. So you should see this finish much sooner. May still take a few
hours, but it shouldn't be much longer. The biggest culprit with autovacuum
taking a long time on a given table, besides there being a lot of bloat, is
typically autovacuum_vacuum_cost_delay, which can cause autovacuum to pause
if autovacuum_vacuum_cost_limit is reached. With a manual vacuum, this
delay is disabled (it uses vaccum_cost_delay which is by default 0). You
can see more in this section of the documentation -
https://www.postgresql.org/docs/9.6/runtime-config-autovacuum.html
You may want to adjust the autovac cost delay (lower it from 2 to 1 maybe).
I believe you can do this on a per-table basis vs changing it for the
entire database. I would not recommend completely disabling the delay until
you at least get your system fully vacuumed and in a better state. I'd also
recommend some more aggressive vacuuming be configured for your given table
as well, so it doesn't accumulate so many dead rows before vacuum is able
to run. I wrote about how to do this here -
https://www.keithf4.com/per-table-autovacuum-tuning/
After it finishes running, in addition to upgrading to the latest version
of 9.6, I would recommend checking the table & indexes for bloat. You can
use the pgstattuple contrib module or this script which makes it a little
more user-friendly https://github.com/keithf4/pg_bloat_check
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
@ 2020-07-27 21:35 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 1 reply; 22+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-07-27 21:35 UTC (permalink / raw)
To: Keith Fiske <keith.fiske@crunchydata.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you so much Keith.
We will try manual vacuum as you suggested.
I read something like this but did not give the right attention to it.
About pgstattuple was a bit confusing its free area information when we compared to pg_freespace.
Can you please explain this difference?
________________________________
De: Keith Fiske <keith.fiske@crunchydata.com>
Enviado: segunda-feira, 27 de julho de 2020 17:59
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: David G. Johnston <david.g.johnston@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 Mon, Jul 27, 2020 at 4:36 PM Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>> wrote:
Thank you, David.
So can i assume that autovacuum only release dead tuples when it ends its execution?
Autovacuum is not finishing and is running for days.
We did not find a parameter to limit autovacuum executing time.
Do you have any recomendation?
Thank you.
Sidney.
________________________________
De: David G. Johnston <david.g.johnston@gmail.com<mailto:david.g.johnston@gmail.com>>
Enviado: segunda-feira, 27 de julho de 2020 17:17
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>>
Cc: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
On Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>> wrote:
Hi, we have a 9.6.5 postgres
Autovacuum is set with default parameters and is running on this table for days without naping or stopping.
3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE public.flip_pagina_edicao (to prevent wraparound)
(1 rows)
Its probably best avoid drawing conclusions here until the anti-wraparound vacuum has completed and you’ve updated to the latest minor version of PostgreSQL.
David J.
Correct, the tuples will only be released once the autovacuum transaction has committed.
If it's been running for days without being committed, I would recommend cancelling it and immediately running a manual vacuum before it starts up again. You'll have to be quick and double-check that your manual vacuum is the one running and not getting blocked by the wraparound vac starting up again. You can check this by looking at pg_stat_activity. You may see the wraparound vacuum try and start up again as well, but it should be blocked by yours and typically cancel out once yours finishes.
A manual vacuum runs with different, more aggressive settings than autovacuum. So you should see this finish much sooner. May still take a few hours, but it shouldn't be much longer. The biggest culprit with autovacuum taking a long time on a given table, besides there being a lot of bloat, is typically autovacuum_vacuum_cost_delay, which can cause autovacuum to pause if autovacuum_vacuum_cost_limit is reached. With a manual vacuum, this delay is disabled (it uses vaccum_cost_delay which is by default 0). You can see more in this section of the documentation - https://www.postgresql.org/docs/9.6/runtime-config-autovacuum.html<https://nam10.safelinks.protec...;
You may want to adjust the autovac cost delay (lower it from 2 to 1 maybe). I believe you can do this on a per-table basis vs changing it for the entire database. I would not recommend completely disabling the delay until you at least get your system fully vacuumed and in a better state. I'd also recommend some more aggressive vacuuming be configured for your given table as well, so it doesn't accumulate so many dead rows before vacuum is able to run. I wrote about how to do this here - https://www.keithf4.com/per-table-autovacuum-tuning/<https://nam10.safelinks.protection.outlook.c...;
After it finishes running, in addition to upgrading to the latest version of 9.6, I would recommend checking the table & indexes for bloat. You can use the pgstattuple contrib module or this script which makes it a little more user-friendly https://github.com/keithf4/pg_bloat_check<https://nam10.safelinks.protection.outlook.com/?url=htt...;
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com<https://nam10.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcrunchyda...;
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
@ 2020-07-27 22:19 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:21 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 03:39 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 22+ messages in thread
From: Keith Fiske @ 2020-07-27 22:19 UTC (permalink / raw)
To: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
As the documentation for pg_freespacemap says, it's values are not exact
and they may not be up to date because you are viewing the statistics that
were gathered during the last analyze, not the actual freespace map
https://www.postgresql.org/docs/9.6/pgfreespacemap.html
When you run pgstattuple, it is actually scanning the target object. Also
note that scanning a table does not also scan its indexes, those must be
done individually. So the free space reported by pgstattuple should be the
actual free space, given as both byte size and % of the whole object.
pg_freespacemap is used by the planner and several other internal parts of
PG, so checking it must be quick. pgstattuple can take a while to run on
larger objects, but it will give you 100% accurate results as of the time
it ran. This is why I prefer it when checking for bloat since many of the
queries you see out there also try and base their results on stats. While
the query runs quick, I've often seen them be wildly wrong.
Also, I would not recommend partitioning simply to improve vacuuming.
Especially if extensive tuning hasn't been tried first. Most times you can
get per-table tuning working well enough to get autovacuum running
properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
itself been improved.
On Mon, Jul 27, 2020 at 5:35 PM Sidney Aloisio Ferreira Pryor <
sidney@tjrj.jus.br> wrote:
> Thank you so much Keith.
> We will try manual vacuum as you suggested.
> I read something like this but did not give the right attention to it.
>
>
> About pgstattuple was a bit confusing its free area information when we
> compared to pg_freespace.
> Can you please explain this difference?
> ------------------------------
> *De:* Keith Fiske <keith.fiske@crunchydata.com>
> *Enviado:* segunda-feira, 27 de julho de 2020 17:59
> *Para:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
> *Cc:* David G. Johnston <david.g.johnston@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 Mon, Jul 27, 2020 at 4:36 PM Sidney Aloisio Ferreira Pryor <
> sidney@tjrj.jus.br> wrote:
>
> Thank you, David.
> So can i assume that autovacuum only release dead tuples when it ends its
> execution?
>
> Autovacuum is not finishing and is running for days.
> We did not find a parameter to limit autovacuum executing time.
> Do you have any recomendation?
>
> Thank you.
> Sidney.
> ------------------------------
> *De:* David G. Johnston <david.g.johnston@gmail.com>
> *Enviado:* segunda-feira, 27 de julho de 2020 17:17
> *Para:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
> *Cc:* 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 Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor <
> sidney@tjrj.jus.br> wrote:
>
> Hi, we have a 9.6.5 postgres
>
>
>
> Autovacuum is set with default parameters and is running on this table for
> days without naping or stopping.
>
> 3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE
> public.flip_pagina_edicao (to prevent wraparound)
> (1 rows)
>
>
> Its probably best avoid drawing conclusions here until the anti-wraparound
> vacuum has completed and you’ve updated to the latest minor version of
> PostgreSQL.
>
> David J.
>
>
>
> Correct, the tuples will only be released once the autovacuum transaction
> has committed.
>
> If it's been running for days without being committed, I would recommend
> cancelling it and immediately running a manual vacuum before it starts up
> again. You'll have to be quick and double-check that your manual vacuum is
> the one running and not getting blocked by the wraparound vac starting up
> again. You can check this by looking at pg_stat_activity. You may see the
> wraparound vacuum try and start up again as well, but it should be blocked
> by yours and typically cancel out once yours finishes.
>
> A manual vacuum runs with different, more aggressive settings than
> autovacuum. So you should see this finish much sooner. May still take a few
> hours, but it shouldn't be much longer. The biggest culprit with autovacuum
> taking a long time on a given table, besides there being a lot of bloat, is
> typically autovacuum_vacuum_cost_delay, which can cause autovacuum to pause
> if autovacuum_vacuum_cost_limit is reached. With a manual vacuum, this
> delay is disabled (it uses vaccum_cost_delay which is by default 0). You
> can see more in this section of the documentation -
> https://www.postgresql.org/docs/9.6/runtime-config-autovacuum.html
> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F9.6%2F...;
>
> You may want to adjust the autovac cost delay (lower it from 2 to 1
> maybe). I believe you can do this on a per-table basis vs changing it for
> the entire database. I would not recommend completely disabling the delay
> until you at least get your system fully vacuumed and in a better state.
> I'd also recommend some more aggressive vacuuming be configured for your
> given table as well, so it doesn't accumulate so many dead rows before
> vacuum is able to run. I wrote about how to do this here -
> https://www.keithf4.com/per-table-autovacuum-tuning/
> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.keithf4.com%2Fper-table-autova...;
>
> After it finishes running, in addition to upgrading to the latest version
> of 9.6, I would recommend checking the table & indexes for bloat. You can
> use the pgstattuple contrib module or this script which makes it a little
> more user-friendly https://github.com/keithf4/pg_bloat_check
> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkeithf4%2Fpg_bloat_ch...;
>
> --
> Keith Fiske
> Senior Database Engineer
> Crunchy Data - http://crunchydata.com
> <https://nam10.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcrunchydata.com%2F&data=02%7C01...;
>
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
@ 2020-07-27 22:21 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:48 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
1 sibling, 1 reply; 22+ messages in thread
From: Keith Fiske @ 2020-07-27 22:21 UTC (permalink / raw)
To: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Mon, Jul 27, 2020 at 6:19 PM Keith Fiske <keith.fiske@crunchydata.com>
wrote:
> As the documentation for pg_freespacemap says, it's values are not exact
> and they may not be up to date because you are viewing the statistics that
> were gathered during the last analyze, not the actual freespace map
>
Sorry, just wanted to clarify this. You are looking at the actual freespace
map. It's that the values actually stored in it are not exact and based off
of statistics gathering
>
> https://www.postgresql.org/docs/9.6/pgfreespacemap.html
>
> When you run pgstattuple, it is actually scanning the target object. Also
> note that scanning a table does not also scan its indexes, those must be
> done individually. So the free space reported by pgstattuple should be the
> actual free space, given as both byte size and % of the whole object.
>
> pg_freespacemap is used by the planner and several other internal parts of
> PG, so checking it must be quick. pgstattuple can take a while to run on
> larger objects, but it will give you 100% accurate results as of the time
> it ran. This is why I prefer it when checking for bloat since many of the
> queries you see out there also try and base their results on stats. While
> the query runs quick, I've often seen them be wildly wrong.
>
> Also, I would not recommend partitioning simply to improve vacuuming.
> Especially if extensive tuning hasn't been tried first. Most times you can
> get per-table tuning working well enough to get autovacuum running
> properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
> itself been improved.
>
>
>
> On Mon, Jul 27, 2020 at 5:35 PM Sidney Aloisio Ferreira Pryor <
> sidney@tjrj.jus.br> wrote:
>
>> Thank you so much Keith.
>> We will try manual vacuum as you suggested.
>> I read something like this but did not give the right attention to it.
>>
>>
>> About pgstattuple was a bit confusing its free area information when we
>> compared to pg_freespace.
>> Can you please explain this difference?
>> ------------------------------
>> *De:* Keith Fiske <keith.fiske@crunchydata.com>
>> *Enviado:* segunda-feira, 27 de julho de 2020 17:59
>> *Para:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
>> *Cc:* David G. Johnston <david.g.johnston@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 Mon, Jul 27, 2020 at 4:36 PM Sidney Aloisio Ferreira Pryor <
>> sidney@tjrj.jus.br> wrote:
>>
>> Thank you, David.
>> So can i assume that autovacuum only release dead tuples when it ends its
>> execution?
>>
>> Autovacuum is not finishing and is running for days.
>> We did not find a parameter to limit autovacuum executing time.
>> Do you have any recomendation?
>>
>> Thank you.
>> Sidney.
>> ------------------------------
>> *De:* David G. Johnston <david.g.johnston@gmail.com>
>> *Enviado:* segunda-feira, 27 de julho de 2020 17:17
>> *Para:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
>> *Cc:* 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 Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor <
>> sidney@tjrj.jus.br> wrote:
>>
>> Hi, we have a 9.6.5 postgres
>>
>>
>>
>> Autovacuum is set with default parameters and is running on this table
>> for days without naping or stopping.
>>
>> 3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE
>> public.flip_pagina_edicao (to prevent wraparound)
>> (1 rows)
>>
>>
>> Its probably best avoid drawing conclusions here until the
>> anti-wraparound vacuum has completed and you’ve updated to the latest minor
>> version of PostgreSQL.
>>
>> David J.
>>
>>
>>
>> Correct, the tuples will only be released once the autovacuum transaction
>> has committed.
>>
>> If it's been running for days without being committed, I would recommend
>> cancelling it and immediately running a manual vacuum before it starts up
>> again. You'll have to be quick and double-check that your manual vacuum is
>> the one running and not getting blocked by the wraparound vac starting up
>> again. You can check this by looking at pg_stat_activity. You may see the
>> wraparound vacuum try and start up again as well, but it should be blocked
>> by yours and typically cancel out once yours finishes.
>>
>> A manual vacuum runs with different, more aggressive settings than
>> autovacuum. So you should see this finish much sooner. May still take a few
>> hours, but it shouldn't be much longer. The biggest culprit with autovacuum
>> taking a long time on a given table, besides there being a lot of bloat, is
>> typically autovacuum_vacuum_cost_delay, which can cause autovacuum to pause
>> if autovacuum_vacuum_cost_limit is reached. With a manual vacuum, this
>> delay is disabled (it uses vaccum_cost_delay which is by default 0). You
>> can see more in this section of the documentation -
>> https://www.postgresql.org/docs/9.6/runtime-config-autovacuum.html
>> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F9.6%2F...;
>>
>> You may want to adjust the autovac cost delay (lower it from 2 to 1
>> maybe). I believe you can do this on a per-table basis vs changing it for
>> the entire database. I would not recommend completely disabling the delay
>> until you at least get your system fully vacuumed and in a better state.
>> I'd also recommend some more aggressive vacuuming be configured for your
>> given table as well, so it doesn't accumulate so many dead rows before
>> vacuum is able to run. I wrote about how to do this here -
>> https://www.keithf4.com/per-table-autovacuum-tuning/
>> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.keithf4.com%2Fper-table-autova...;
>>
>> After it finishes running, in addition to upgrading to the latest version
>> of 9.6, I would recommend checking the table & indexes for bloat. You can
>> use the pgstattuple contrib module or this script which makes it a little
>> more user-friendly https://github.com/keithf4/pg_bloat_check
>> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkeithf4%2Fpg_bloat_ch...;
>>
>> --
>> Keith Fiske
>> Senior Database Engineer
>> Crunchy Data - http://crunchydata.com
>> <https://nam10.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcrunchydata.com%2F&data=02%7C01...;
>>
>
>
> --
> Keith Fiske
> Senior Database Engineer
> Crunchy Data - http://crunchydata.com
>
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:21 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
@ 2020-07-27 22:48 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 23:55 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 1 reply; 22+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-07-27 22:48 UTC (permalink / raw)
To: Keith Fiske <keith.fiske@crunchydata.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you for the explanation Keith.
Pgstattuple runs for 8 hours approximately before printing its result.
and pg_freespace runs for "only" 16 minutes so we presumed really that pgstattuple scans the relation and brings more accurate information.
The strange fact is that pg_freespace result is changing from day to day, even without vacuum or analyze finishing.
At day one we start monitoring it was saying it has 108GB and kept changing each day.
And the worse fact is the different values for free space: pgstattupple with 2.3TB free VS pg_freespace with 96GB.
But i just killed autovacuum and started a manual vacuum verbose.
and 20 seconds later a autovacuum started.
it seems they are both running together.
Both sessions gets row exclusive locks.
I think it is better disabling autovacuum, do you agree?
________________________________
De: Keith Fiske <keith.fiske@crunchydata.com>
Enviado: segunda-feira, 27 de julho de 2020 19:21
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: David G. Johnston <david.g.johnston@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 Mon, Jul 27, 2020 at 6:19 PM Keith Fiske <keith.fiske@crunchydata.com<mailto:keith.fiske@crunchydata.com>> wrote:
As the documentation for pg_freespacemap says, it's values are not exact and they may not be up to date because you are viewing the statistics that were gathered during the last analyze, not the actual freespace map
Sorry, just wanted to clarify this. You are looking at the actual freespace map. It's that the values actually stored in it are not exact and based off of statistics gathering
https://www.postgresql.org/docs/9.6/pgfreespacemap.html<https://nam10.safelinks.protection.outloo...;
When you run pgstattuple, it is actually scanning the target object. Also note that scanning a table does not also scan its indexes, those must be done individually. So the free space reported by pgstattuple should be the actual free space, given as both byte size and % of the whole object.
pg_freespacemap is used by the planner and several other internal parts of PG, so checking it must be quick. pgstattuple can take a while to run on larger objects, but it will give you 100% accurate results as of the time it ran. This is why I prefer it when checking for bloat since many of the queries you see out there also try and base their results on stats. While the query runs quick, I've often seen them be wildly wrong.
Also, I would not recommend partitioning simply to improve vacuuming. Especially if extensive tuning hasn't been tried first. Most times you can get per-table tuning working well enough to get autovacuum running properly. Especially on 9.6 and even more-so on PG11, where autovacuum has itself been improved.
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com<https://nam10.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcrunchyda...;
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:21 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:48 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
@ 2020-07-27 23:55 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 00:17 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 1 reply; 22+ messages in thread
From: Keith Fiske @ 2020-07-27 23:55 UTC (permalink / raw)
To: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
What were the results of pgstattuple? Curious what the live tuple_* and
dead_tuple* values vs the free space were. Also note that if you just ran
it on the table, that is the results for JUST the table. If there are
indexes as well, you'd have to check those separately and they may be even
worse (index bloat is often worse than table). The pg_bloat_check script I
linked, when run against a single table, will run against the table and all
b-tree indexes (it cannot check gin/gist).
Unfortunately, since this is a wraparound vacuum that is starting,
disabling autovacuum will not work. Wraparound vacuums run even when
autovac is disabled in order to try and keep you from getting into
transaction exhaustion territory which means you must take the database
offline to fix things and avoid data corruption. As I said autovac will
attempt to run again, even if you start yours. And both will attempt to get
the lock, but only one will actually succeed in getting it. Check the
wait_event_type and wait_event column in pg_stat_activity to determine
which one actually obtained the lock and you want to make sure yours is the
one that did. Can check the documentation for the meanings of those columns
https://www.postgresql.org/docs/9.6/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
In all honesty, though, if this table is as bloated as it seems, you may
want to just try and schedule an outage to run a VACUUM FULL on this table
and get it fully cleaned up. 2.3TB of empty space in just the table is
quite a lot and each index may be just as bad. Until regular vacuums can
efficiently run on this table it's only going to get progressively worse
and just keep taking longer and longer. I would also highly suggest getting
on the latest version of 9.6 before doing so that you're sure all bug fixes
are in place.
An outage now may be costly, but it will be nowhere near as costly as
autovacuum never being able to run on this table and reaching transaction
exhaustion.
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
On Mon, Jul 27, 2020 at 6:49 PM Sidney Aloisio Ferreira Pryor <
sidney@tjrj.jus.br> wrote:
> Thank you for the explanation Keith.
>
> Pgstattuple runs for 8 hours approximately before printing its result.
> and pg_freespace runs for "only" 16 minutes so we presumed really that
> pgstattuple scans the relation and brings more accurate information.
>
> The strange fact is that pg_freespace result is changing from day to day,
> even without vacuum or analyze finishing.
> At day one we start monitoring it was saying it has 108GB and kept
> changing each day.
> And the worse fact is the different values for free space: pgstattupple
> with 2.3TB free VS pg_freespace with 96GB.
>
> But i just killed autovacuum and started a manual vacuum verbose.
> and 20 seconds later a autovacuum started.
> it seems they are both running together.
>
> Both sessions gets row exclusive locks.
> I think it is better disabling autovacuum, do you agree?
>
>
> ------------------------------
> *De:* Keith Fiske <keith.fiske@crunchydata.com>
> *Enviado:* segunda-feira, 27 de julho de 2020 19:21
> *Para:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
> *Cc:* David G. Johnston <david.g.johnston@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 Mon, Jul 27, 2020 at 6:19 PM Keith Fiske <keith.fiske@crunchydata.com>
> wrote:
>
> As the documentation for pg_freespacemap says, it's values are not exact
> and they may not be up to date because you are viewing the statistics that
> were gathered during the last analyze, not the actual freespace map
>
>
>
> Sorry, just wanted to clarify this. You are looking at the actual
> freespace map. It's that the values actually stored in it are not exact and
> based off of statistics gathering
>
>
>
>
> https://www.postgresql.org/docs/9.6/pgfreespacemap.html
> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F9.6%2F...;
>
> When you run pgstattuple, it is actually scanning the target object. Also
> note that scanning a table does not also scan its indexes, those must be
> done individually. So the free space reported by pgstattuple should be the
> actual free space, given as both byte size and % of the whole object.
>
> pg_freespacemap is used by the planner and several other internal parts of
> PG, so checking it must be quick. pgstattuple can take a while to run on
> larger objects, but it will give you 100% accurate results as of the time
> it ran. This is why I prefer it when checking for bloat since many of the
> queries you see out there also try and base their results on stats. While
> the query runs quick, I've often seen them be wildly wrong.
>
> Also, I would not recommend partitioning simply to improve vacuuming.
> Especially if extensive tuning hasn't been tried first. Most times you can
> get per-table tuning working well enough to get autovacuum running
> properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
> itself been improved.
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:21 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:48 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 23:55 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
@ 2020-07-28 00:17 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 00:49 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith <keith@keithf4.com>
0 siblings, 1 reply; 22+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-07-28 00:17 UTC (permalink / raw)
To: Keith Fiske <keith.fiske@crunchydata.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you for the explanation Keith.
We will really improve our minor release update policy.
We have few but important postgres database here.
I guess an outage would really be better.
Index bloat is shorter than table bloat but it is present too.
So far manual vacuum 3 of 8 indexes.
An alter table to disable autovacuum and an autovacuum are both waiting for manual vacuum verbose.
flip=# SELECT pid,
flip-# Age(query_start, Clock_timestamp()),
flip-# usename,
flip-# query,
flip-# state,
flip-# wait_event
flip-# FROM pg_stat_activity
flip-# WHERE query != '<IDLE>'
flip-# AND query ilike '%vacuum%'
flip-# ORDER BY query_start ASC;
pid | age | usename | query | state | wait_event
-------+------------------+----------+------------------------------------------------------------------------------+--------+------------
25849 | -01:59:22.309787 | postgres | vacuum verbose flip_pagina_edicao; | active |
22317 | -00:55:09.848074 | postgres | ALTER TABLE flip_pagina_edicao SET (autovacuum_enabled = false); | active | relation
27718 | -00:53:00.368776 | postgres | autovacuum: VACUUM ANALYZE public.flip_pagina_edicao (to prevent wraparound) | active | relation
I am truly optimist it will solve.
So far no user session is locked or waiting.
________________________________
De: Keith Fiske <keith.fiske@crunchydata.com>
Enviado: segunda-feira, 27 de julho de 2020 20:55
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: David G. Johnston <david.g.johnston@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
What were the results of pgstattuple? Curious what the live tuple_* and dead_tuple* values vs the free space were. Also note that if you just ran it on the table, that is the results for JUST the table. If there are indexes as well, you'd have to check those separately and they may be even worse (index bloat is often worse than table). The pg_bloat_check script I linked, when run against a single table, will run against the table and all b-tree indexes (it cannot check gin/gist).
Unfortunately, since this is a wraparound vacuum that is starting, disabling autovacuum will not work. Wraparound vacuums run even when autovac is disabled in order to try and keep you from getting into transaction exhaustion territory which means you must take the database offline to fix things and avoid data corruption. As I said autovac will attempt to run again, even if you start yours. And both will attempt to get the lock, but only one will actually succeed in getting it. Check the wait_event_type and wait_event column in pg_stat_activity to determine which one actually obtained the lock and you want to make sure yours is the one that did. Can check the documentation for the meanings of those columns https://www.postgresql.org/docs/9.6/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW<https://nam10.saf...;
In all honesty, though, if this table is as bloated as it seems, you may want to just try and schedule an outage to run a VACUUM FULL on this table and get it fully cleaned up. 2.3TB of empty space in just the table is quite a lot and each index may be just as bad. Until regular vacuums can efficiently run on this table it's only going to get progressively worse and just keep taking longer and longer. I would also highly suggest getting on the latest version of 9.6 before doing so that you're sure all bug fixes are in place.
An outage now may be costly, but it will be nowhere near as costly as autovacuum never being able to run on this table and reaching transaction exhaustion.
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com<https://nam10.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcrunchyda...;
On Mon, Jul 27, 2020 at 6:49 PM Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>> wrote:
Thank you for the explanation Keith.
Pgstattuple runs for 8 hours approximately before printing its result.
and pg_freespace runs for "only" 16 minutes so we presumed really that pgstattuple scans the relation and brings more accurate information.
The strange fact is that pg_freespace result is changing from day to day, even without vacuum or analyze finishing.
At day one we start monitoring it was saying it has 108GB and kept changing each day.
And the worse fact is the different values for free space: pgstattupple with 2.3TB free VS pg_freespace with 96GB.
But i just killed autovacuum and started a manual vacuum verbose.
and 20 seconds later a autovacuum started.
it seems they are both running together.
Both sessions gets row exclusive locks.
I think it is better disabling autovacuum, do you agree?
________________________________
De: Keith Fiske <keith.fiske@crunchydata.com<mailto:keith.fiske@crunchydata.com>>
Enviado: segunda-feira, 27 de julho de 2020 19:21
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>>
Cc: David G. Johnston <david.g.johnston@gmail.com<mailto:david.g.johnston@gmail.com>>; pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
On Mon, Jul 27, 2020 at 6:19 PM Keith Fiske <keith.fiske@crunchydata.com<mailto:keith.fiske@crunchydata.com>> wrote:
As the documentation for pg_freespacemap says, it's values are not exact and they may not be up to date because you are viewing the statistics that were gathered during the last analyze, not the actual freespace map
Sorry, just wanted to clarify this. You are looking at the actual freespace map. It's that the values actually stored in it are not exact and based off of statistics gathering
https://www.postgresql.org/docs/9.6/pgfreespacemap.html<https://nam10.safelinks.protection.outloo...;
When you run pgstattuple, it is actually scanning the target object. Also note that scanning a table does not also scan its indexes, those must be done individually. So the free space reported by pgstattuple should be the actual free space, given as both byte size and % of the whole object.
pg_freespacemap is used by the planner and several other internal parts of PG, so checking it must be quick. pgstattuple can take a while to run on larger objects, but it will give you 100% accurate results as of the time it ran. This is why I prefer it when checking for bloat since many of the queries you see out there also try and base their results on stats. While the query runs quick, I've often seen them be wildly wrong.
Also, I would not recommend partitioning simply to improve vacuuming. Especially if extensive tuning hasn't been tried first. Most times you can get per-table tuning working well enough to get autovacuum running properly. Especially on 9.6 and even more-so on PG11, where autovacuum has itself been improved.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:21 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:48 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 23:55 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 00:17 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
@ 2020-07-28 00:49 ` Keith <keith@keithf4.com>
2020-07-28 01:42 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 1 reply; 22+ messages in thread
From: Keith @ 2020-07-28 00:49 UTC (permalink / raw)
To: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; +Cc: Keith Fiske <keith.fiske@crunchydata.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
I would cancel that alter table to disable autovacuum. It's not going to go
through until you manual vacuum finishes and at that point you don't want
it to be disabled anymore.
On Mon, Jul 27, 2020 at 8:18 PM Sidney Aloisio Ferreira Pryor <
sidney@tjrj.jus.br> wrote:
> Thank you for the explanation Keith.
> We will really improve our minor release update policy.
> We have few but important postgres database here.
>
> I guess an outage would really be better.
> Index bloat is shorter than table bloat but it is present too.
>
> So far manual vacuum 3 of 8 indexes.
> An alter table to disable autovacuum and an autovacuum are both waiting
> for manual vacuum verbose.
>
> flip=# SELECT pid,
> flip-# Age(query_start, Clock_timestamp()),
> flip-# usename,
> flip-# query,
> flip-# state,
> flip-# wait_event
> flip-# FROM pg_stat_activity
> flip-# WHERE query != '<IDLE>'
> flip-# AND query ilike '%vacuum%'
> flip-# ORDER BY query_start ASC;
> pid | age | usename |
> query | state | wait_event
>
> -------+------------------+----------+------------------------------------------------------------------------------+--------+------------
> 25849 | -01:59:22.309787 | postgres | vacuum verbose flip_pagina_edicao;
> | active |
> 22317 | -00:55:09.848074 | postgres | ALTER TABLE flip_pagina_edicao SET
> (autovacuum_enabled = false); | active | relation
> 27718 | -00:53:00.368776 | postgres | autovacuum: VACUUM ANALYZE
> public.flip_pagina_edicao (to prevent wraparound) | active | relation
>
>
> I am truly optimist it will solve.
> So far no user session is locked or waiting.
>
>
> ------------------------------
> *De:* Keith Fiske <keith.fiske@crunchydata.com>
> *Enviado:* segunda-feira, 27 de julho de 2020 20:55
> *Para:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
> *Cc:* David G. Johnston <david.g.johnston@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
>
> What were the results of pgstattuple? Curious what the live tuple_* and
> dead_tuple* values vs the free space were. Also note that if you just ran
> it on the table, that is the results for JUST the table. If there are
> indexes as well, you'd have to check those separately and they may be even
> worse (index bloat is often worse than table). The pg_bloat_check script I
> linked, when run against a single table, will run against the table and all
> b-tree indexes (it cannot check gin/gist).
>
> Unfortunately, since this is a wraparound vacuum that is starting,
> disabling autovacuum will not work. Wraparound vacuums run even when
> autovac is disabled in order to try and keep you from getting into
> transaction exhaustion territory which means you must take the database
> offline to fix things and avoid data corruption. As I said autovac will
> attempt to run again, even if you start yours. And both will attempt to get
> the lock, but only one will actually succeed in getting it. Check the
> wait_event_type and wait_event column in pg_stat_activity to determine
> which one actually obtained the lock and you want to make sure yours is the
> one that did. Can check the documentation for the meanings of those columns
> https://www.postgresql.org/docs/9.6/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F9.6%2F...;
>
> In all honesty, though, if this table is as bloated as it seems, you may
> want to just try and schedule an outage to run a VACUUM FULL on this table
> and get it fully cleaned up. 2.3TB of empty space in just the table is
> quite a lot and each index may be just as bad. Until regular vacuums can
> efficiently run on this table it's only going to get progressively worse
> and just keep taking longer and longer. I would also highly suggest getting
> on the latest version of 9.6 before doing so that you're sure all bug fixes
> are in place.
>
> An outage now may be costly, but it will be nowhere near as costly as
> autovacuum never being able to run on this table and reaching transaction
> exhaustion.
>
> --
> Keith Fiske
> Senior Database Engineer
> Crunchy Data - http://crunchydata.com
> <https://nam10.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcrunchydata.com%2F&data=02%7C01...;
>
>
> On Mon, Jul 27, 2020 at 6:49 PM Sidney Aloisio Ferreira Pryor <
> sidney@tjrj.jus.br> wrote:
>
> Thank you for the explanation Keith.
>
> Pgstattuple runs for 8 hours approximately before printing its result.
> and pg_freespace runs for "only" 16 minutes so we presumed really that
> pgstattuple scans the relation and brings more accurate information.
>
> The strange fact is that pg_freespace result is changing from day to day,
> even without vacuum or analyze finishing.
> At day one we start monitoring it was saying it has 108GB and kept
> changing each day.
> And the worse fact is the different values for free space: pgstattupple
> with 2.3TB free VS pg_freespace with 96GB.
>
> But i just killed autovacuum and started a manual vacuum verbose.
> and 20 seconds later a autovacuum started.
> it seems they are both running together.
>
> Both sessions gets row exclusive locks.
> I think it is better disabling autovacuum, do you agree?
>
>
> ------------------------------
> *De:* Keith Fiske <keith.fiske@crunchydata.com>
> *Enviado:* segunda-feira, 27 de julho de 2020 19:21
> *Para:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
> *Cc:* David G. Johnston <david.g.johnston@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 Mon, Jul 27, 2020 at 6:19 PM Keith Fiske <keith.fiske@crunchydata.com>
> wrote:
>
> As the documentation for pg_freespacemap says, it's values are not exact
> and they may not be up to date because you are viewing the statistics that
> were gathered during the last analyze, not the actual freespace map
>
>
>
> Sorry, just wanted to clarify this. You are looking at the actual
> freespace map. It's that the values actually stored in it are not exact and
> based off of statistics gathering
>
>
>
>
> https://www.postgresql.org/docs/9.6/pgfreespacemap.html
> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F9.6%2F...;
>
> When you run pgstattuple, it is actually scanning the target object. Also
> note that scanning a table does not also scan its indexes, those must be
> done individually. So the free space reported by pgstattuple should be the
> actual free space, given as both byte size and % of the whole object.
>
> pg_freespacemap is used by the planner and several other internal parts of
> PG, so checking it must be quick. pgstattuple can take a while to run on
> larger objects, but it will give you 100% accurate results as of the time
> it ran. This is why I prefer it when checking for bloat since many of the
> queries you see out there also try and base their results on stats. While
> the query runs quick, I've often seen them be wildly wrong.
>
> Also, I would not recommend partitioning simply to improve vacuuming.
> Especially if extensive tuning hasn't been tried first. Most times you can
> get per-table tuning working well enough to get autovacuum running
> properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
> itself been improved.
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:21 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:48 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 23:55 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 00:17 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 00:49 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith <keith@keithf4.com>
@ 2020-07-28 01:42 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 0 replies; 22+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-07-28 01:42 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: Keith Fiske <keith.fiske@crunchydata.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Yes, it is true.
But i think it would be better to disable it just after manual vacuum so i can tune cost_delay of autovacuum to make it more agressive/tolerant and not last for days anymore in the future.
________________________________
De: Keith <keith@keithf4.com>
Enviado: segunda-feira, 27 de julho de 2020 21:49
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: Keith Fiske <keith.fiske@crunchydata.com>; David G. Johnston <david.g.johnston@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
I would cancel that alter table to disable autovacuum. It's not going to go through until you manual vacuum finishes and at that point you don't want it to be disabled anymore.
On Mon, Jul 27, 2020 at 8:18 PM Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>> wrote:
Thank you for the explanation Keith.
We will really improve our minor release update policy.
We have few but important postgres database here.
I guess an outage would really be better.
Index bloat is shorter than table bloat but it is present too.
So far manual vacuum 3 of 8 indexes.
An alter table to disable autovacuum and an autovacuum are both waiting for manual vacuum verbose.
flip=# SELECT pid,
flip-# Age(query_start, Clock_timestamp()),
flip-# usename,
flip-# query,
flip-# state,
flip-# wait_event
flip-# FROM pg_stat_activity
flip-# WHERE query != '<IDLE>'
flip-# AND query ilike '%vacuum%'
flip-# ORDER BY query_start ASC;
pid | age | usename | query | state | wait_event
-------+------------------+----------+------------------------------------------------------------------------------+--------+------------
25849 | -01:59:22.309787 | postgres | vacuum verbose flip_pagina_edicao; | active |
22317 | -00:55:09.848074 | postgres | ALTER TABLE flip_pagina_edicao SET (autovacuum_enabled = false); | active | relation
27718 | -00:53:00.368776 | postgres | autovacuum: VACUUM ANALYZE public.flip_pagina_edicao (to prevent wraparound) | active | relation
I am truly optimist it will solve.
So far no user session is locked or waiting.
________________________________
De: Keith Fiske <keith.fiske@crunchydata.com<mailto:keith.fiske@crunchydata.com>>
Enviado: segunda-feira, 27 de julho de 2020 20:55
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>>
Cc: David G. Johnston <david.g.johnston@gmail.com<mailto:david.g.johnston@gmail.com>>; pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
What were the results of pgstattuple? Curious what the live tuple_* and dead_tuple* values vs the free space were. Also note that if you just ran it on the table, that is the results for JUST the table. If there are indexes as well, you'd have to check those separately and they may be even worse (index bloat is often worse than table). The pg_bloat_check script I linked, when run against a single table, will run against the table and all b-tree indexes (it cannot check gin/gist).
Unfortunately, since this is a wraparound vacuum that is starting, disabling autovacuum will not work. Wraparound vacuums run even when autovac is disabled in order to try and keep you from getting into transaction exhaustion territory which means you must take the database offline to fix things and avoid data corruption. As I said autovac will attempt to run again, even if you start yours. And both will attempt to get the lock, but only one will actually succeed in getting it. Check the wait_event_type and wait_event column in pg_stat_activity to determine which one actually obtained the lock and you want to make sure yours is the one that did. Can check the documentation for the meanings of those columns https://www.postgresql.org/docs/9.6/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW<https://nam10.saf...;
In all honesty, though, if this table is as bloated as it seems, you may want to just try and schedule an outage to run a VACUUM FULL on this table and get it fully cleaned up. 2.3TB of empty space in just the table is quite a lot and each index may be just as bad. Until regular vacuums can efficiently run on this table it's only going to get progressively worse and just keep taking longer and longer. I would also highly suggest getting on the latest version of 9.6 before doing so that you're sure all bug fixes are in place.
An outage now may be costly, but it will be nowhere near as costly as autovacuum never being able to run on this table and reaching transaction exhaustion.
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com<https://nam10.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcrunchyda...;
On Mon, Jul 27, 2020 at 6:49 PM Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>> wrote:
Thank you for the explanation Keith.
Pgstattuple runs for 8 hours approximately before printing its result.
and pg_freespace runs for "only" 16 minutes so we presumed really that pgstattuple scans the relation and brings more accurate information.
The strange fact is that pg_freespace result is changing from day to day, even without vacuum or analyze finishing.
At day one we start monitoring it was saying it has 108GB and kept changing each day.
And the worse fact is the different values for free space: pgstattupple with 2.3TB free VS pg_freespace with 96GB.
But i just killed autovacuum and started a manual vacuum verbose.
and 20 seconds later a autovacuum started.
it seems they are both running together.
Both sessions gets row exclusive locks.
I think it is better disabling autovacuum, do you agree?
________________________________
De: Keith Fiske <keith.fiske@crunchydata.com<mailto:keith.fiske@crunchydata.com>>
Enviado: segunda-feira, 27 de julho de 2020 19:21
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>>
Cc: David G. Johnston <david.g.johnston@gmail.com<mailto:david.g.johnston@gmail.com>>; pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
On Mon, Jul 27, 2020 at 6:19 PM Keith Fiske <keith.fiske@crunchydata.com<mailto:keith.fiske@crunchydata.com>> wrote:
As the documentation for pg_freespacemap says, it's values are not exact and they may not be up to date because you are viewing the statistics that were gathered during the last analyze, not the actual freespace map
Sorry, just wanted to clarify this. You are looking at the actual freespace map. It's that the values actually stored in it are not exact and based off of statistics gathering
https://www.postgresql.org/docs/9.6/pgfreespacemap.html<https://nam10.safelinks.protection.outloo...;
When you run pgstattuple, it is actually scanning the target object. Also note that scanning a table does not also scan its indexes, those must be done individually. So the free space reported by pgstattuple should be the actual free space, given as both byte size and % of the whole object.
pg_freespacemap is used by the planner and several other internal parts of PG, so checking it must be quick. pgstattuple can take a while to run on larger objects, but it will give you 100% accurate results as of the time it ran. This is why I prefer it when checking for bloat since many of the queries you see out there also try and base their results on stats. While the query runs quick, I've often seen them be wildly wrong.
Also, I would not recommend partitioning simply to improve vacuuming. Especially if extensive tuning hasn't been tried first. Most times you can get per-table tuning working well enough to get autovacuum running properly. Especially on 9.6 and even more-so on PG11, where autovacuum has itself been improved.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
@ 2020-07-28 03:39 ` Ron <ronljohnsonjr@gmail.com>
2020-07-28 03:54 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
1 sibling, 1 reply; 22+ messages in thread
From: Ron @ 2020-07-28 03:39 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 7/27/20 5:19 PM, Keith Fiske wrote:
[snip]
> Also, I would not recommend partitioning simply to improve vacuuming.
> Especially if extensive tuning hasn't been tried first. Most times you can
> get per-table tuning working well enough to get autovacuum running
> properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
> itself been improved.
SIMPLY to improve vacuum performance? No. But there are reasons that
partitioning was "invented", and minimizing the work needed to be done on
the whole of a Very Large and Rapidly Growing table is one of them.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 03:39 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
@ 2020-07-28 03:54 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 05:15 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-07-28 03:54 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you Ron.
Yes there is a column id in which value is provided by a sequence.
But it was not clear to me exactly how to partition?
The idea is to cover a limited range of "id" so there will be less lines to vacuum on each operation?
Because yet we will need eventually to vacuum all others.
All lines even old ones might be updated by our application.
________________________________
De: Ron <ronljohnsonjr@gmail.com>
Enviado: terça-feira, 28 de julho de 2020 00:39
Para: 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 7/27/20 5:19 PM, Keith Fiske wrote:
[snip]
> Also, I would not recommend partitioning simply to improve vacuuming.
> Especially if extensive tuning hasn't been tried first. Most times you can
> get per-table tuning working well enough to get autovacuum running
> properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
> itself been improved.
SIMPLY to improve vacuum performance? No. But there are reasons that
partitioning was "invented", and minimizing the work needed to be done on
the whole of a Very Large and Rapidly Growing table is one of them.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 03:39 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
2020-07-28 03:54 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
@ 2020-07-28 05:15 ` Ron <ronljohnsonjr@gmail.com>
2020-07-28 10:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 1 reply; 22+ messages in thread
From: Ron @ 2020-07-28 05:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> All lines even old ones might be updated by our application.
Ah. Even so, there are benefits to maintaining "lots of smaller chunks"
instead of One Giant Table: "pg_dump --jobs=X" will run faster, for example,
when there are more small-to-mid-sized tables instead of A Few Giant
Tables. Ditto "reindexdb --jobs=X".
Given that even "old" records get updated, I'd look into some other key that
you can "naturally" partition on.
On 7/27/20 10:54 PM, Sidney Aloisio Ferreira Pryor wrote:
> Thank you Ron.
> Yes there is a column id in which value is provided by a sequence.
>
> But it was not clear to me exactly how to partition?
> The idea is to cover a limited range of "id" so there will be less lines
> to vacuum on each operation?
>
> Because yet we will need eventually to vacuum all others.
> All lines even old ones might be updated by our application.
>
> ----------------------------------------------------------------------------
> *De:* Ron <ronljohnsonjr@gmail.com>
> *Enviado:* terça-feira, 28 de julho de 2020 00:39
> *Para:* 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 7/27/20 5:19 PM, Keith Fiske wrote:
> [snip]
> > Also, I would not recommend partitioning simply to improve vacuuming.
> > Especially if extensive tuning hasn't been tried first. Most times you can
> > get per-table tuning working well enough to get autovacuum running
> > properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
> > itself been improved.
>
> SIMPLY to improve vacuum performance? No. But there are reasons that
> partitioning was "invented", and minimizing the work needed to be done on
> the whole of a Very Large and Rapidly Growing table is one of them.
>
> --
> Angular momentum makes the world go 'round.
>
>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 03:39 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
2020-07-28 03:54 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 05:15 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
@ 2020-07-28 10:17 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 13:55 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 1 reply; 22+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-07-28 10:17 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you Ron
I would appreciate if you could share a good documentation or review link about partitioning tables on postgres.
Enviado do meu iPhone
Em 28 de jul de 2020, à(s) 02:16, Ron <ronljohnsonjr@gmail.com> escreveu:
> All lines even old ones might be updated by our application.
Ah. Even so, there are benefits to maintaining "lots of smaller chunks" instead of One Giant Table: "pg_dump --jobs=X" will run faster, for example, when there are more small-to-mid-sized tables instead of A Few Giant Tables. Ditto "reindexdb --jobs=X".
Given that even "old" records get updated, I'd look into some other key that you can "naturally" partition on.
On 7/27/20 10:54 PM, Sidney Aloisio Ferreira Pryor wrote:
Thank you Ron.
Yes there is a column id in which value is provided by a sequence.
But it was not clear to me exactly how to partition?
The idea is to cover a limited range of "id" so there will be less lines to vacuum on each operation?
Because yet we will need eventually to vacuum all others.
All lines even old ones might be updated by our application.
________________________________
De: Ron <ronljohnsonjr@gmail.com><mailto:ronljohnsonjr@gmail.com>
Enviado: terça-feira, 28 de julho de 2020 00:39
Para: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org><mailto:pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
On 7/27/20 5:19 PM, Keith Fiske wrote:
[snip]
> Also, I would not recommend partitioning simply to improve vacuuming.
> Especially if extensive tuning hasn't been tried first. Most times you can
> get per-table tuning working well enough to get autovacuum running
> properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
> itself been improved.
SIMPLY to improve vacuum performance? No. But there are reasons that
partitioning was "invented", and minimizing the work needed to be done on
the whole of a Very Large and Rapidly Growing table is one of them.
--
Angular momentum makes the world go 'round.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 03:39 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
2020-07-28 03:54 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 05:15 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
2020-07-28 10:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
@ 2020-07-28 13:55 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 18:20 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 1 reply; 22+ messages in thread
From: Keith Fiske @ 2020-07-28 13:55 UTC (permalink / raw)
To: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Again, I would caution against going with partitioning until you can first
try and tune the table itself. I have quite a bit of experience with it as
I'm the author of pg_partman so you can see I have nothing against
partitioning itself, but I'm only for using it when it's necessary. If you
decide to go that route, it would be a good tool to help you automate much
of the maintenance process of partitioning either by time or integer.
https://github.com/pgpartman/pg_partman
I say this because, while it could possibly help with making vacuum more
efficient, there are many caveats to partitioning. Since you're on 9.6,
there is no native partitioning, so you'd have to do trigger based
partitioning which has a HUGE impact on write performance (at least 2x
slower if not worse). Even with native partitioning in 10+, there is a
performance penalty during tuple routing and you would want to test that
impact. Especially if you are doing updates which would move data across
child partitions (updates that move data prior to PG10 are very, very
complicated and not supported by pg_partman). Also, if you have any unique
keys to maintain, they are not enforced across the partition set, and only
supported in native if they are also part of the partition key itself.
You're also just adding to the general complexity of your database overall.
I would in general say the primary reason you would want to consider
partitioning in PG is if you're trying to expire old data out. Dropping a
table is much more efficient than running large/many DELETE statements due
to the very issues you're encountering now: bloat. But it sounds like you
are not expiring data, so you also have to take into account how many
partitions you will have long term. It wasn't until PG12 that having over
just even a few hundred partitions could have major performance impact
overall. So, before even considering partitioning, you would want to get
onto the latest major release.
Partitioning can help with vacuum and general maintenance, but that's
really only when the actual live data in a single table starts getting
very, very large. And in that case you may want to first consider your
hardware resources before going down the road of partitioning since that
may be the better benefit long term. You've got nearly 2.3TB of space to
clean up in this table, so, again, you want to solve that problem first. If
you had that much free space before this vacuum finished, you likely have
even more now after it finishes. And just note that a regular vacuum is
likely not going to clean up this space. It may clean up some, but vacuum
only returns space to the system in very specific circumstances (the
tail-end pages are empty). You could use something like pg_repack or
pg_squeeze to do this without a long outage, but with bloat this size, you
may run into issues with how long those tools would need to run. Especially
if the table is still in use. So, again, I would recommend an outage to do
a VACUUM FULL which will lock and rewrite the table. I'd be curious to see
what your total size of the entire table before and after is. The following
function will give that total size (table + indexes)
select pg_total_relation_size('public.mytable');
So, while partitioning could possibly be the answer long term, I would not
recommend it until you've cleaned up the existing bloat on this table and
its indexes and try to tune autovacuum run more efficiently. If that works,
you've saved yourself a great deal of complexity. And again, you'd
definitely want to get on the latest major version of PG where partitioning
has improved tremendously, so if a major version upgrade isn't in the works
for a while, I wouldn't even consider it.
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
On Tue, Jul 28, 2020 at 6:18 AM Sidney Aloisio Ferreira Pryor <
sidney@tjrj.jus.br> wrote:
> Thank you Ron
> I would appreciate if you could share a good documentation or review link
> about partitioning tables on postgres.
>
> Enviado do meu iPhone
>
> Em 28 de jul de 2020, à(s) 02:16, Ron <ronljohnsonjr@gmail.com> escreveu:
>
>
> > All lines even old ones might be updated by our application.
>
> Ah. Even so, there are benefits to maintaining "lots of smaller chunks"
> instead of One Giant Table: "pg_dump --jobs=X" will run faster, for
> example, when there are more small-to-mid-sized tables instead of A Few
> Giant Tables. Ditto "reindexdb --jobs=X".
>
> Given that even "old" records get updated, I'd look into some other key
> that you can "naturally" partition on.
>
> On 7/27/20 10:54 PM, Sidney Aloisio Ferreira Pryor wrote:
>
> Thank you Ron.
> Yes there is a column id in which value is provided by a sequence.
>
> But it was not clear to me exactly how to partition?
> The idea is to cover a limited range of "id" so there will be less lines
> to vacuum on each operation?
>
> Because yet we will need eventually to vacuum all others.
> All lines even old ones might be updated by our application.
>
> ------------------------------
> *De:* Ron <ronljohnsonjr@gmail.com> <ronljohnsonjr@gmail.com>
> *Enviado:* terça-feira, 28 de julho de 2020 00:39
> *Para:* pgsql-admin@lists.postgresql.org
> <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 7/27/20 5:19 PM, Keith Fiske wrote:
> [snip]
> > Also, I would not recommend partitioning simply to improve vacuuming.
> > Especially if extensive tuning hasn't been tried first. Most times you
> can
> > get per-table tuning working well enough to get autovacuum running
> > properly. Especially on 9.6 and even more-so on PG11, where autovacuum
> has
> > itself been improved.
>
> SIMPLY to improve vacuum performance? No. But there are reasons that
> partitioning was "invented", and minimizing the work needed to be done on
> the whole of a Very Large and Rapidly Growing table is one of them.
>
> --
> Angular momentum makes the world go 'round.
>
>
>
> --
> Angular momentum makes the world go 'round.
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 03:39 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
2020-07-28 03:54 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 05:15 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
2020-07-28 10:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 13:55 ` Re: How can i be certain autovacuum is causing reuse if table still grows Keith Fiske <keith.fiske@crunchydata.com>
@ 2020-07-28 18:20 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 0 replies; 22+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-07-28 18:20 UTC (permalink / raw)
To: Keith Fiske <keith.fiske@crunchydata.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you very much for this complete explanation and opinion, Keith.
The first goal of publishing this mail on the list was to answer how to measure precisely free space: pgstattuple is the correct one.
The second goal is running so far: manual vacuum to accomplish reuse of that large free area. (i will post a pgstattuple of the table when this vacuum ends)
And the third goal is to free and downsize this huge allocation of space: schedule an outage to run vacuum full.
Again i am truly grateful for your precious help.
And i will post here Pg_total_relation_size before and after running vacuum full when we schedule that outage.
Best Regards,
Sidney Pryor
________________________________
De: Keith Fiske <keith.fiske@crunchydata.com>
Enviado: terça-feira, 28 de julho de 2020 10:55
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: 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
Again, I would caution against going with partitioning until you can first try and tune the table itself. I have quite a bit of experience with it as I'm the author of pg_partman so you can see I have nothing against partitioning itself, but I'm only for using it when it's necessary. If you decide to go that route, it would be a good tool to help you automate much of the maintenance process of partitioning either by time or integer.
https://github.com/pgpartman/pg_partman<https://nam10.safelinks.protection.outlook.com/?url=https...;
I say this because, while it could possibly help with making vacuum more efficient, there are many caveats to partitioning. Since you're on 9.6, there is no native partitioning, so you'd have to do trigger based partitioning which has a HUGE impact on write performance (at least 2x slower if not worse). Even with native partitioning in 10+, there is a performance penalty during tuple routing and you would want to test that impact. Especially if you are doing updates which would move data across child partitions (updates that move data prior to PG10 are very, very complicated and not supported by pg_partman). Also, if you have any unique keys to maintain, they are not enforced across the partition set, and only supported in native if they are also part of the partition key itself. You're also just adding to the general complexity of your database overall.
I would in general say the primary reason you would want to consider partitioning in PG is if you're trying to expire old data out. Dropping a table is much more efficient than running large/many DELETE statements due to the very issues you're encountering now: bloat. But it sounds like you are not expiring data, so you also have to take into account how many partitions you will have long term. It wasn't until PG12 that having over just even a few hundred partitions could have major performance impact overall. So, before even considering partitioning, you would want to get onto the latest major release.
Partitioning can help with vacuum and general maintenance, but that's really only when the actual live data in a single table starts getting very, very large. And in that case you may want to first consider your hardware resources before going down the road of partitioning since that may be the better benefit long term. You've got nearly 2.3TB of space to clean up in this table, so, again, you want to solve that problem first. If you had that much free space before this vacuum finished, you likely have even more now after it finishes. And just note that a regular vacuum is likely not going to clean up this space. It may clean up some, but vacuum only returns space to the system in very specific circumstances (the tail-end pages are empty). You could use something like pg_repack or pg_squeeze to do this without a long outage, but with bloat this size, you may run into issues with how long those tools would need to run. Especially if the table is still in use. So, again, I would recommend an outage to do a VACUUM FULL which will lock and rewrite the table. I'd be curious to see what your total size of the entire table before and after is. The following function will give that total size (table + indexes)
select pg_total_relation_size('public.mytable');
So, while partitioning could possibly be the answer long term, I would not recommend it until you've cleaned up the existing bloat on this table and its indexes and try to tune autovacuum run more efficiently. If that works, you've saved yourself a great deal of complexity. And again, you'd definitely want to get on the latest major version of PG where partitioning has improved tremendously, so if a major version upgrade isn't in the works for a while, I wouldn't even consider it.
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com<https://nam10.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcrunchyda...;
On Tue, Jul 28, 2020 at 6:18 AM Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>> wrote:
Thank you Ron
I would appreciate if you could share a good documentation or review link about partitioning tables on postgres.
Enviado do meu iPhone
Em 28 de jul de 2020, à(s) 02:16, Ron <ronljohnsonjr@gmail.com<mailto:ronljohnsonjr@gmail.com>> escreveu:
> All lines even old ones might be updated by our application.
Ah. Even so, there are benefits to maintaining "lots of smaller chunks" instead of One Giant Table: "pg_dump --jobs=X" will run faster, for example, when there are more small-to-mid-sized tables instead of A Few Giant Tables. Ditto "reindexdb --jobs=X".
Given that even "old" records get updated, I'd look into some other key that you can "naturally" partition on.
On 7/27/20 10:54 PM, Sidney Aloisio Ferreira Pryor wrote:
Thank you Ron.
Yes there is a column id in which value is provided by a sequence.
But it was not clear to me exactly how to partition?
The idea is to cover a limited range of "id" so there will be less lines to vacuum on each operation?
Because yet we will need eventually to vacuum all others.
All lines even old ones might be updated by our application.
________________________________
De: Ron <ronljohnsonjr@gmail.com><mailto:ronljohnsonjr@gmail.com>
Enviado: terça-feira, 28 de julho de 2020 00:39
Para: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org><mailto:pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
On 7/27/20 5:19 PM, Keith Fiske wrote:
[snip]
> Also, I would not recommend partitioning simply to improve vacuuming.
> Especially if extensive tuning hasn't been tried first. Most times you can
> get per-table tuning working well enough to get autovacuum running
> properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
> itself been improved.
SIMPLY to improve vacuum performance? No. But there are reasons that
partitioning was "invented", and minimizing the work needed to be done on
the whole of a Very Large and Rapidly Growing table is one of them.
--
Angular momentum makes the world go 'round.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
@ 2020-07-27 21:27 ` Ron <ronljohnsonjr@gmail.com>
2020-07-27 21:38 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
1 sibling, 1 reply; 22+ messages in thread
From: Ron @ 2020-07-27 21:27 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Another suggestion is to partition the table by a chronological key, so that
only a fraction of the table needs to be vacuumed.
On 7/27/20 3:36 PM, Sidney Aloisio Ferreira Pryor wrote:
> Thank you, David.
> So can i assume that autovacuum only release dead tuples when it ends its
> execution?
>
> Autovacuum is not finishing and is running for days.
> We did not find a parameter to limit autovacuum executing time.
> Do you have any recomendation?
>
> Thank you.
> Sidney.
> ----------------------------------------------------------------------------
> *De:* David G. Johnston <david.g.johnston@gmail.com>
> *Enviado:* segunda-feira, 27 de julho de 2020 17:17
> *Para:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
> *Cc:* 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 Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor
> <sidney@tjrj.jus.br <mailto:sidney@tjrj.jus.br>> wrote:
>
> Hi, we have a 9.6.5 postgres
>
> Autovacuum is set with default parameters and is running on this table
> for days without naping or stopping.
>
> 3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM
> ANALYZE public.flip_pagina_edicao (to prevent wraparound)
> (1 rows)
>
>
> Its probably best avoid drawing conclusions here until the anti-wraparound
> vacuum has completed and you’ve updated to the latest minor version of
> PostgreSQL.
>
> David J.
>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 22+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 21:27 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
@ 2020-07-27 21:38 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 03:32 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-07-27 21:38 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you Ron but this table has no date column.
It has one line for each page of each scanned document.
________________________________
De: Ron <ronljohnsonjr@gmail.com>
Enviado: segunda-feira, 27 de julho de 2020 18:27
Para: 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
Another suggestion is to partition the table by a chronological key, so that only a fraction of the table needs to be vacuumed.
On 7/27/20 3:36 PM, Sidney Aloisio Ferreira Pryor wrote:
Thank you, David.
So can i assume that autovacuum only release dead tuples when it ends its execution?
Autovacuum is not finishing and is running for days.
We did not find a parameter to limit autovacuum executing time.
Do you have any recomendation?
Thank you.
Sidney.
________________________________
De: David G. Johnston <david.g.johnston@gmail.com><mailto:david.g.johnston@gmail.com>
Enviado: segunda-feira, 27 de julho de 2020 17:17
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br><mailto:sidney@tjrj.jus.br>
Cc: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org><mailto:pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
On Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>> wrote:
Hi, we have a 9.6.5 postgres
Autovacuum is set with default parameters and is running on this table for days without naping or stopping.
3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE public.flip_pagina_edicao (to prevent wraparound)
(1 rows)
Its probably best avoid drawing conclusions here until the anti-wraparound vacuum has completed and you’ve updated to the latest minor version of PostgreSQL.
David J.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 21:27 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
2020-07-27 21:38 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
@ 2020-07-28 03:32 ` Ron <ronljohnsonjr@gmail.com>
2020-07-28 06:44 ` Re: How can i be certain autovacuum is causing reuse if table still grows Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 22+ messages in thread
From: Ron @ 2020-07-28 03:32 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Any key that "increases linearly over time" is a chronological key;
sequences are a perfect example.
On 7/27/20 4:38 PM, Sidney Aloisio Ferreira Pryor wrote:
>
> Thank you Ron but this table has no date column.
>
> It has one line for each page of each scanned document.
>
> ----------------------------------------------------------------------------
> *De:* Ron <ronljohnsonjr@gmail.com>
> *Enviado:* segunda-feira, 27 de julho de 2020 18:27
> *Para:* 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
> Another suggestion is to partition the table by a chronological key, so
> that only a fraction of the table needs to be vacuumed.
>
> On 7/27/20 3:36 PM, Sidney Aloisio Ferreira Pryor wrote:
>> Thank you, David.
>> So can i assume that autovacuum only release dead tuples when it ends its
>> execution?
>>
>> Autovacuum is not finishing and is running for days.
>> We did not find a parameter to limit autovacuum executing time.
>> Do you have any recomendation?
>>
>> Thank you.
>> Sidney.
>> ----------------------------------------------------------------------------
>> *De:* David G. Johnston <david.g.johnston@gmail.com>
>> <mailto:david.g.johnston@gmail.com>
>> *Enviado:* segunda-feira, 27 de julho de 2020 17:17
>> *Para:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
>> <mailto:sidney@tjrj.jus.br>
>> *Cc:* pgsql-admin@lists.postgresql.org
>> <mailto:pgsql-admin@lists.postgresql.org>
>> <pgsql-admin@lists.postgresql.org> <mailto:pgsql-admin@lists.postgresql.org>
>> *Assunto:* Re: How can i be certain autovacuum is causing reuse if table
>> still grows
>> On Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor
>> <sidney@tjrj.jus.br <mailto:sidney@tjrj.jus.br>> wrote:
>>
>> Hi, we have a 9.6.5 postgres
>>
>> Autovacuum is set with default parameters and is running on this
>> table for days without naping or stopping.
>>
>> 3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM
>> ANALYZE public.flip_pagina_edicao (to prevent wraparound)
>> (1 rows)
>>
>>
>> Its probably best avoid drawing conclusions here until the
>> anti-wraparound vacuum has completed and you’ve updated to the latest
>> minor version of PostgreSQL.
>>
>> David J.
>>
>
> --
> Angular momentum makes the world go 'round.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` Re: How can i be certain autovacuum is causing reuse if table still grows David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 21:27 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
2020-07-27 21:38 ` RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 03:32 ` Re: How can i be certain autovacuum is causing reuse if table still grows Ron <ronljohnsonjr@gmail.com>
@ 2020-07-28 06:44 ` Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 22+ messages in thread
From: Holger Jakobs @ 2020-07-28 06:44 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
So you can use your SERIAL (or GENERATED AS IDENTITY) column for this.
If you don't have this, you might add a DATE DEFAULT CURRENT_DATE
column, even if it's of no use except being the partition key.
Am 28.07.20 um 05:32 schrieb Ron:
> Any key that "increases linearly over time" is a chronological key;
> sequences are a perfect example.
>
> On 7/27/20 4:38 PM, Sidney Aloisio Ferreira Pryor wrote:
>>
>> Thank you Ron but this table has no date column.
>>
>> It has one line for each page of each scanned document.
>>
>> ------------------------------------------------------------------------
>> *De:* Ron <ronljohnsonjr@gmail.com>
>> *Enviado:* segunda-feira, 27 de julho de 2020 18:27
>> *Para:* 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
>>
>> Another suggestion is to partition the table by a chronological key,
>> so that only a fraction of the table needs to be vacuumed.
>>
>> On 7/27/20 3:36 PM, Sidney Aloisio Ferreira Pryor wrote:
>>> Thank you, David.
>>> So can i assume that autovacuum only release dead tuples when it
>>> ends its execution?
>>>
>>> Autovacuum is not finishing and is running for days.
>>> We did not find a parameter to limit autovacuum executing time.
>>> Do you have any recomendation?
>>>
>>> Thank you.
>>> Sidney.
>>> ------------------------------------------------------------------------
>>> *De:* David G. Johnston <david.g.johnston@gmail.com>
>>> <mailto:david.g.johnston@gmail.com>
>>> *Enviado:* segunda-feira, 27 de julho de 2020 17:17
>>> *Para:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
>>> <mailto:sidney@tjrj.jus.br>
>>> *Cc:* pgsql-admin@lists.postgresql.org
>>> <mailto:pgsql-admin@lists.postgresql.org>
>>> <pgsql-admin@lists.postgresql.org>
>>> <mailto:pgsql-admin@lists.postgresql.org>
>>> *Assunto:* Re: How can i be certain autovacuum is causing reuse if
>>> table still grows
>>>
>>> On Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor
>>> <sidney@tjrj.jus.br <mailto:sidney@tjrj.jus.br>> wrote:
>>>
>>> Hi, we have a 9.6.5 postgres
>>>
>>>
>>>
>>> Autovacuum is set with default parameters and is running on this
>>> table for days without naping or stopping.
>>>
>>> 3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM
>>> ANALYZE public.flip_pagina_edicao (to prevent wraparound)
>>> (1 rows)
>>>
>>>
>>> Its probably best avoid drawing conclusions here until the
>>> anti-wraparound vacuum has completed and you’ve updated to the
>>> latest minor version of PostgreSQL.
>>>
>>> David J.
>>>
>>
>> --
>> Angular momentum makes the world go 'round.
>
> --
> Angular momentum makes the world go 'round.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2020-07-28 18:20 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-27 20:08 How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:17 ` David G. Johnston <david.g.johnston@gmail.com>
2020-07-27 20:36 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 20:59 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 21:35 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 22:19 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:21 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-07-27 22:48 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 23:55 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 00:17 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 00:49 ` Keith <keith@keithf4.com>
2020-07-28 01:42 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 03:39 ` Ron <ronljohnsonjr@gmail.com>
2020-07-28 03:54 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 05:15 ` Ron <ronljohnsonjr@gmail.com>
2020-07-28 10:17 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 13:55 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-07-28 18:20 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-27 21:27 ` Ron <ronljohnsonjr@gmail.com>
2020-07-27 21:38 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-07-28 03:32 ` Ron <ronljohnsonjr@gmail.com>
2020-07-28 06:44 ` Holger Jakobs <holger@jakobs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox