pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRE: How can i be certain autovacuum is causing reuse if table still grows
12+ messages / 5 participants
[nested] [flat]
* RE: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-03 21:36 Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 1 reply; 12+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-08-03 21:36 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>
Hi Keith, how are you?
Hope you are very fine.
As i promissed we ran vacuum full on that table on a test environment similar to production and it took 11 hours to complete.
It reduced the original table segment from 2.3TB to 100GB as below (note that it is still growing...).
So we are now proposing to our Direction a maintenance window to vacuum full production.
Original:
table_schema | table_name | total_size | data_size | external_size
--------------+---------------------+------------+-----------+---------------
public | flip_pagina_edicao | 4578 GB | 2880 GB | 1699 GB
Copy of Orginal (after vacuum full):
table_schema | table_name | total_size | data_size | external_size
--------------+---------------------+------------+-----------+---------------
public | flip_pagina_edicao | 180 GB | 100 GB | 80 GB
In meantime if you could please help us understand some curious facts below about vacuum verbose (not full).
I am running on production another vacuum verbose (not full) because that first one stucked on the same indexes for 3 days.
But now is on a 3rd sequential scan of all indexes of that table followed by a scan on the table itself.
It is not finishing even completing scans.
1) Why it is scanning again and not finishing vacuum?
2) I noticed that on all scans it is taking 12 hours only on an index on a bpchar(1) column (character(1) DEFAULT 'N'::bpchar).
All other indexes and even the table it self are running too much faster, can you guess a reason for this?
CREATE INDEX ocrindex
ON public.flip_pagina_edicao
USING btree
(ocr COLLATE pg_catalog."default");
3) And is repeatly scanning 11.184.520 or 521 or 528 row versions.
Why does it not end and scans again the same amount of row versions?
flip=# vacuum verbose flip_pagina_edicao;
INFO: vacuuming "public.flip_pagina_edicao"
INFO: scanned index "id_caderno" to remove 11184520 row versions
DETAIL: CPU 155.34s/540.40u sec elapsed 1101.49 sec
INFO: scanned index "ordem_index" to remove 11184520 row versions
DETAIL: CPU 253.35s/870.77u sec elapsed 1962.36 sec
INFO: scanned index "primary00024" to remove 11184520 row versions
DETAIL: CPU 134.10s/478.79u sec elapsed 922.02 sec
INFO: scanned index "ordem" to remove 11184520 row versions
DETAIL: CPU 265.16s/932.90u sec elapsed 1878.20 sec
INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184520 row versions
DETAIL: CPU 149.51s/523.42u sec elapsed 949.68 sec
INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184520 row versions
DETAIL: CPU 460.19s/1171.75u sec elapsed 2696.89 sec
INFO: scanned index "nomepdfindex" to remove 11184520 row versions
DETAIL: CPU 598.88s/1286.84u sec elapsed 3543.26 sec
INFO: scanned index "ocrindex" to remove 11184520 row versions
DETAIL: CPU 25657.30s/18907.82u sec elapsed 46652.49 sec
INFO: scanned index "idr_documento_index" to remove 11184520 row versions
DETAIL: CPU 321.96s/968.75u sec elapsed 2004.91 sec
INFO: "flip_pagina_edicao": removed 11184520 row versions in 3762529 pages
DETAIL: CPU 165.65s/95.06u sec elapsed 647.49 sec
INFO: scanned index "id_caderno" to remove 11184521 row versions
DETAIL: CPU 175.89s/461.26u sec elapsed 1098.74 sec
INFO: scanned index "ordem_index" to remove 11184521 row versions
DETAIL: CPU 301.57s/887.26u sec elapsed 2052.44 sec
INFO: scanned index "primary00024" to remove 11184521 row versions
DETAIL: CPU 150.12s/461.92u sec elapsed 947.74 sec
INFO: scanned index "ordem" to remove 11184521 row versions
DETAIL: CPU 286.29s/896.03u sec elapsed 1954.21 sec
INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184521 row versions
DETAIL: CPU 140.75s/423.38u sec elapsed 901.04 sec
INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184521 row versions
DETAIL: CPU 455.24s/1043.27u sec elapsed 2551.54 sec
INFO: scanned index "nomepdfindex" to remove 11184521 row versions
DETAIL: CPU 644.78s/1163.80u sec elapsed 3469.67 sec
INFO: scanned index "ocrindex" to remove 11184521 row versions
DETAIL: CPU 25951.95s/18712.36u sec elapsed 49880.37 sec
INFO: scanned index "idr_documento_index" to remove 11184521 row versions
DETAIL: CPU 274.05s/710.37u sec elapsed 1799.98 sec
INFO: "flip_pagina_edicao": removed 11184521 row versions in 2358457 pages
DETAIL: CPU 68.83s/36.81u sec elapsed 353.40 sec
INFO: scanned index "id_caderno" to remove 11184528 row versions
DETAIL: CPU 156.47s/372.75u sec elapsed 1022.31 sec
INFO: scanned index "ordem_index" to remove 11184528 row versions
DETAIL: CPU 257.13s/669.86u sec elapsed 2057.05 sec
INFO: scanned index "primary00024" to remove 11184528 row versions
DETAIL: CPU 116.29s/319.86u sec elapsed 842.55 sec
INFO: scanned index "ordem" to remove 11184528 row versions
DETAIL: CPU 232.78s/562.22u sec elapsed 2676.35 sec
INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184528 row versions
DETAIL: CPU 91.21s/241.00u sec elapsed 875.27 sec
INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184528 row versions
DETAIL: CPU 285.71s/585.63u sec elapsed 2593.08 sec
INFO: scanned index "nomepdfindex" to remove 11184528 row versions
DETAIL: CPU 413.55s/692.77u sec elapsed 3843.78 sec
Thank you.
Sidney Pryor.
________________________________
De: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Enviado: terça-feira, 28 de julho de 2020 15:20
Para: Keith Fiske <keith.fiske@crunchydata.com>
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
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] 12+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-04 09:03 Guillaume Lelarge <guillaume@lelarge.info>
parent: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 1 reply; 12+ messages in thread
From: Guillaume Lelarge @ 2020-08-04 09:03 UTC (permalink / raw)
To: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; +Cc: Keith Fiske <keith.fiske@crunchydata.com>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Le lun. 3 août 2020 à 23:36, Sidney Aloisio Ferreira Pryor <
sidney@tjrj.jus.br> a écrit :
> Hi Keith, how are you?
> Hope you are very fine.
>
> As i promissed we ran vacuum full on that table on a test environment
> similar to production and it took 11 hours to complete.
> It reduced the original table segment from 2.3TB to 100GB as below (note
> that it is still growing...).
> So we are now proposing to our Direction a maintenance window to vacuum
> full production.
>
> Original:
> table_schema | table_name | total_size | data_size |
> external_size
>
> --------------+---------------------+------------+-----------+---------------
> public | flip_pagina_edicao | 4578 GB | 2880 GB | 1699 GB
>
> Copy of Orginal (after vacuum full):
> table_schema | table_name | total_size | data_size |
> external_size
>
> --------------+---------------------+------------+-----------+---------------
> public | flip_pagina_edicao | 180 GB | 100 GB | 80 GB
>
>
> In meantime if you could please help us understand some curious facts
> below about vacuum verbose (not full).
> I am running on production another vacuum verbose (not full) because that
> first one stucked on the same indexes for 3 days.
> But now is on a 3rd sequential scan of all indexes of that table followed
> by a scan on the table itself.
> It is not finishing even completing scans.
>
> 1) Why it is scanning again and not finishing vacuum?
>
>
Your maintenance_work_mem parameter value is probably too low.
2) I noticed that on all scans it is taking 12 hours only on an index on a
> bpchar(1) column (character(1) DEFAULT 'N'::bpchar).
> All other indexes and even the table it self are running too much faster,
> can you guess a reason for this?
>
> CREATE INDEX ocrindex
> ON public.flip_pagina_edicao
> USING btree
> (ocr COLLATE pg_catalog."default");
>
>
Not used, so not in cache? or much bigger than the other ones?
3) And is repeatly scanning 11.184.520 or 521 or 528 row versions.
> Why does it not end and scans again the same amount of row versions?
>
>
Well, VACUUM is a multi-steps operation. It first scans the table and puts
every tid that needs to be deleted in memory. Once done, il will scan
indexes. But if there's more TID than memory can hold, it will have to do
multiple scans of the table and the indexes. It seems clear in your case
that, when VACUUM finds 11184520 TID, memory is full. By the way, memory is
limited by a parameter named maintenance_work_mem. So what is its current
value?
flip=# vacuum verbose flip_pagina_edicao;
> INFO: vacuuming "public.flip_pagina_edicao"
> INFO: scanned index "id_caderno" to remove 11184520 row versions
> DETAIL: CPU 155.34s/540.40u sec elapsed 1101.49 sec
> INFO: scanned index "ordem_index" to remove 11184520 row versions
> DETAIL: CPU 253.35s/870.77u sec elapsed 1962.36 sec
> INFO: scanned index "primary00024" to remove 11184520 row versions
> DETAIL: CPU 134.10s/478.79u sec elapsed 922.02 sec
> INFO: scanned index "ordem" to remove 11184520 row versions
> DETAIL: CPU 265.16s/932.90u sec elapsed 1878.20 sec
> INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184520 row
> versions
> DETAIL: CPU 149.51s/523.42u sec elapsed 949.68 sec
> INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184520 row
> versions
> DETAIL: CPU 460.19s/1171.75u sec elapsed 2696.89 sec
> INFO: scanned index "nomepdfindex" to remove 11184520 row versions
> DETAIL: CPU 598.88s/1286.84u sec elapsed 3543.26 sec
> INFO: scanned index "ocrindex" to remove 11184520 row versions
> DETAIL: CPU 25657.30s/18907.82u sec elapsed 46652.49 sec
> INFO: scanned index "idr_documento_index" to remove 11184520 row versions
> DETAIL: CPU 321.96s/968.75u sec elapsed 2004.91 sec
> INFO: "flip_pagina_edicao": removed 11184520 row versions in 3762529 pages
> DETAIL: CPU 165.65s/95.06u sec elapsed 647.49 sec
> INFO: scanned index "id_caderno" to remove 11184521 row versions
> DETAIL: CPU 175.89s/461.26u sec elapsed 1098.74 sec
> INFO: scanned index "ordem_index" to remove 11184521 row versions
> DETAIL: CPU 301.57s/887.26u sec elapsed 2052.44 sec
> INFO: scanned index "primary00024" to remove 11184521 row versions
> DETAIL: CPU 150.12s/461.92u sec elapsed 947.74 sec
> INFO: scanned index "ordem" to remove 11184521 row versions
> DETAIL: CPU 286.29s/896.03u sec elapsed 1954.21 sec
> INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184521 row
> versions
> DETAIL: CPU 140.75s/423.38u sec elapsed 901.04 sec
> INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184521 row
> versions
> DETAIL: CPU 455.24s/1043.27u sec elapsed 2551.54 sec
> INFO: scanned index "nomepdfindex" to remove 11184521 row versions
> DETAIL: CPU 644.78s/1163.80u sec elapsed 3469.67 sec
> INFO: scanned index "ocrindex" to remove 11184521 row versions
> DETAIL: CPU 25951.95s/18712.36u sec elapsed 49880.37 sec
> INFO: scanned index "idr_documento_index" to remove 11184521 row versions
> DETAIL: CPU 274.05s/710.37u sec elapsed 1799.98 sec
> INFO: "flip_pagina_edicao": removed 11184521 row versions in 2358457 pages
> DETAIL: CPU 68.83s/36.81u sec elapsed 353.40 sec
> INFO: scanned index "id_caderno" to remove 11184528 row versions
> DETAIL: CPU 156.47s/372.75u sec elapsed 1022.31 sec
> INFO: scanned index "ordem_index" to remove 11184528 row versions
> DETAIL: CPU 257.13s/669.86u sec elapsed 2057.05 sec
> INFO: scanned index "primary00024" to remove 11184528 row versions
> DETAIL: CPU 116.29s/319.86u sec elapsed 842.55 sec
> INFO: scanned index "ordem" to remove 11184528 row versions
> DETAIL: CPU 232.78s/562.22u sec elapsed 2676.35 sec
> INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184528 row
> versions
> DETAIL: CPU 91.21s/241.00u sec elapsed 875.27 sec
> INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184528 row
> versions
> DETAIL: CPU 285.71s/585.63u sec elapsed 2593.08 sec
> INFO: scanned index "nomepdfindex" to remove 11184528 row versions
> DETAIL: CPU 413.55s/692.77u sec elapsed 3843.78 sec
>
> Thank you.
> Sidney Pryor.
>
>
> ------------------------------
> *De:* Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
> *Enviado:* terça-feira, 28 de julho de 2020 15:20
> *Para:* Keith Fiske <keith.fiske@crunchydata.com>
> *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
>
> 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%3A%2F%2Fgithub.com%2Fpgpartman%2Fpg_partma...;
>
> 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%2Fcrunchydata.com%2F&data=02%7C01...;
>
>
> 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.
>
>
>
>
--
Guillaume.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-04 13:49 Keith Fiske <keith.fiske@crunchydata.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 12+ messages in thread
From: Keith Fiske @ 2020-08-04 13:49 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tue, Aug 4, 2020 at 5:03 AM Guillaume Lelarge <guillaume@lelarge.info>
wrote:
> Le lun. 3 août 2020 à 23:36, Sidney Aloisio Ferreira Pryor <
> sidney@tjrj.jus.br> a écrit :
>
>> Hi Keith, how are you?
>> Hope you are very fine.
>>
>> As i promissed we ran vacuum full on that table on a test environment
>> similar to production and it took 11 hours to complete.
>> It reduced the original table segment from 2.3TB to 100GB as below (note
>> that it is still growing...).
>> So we are now proposing to our Direction a maintenance window to vacuum
>> full production.
>>
>> Original:
>> table_schema | table_name | total_size | data_size |
>> external_size
>>
>> --------------+---------------------+------------+-----------+---------------
>> public | flip_pagina_edicao | 4578 GB | 2880 GB | 1699 GB
>>
>> Copy of Orginal (after vacuum full):
>> table_schema | table_name | total_size | data_size |
>> external_size
>>
>> --------------+---------------------+------------+-----------+---------------
>> public | flip_pagina_edicao | 180 GB | 100 GB | 80 GB
>>
>>
>> In meantime if you could please help us understand some curious facts
>> below about vacuum verbose (not full).
>> I am running on production another vacuum verbose (not full) because that
>> first one stucked on the same indexes for 3 days.
>> But now is on a 3rd sequential scan of all indexes of that table followed
>> by a scan on the table itself.
>> It is not finishing even completing scans.
>>
>> 1) Why it is scanning again and not finishing vacuum?
>>
>>
> Your maintenance_work_mem parameter value is probably too low.
>
> 2) I noticed that on all scans it is taking 12 hours only on an index on
>> a bpchar(1) column (character(1) DEFAULT 'N'::bpchar).
>> All other indexes and even the table it self are running too much faster,
>> can you guess a reason for this?
>>
>> CREATE INDEX ocrindex
>> ON public.flip_pagina_edicao
>> USING btree
>> (ocr COLLATE pg_catalog."default");
>>
>>
> Not used, so not in cache? or much bigger than the other ones?
>
> 3) And is repeatly scanning 11.184.520 or 521 or 528 row versions.
>> Why does it not end and scans again the same amount of row versions?
>>
>>
> Well, VACUUM is a multi-steps operation. It first scans the table and puts
> every tid that needs to be deleted in memory. Once done, il will scan
> indexes. But if there's more TID than memory can hold, it will have to do
> multiple scans of the table and the indexes. It seems clear in your case
> that, when VACUUM finds 11184520 TID, memory is full. By the way, memory is
> limited by a parameter named maintenance_work_mem. So what is its current
> value?
>
> flip=# vacuum verbose flip_pagina_edicao;
>> INFO: vacuuming "public.flip_pagina_edicao"
>> INFO: scanned index "id_caderno" to remove 11184520 row versions
>> DETAIL: CPU 155.34s/540.40u sec elapsed 1101.49 sec
>> INFO: scanned index "ordem_index" to remove 11184520 row versions
>> DETAIL: CPU 253.35s/870.77u sec elapsed 1962.36 sec
>> INFO: scanned index "primary00024" to remove 11184520 row versions
>> DETAIL: CPU 134.10s/478.79u sec elapsed 922.02 sec
>> INFO: scanned index "ordem" to remove 11184520 row versions
>> DETAIL: CPU 265.16s/932.90u sec elapsed 1878.20 sec
>> INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184520 row
>> versions
>> DETAIL: CPU 149.51s/523.42u sec elapsed 949.68 sec
>> INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184520 row
>> versions
>> DETAIL: CPU 460.19s/1171.75u sec elapsed 2696.89 sec
>> INFO: scanned index "nomepdfindex" to remove 11184520 row versions
>> DETAIL: CPU 598.88s/1286.84u sec elapsed 3543.26 sec
>> INFO: scanned index "ocrindex" to remove 11184520 row versions
>> DETAIL: CPU 25657.30s/18907.82u sec elapsed 46652.49 sec
>> INFO: scanned index "idr_documento_index" to remove 11184520 row versions
>> DETAIL: CPU 321.96s/968.75u sec elapsed 2004.91 sec
>> INFO: "flip_pagina_edicao": removed 11184520 row versions in 3762529
>> pages
>> DETAIL: CPU 165.65s/95.06u sec elapsed 647.49 sec
>> INFO: scanned index "id_caderno" to remove 11184521 row versions
>> DETAIL: CPU 175.89s/461.26u sec elapsed 1098.74 sec
>> INFO: scanned index "ordem_index" to remove 11184521 row versions
>> DETAIL: CPU 301.57s/887.26u sec elapsed 2052.44 sec
>> INFO: scanned index "primary00024" to remove 11184521 row versions
>> DETAIL: CPU 150.12s/461.92u sec elapsed 947.74 sec
>> INFO: scanned index "ordem" to remove 11184521 row versions
>> DETAIL: CPU 286.29s/896.03u sec elapsed 1954.21 sec
>> INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184521 row
>> versions
>> DETAIL: CPU 140.75s/423.38u sec elapsed 901.04 sec
>> INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184521 row
>> versions
>> DETAIL: CPU 455.24s/1043.27u sec elapsed 2551.54 sec
>> INFO: scanned index "nomepdfindex" to remove 11184521 row versions
>> DETAIL: CPU 644.78s/1163.80u sec elapsed 3469.67 sec
>> INFO: scanned index "ocrindex" to remove 11184521 row versions
>> DETAIL: CPU 25951.95s/18712.36u sec elapsed 49880.37 sec
>> INFO: scanned index "idr_documento_index" to remove 11184521 row versions
>> DETAIL: CPU 274.05s/710.37u sec elapsed 1799.98 sec
>> INFO: "flip_pagina_edicao": removed 11184521 row versions in 2358457
>> pages
>> DETAIL: CPU 68.83s/36.81u sec elapsed 353.40 sec
>> INFO: scanned index "id_caderno" to remove 11184528 row versions
>> DETAIL: CPU 156.47s/372.75u sec elapsed 1022.31 sec
>> INFO: scanned index "ordem_index" to remove 11184528 row versions
>> DETAIL: CPU 257.13s/669.86u sec elapsed 2057.05 sec
>> INFO: scanned index "primary00024" to remove 11184528 row versions
>> DETAIL: CPU 116.29s/319.86u sec elapsed 842.55 sec
>> INFO: scanned index "ordem" to remove 11184528 row versions
>> DETAIL: CPU 232.78s/562.22u sec elapsed 2676.35 sec
>> INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184528 row
>> versions
>> DETAIL: CPU 91.21s/241.00u sec elapsed 875.27 sec
>> INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184528 row
>> versions
>> DETAIL: CPU 285.71s/585.63u sec elapsed 2593.08 sec
>> INFO: scanned index "nomepdfindex" to remove 11184528 row versions
>> DETAIL: CPU 413.55s/692.77u sec elapsed 3843.78 sec
>>
>> Thank you.
>> Sidney Pryor.
>>
>>
>> --
> Guillaume.
>
Guillaume answered most of your questions (thanks!).
Since this shrunk the size of your table so dramatically, the best thing
going forward after fixing things would be to tune this table specifically
until you find the point where vacuum is running often enough to clean up
enough space for future updates/deletes to just use that space instead of
allocating more. For reference again
https://www.keithf4.com/per-table-autovacuum-tuning/
There will likely always be a certain level of bloat, but that's not a bad
thing as long as it doesn't continually keep growing over time. So I'd
recommend running something like pg_bloat_check on this table maybe once a
week, or even once a day, during off-peak hours to keep an eye on it. It
may take a while to narrow down the sweet spot of how and when to get
autovacuum to run and keep the free space in balance with future writes.
And if you have particularly heavy periods of writes, it may require
manually scheduling vacuums around those times as well.
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-04 15:46 Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
parent: Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 1 reply; 12+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-08-04 15:46 UTC (permalink / raw)
To: Keith Fiske <keith.fiske@crunchydata.com>; Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you very much Guillaume and Keith for your straight and quick answers.
I was looking this database size and tup_update history.
It never shrank even with autovacuum enabled (growing 100GB a month).
The number of updates was always this high.
But on last 2 months its size started to grow a lot (growing 50GB a day).
We will schedule a vacuum full to stop adding disks.
But i am truly afraid postgres is not suitable for this application.
We are talkiing with dev team for the last three weeks.
And so far no answer about optimizing the number of updates.
It is true we have never executed a vacuum full on this database.
But its size even growing was not so much to worry about.
And if postgres was never able to follow the number of updates and reuse efficiently.
I am afraid an autovacuum tuning may not be sufficente to reuse after we finish vacuum full and downsize the database from 4tb to 200gb.
________________________________
De: Keith Fiske <keith.fiske@crunchydata.com>
Enviado: terça-feira, 4 de agosto de 2020 10:49
Para: Guillaume Lelarge <guillaume@lelarge.info>
Cc: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
On Tue, Aug 4, 2020 at 5:03 AM Guillaume Lelarge <guillaume@lelarge.info<mailto:guillaume@lelarge.info>> wrote:
Le lun. 3 août 2020 à 23:36, Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>> a écrit :
Hi Keith, how are you?
Hope you are very fine.
As i promissed we ran vacuum full on that table on a test environment similar to production and it took 11 hours to complete.
It reduced the original table segment from 2.3TB to 100GB as below (note that it is still growing...).
So we are now proposing to our Direction a maintenance window to vacuum full production.
Original:
table_schema | table_name | total_size | data_size | external_size
--------------+---------------------+------------+-----------+---------------
public | flip_pagina_edicao | 4578 GB | 2880 GB | 1699 GB
Copy of Orginal (after vacuum full):
table_schema | table_name | total_size | data_size | external_size
--------------+---------------------+------------+-----------+---------------
public | flip_pagina_edicao | 180 GB | 100 GB | 80 GB
In meantime if you could please help us understand some curious facts below about vacuum verbose (not full).
I am running on production another vacuum verbose (not full) because that first one stucked on the same indexes for 3 days.
But now is on a 3rd sequential scan of all indexes of that table followed by a scan on the table itself.
It is not finishing even completing scans.
1) Why it is scanning again and not finishing vacuum?
Your maintenance_work_mem parameter value is probably too low.
2) I noticed that on all scans it is taking 12 hours only on an index on a bpchar(1) column (character(1) DEFAULT 'N'::bpchar).
All other indexes and even the table it self are running too much faster, can you guess a reason for this?
CREATE INDEX ocrindex
ON public.flip_pagina_edicao
USING btree
(ocr COLLATE pg_catalog."default");
Not used, so not in cache? or much bigger than the other ones?
3) And is repeatly scanning 11.184.520 or 521 or 528 row versions.
Why does it not end and scans again the same amount of row versions?
Well, VACUUM is a multi-steps operation. It first scans the table and puts every tid that needs to be deleted in memory. Once done, il will scan indexes. But if there's more TID than memory can hold, it will have to do multiple scans of the table and the indexes. It seems clear in your case that, when VACUUM finds 11184520 TID, memory is full. By the way, memory is limited by a parameter named maintenance_work_mem. So what is its current value?
flip=# vacuum verbose flip_pagina_edicao;
INFO: vacuuming "public.flip_pagina_edicao"
INFO: scanned index "id_caderno" to remove 11184520 row versions
DETAIL: CPU 155.34s/540.40u sec elapsed 1101.49 sec
INFO: scanned index "ordem_index" to remove 11184520 row versions
DETAIL: CPU 253.35s/870.77u sec elapsed 1962.36 sec
INFO: scanned index "primary00024" to remove 11184520 row versions
DETAIL: CPU 134.10s/478.79u sec elapsed 922.02 sec
INFO: scanned index "ordem" to remove 11184520 row versions
DETAIL: CPU 265.16s/932.90u sec elapsed 1878.20 sec
INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184520 row versions
DETAIL: CPU 149.51s/523.42u sec elapsed 949.68 sec
INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184520 row versions
DETAIL: CPU 460.19s/1171.75u sec elapsed 2696.89 sec
INFO: scanned index "nomepdfindex" to remove 11184520 row versions
DETAIL: CPU 598.88s/1286.84u sec elapsed 3543.26 sec
INFO: scanned index "ocrindex" to remove 11184520 row versions
DETAIL: CPU 25657.30s/18907.82u sec elapsed 46652.49 sec
INFO: scanned index "idr_documento_index" to remove 11184520 row versions
DETAIL: CPU 321.96s/968.75u sec elapsed 2004.91 sec
INFO: "flip_pagina_edicao": removed 11184520 row versions in 3762529 pages
DETAIL: CPU 165.65s/95.06u sec elapsed 647.49 sec
INFO: scanned index "id_caderno" to remove 11184521 row versions
DETAIL: CPU 175.89s/461.26u sec elapsed 1098.74 sec
INFO: scanned index "ordem_index" to remove 11184521 row versions
DETAIL: CPU 301.57s/887.26u sec elapsed 2052.44 sec
INFO: scanned index "primary00024" to remove 11184521 row versions
DETAIL: CPU 150.12s/461.92u sec elapsed 947.74 sec
INFO: scanned index "ordem" to remove 11184521 row versions
DETAIL: CPU 286.29s/896.03u sec elapsed 1954.21 sec
INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184521 row versions
DETAIL: CPU 140.75s/423.38u sec elapsed 901.04 sec
INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184521 row versions
DETAIL: CPU 455.24s/1043.27u sec elapsed 2551.54 sec
INFO: scanned index "nomepdfindex" to remove 11184521 row versions
DETAIL: CPU 644.78s/1163.80u sec elapsed 3469.67 sec
INFO: scanned index "ocrindex" to remove 11184521 row versions
DETAIL: CPU 25951.95s/18712.36u sec elapsed 49880.37 sec
INFO: scanned index "idr_documento_index" to remove 11184521 row versions
DETAIL: CPU 274.05s/710.37u sec elapsed 1799.98 sec
INFO: "flip_pagina_edicao": removed 11184521 row versions in 2358457 pages
DETAIL: CPU 68.83s/36.81u sec elapsed 353.40 sec
INFO: scanned index "id_caderno" to remove 11184528 row versions
DETAIL: CPU 156.47s/372.75u sec elapsed 1022.31 sec
INFO: scanned index "ordem_index" to remove 11184528 row versions
DETAIL: CPU 257.13s/669.86u sec elapsed 2057.05 sec
INFO: scanned index "primary00024" to remove 11184528 row versions
DETAIL: CPU 116.29s/319.86u sec elapsed 842.55 sec
INFO: scanned index "ordem" to remove 11184528 row versions
DETAIL: CPU 232.78s/562.22u sec elapsed 2676.35 sec
INFO: scanned index "flip_pagina_edicao_pkey" to remove 11184528 row versions
DETAIL: CPU 91.21s/241.00u sec elapsed 875.27 sec
INFO: scanned index "flip_flippagedic_idcaderno" to remove 11184528 row versions
DETAIL: CPU 285.71s/585.63u sec elapsed 2593.08 sec
INFO: scanned index "nomepdfindex" to remove 11184528 row versions
DETAIL: CPU 413.55s/692.77u sec elapsed 3843.78 sec
Thank you.
Sidney Pryor.
--
Guillaume.
Guillaume answered most of your questions (thanks!).
Since this shrunk the size of your table so dramatically, the best thing going forward after fixing things would be to tune this table specifically until you find the point where vacuum is running often enough to clean up enough space for future updates/deletes to just use that space instead of allocating more. For reference again
https://www.keithf4.com/per-table-autovacuum-tuning/<https://nam10.safelinks.protection.outlook.c...;
There will likely always be a certain level of bloat, but that's not a bad thing as long as it doesn't continually keep growing over time. So I'd recommend running something like pg_bloat_check on this table maybe once a week, or even once a day, during off-peak hours to keep an eye on it. It may take a while to narrow down the sweet spot of how and when to get autovacuum to run and keep the free space in balance with future writes. And if you have particularly heavy periods of writes, it may require manually scheduling vacuums around those times as well.
--
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] 12+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-04 16:34 Keith Fiske <keith.fiske@crunchydata.com>
parent: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 1 reply; 12+ messages in thread
From: Keith Fiske @ 2020-08-04 16:34 UTC (permalink / raw)
To: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tue, Aug 4, 2020 at 11:46 AM Sidney Aloisio Ferreira Pryor <
sidney@tjrj.jus.br> wrote:
>
> Thank you very much Guillaume and Keith for your straight and quick
> answers.
>
> I was looking this database size and tup_update history.
> It never shrank even with autovacuum enabled (growing 100GB a month).
> The number of updates was always this high.
> But on last 2 months its size started to grow a lot (growing 50GB a day).
>
> We will schedule a vacuum full to stop adding disks.
> But i am truly afraid postgres is not suitable for this application.
> We are talkiing with dev team for the last three weeks.
> And so far no answer about optimizing the number of updates.
>
> It is true we have never executed a vacuum full on this database.
> But its size even growing was not so much to worry about.
> And if postgres was never able to follow the number of updates and reuse
> efficiently.
> I am afraid an autovacuum tuning may not be sufficente to reuse after we
> finish vacuum full and downsize the database from 4tb to 200gb.
>
>
>
>
You stated in your opening email that you just had the default autovacuum
settings in place. The defaults are often nowhere near sufficient for most
production use cases. But everyone's production use case is different, so
common settings that work with the least issues are the default. If
autovacuum is not running frequently enough, especially on larger tables
with frequent updates, you will see exactly what happened here over time:
autovacuum will not keep up with the write rate and take longer and longer
to run over time.
If autovacuum, or manual vacuums, are scheduled often enough to keep up
with marking old rows as reusable space, then any new writes will use that
empty space vs allocating additional pages and constantly growing your
table's size. This includes indexes as well. If you're adding NEW rows, of
course it will continue to grow, but that is not autovacuum's problem. The
trick is, for now, figuring out how often autovac will need to run to keep
up with your write rate. You may need to adjust your cost settings as well
to ensure autovac runs a little more aggressively, at least on this table.
The blog I just shared goes over how to figure out how to ensure autovacuum
is run at least once per day based on a consistent avg row change per day.
If you have occasions where higher writes than normal are occurring,
autovac may run more often in that case or you can even schedule a manual
vacuum.
Thankfully you are on at least PG 9.6 as well, where vacuum has been made
to run much more efficiently. Please just make sure you are on the latest
minor release. Also, more recent major versions of PostgreSQL have improved
upon this even more, so I would highly suggest starting to plan for a major
version upgrade. PG9.6 is scheduled to be EOL next year as well, so you
want to be looking into that not just for vacuum efficiency.
If you get vacuum tuned properly, on a table that isn't as massively
bloated as that one was, I think you will be fine.
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-04 16:50 Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
parent: Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 1 reply; 12+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-08-04 16:50 UTC (permalink / raw)
To: Keith Fiske <keith.fiske@crunchydata.com>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you so much Keith for sharing your opinion again.
I will have a discussion this afternoon with my superior.
And i will propose vacuum full as soon as possible, will follow your blog's tuning information and promote upgrade PG version.
________________________________
De: Keith Fiske <keith.fiske@crunchydata.com>
Enviado: terça-feira, 4 de agosto de 2020 13:34
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
On Tue, Aug 4, 2020 at 11:46 AM Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>> wrote:
Thank you very much Guillaume and Keith for your straight and quick answers.
I was looking this database size and tup_update history.
It never shrank even with autovacuum enabled (growing 100GB a month).
The number of updates was always this high.
But on last 2 months its size started to grow a lot (growing 50GB a day).
We will schedule a vacuum full to stop adding disks.
But i am truly afraid postgres is not suitable for this application.
We are talkiing with dev team for the last three weeks.
And so far no answer about optimizing the number of updates.
It is true we have never executed a vacuum full on this database.
But its size even growing was not so much to worry about.
And if postgres was never able to follow the number of updates and reuse efficiently.
I am afraid an autovacuum tuning may not be sufficente to reuse after we finish vacuum full and downsize the database from 4tb to 200gb.
You stated in your opening email that you just had the default autovacuum settings in place. The defaults are often nowhere near sufficient for most production use cases. But everyone's production use case is different, so common settings that work with the least issues are the default. If autovacuum is not running frequently enough, especially on larger tables with frequent updates, you will see exactly what happened here over time: autovacuum will not keep up with the write rate and take longer and longer to run over time.
If autovacuum, or manual vacuums, are scheduled often enough to keep up with marking old rows as reusable space, then any new writes will use that empty space vs allocating additional pages and constantly growing your table's size. This includes indexes as well. If you're adding NEW rows, of course it will continue to grow, but that is not autovacuum's problem. The trick is, for now, figuring out how often autovac will need to run to keep up with your write rate. You may need to adjust your cost settings as well to ensure autovac runs a little more aggressively, at least on this table. The blog I just shared goes over how to figure out how to ensure autovacuum is run at least once per day based on a consistent avg row change per day. If you have occasions where higher writes than normal are occurring, autovac may run more often in that case or you can even schedule a manual vacuum.
Thankfully you are on at least PG 9.6 as well, where vacuum has been made to run much more efficiently. Please just make sure you are on the latest minor release. Also, more recent major versions of PostgreSQL have improved upon this even more, so I would highly suggest starting to plan for a major version upgrade. PG9.6 is scheduled to be EOL next year as well, so you want to be looking into that not just for vacuum efficiency.
If you get vacuum tuned properly, on a table that isn't as massively bloated as that one was, I think you will be fine.
--
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] 12+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-04 18:31 Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
parent: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 1 reply; 12+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-08-04 18:31 UTC (permalink / raw)
To: Keith Fiske <keith.fiske@crunchydata.com>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
I read your blog and something is not right beside tuning PG.
As i saw on zabbix history of monitoring tup_update was always high.
Now a days i count an average of 30million updates a day on pg_stat_all_tables.
Daily i put values on excel and state the difference from the last value.
And historically as zabbix registered number of updates was approximately 70% of this 30M i have these days.
So even with default values i calculated that i always had 20% + 50 that autovacuum waits to run.
Beside this, i saw on pg_stat_tables and the vacuum and autovacuum count for this table is 0.
That is not possible because i have a registered on this very view of a finished autovacuum on May 10th.
data n_tup_ins n_tup_upd n_tup_del n_tup_live n_tup_dead last_autovacuum last_autoanalyze
30/06/2020 13:30 57.308.133 6.744.431.635 1.614.325 165.531.847 2.458.943.354 2020-05-10 19:31:11.792893-03 2020-05-10 19:33:55.497008-03
30/06/2020 19:00 57.386.558 6.748.230.176 1.618.162 165.606.435 2.462.729.202 2020-05-10 19:31:11.792893-03 2020-05-10 19:33:55.497008-03
01/07/2020 13:30 57.736.340 6.783.682.703 1.636.765 165.937.614 2.483.584.486 2020-05-10 19:31:11.792893-03 2020-05-10 19:33:55.497008-03
01/07/2020 19:30 57.837.003 6.837.618.849 1.650.215 166.024.827 2.489.316.701 2020-05-10 19:31:11.792893-03 2020-05-10 19:33:55.497008-03
02/07/2020 13:30 58.143.439 6.865.274.910 1.660.968 166.320.510 2.515.717.862 2020-05-10 19:31:11.792893-03 2020-05-10 19:33:55.497008-03
02/07/2020 19:30 58.226.810 6.882.586.543 1.663.168 166.401.681 2.527.673.618 2020-05-10 19:31:11.792893-03 2020-05-10 19:33:55.497008-03
03/07/2020 13:30 58.544.013 6.908.650.471 1.687.055 166.694.933 2.551.229.982 2020-05-10 19:31:11.792893-03 2020-05-10 19:33:55.497008-03
03/07/2020 19:30 58.640.756 6.931.648.963 1.693.146 166.785.585 2.564.810.596 2020-05-10 19:31:11.792893-03 2020-05-10 19:33:55.497008-03
And when this database was restarted on July 6th the above last_autovacuum register was gone.
I have no history of vacuum or autovacuum count.
Neither altering fillfactor seemed to me a solution on my case. Please tell me if i am wrong.
I am not seeing something to improve (auto)vacuum execution performance itself (please consider that i have already acomplished a successfull vacuum full) beside this below:
1) resize maintenance_work_mem - eliminate memory limitation;
2) raise autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit to a greater value than default - eliminate io limitation;
________________________________
De: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Enviado: terça-feira, 4 de agosto de 2020 13:50
Para: Keith Fiske <keith.fiske@crunchydata.com>
Cc: Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Assunto: RE: How can i be certain autovacuum is causing reuse if table still grows
Thank you so much Keith for sharing your opinion again.
I will have a discussion this afternoon with my superior.
And i will propose vacuum full as soon as possible, will follow your blog's tuning information and promote upgrade PG version.
________________________________
De: Keith Fiske <keith.fiske@crunchydata.com>
Enviado: terça-feira, 4 de agosto de 2020 13:34
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
On Tue, Aug 4, 2020 at 11:46 AM Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br<mailto:sidney@tjrj.jus.br>> wrote:
Thank you very much Guillaume and Keith for your straight and quick answers.
I was looking this database size and tup_update history.
It never shrank even with autovacuum enabled (growing 100GB a month).
The number of updates was always this high.
But on last 2 months its size started to grow a lot (growing 50GB a day).
We will schedule a vacuum full to stop adding disks.
But i am truly afraid postgres is not suitable for this application.
We are talkiing with dev team for the last three weeks.
And so far no answer about optimizing the number of updates.
It is true we have never executed a vacuum full on this database.
But its size even growing was not so much to worry about.
And if postgres was never able to follow the number of updates and reuse efficiently.
I am afraid an autovacuum tuning may not be sufficente to reuse after we finish vacuum full and downsize the database from 4tb to 200gb.
You stated in your opening email that you just had the default autovacuum settings in place. The defaults are often nowhere near sufficient for most production use cases. But everyone's production use case is different, so common settings that work with the least issues are the default. If autovacuum is not running frequently enough, especially on larger tables with frequent updates, you will see exactly what happened here over time: autovacuum will not keep up with the write rate and take longer and longer to run over time.
If autovacuum, or manual vacuums, are scheduled often enough to keep up with marking old rows as reusable space, then any new writes will use that empty space vs allocating additional pages and constantly growing your table's size. This includes indexes as well. If you're adding NEW rows, of course it will continue to grow, but that is not autovacuum's problem. The trick is, for now, figuring out how often autovac will need to run to keep up with your write rate. You may need to adjust your cost settings as well to ensure autovac runs a little more aggressively, at least on this table. The blog I just shared goes over how to figure out how to ensure autovacuum is run at least once per day based on a consistent avg row change per day. If you have occasions where higher writes than normal are occurring, autovac may run more often in that case or you can even schedule a manual vacuum.
Thankfully you are on at least PG 9.6 as well, where vacuum has been made to run much more efficiently. Please just make sure you are on the latest minor release. Also, more recent major versions of PostgreSQL have improved upon this even more, so I would highly suggest starting to plan for a major version upgrade. PG9.6 is scheduled to be EOL next year as well, so you want to be looking into that not just for vacuum efficiency.
If you get vacuum tuned properly, on a table that isn't as massively bloated as that one was, I think you will be fine.
--
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] 12+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-04 19:53 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
0 siblings, 2 replies; 12+ messages in thread
From: Scott Ribe @ 2020-08-04 19:53 UTC (permalink / raw)
To: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; +Cc: Keith Fiske <keith.fiske@crunchydata.com>; Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On Aug 4, 2020, at 12:31 PM, Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br> wrote:
>
> Beside this, i saw on pg_stat_tables and the vacuum and autovacuum count for this table is 0.
Those values are not persistent; they are only since the last start of PG.
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-04 20:12 Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 12+ messages in thread
From: Sidney Aloisio Ferreira Pryor @ 2020-08-04 20:12 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Keith Fiske <keith.fiske@crunchydata.com>; Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you Scott.
Sorry for that. Did not attempted it is a view.
________________________________
De: Scott Ribe <scott_ribe@elevated-dev.com>
Enviado: terça-feira, 4 de agosto de 2020 16:53
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: Keith Fiske <keith.fiske@crunchydata.com>; Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
> On Aug 4, 2020, at 12:31 PM, Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br> wrote:
>
> Beside this, i saw on pg_stat_tables and the vacuum and autovacuum count for this table is 0.
Those values are not persistent; they are only since the last start of PG.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-04 22:25 Keith Fiske <keith.fiske@crunchydata.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 12+ messages in thread
From: Keith Fiske @ 2020-08-04 22:25 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tue, Aug 4, 2020 at 3:54 PM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> > On Aug 4, 2020, at 12:31 PM, Sidney Aloisio Ferreira Pryor <
> sidney@tjrj.jus.br> wrote:
> >
> > Beside this, i saw on pg_stat_tables and the vacuum and autovacuum count
> for this table is 0.
>
> Those values are not persistent; they are only since the last start of PG.
>
>
This is not correct. Statistics are persistent through restarts. They are
only reset during specific cases such as a crash or a major version
upgrade. They can also be reset at any time manually.
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-05 13:29 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 1 reply; 12+ messages in thread
From: MichaelDBA @ 2020-08-05 13:29 UTC (permalink / raw)
To: Keith Fiske <keith.fiske@crunchydata.com>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Keith,
I think statistics are also reset and not persistent through restarts
when the pg_ctl option "-m immediate" is used when shutting down PG
since it forces recovery mode when starting PG back up. By the way, for
PostgreSQL Aurora, statistics are reset automatically every time aurora
forces PG to restart or reboot or the user selects that option
manually. I reported this as a bug with Aurora a few months ago. They
said it was not a bug, but a feature that the might implement in the
future. YUK.
Regards,
Michael Vitale
Keith Fiske wrote on 8/4/2020 6:25 PM:
>
>
> On Tue, Aug 4, 2020 at 3:54 PM Scott Ribe <scott_ribe@elevated-dev.com
> <mailto:scott_ribe@elevated-dev.com>> wrote:
>
> > On Aug 4, 2020, at 12:31 PM, Sidney Aloisio Ferreira Pryor
> <sidney@tjrj.jus.br <mailto:sidney@tjrj.jus.br>> wrote:
> >
> > Beside this, i saw on pg_stat_tables and the vacuum and
> autovacuum count for this table is 0.
>
> Those values are not persistent; they are only since the last
> start of PG.
>
>
> This is not correct. Statistics are persistent through restarts. They
> are only reset during specific cases such as a crash or a major
> version upgrade. They can also be reset at any time manually.
>
> --
> Keith Fiske
> Senior Database Engineer
> Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How can i be certain autovacuum is causing reuse if table still grows
@ 2020-08-05 14:25 Keith Fiske <keith.fiske@crunchydata.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 0 replies; 12+ messages in thread
From: Keith Fiske @ 2020-08-05 14:25 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>; Guillaume Lelarge <guillaume@lelarge.info>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Wed, Aug 5, 2020 at 9:29 AM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
> Hi Keith,
>
> I think statistics are also reset and not persistent through restarts when
> the pg_ctl option "-m immediate" is used when shutting down PG since it
> forces recovery mode when starting PG back up. By the way, for PostgreSQL
> Aurora, statistics are reset automatically every time aurora forces PG to
> restart or reboot or the user selects that option manually. I reported
> this as a bug with Aurora a few months ago. They said it was not a bug,
> but a feature that the might implement in the future. YUK.
>
> Regards,
> Michael Vitale
>
>
An "immediate" shutdown is often considered a crash to Postgres. So yes, it
likely will reset the statistics.
>
> Keith Fiske wrote on 8/4/2020 6:25 PM:
>
>
>
> On Tue, Aug 4, 2020 at 3:54 PM Scott Ribe <scott_ribe@elevated-dev.com>
> wrote:
>
>> > On Aug 4, 2020, at 12:31 PM, Sidney Aloisio Ferreira Pryor <
>> sidney@tjrj.jus.br> wrote:
>> >
>> > Beside this, i saw on pg_stat_tables and the vacuum and autovacuum
>> count for this table is 0.
>>
>> Those values are not persistent; they are only since the last start of PG.
>>
>>
> This is not correct. Statistics are persistent through restarts. They are
> only reset during specific cases such as a crash or a major version
> upgrade. They can also be reset at any time manually.
>
> --
> 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] 12+ messages in thread
end of thread, other threads:[~2020-08-05 14:25 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-03 21:36 RE: How can i be certain autovacuum is causing reuse if table still grows Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-08-04 09:03 ` Guillaume Lelarge <guillaume@lelarge.info>
2020-08-04 13:49 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-08-04 15:46 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-08-04 16:34 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-08-04 16:50 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-08-04 18:31 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-08-04 19:53 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-08-04 20:12 ` Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
2020-08-04 22:25 ` Keith Fiske <keith.fiske@crunchydata.com>
2020-08-05 13:29 ` MichaelDBA <MichaelDBA@sqlexec.com>
2020-08-05 14:25 ` Keith Fiske <keith.fiske@crunchydata.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